[
https://issues.apache.org/jira/browse/CALCITE-7362?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18051107#comment-18051107
]
Julian Hyde commented on CALCITE-7362:
--------------------------------------
I think of FILTER(WHERE) as canonical, and SUM(CASE …) as a workaround for
dialects that do not support it. Why not rewrite to the former?
Have you checked that the queries are equivalent in the case that the filter
removes all rows in the table (or all rows in a group)? .
> 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 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)
