[
https://issues.apache.org/jira/browse/CALCITE-4687?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17377856#comment-17377856
]
Lukas Eder commented on CALCITE-4687:
-------------------------------------
The SQL standard mandates {{ARRAY_AGG(x ORDER BY y)}}. The syntaxes are not
interchangeable, and I think that supporting both as equivalents might be doing
you a disservice in the long run, though I understand it looks tempting in the
short run, for greater compatibility with other dialects.
In theory, there could be an aggregate function that offers both types of
groupings (and a third one when you add {{OVER(ORDER BY ...)}}), though I don't
think I've seen that being done yet. Outside of PostgreSQL, it's rare to be
able to order a window function twice. Standard SQL {{LISTAGG}} is ill defined.
It should also use the {{LISTAGG(x ORDER BY y)}} syntax, similar to MySQL's
{{GROUP_CONCAT()}}, not the {{WITHIN GROUP}} syntax.
Here's some background info:
https://twitter.com/pg_xocolatl/status/1237427035468967938
> Add LIMIT to WITHIN GROUP clause of aggregate functions
> -------------------------------------------------------
>
> Key: CALCITE-4687
> URL: https://issues.apache.org/jira/browse/CALCITE-4687
> Project: Calcite
> Issue Type: Bug
> Reporter: Julian Hyde
> Priority: Major
>
> Add LIMIT to WITHIN GROUP clause of aggregate functions. LIMIT is not in the
> SQL standard, but it is useful, and is not hard to implement.
> The following query computes the 3 highest paid employees in each department:
> {code:java}
> SELECT deptno, ARRAY_AGG(sal) WITHIN GROUP (ORDER BY sal DESC LIMIT 3)
> FROM Emp
> GROUP BY deptno {code}
> It can be implemented efficiently (using a merge sort that discards all but
> the top 3 rows in each key, at each pass).
> Note that BigQuery does not support the {{WITHIN GROUP}} clause, but in the
> {{ARRAY_AGG}} function, the {{ORDER BY}} and {{LIMIT}} sub-clauses appear
> within the parentheses, like this: {{ARRAY_AGG(sal ORDER BY sal DESC LIMIT
> 3)}}. In Calcite, you can use either syntax for {{ARRAY_AGG}},
> {{ARRAY_CONCAT_AGG}}, {{GROUP_CONCAT}}, {{STRING_AGG}} functions; we should
> add {{LIMIT}} in both.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
