[
https://issues.apache.org/jira/browse/CALCITE-1757?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15975149#comment-15975149
]
Jesus Camacho Rodriguez commented on CALCITE-1757:
--------------------------------------------------
[~julianhyde], nice! Thanks for pointing that out. I was not aware of the
paper, I will take a look.
Actually I was thinking today once the (initial) work for materialized views is
done, it might be worth visiting again the _Spool_ operator implementation, as
same work could be partially reused for that.
> 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)
