SQL修行vol.3

『達人に学ぶSQL徹底指南書』ー2.必ずわかるウィンドウ関数

「必ずわかる」って書いてるのに全然わかんないじゃん。って感じなので、自分に不足している知識を随時補足しながらまとめnoteを綴ってみる。ちなみに今日は3月下旬にもかかわらず朝起きたら庭に雪が積もってた☃️

ウィンドウ関数とは何か

集合関数（AVG、MAX、SUMとか）に制限範囲を加えたもの。一般的な集合関数の後ろにOVER句をつけると大体ウィンドウ関数と思えば良さそう。実行結果が集合関数は一つの行にまとめられるのに対し、ウィンドウ関数は行の数はそのままになる。

--ノーマル集合関数
SELECT
   fieldName1,
   fieldName2,
   AVG (fieldName3) AS avg
FROM
   tableName

--ウィンドウ関数
SELECT
   fieldName1,
   fieldName2,
   AVG (fieldName3) 
      OVER (ORDER BY
               fieldName1 
            ROWS
               BETWEEN 2 PRECEDING AND CURRENT ROW
           ) AS avg
FROM
   tableName

ウィンドウ関数にはOVER句は必須で、その中には下記3つを含むことができる。（つまりウィンドウ関数の機能は次の３つ）

• PARTITION BY：ウィンドウ関数用のGROUP BYで集約に使う。なお上述の通り行は集約されない。

• ORDER BY：普通のORDER BY句と同じで指定したカラムで順序付けをするが、行を順番に並べた上で最初の行から現在行までのみを集計の対象にする。つまり、集計に影響があるところが普通のORDER BY句とは違う。集計の範囲を指定したい場合は次のFRAME句を使う。

• FRAME（ROWS / RANGE）：ROWは行数指定。RANGEは列の値に基づいて範囲を指定（ORDER BY句が数値か日付の場合は使える、文字列の場合は使えない）。

〜FRAMEの中で使えるもの〜
BETWEEN：よくあるやつ
n PRECEDING：n（正の整数）だけ前（小さいほう）へ移動する
n FOLLOWING：n（正の整数）だけ後（大きいほう）へ移動する
UNBOUNDED PRECEDING：無制限にさかのぼる方（先頭行）へ移動する
UNBOUNDED FOLLOWING：無制限に下る方（末尾の行）へ移動する
CURRENT ROW：現在行

🐣名前付きウィンドウ構文
SELECTの中では、OVER Wって書いておいて、最後に「WINDOW W」でウィンドウの定義を書くと、一つのウィンドウを使い回すことができる。が、簡略的ではない、構文エラーになるDMBSもあるということから、無名構文の方が普及しているというのが現状である。 

SELECT
   fieldName1,
   fieldName2,
   AVG (fieldName3) OVER W AS avg
FROM
   tableName
WINDOW W AS ( ORDER BY 
                 fieldName1 
              ROWS
                 BETWEEN 2 PRECEDING AND CURRENT ROW);

FRAME

３つのウィンドウ関数の機能のうち３つ目のFRAMEについてもう少し詳細を。それ以外はイメージつきやすいと思うので割愛。

FRAME句の原理は「カーソル」。RDBの世界では基本的にはレコードに順序はないが、手続き型言語（いわゆるプログラミング言語）では順序が必要で、1行のレコードを操作の基本単位となっている。これを仲介するのがFRAME句である。つまり、カレントレコードを基準に計算する統計指標をSQLで簡単に算出できる。（昨日の私は全然意味わからんって思ってたけど、今日はなんとなくイメージ出来ながら書いている）

活用ケース：FRAME句を使って違う行を自分の行に持ってくる（行間比較）

例１：各行における過去直近の日付を求める（＝１行前の日付を求める）

SELECT
   sample_date AS cur_date,
   MIN(sample_date)
      OVER (ORDER BY
               sample_date ASC
            ROWS
               BETWEEN 1 PRECEDING AND 1 PRECEDING
           ) AS latest_date
FROM
   LoadSample;

なぜMIN？
範囲が１行に限定されているので、MINでもMAXでもSUMでもAVGでも結果は同じ。複数行を指定する場合は、適切な集約関数を適用する必要がある。

例２：各行における２日前の日付を求める

SELECT
   sample_date AS cur_date
   MIN(sample_date)
      OVER (ORDER BY
               sample_date ASC
            RANGE
               BETWEEN interval '2' day PRECEDING 
                   AND interval '2' day PRECEDING 
           ) AS day2_before
FROM
   LoadSample;

例３：n行前の日付と比較する（行間比較の一般化）

SELECT
   sample_date AS cur_date
   MIN(sample_date)
      OVER (ORDER BY
               sample_date ASC
            ROWS
               BETWEEN n PRECEDING AND n `RECEDING
            ) AS latest_n,
FROM
   LoadSample;

この行間比較ができるようになったというのは大きいようで、別章で取り上げられているので、そちらで詳細をば。

ウィンドウ関数の正体はソート

DBMSのウィンドウ関数の実行計画をみるとわかるように、ウィンドウ関数は内部でレコード集合に対してソートを行っている。その理由は、PARTITION BY句によるグループへの分類や、ORDER BY句によるレコードのソートが必要であるから。

本当にソートでいいの？議論がある。PARTITION BY句はハッシュによって計算する方が性能が良いというエビデンスもある。ハッシュ値の方が列数はデータ型を気にする必要がないという利点がある。実際GROUP BY句がOracleやPostgreSQLではソートではなくハッシュで計算されることがある。ハッシュが有利になるにはいくつかの前提が必要なので、常に有利というわけではないのが現状。