[
https://issues.apache.org/jira/browse/CALCITE-2224?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16676757#comment-16676757
]
Hongze Zhang commented on CALCITE-2224:
---------------------------------------
[~julianhyde]
Thank you very much for your work and for the list, and I have added some
commits on top of your work, the commits are now forcibly pushed to PR #871.
content:
* I have add an new operator for within group to replace order args in SqlCall,
therefore SqlOperator.allowsOrderedAggregate is removed.
* I have implemented AggregateOrderLevel for validate WITHIN GROUP operator in
different ways (for now, MANDATORY, OPTIONAL, IGNORED, FORBIDDEN), removed
SqlOperator.allowsOrderedAggregate and SqlAggFunction.ignoreAggregateOrder.
* I have replaced SqlNodeList.EMPTY usage with null, to represent a missing
WITHIN GROUP clause.
For AggregateCall in rel nodes, I think there is no need to distinguish between
a missing and empty collation, leave it non-null could be better.
* I have removed immutability of LazySource in the lambda factory, immutability
is not that important here.
* I have added test similar to to CALCITE-1910.
Please review again when you have convenience, 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
> Fix For: 1.18.0
>
>
> 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)
