16.20 - Example: Moving Sum - Teradata Vantage NewSQL Engine

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

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

The following query returns the moving sum of meat sales by city. Notice that the query returns the moving sum of sales by city (the partition) for the current row (of the partition) and three preceding rows where possible.

The order in which each meat variety is returned is the default ascending order according to profit.

Where no sales figures are available, no moving sum of sales is possible. In this case, there is a null in the sum(sales) column.

   SELECT city, kind, sales, profit,
   SUM(sales) OVER (PARTITION BY city, kind
                    ORDER BY profit ROWS 3 PRECEDING)
   FROM monthly;
city kind sales profit Moving sum (sales)
Omaha pure pork 25 40 25
Omaha pure pork 25 120 50
Omaha pure pork 45 140 95
Omaha pure pork 125 190 220
Omaha pure pork 45 320 240
Omaha pure pork 1255 400 340
Omaha variety pack ? ? ?
Omaha variety pack 25 40 25
Omaha variety pack 25 120 50
Chicago pure pork ? ? ?
Chicago pure pork 15 10 15
Chicago pure pork 54 12 69
Chicago pure pork 14 20 83
Chicago pure pork 54 24 137
Chicago pure pork 14 34 136
Chicago pure pork 95 80 177
Chicago pure pork 95 140 258
Chicago pure pork 15 220 219
Chicago variety pack 23 39 23
Chicago variety pack 25 40 48
Chicago variety pack 125 70 173
Chicago variety pack 125 100 298
Chicago variety pack 23 100 298
Chicago variety pack 25 120 298