[
https://issues.apache.org/jira/browse/CALCITE-4687?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17378569#comment-17378569
]
Julian Hyde commented on CALCITE-4687:
--------------------------------------
[~lukas.eder], Thanks for letting me know about the standard. I had assumed
that when {{ARRAY_AGG}} was standardized it would follow Oracle's syntax (which
uses {{WITHIN GROUP}}).
And {{LISTAGG}} is a total mess... the various vendors can't agree on names, it
certainly has nothing to do with lists.
One thing to be said for the {{WITHIN GROUP}} clause: it allows users to define
their own aggregate functions where order is significant and not change the
parser.
> 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)
