calcite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Julian Hyde (JIRA)" <j...@apache.org>
Subject [jira] [Created] (CALCITE-1757) Convert sub-query to windowed aggregate function
Date Mon, 17 Apr 2017 22:04:42 GMT
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)

Mime
View raw message