Example: Window Frame Excludes Current Row

In this example, you can define the window frame to exclude the current row. The query calculates the sum over four rows, excluding the current row.

SELECT prod_id, month_num, sales, sum(sales) OVER
  (PARTITION BY prod_id ORDER BY month_num RANGE
  BETWEEN 6 PRECEDING AND 2 PRECEDING) 
FROM sale 
ORDER BY prod_id, month_num;

prod_id   month_num       sales    sum(sales)
-------   ---------       -----    ----------
10                1         100        (NULL)
10                1         150        (NULL)
10                2         120        (NULL)
10                3         100          250
10                4         130          370
10                5         120          470
10                5          31          470
10                6         110          600
20                1          20        (NULL)
20                2          30        (NULL)
20                3          25           20
20                4          30           50
20                5          31           75
20                6          20          105
30                1          10        (NULL)
30                2          11        (NULL)
30                3          12           10
30                4           1           21
30                4           1           21