Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-4687

Add LIMIT to WITHIN GROUP clause of aggregate functions

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None

      Description

      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:

      SELECT deptno, ARRAY_AGG(sal) WITHIN GROUP (ORDER BY sal DESC LIMIT 3)
      FROM Emp
      GROUP BY deptno 

      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.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                julianhyde Julian Hyde
              • Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated: