[
https://issues.apache.org/jira/browse/CALCITE-7362?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18050813#comment-18050813
]
Stamatis Zampetakis commented on CALCITE-7362:
----------------------------------------------
The most trivial example of MV rewrite is present in the description. Consider
"After" as the materialized view and "Before" as the incoming query. With the
proposed rule the incoming query can be rewritten as a plain scan over the
materialized view.
Below a slightly more elaborate example using a reduced form of TPC-DS query9.
+Query+
{code:sql}
select case when (select count(*)
from store_sales
where ss_quantity between 1 and 20) > 409437
then (select avg(ss_ext_list_price)
from store_sales
where ss_quantity between 1 and 20)
else (select avg(ss_net_paid_inc_tax)
from store_sales
where ss_quantity between 1 and 20) end bucket1,
case when (select count(*)
from store_sales
where ss_quantity between 21 and 40) > 4595804
then (select avg(ss_ext_list_price)
from store_sales
where ss_quantity between 21 and 40)
else (select avg(ss_net_paid_inc_tax)
from store_sales
where ss_quantity between 21 and 40) end bucket2
{code}
+View+
{code:sql}
create materialized view mvq as
select
COUNT(case when ss_quantity between 1 and 20 then 1 else NULL) b1e1,
AVG(case when ss_quantity between 1 and 20 then ss_ext_list_price else NULL)
b1e2,
AVG(case when ss_quantity between 1 and 20 then ss_net_paid_inc_tax else
NULL) b1e3,
COUNT(case when ss_quantity between 21 and 40 then 1 else NULL) b2e1,
AVG(case when ss_quantity between 21 and 40 then ss_ext_list_price else NULL)
b2e2,
AVG(case when ss_quantity between 21 and 40 then ss_net_paid_inc_tax else
NULL) b2e3
FROM store_sales
{code}
+Rewrite+
{code:sql}
select case when (select b1e1 from mvq) > 409437
then (select b1e2 from mvq)
else (select b1e3 from mvq) end bucket1,
case when (select b2e1 from mvq) > 4595804
then (select b2e2 from mvq)
else (select b2e3 from mvq) end bucket2
{code}
Note that "mvq" has one row so compared to the scan of "store_sales" table that
has million/billion rows the query perf improvement can be huge.
> 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)
