ウィンドウ関数

ウィンドウ関数は、現在行に関係する行集合を読み、一つの値を計算してその行へ加える。累積和や順位を求めても元の各行を保つ。GROUP BY は各群を集約行へ縮め、結果の粒度を変える。

OVER は三点を指定する。PARTITION BY は計算を共有する範囲、内部 ORDER BY は計算順、関数と必要なフレームは現在値へ寄与する順序付けた行を決める。内部順序は最終返却順を保証せず、必要ならクエリ全体の ORDER BY も書く。

累積 SUM の仕組み

本レッスンは範囲を明示する。

SELECT
  id,
  region,
  day,
  amount,
  SUM(amount) OVER (
    PARTITION BY region
    ORDER BY day, id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_sum
FROM sales
ORDER BY region, day, id;

内部 ORDER BY day, id が累積の接頭部分を決め、外側 ORDER BY region, day, id が返却順を決める。各行の ROWS フレームは地域の最初から現在行までで、SUM はその amount だけを足す。

ウィンドウ関数のステップ

既定の元表は地域を交互に配置している。

region で分け、day、id で並べると、North は s3 → s5 → s1 → s7、South は s2 → s6 → s4。

1. North の s3 は [s3] を読み、30。
2. s5 は [s3, s5] を読み、30 + 50 = 80。
3. s1 は [s3, s5, s1] を読み、30 + 50 + 30 = 110。
4. s7 は [s3, s5, s1, s7] を読み、30 + 50 + 30 + 20 = 130。
5. South はゼロから始め直し、ウィンドウ順の値は 40、50、90。

行 ID で書き戻すと、元順を保つ教材表は次になる。

元順は行の対応を調べるための表示であり、外側 ORDER BY のない SQL の保証ではない。

RANK と DENSE_RANK

順位もパーティションと順序を使うが、集約用の行フレームは使わない。

SELECT
  id,
  region,
  amount,
  RANK() OVER (
    PARTITION BY region
    ORDER BY amount DESC
  ) AS rank
FROM sales;

ORDER BY 値が同じ行はピアで、同じ順位を得る。現在の amount 降順では、RANK は 1 に前の厳密に大きい金額の行数を加え、DENSE_RANK は前の異なる金額の種類数を加える。

North は s5(50), s1(30), s3(30), s7(20)。二つの 30 は 2 で、その次は前に三行あるため RANK=4、異なる金額が二つなので DENSE_RANK=3。South は s2(40), s4(40), s6(10)。二つの 40 が同順位で、10 は RANK=3、DENSE_RANK=2 になる。

大きいピア群のプリセットは West の四行を 90 でそろえる。次の二行は RANK が 5、6、DENSE_RANK が 2、3 となり、順位の空きを確認できる。

ウィンドウと GROUP BY

ウィンドウは n 元行に計算列を加え、n 行を返す。GROUP BY region は n 行を p 地域の集約行へ変える。既定では七行対二行となり、後者は s1、s3 などの行の識別を結果の粒度に残さない。

PARTITION BY を消すのは別の変更だ。パーティションリセット例では、全体の ORDER BY day, id が p2, p3, p1, p4、累積値が 10, 110, 210, 215 になる。最初の South はまだ 10 で、両地域が一つの接頭部分を共有してから違いが出る。分ければ North は 100、200、South は 10、15。

境界ケース

• 空入力にはパーティション、フレーム、ピア、結果行がなく、ウィンドウは行を作り出さない。
• 一行のパーティションは自分だけを累積し、両順位は 1。
• 同値行は順位を共有し、次の異なる値で隙間の有無が分かる。
• ROWS は位置に依存する。同じ day の順序で中間和が変わるなら、id などの決定的な同点処理を加える。
• 外側 ORDER BY がなければ、内部順序は計算だけを決め、表示順は決めない。

よくある間違い

• PARTITION BY が行を縮めると思う。独立した計算範囲を定めるだけだ。
• 元表の順に累積する。OVER 内の ORDER BY に従う必要がある。
• フレームを省いて ROWS と同じだと思う。既定の RANGE は同値行をまとめて含み、一行ずつの接頭範囲と異なる場合がある。
• 異なる値の数で RANK を求める。それは DENSE_RANK で、RANK は前の行数を数えて隙間を残す。
• 内部 ORDER BY だけで返却順も安定すると思う。外側の指定が必要だ。
• 表示を物理ソートやバッファー計画と読む。SQL は論理を定め、最適化器が実行を選ぶ。

モデルの範囲

論理的なパーティション、内部順序、明示的な接頭 ROWS、ピア、順位の隙間、行の識別、結果行数を扱う。実行方式、メモリーとディスク退避、照合規則、NULL 順、精度・桁あふれ、名前付きウィンドウ、FILTER、他の境界、RANGE、GROUPS、除外句、方言の既定値は省く。暗黙フレームと NULL 順は対象データベースで確認する必要がある。