Feat(SQL): Ordered-Set Aggregate Functions

[st1page]

Tracking

• parse WITHIN GROUP feat(sqlparser): support WITHIN GROUP #10146
  it is just very similar to the string_agg(x order by y) that adds an order tag on the aggregator, in fact, the SQL standard and most databases use that syntax to implement it. https://wiki.postgresql.org/wiki/PostgreSQL_vs_SQL_Standard

  LISTAGG()
  LISTAGG() is the standard SQL equivalent of our string_agg(), but it uses the ordered-set aggregate syntax.
  Implementing this in the most straightforward way would require erasing many of the existing distinctions between ordered-set aggregate syntax and conventional aggregate syntax. Currently, we require that ordered-set aggs be called using WITHIN GROUP, while ordinary aggs must not use WITHIN GROUP but may have an embedded ORDER BY clause in their arguments (as per the standard ARRAY_AGG()).
  LISTAGG() does not have the semantics of an ordered-set aggregate, so implementing it other than as a special case would mean allowing ordinary aggs to be called using WITHIN GROUP as an alternative to their normal syntax.

• bind direct arguments
  • constant direct argument support: feat(binder): Support bind args in Ordered-Set Agg #10193
  • feat(ordered-set agg): support variable to be direct argument of ordered-set aggregate functions #14079
• ordered-set aggregate functions (mode, percentile_cont, percentile_dist)
  • Batch mode: feat(expr, agg): support PERCENTILE_CONT, PERCENTILE_DISC and MODE aggregation #10252
  • feat: ordered-set aggregate in streaming queries #12857
• feat(ordered-set agg): support double precision[] as direct argument #14083

refers

https://www.postgresql.org/docs/9.4/sql-expressions.html#SYNTAX-AGGREGATES
https://www.postgresql.org/docs/9.4/functions-aggregate.html

Placing ORDER BY within the aggregate's regular argument list, as described so far, is used when ordering the input rows for a "normal" aggregate for which ordering is optional. There is a subclass of aggregate functions called ordered-set aggregates for which an order_by_clause is required, usually because the aggregate's computation is only sensible in terms of a specific ordering of its input rows. Typical examples of ordered-set aggregates include rank and percentile calculations. For an ordered-set aggregate, the order_by_clause is written inside WITHIN GROUP (...), as shown in the final syntax alternative above. The expressions in the order_by_clause are evaluated once per input row just like normal aggregate arguments, sorted as per the order_by_clause's requirements, and fed to the aggregate function as input arguments. (This is unlike the case for a non-WITHIN GROUP order_by_clause, which is not treated as argument(s) to the aggregate function.) The argument expressions preceding WITHIN GROUP, if any, are called direct arguments to distinguish them from the aggregated arguments listed in the order_by_clause. Unlike normal aggregate arguments, direct arguments are evaluated only once per aggregate call, not once per input row. This means that they can contain variables only if those variables are grouped by GROUP BY; this restriction is the same as if the direct arguments were not inside an aggregate expression at all. Direct arguments are typically used for things like percentile fractions, which only make sense as a single value per aggregation calculation. The direct argument list can be empty; in this case, write just () not (*). (PostgreSQL will actually accept either spelling, but only the first way conforms to the SQL standard.)An example of an ordered-set aggregate call is:

SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY income) FROM households;
 percentile_cont
-----------------
           50489

which obtains the 50th percentile, or median, value of the income column from table households. Here, 0.5 is a direct argument; it would make no sense for the percentile fraction to be a value varying across rows.

[st1page]

@TennyZhuang I heard that you want to use another algorithm to support 2-phase percentile?

[TennyZhuang]

I just provide some information and don’t do enough investigation, it should be decided by @yuhao-su

[github-actions]

This issue has been open for 60 days with no activity.

If you think it is still relevant today, and needs to be done in the near future, you can comment to update the status, or just manually remove the no-issue-activity label.

You can also confidently close this issue as not planned to keep our backlog clean.
Don't worry if you think the issue is still valuable to continue in the future.
It's searchable and can be reopened when it's time. 😄