sql - 在窗口函数内过滤(通过...分区)？

Question

我正在尝试使用 sum() over (partition by) 但在总和中过滤。我的用例是将每个产品的 12 个月累计到一个月的条目，因此:

ITEM    MONTH    SALES
Item A  1/1/2011     2
Item A  2/1/2011     5
Item A  3/1/2011     3
Item A  4/1/2011     7
Item A  5/1/2011    12
Item A  6/1/2011     8
Item A  7/1/2011     9
Item A  8/1/2011    15
Item A  9/1/2011     6
Item A  10/1/2011    7
Item A  11/1/2011   12
Item A  12/1/2011    1
Item A  1/1/2012     3
Item A  2/1/2012     4
Item A  3/1/2012     5
Item A  4/1/2012     6
Item A  5/1/2012     4
Item A  6/1/2012     8
Item A  7/1/2012     9
Item A  8/1/2012    12
Item A  9/1/2012    14
Item A  10/1/2012    8
Item A  11/1/2012   12
Item A  12/1/2012   16

然后会返回:

ITEM      MONTH_BEGIN SALES TTM SALES
Item A    1/1/2012        3        87
Item A    2/1/2012        4        88
Item A    3/1/2012        5        87
Item A    4/1/2012        6        89

其中 1/1/12 的 TTM SALES 是 1/1/11-12/1/11 的总和

Answer 1

下面的查询显示了我将如何使用 Oracle Analytic Functions :

SELECT
   "ITEM",
   TO_CHAR("MONTH", 'MM/DD/YYYY') AS "MONTH_BEGIN",
   "SALES",
   SUM("SALES") OVER (
    PARTITION BY 
       "ITEM" 
    ORDER BY 
       "MONTH" 
    RANGE BETWEEN 
       INTERVAL '12' MONTH PRECEDING
       AND 
       INTERVAL '1' MONTH PRECEDING
   ) AS "TTM_SALES"  
FROM
   "SALES"  
ORDER BY
   "MONTH";

Working SQLFiddle demo

这将在当前行月份之前 12 个月开始并在其之前 1 个月结束的窗口内计算 sum 函数。

我假设您不需要在 where 子句中过滤任何内容。如果这样做，请小心处理。引用 Oracle documentation :

Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP
BY, and HAVING clauses are completed before the analytic functions are processed.

因此，假设您只想显示 的结果2012年第一季度 ;如果尝试通过在where 子句中进行过滤来实现，则会影响 TTM_SALES 的累积结果。以及(输出 null 、 3 、 7 和 12 )。

这里的底线是:如果您需要过滤掉分析函数窗口内的行，请将分析函数移动到子查询并按照@peterm 答案过滤外部查询:

SELECT 
   "X"."ITEM",
   TO_CHAR("X"."MONTH", 'MM/DD/YYYY') AS "MONTH_BEGIN",
   "X"."SALES",
   "X"."TTM_SALES"
FROM
(
   SELECT
      "ITEM",
      "MONTH",
      "SALES",
      SUM("SALES") OVER (
       PARTITION BY 
          "ITEM" 
       ORDER BY 
          "MONTH" 
       RANGE BETWEEN 
          INTERVAL '12' MONTH PRECEDING
          AND 
          INTERVAL '1' MONTH PRECEDING
      ) AS "TTM_SALES"  
   FROM
      "SALES"  
   ORDER BY
      "MONTH"
) "X"
WHERE 
  EXTRACT(MONTH FROM "X"."MONTH") BETWEEN 1 AND 4
  AND EXTRACT(YEAR FROM "X"."MONTH") = 2012;