Stamatis Zampetakis created CALCITE-7362:
--------------------------------------------
Summary: Add rule to transform WHERE clauses into conditional
aggregates
Key: CALCITE-7362
URL: https://issues.apache.org/jira/browse/CALCITE-7362
Project: Calcite
Issue Type: New Feature
Components: core
Reporter: Stamatis Zampetakis
Assignee: Stamatis Zampetakis
Add a rule to transform aggregate queries with filtering based on WHERE clauses
to conditional aggregates (CASE WHEN) without a WHERE clause.
The proposed transformation using the SQL representation is shown below:
++
+Before+
{code:sql}
select sum(ss_net_paid_inc_tax)
from store_sales
where ss_quantity < 20
{code}
+After+
{code:sql}
select sum(case when ss_quantity < 20 then ss_net_paid_inc_tax else null)
from store_sales
{code}
The queries are equivalent and the transformation is valid for all aggregate
functions that [skip NULL input
values|https://github.com/apache/calcite/blob/c0d5a0832808fabfa32dea744415c0f46c516bce/core/src/main/java/org/apache/calcite/sql/SqlAggFunction.java#L233].
The main motivation for introducing this rule is view-based rewriting where it
is quite common to have multiple conditional aggregates in the same
(materialized) view definition for precomputing and reusing expensive
aggregations.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
