[CALCITE-2224] WITHIN GROUP clause for aggregate functions

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.18.0
• Assignee: Julian Hyde
• Reporter: Julian Hyde
• Created: 27/Mar/18 19:25
• Updated: 08/Jul/21 17:29
• Resolved: 08/Nov/18 18:20

Description

The WITHIN GROUP clause lets aggregate functions operate on a sorted list of rows, rather than the usual unsorted collection. Order only matters for a few aggregate functions, but we should allow it for all.

Other analytic functions where WITHIN GROUP would have an effect: RANK, PERCENT_RANK, FIRST_VALUE, LAST_VALUE, PERCENTILE_CONT, PERCENTILE_DISC.

LISTAGG(value [, separator]) is an Oracle function that concatenates strings. E.g.

SELECT LISTAGG(last_name, '; ')
         WITHIN GROUP (ORDER BY hire_date, last_name)
  FROM Emp
GROUP BY deptno

STRING_AGG(value [, separator]) is a [Microsoft SQL Server function|] that does something similar.

GROUP_CONCAT(value [, separator] [ORDER BY expr [, expr]...) is the MySQL equivalent to LISTAGG. Note the optional ORDER BY clause within the parentheses.

COLLECT(value) is a SQL standard aggregate function that creates multisets. Oracle added a non-standard ORDER BY clause within the parentheses.

In my opinion, WITHIN GROUP should always be optional. LISTAGG without WITHIN GROUP would produce non-deterministic output (which is OK); other aggregate functions such as MIN and SUM would just ignore WITHIN GROUP.

Issue Links

• relates to: CALCITE-878 Allow ORDER BY within COLLECT (an Oracle extension)
• relates to: CALCITE-4687 Add LIMIT to WITHIN GROUP clause of aggregate functions
• relates to: CALCITE-2932 DruidAdapterIT regression after 1.17 release
• links to: PR #871