[
https://issues.apache.org/jira/browse/CALCITE-1757?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15975086#comment-15975086
]
Julian Hyde commented on CALCITE-1757:
--------------------------------------
[~jcamachorodriguez], The transformation to convert sub-queries to windowed
aggregates would have to unify two relational expressions:
{quote}(join R1 (aggregate R2)) → (filter (window R)){quote}
where R is a unification of R1 and R2. Thus it would be similar to the work
we've been doing on materialized views. Something to think about.
> Convert sub-query to windowed aggregate function
> ------------------------------------------------
>
> Key: CALCITE-1757
> URL: https://issues.apache.org/jira/browse/CALCITE-1757
> Project: Calcite
> Issue Type: Bug
> Reporter: Julian Hyde
> Assignee: Julian Hyde
>
> The [WinMagic paper|http://dl.acm.org/citation.cfm?doid=872757.872840]
> describes how to convert sub-queries into windowed aggregate functions.
> For example, TPC-H query
> {code}
> SELECT SUM(l_extendedprice) / 7.0 AS avg_yearly
> FROM tpcd.lineitem, tpcd.part
> WHERE p_partkey = l_partkey
> AND p_brand = 'Brand#23'
> AND p_container = 'MED BOX'
> AND l_quantity < (
> SELECT 0.2 * avg(l_quantity)
> FROM tpcd.lineitem
> WHERE l_partkey = p_partkey)
> {code}
> becomes
> {code}
> WITH WinMagic AS (
> SELECT l_extendedprice, l_quantity,
> avg(l_quantity) OVER (PARTITION BY p_partkey) AS avg_l_quantity
> FROM tpcd.lineitem, tpcd.part
> WHERE p_partkey = l_partkey
> AND p_brand = 'Brand#23'
> AND p_container = 'MED BOX')
> SELECT SUM(l_extendedprice) / 7.0 as avg_yearly
> FROM WinMagic
> WHERE l_quantity < 0.2 * avg_l_quantity;
> {code}
> It is applicable to several TPC-H and TPC-DS queries, and also helps with
> temporal database queries (e.g. effective dates).
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
