Julian Hyde created CALCITE-6217: ------------------------------------ Summary: PER clause for aggregate functions Key: CALCITE-6217 URL: https://issues.apache.org/jira/browse/CALCITE-6217 Project: Calcite Issue Type: Improvement Reporter: Julian Hyde
Add a {{PER}} clause for windowed aggregate functions, allowing multi-level aggregation. For example, {code} SELECT MIN(AVG(revenue) PER MONTH(orderDate)) FROM Orders {code} 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)}}. -- This message was sent by Atlassian Jira (v8.20.10#820010)