ROWS Phrase - Advanced SQL Engine - Teradata Database

SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-28
dita:mapPath
SQL_Functions__Expressions__and_Predicates.Upload_071421/djk1612415574830.ditamap
dita:ditavalPath
SQL_Functions__Expressions__and_Predicates.Upload_071421/wrg1590696035526.ditaval
dita:id
B035-1145
lifecycle
previous
Product Category
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;
   
   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