This example finds cumulative sales for all periods of increasing sales for each region.
SUM(sales) OVER ( PARTITION BY region ORDER BY day_of_calendar RESET WHEN sales < /* preceding row */ SUM(sales) OVER ( PARTITION BY region ORDER BY day_of_calendar ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) ROWS UNBOUNDED PRECEDING )
This example finds sequences of increasing balances, resetting whenever the current balance is less than or equal to the preceding balance.
SELECT account_key, month, balance, ROW_NUMBER() over (PARTITION BY account_key ORDER BY month RESET WHEN balance /* current row balance */ <= SUM(balance) over (PARTITION BY account_key ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) /* prev row */ ) - 1 /* to get the count started at 0 */ as balance_increase FROM accounts;
Output:
account_key month balance balance_increase ----------- ----- ------- ---------------- 1 1 60 0 1 2 99 1 1 3 94 0 1 4 90 0 1 5 80 0 1 6 88 1 1 7 90 2 1 8 92 3 1 9 10 0 1 10 60 1 1 11 80 2 1 12 10 0
The following example shows a window function with a nested aggregate. The query is processed as follows:
- Use the SUM(balance) aggregate function to calculate the sum of all the balances for a given account in a given quarter.
- Check to see if a balance in a given quarter (for a given account) is greater than the balance of the previous quarter.
- If the balance increased, track a cumulative count value. If the RESET WHEN condition evaluates to false, the balance is increasing over successive quarters, and continue to increase the count.
- Use the ROW_NUMBER() ordered analytical function to calculate the count value. When a quarter whose balance is less than or equal to that of the previous quarter, the RESET WHEN condition evaluates to true, a new partition stars and ROW_NUMBER() restarts the count from 1. Specify ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING to access the previous value.
- Subtract 1 so the count values start with 0.
The balance_increase column shows the number of successive quarters where the balance was increasing. This exampl e has only one quarter (1->2) where the balance has increased.
SELECT account_key, quarter, sum(balance), ROW_NUMBER() over (PARTITION BY account_key ORDER BY quarter RESET WHEN sum(balance) /* current row balance */ <= SUM(sum(balance)) over (PARTITION BY account_key ORDER BY quarter ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)/* prev row */ ) - 1 /* to get the count started at 0 */ as balance_increase FROM accounts GROUP BY account_key, quarter;
Output:
account_key quarter balance balance_increase ----------- ------- ------- ---------------- 1 1 253 0 1 2 258 1 1 3 192 0 1 4 150 0
In the following example, the condition in the RESET WHEN clause contains SELECT as a nested subquery. This is an error.
SELECT SUM(a1) OVER (ORDER BY 1 RESET WHEN 1 in (SELECT 1)) FROM t1;
Output:
$ *** Failure 3706 Syntax error: SELECT clause not supported in RESET...WHEN clause.