[
https://issues.apache.org/jira/browse/CALCITE-1757?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-1757:
---------------------------------
Summary: Convert sub-query to windowed aggregate function ("WinMagic")
(was: Convert sub-query to windowed aggregate function)
> Convert sub-query to windowed aggregate function ("WinMagic")
> -------------------------------------------------------------
>
> Key: CALCITE-1757
> URL: https://issues.apache.org/jira/browse/CALCITE-1757
> Project: Calcite
> Issue Type: Bug
> Reporter: Julian Hyde
> Assignee: Julian Hyde
> Priority: Major
>
> 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
(v7.6.3#76005)
