[jira] [Commented] (CALCITE-2224) WITHIN GROUP clause for aggregate functions

["Hongze Zhang (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-2224?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16671185#comment-16671185 ] 

Hongze Zhang commented on CALCITE-2224:
---------------------------------------

Hi [~julianhyde]  

Do you or someone else have any plan on reviewing [PR #871|https://github.com/apache/calcite/pull/871]? Thank you very much.

> 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
>            Priority: Major
>
> 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)