[
https://issues.apache.org/jira/browse/CALCITE-7362?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Stamatis Zampetakis updated CALCITE-7362:
-----------------------------------------
Description:
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 transformation is correct only when there is *no* GROUP BY clause (or GROUP
BY ()).
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.
was:
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.
> 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
> Priority: Major
>
> 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 transformation is correct only when there is *no* GROUP BY clause (or
> GROUP BY ()).
> 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)
