次の例では、ネストされた集約を伴うウィンドウ関数を示します。問合わせは、次のように処理されます。
- 集約関数のSUM(balance)を使用して、特定の口座に対して、特定の四半期内の残高すべての合計を計算します。
- 特定の四半期内の(特定の口座に対する)残高が、直前の四半期の残高を上回っているかどうかを確認します。
- 残高が増加していれば、累積カウント値に記録します。RESET WHEN条件の評価が偽の間は、連続する四半期を通して残高が増加し続けているので、引き続き累積カウントを増加させます。
- 累積カウント値を計算するために、順序付き分析関数ROW_NUMBER()を使用します。残高が直前の四半期の残高以下になる四半期に到達すると、RESET WHEN条件の評価が真になるので、新しいパーティションを開始します。ROW_NUMBER()はカウントを1から再開します。直前の値にアクセスするために、ROWS BETWEEN 1 PRECEDING AND 1 PRECEDINGを指定しています。
- 最後に、カウント値が確実に0から始まるようにするために、1を引いています。
balance_increase列では、残高が増加し続けていた四半期の連続数を示します。この例では、残高が増加した四半期は1期(1->2)だけでした。
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;
上記のSELECT文の結果は、例えば以下のようなテーブルになります。
account_key quarter balance balance_increase ----------- ------- ------- ---------------- 1 1 253 0 1 2 258 1 1 3 192 0 1 4 150 0