[
https://issues.apache.org/jira/browse/CALCITE-7362?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18052372#comment-18052372
]
Stamatis Zampetakis commented on CALCITE-7362:
----------------------------------------------
I am OK adopting the FILTER (WHERE) structure for expressing conditional
aggregates. I don't think we need to insist a lot on this point.
So to recap the transformation using the filter clause in the aggregate would
look like the following.
+Before+
{code:sql}
select sum(ss_net_paid_inc_tax)
from store_sales
where ss_quantity < 20
{code}
{noformat}
LogicalAggregate(group=[{}], EXPR$0=[SUM($0)])
LogicalProject(SS_NET_PAID_INC_TAX=[$7])
LogicalFilter(condition=[<($0, 10)])
LogicalTableScan(table=[[TPCDS, STORE_SALES]])
{noformat}
+After+
{code:sql}
select sum(ss_net_paid_inc_tax) filter (where ss_quantity < 20)
from store_sales;
{code}
{noformat}
LogicalAggregate(group=[{}], EXPR$0=[SUM($0) FILTER $1])
LogicalProject(SS_NET_PAID_INC_TAX=[$7], $f1=[<($0, 10)])
LogicalTableScan(table=[[TPCDS, STORE_SALES]])
{noformat}
If there are no further suggestions I will start working on this sometime next
week.
> 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)
