[
https://issues.apache.org/jira/browse/CALCITE-4687?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17378721#comment-17378721
]
Lukas Eder commented on CALCITE-4687:
-------------------------------------
{{ARRAY_AGG}} corresponds to Oracle's {{COLLECT}} which also has its {{ORDER
BY}} clause inside of the aggregate argument list:
[https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/COLLECT.html#GUID-A0A74602-2A97-449B-A3EC-847D38D3DA90]
In PostgreSQL, you can define custom aggregate functions, and use the {{ORDER
BY}} clause as expected:
{code:java}
CREATE FUNCTION any_value_sfunc (state INTEGER, data INTEGER) RETURNS INTEGER
AS
$$
BEGIN
IF state IS NULL THEN
RETURN data;
ELSE
RETURN state;
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION any_value_ffunc (state INTEGER) RETURNS INTEGER
AS
$$
BEGIN
RETURN state;
END;
$$ LANGUAGE plpgsql;
CREATE AGGREGATE any_value (INTEGER) (
SFUNC = any_value_sfunc,
STYPE = INTEGER,
FINALFUNC = any_value_ffunc
);
SELECT any_value (a ORDER BY a DESC)
FROM (VALUES (1),(3),(2),(4)) t (a); {code}
This produces 4, and there's no need to adapt the parser for custom aggregates.
As I mentioned, the mechanism here is different from "ordered-set aggregate
functions", i.e. functions with a {{WITHIN GROUP}} clause. I think the
PostgreSQL docs nicely explains it:
[https://www.postgresql.org/docs/current/xaggr.html#XAGGR-ORDERED-SET-AGGREGATES]
It's your choice, of course. The status quo is a bit messy, but the idea behind
the syntaxes are distinct. They're not the same, even if a lot of vendors
confuse them.
> Add LIMIT to WITHIN GROUP clause of aggregate functions
> -------------------------------------------------------
>
> Key: CALCITE-4687
> URL: https://issues.apache.org/jira/browse/CALCITE-4687
> Project: Calcite
> Issue Type: Bug
> Reporter: Julian Hyde
> Priority: Major
>
> Add LIMIT to WITHIN GROUP clause of aggregate functions. LIMIT is not in the
> SQL standard, but it is useful, and is not hard to implement.
> The following query computes the 3 highest paid employees in each department:
> {code:java}
> SELECT deptno, ARRAY_AGG(sal) WITHIN GROUP (ORDER BY sal DESC LIMIT 3)
> FROM Emp
> GROUP BY deptno {code}
> It can be implemented efficiently (using a merge sort that discards all but
> the top 3 rows in each key, at each pass).
> Note that BigQuery does not support the {{WITHIN GROUP}} clause, but in the
> {{ARRAY_AGG}} function, the {{ORDER BY}} and {{LIMIT}} sub-clauses appear
> within the parentheses, like this: {{ARRAY_AGG(sal ORDER BY sal DESC LIMIT
> 3)}}. In Calcite, you can use either syntax for {{ARRAY_AGG}},
> {{ARRAY_CONCAT_AGG}}, {{GROUP_CONCAT}}, {{STRING_AGG}} functions; we should
> add {{LIMIT}} in both.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
