[jira] [Commented] (CALCITE-5283) Add ARG_MIN, ARG_MAX (aka MIN_BY, MAX_BY) aggregate functions

["Julian Hyde (Jira)" <ji...@apache.org>]

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

Julian Hyde commented on CALCITE-5283:
--------------------------------------

Reviewing now.

I am debating whether {{ARG_MIN}}, {{ARG_MAX}} should be in the "standard" operator table (and enabled by default). It's tricky because they are *almost* industry-standard (not ISO standard), but the industry is divided as to the name.

I think that {{ARG_MIN}}, {{ARG_MAX}} should be enabled by default, and {{MIN_BY}} and {{MAX_BY}} should be enabled as synonyms in the Spark library. (That is, if you have {{lib=spark}} then {{ARG_MIN}} and {{MIN_BY}} will both work; in any other lib setting, {{ARG_MIN}} will work.)

> Add ARG_MIN, ARG_MAX (aka MIN_BY, MAX_BY) aggregate functions
> -------------------------------------------------------------
>
>                 Key: CALCITE-5283
>                 URL: https://issues.apache.org/jira/browse/CALCITE-5283
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: Julian Hyde
>            Assignee: Zou
>            Priority: Major
>              Labels: pull-request-available
>          Time Spent: 0.5h
>  Remaining Estimate: 0h
>
> Add ARG_MIN, ARG_MAX (aka MIN_BY, MAX_BY) aggregate functions.
> For example, the following query prints the name of the lowest paid employee in each department:
> {code:java}
> SELECT deptno, ARG_MIN(sal, ename)
> FROM emp
> GROUP BY deptno;
> +------------+------------+
> | deptno     | _c1        |
> +------------+------------+
> | 10         | MILLER     |
> | 20         | SMITH      |
> | 30         | JAMES      |
> +------------+------------+
> {code}
> If multiple employees have the same salary, arbitrarily chooses one to return.
> See [Yandex description of ARG_MIN|https://cloud.yandex.com/en/docs/datalens/function-ref/ARG_MIN]. Also implemented in Alibaba MaxCompute, Clickhouse, DuckDB. (And in Alibaba MaxCompute, Spark SQL, Firebolt, PrestoDB, Trino as MIN_BY/MAX_BY.)



--
This message was sent by Atlassian Jira
(v8.20.10#820010)