The following example illustrates a window function with a nested aggregate. The query is processed as follows:
- We use the SUM(balance) aggregate function to calculate the sum of all the balances for a given account in a given quarter.
- We 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, we track a cumulative count value. As long as the RESET WHEN condition evaluates to false, the balance is increasing over successive quarters, and we continue to increase the count.
- We use the ROW_NUMBER() ordered analytical function to calculate the count value. When we reach a quarter whose balance is less than or equal to that of the previous quarter, the RESET WHEN condition evaluates to true, and we start a new partition and ROW_NUMBER() restarts the count from 1. We specify ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING to access the previous value.
- Finally, we subtract 1 to ensure that the count values start with 0.
The balance_increase column shows the number of successive quarters where the balance was increasing. In this example, we only have 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;
The possible results of the preceding SELECT appear in the table below:
account_key quarter balance balance_increase ----------- ------- ------- ---------------- 1 1 253 0 1 2 258 1 1 3 192 0 1 4 150 0