ROWS Phrase

Teradata Vantageā„¢ SQL Functions, Expressions, and Predicates

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

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;
   
   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