[CALCITE-1473] Ordered-set and hypothetical-set aggregate functions

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Assignee: Unassigned
• Reporter: Julian Hyde
• Created: 27/Oct/16 18:39
• Updated: 23/Jun/21 20:16

Description

Support ordered-set and hypothetical-set aggregate functions. Both have a WITHIN GROUP clause. These are supported by Oracle, PostgreSQL, Microsoft SQL Server.

Examples of ordered-set aggregate functions are mode, percentile_cont, percentile_disc. See PostgreSQL doc.

Examples of hypothetical-set aggregate functions are rank, dense_rank, percent_rank, cume_dist. See PostgreSQL doc.

The SQL standard says (in section 4.16.4 Aggregate Functions):

The hypothetical set functions are related to the window functions RANK, DENSE_RANK, PERCENT_RANK, and CUME_DIST, and use the same names, though with a different syntax.

Issue Links

• is related to: CALCITE-878 Allow ORDER BY within COLLECT (an Oracle extension)
• is related to: CALCITE-1556 Window validation bug with order by agg
• is related to: CALCITE-4644 Add PERCENTILE_CONT and PERCENTILE_DISC aggregate functions
• is related to: CALCITE-4661 Add MODE aggregate function
• relates to: CALCITE-4660 Add MEDIAN as an aggregate function