Example: Moving Sum - Analytics Database - Teradata Vantage

SQL Functions, Expressions, and Predicates

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-01-12
dita:mapPath
obm1628111499646.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
kby1472250656485
lifecycle
latest
Product Category
Teradata Vantageā„¢

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