Julian Hyde created CALCITE-1757:
------------------------------------
Summary: 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)
