Open sim1984 opened 1 year ago
If LISTAGG
is added for standard compatibility, should legacy LIST
also support ordering? If yes, should it follow the standard syntax (so that LIST
and LISTAGG
just become synonyms) or, being non-standard, may use its own syntax for ordering, e.g. LIST(FIELD1 ORDER BY FIELD2)
?
I think it's enough to make it just a synonym. But if they make their own version to simplify, then do not object.
The standard LISTAGG
also has the separator string being mandatory while our LIST
implementation has it optional, with a comma implied if omitted. And the standard LISTAGG
defines the separator as a literal only, while we allow arbitrary value expressions. Would it be OK to preserve these differences for the LISTAGG
too?
Limiting the delimiter to only literal values is not very correct in my opinion. But sorting for LISTAGG must be made mandatory. In principle, the separator can be made mandatory (there are doubts here). Does anyone have other opinions?
Sorting may be mandatory in LISTAGG
(as per SQL standard), but it must be optional (or missing completely) in LIST
due to backward compatibility.
Oracle supports LISTAGG
in mostly standard way (without <listagg overflow clause>
), but it has optional separator. So maybe we should preserve this syntax.
Agree. In this case, we have full compatibility with the current LIST function, with the exception of the mandatory sorting. For LIST, sorting can be left optional for backward compatibility reasons. Please note in the documentation that in new projects it is recommended to use the standard LISTAGG.
Need to add support for ordered-set and hypothetical-set aggregate functions, as well as implementations of the instances defined in SQL:2008 (percentile_cont(), percentile_disc(), rank(), dense_rank(), percent_rank(), cume_dist()). It is good to added mode() though it is not in the spec. In addition, you can gracefully solve the problem of element unordering in the LIST aggregate function (in the SQL standard LISTAGG).
Ordered-Set Aggregate Functions
It is also possible to introduce the LISTAGG function, and make LIST an alias for compatibility with the standard.
Hypothetical-Set Aggregate Functions
You can read more about how this is done in PostgreSQL here. https://www.postgresql.org/docs/current/functions-aggregate.html https://paquier.xyz/postgresql-2/postgres-9-4-feature-highlight-within-group/