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)
