The following query returns the remaining sum of meat sales for all cities. Note there is no PARTITION BY clause in the SUM function, so all cities are included in the remaining sum.
SELECT city, kind, sales, SUM(sales) OVER (ORDER BY city, kind ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) FROM monthly;
The possible results of the preceding SELECT appear in the table below.
city kind sales Remaining Sum(sales) ------- ------------- ------- -------------------- Omaha variety pack 25 ? Omaha pure pork 125 25 Omaha pure pork 25 150 Omaha pure pork 45 175 Chicago variety pack 55 220 Chicago variety pack 25 275 Chicago variety pack 45 300 Chicago pure pork 50 345
Note that the sort order for the computation is alphabetical by city, and then by kind. The results, however, appear in the reverse order.
The sort order that you specify in the window specification defines the sort order of the rows over which the function is applied; it does not define the ordering of the results. To order the results, use an ORDER BY phrase in the SELECT statement.
SELECT city, kind, sales, SUM(sales) OVER (ORDER BY city, kind ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) FROM monthly ORDER BY city, kind;
The possible results of the preceding SELECT appear in the table below:
city kind sales Remaining Sum(sales) ------- ------------- ------- -------------------- Chicago pure pork 50 345 Chicago variety pack 55 265 Chicago variety pack 25 320 Chicago variety pack 45 220 Omaha pure pork 25 70 Omaha pure pork 125 95 Omaha pure pork 45 25 Omaha variety pack 25 ?
|IF you want to compute the …||THEN use this function …|
|cumulative, group, or moving count||COUNT window function|
|group sum||SUM window function|
|moving difference between the current row-column value and the preceding n th row-column value||MDIFF|
|moving linear regression||MLINREG|
|quantile scores for the values in a column||QUANTILE|
|ordered rank of all rows in a group||
|relative rank of a row in a group||PERCENT_RANK window function|
|sequential row number of the row within its window partition according to the window ordering of the window||ROW_NUMBER|
|cumulative, group, or moving maximum value||MAX window function|
|cumulative, group, or moving minimum value||MIN window function|