You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by "Julian Hyde (Jira)" <ji...@apache.org> on 2021/07/08 17:30:00 UTC
[jira] [Created] (CALCITE-4687) Add LIMIT to WITHIN GROUP clause of
aggregate functions
Julian Hyde created CALCITE-4687:
------------------------------------
Summary: 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
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)