Julian Hyde created CALCITE-2224:
------------------------------------
Summary: WITHIN GROUP clause for aggregate functions
Key: CALCITE-2224
URL: https://issues.apache.org/jira/browse/CALCITE-2224
Project: Calcite
Issue Type: Bug
Reporter: Julian Hyde
Assignee: Julian Hyde
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|https://docs.oracle.com/cloud/latest/db112/SQLRF/functions089.htm#SQLRF30030]
that concatenates strings. E.g.
{code:java}
SELECT LISTAGG(last_name, '; ')
WITHIN GROUP (ORDER BY hire_date, last_name)
FROM Emp
GROUP BY deptno{code}
{{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|https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat]
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}}.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
