ROWS Phrase - Teradata Vantage

ROWS defines the rows over which the aggregate function is computed for each row in the partition.

If ROWS is specified, the computation of the aggregate function for each row in the partition includes only the subset of rows in the ROWS phrase.

If there is no ROWS phrase, then the computation includes all the rows in the partition.

To compute the three-month moving average sales for each store in the sales_tbl table, partition by StoreID, order by SMonth, and perform the computation over the current row and the two preceding rows:

   SELECT StoreID, SMonth, ProdID, Sales,
   AVG(Sales) OVER (PARTITION BY StoreID 
                    ORDER BY SMonth
                    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
   FROM sales_tbl;

Output:

   StoreID  SMonth  ProdID      Sales  Moving Avg(Sales)
   -------  ------  ------  ---------  -----------------
      1001       1  C        35000.00           35000.00
      1001       2  C        25000.00           30000.00
      1001       3  C        40000.00           33333.33
      1001       4  C        25000.00           30000.00
      1001       5  C        30000.00           31666.67
      1001       6  C        30000.00           28333.33
      1002       1  C        40000.00           40000.00
      1002       2  C        35000.00           37500.00
      1002       3  C       110000.00           61666.67
      1002       4  C        60000.00           68333.33
      1002       5  C        35000.00           68333.33
      1002       6  C       100000.00           65000.00