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 |