Filtering within an window function (over ... partition by)?

Question

I am trying to use a sum() over (partition by) but filter within that summing. My use case is summing trailing twelve months up to a single month's entry for each product, so:

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

Would then return:

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

Where the TTM SALES for 1/1/12 is the sum of 1/1/11-12/1/11

Answer 1

The bellow query shows how I would do it with 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

---

This will compute the sum function over a window that starts 12 months before the month of the current row and ends 1 month before it.

I assumed that you do not need to filter anything in the where clause. If you do, be careful with it. Quoting the 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.

So lets say that you want to display results only for the first quarter of 2012; if you try to do so by filtering in the where clause, it will affect the cumulative results of TTM_SALES as well (outputing null, 3, 7 and 12).

The bottom line here is: If you need to filter out rows within the window of the analytic function, move the analytic function to a subquery and filter in the outer query as per @peterm answer:

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; 

Answer 2

If you're open to anything other than an analytic SUM() then here is a possible solution with a simple correlated subquery

SELECT s.item, s.month month_begin, s.sales,
       (SELECT SUM(sales) FROM sales 
         WHERE month BETWEEN DATEADD(month, -12, s.month) 
                         AND DATEADD(month,  -1, s.month)) ttm_sales
  FROM sales s 
 WHERE s.month BETWEEN '20120101' AND '20121201'

Sample output:

|   ITEM |                      MONTH_BEGIN | SALES | TTM_SALES |
-----------------------------------------------------------------
| Item A |   January, 01 2012 00:00:00+0000 |     3 |        87 |
| Item A |  February, 01 2012 00:00:00+0000 |     4 |        88 |
| Item A |     March, 01 2012 00:00:00+0000 |     5 |        87 |
| Item A |     April, 01 2012 00:00:00+0000 |     6 |        89 |
...

Here is SQLFiddle demo