[CALCITE-6217] PER clause for aggregate functions

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Assignee: Unassigned
• Reporter: Julian Hyde
• Created: 22/Jan/24 19:56
• Updated: 02/Apr/24 07:32

Description

Add a PER clause for windowed aggregate functions, allowing multi-level aggregation. For example,

SELECT MIN(AVG(revenue) PER MONTH(orderDate))
FROM Orders

CALCITE-4483 added WITHIN DISTINCT, to allow you to eliminate duplicates values on a key before calling an aggregate function, but it required that all values were the same; this change allows you to specify an aggregate expression to combine those values. Thus WITHIN DISTINCT is a specialization of PER that always uses SINGLE_VALUE; for example, AVG(shipping) WITHIN DISTINCT (orderId) is equivalent to AVG(SINGLE_VALUE(shipping) PER orderId).

Applications of PER:

• semi-additive aggregates, e.g. SUM(LAST_VALUE(inventory) PER DAY(inventoryDate));
• symmetric aggregates, e.g. AVG(SINGLE_VALUE(shipping) PER orderId);
• distinct aggregates, e.g. SUM(shipping PER shipping) is equivalent to SUM(DISTINCT shipping).

Issue Links

• is related to: CALCITE-4483 WITHIN DISTINCT clause for aggregate functions (experimental)