SQLのWINDOW関数についての覚え書き
SQLはちょいちょい触ってはいるが、今の仕事では使ったことがないWINDOW関数というものを知った。
ちょっと特徴的な動作をするので、簡単にメモっておく。
簡単な例
SQL文
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 date_table;
取り出されるデータ
cur_date | latest_date |
---|---|
2018-02-01 | |
2018-02-02 | 2018-02-01 |
2018-02-05 | 2018-02-02 |
2018-02-07 | 2018-02-05 |
2018-02-08 | 2018-02-07 |
2018-02-12 | 2018-02-08 |
何が起きているのか
だいたい、下記のように理解した。
-- 1. 集計関数 MIN(sample_date) -- 2. グループ化と順序付け OVER (ORDER BY sample_date ASC -- 3. 範囲指定 ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS latest_date
1. 集計関数
集計関数は、SUM, AVG, MAXなど。
元のSQLの「1つのレコードの値として」取り出されるため、
後述する2, 3の条件から1つの値を取り出す必要がある。
2. グループ化と順序付け
ここでは
OVER (PARTITION BY ~ ORDER BY ~
の形で記述する。
・PARTITION BY句
→グループ化(通常のGROUP BY句に近い)
・ORDER BY句
→順序付け(通常のORDEY BY句と一緒)
両方使っても良いし、片方だけでも良い。
3.範囲指定
ここでは、ROWSかRANGEで範囲を指定する。指定しなければ全範囲が対象になる。
・ROWS
→行数での範囲付け
・RANGE
→値での範囲付け
ROWSは、前後n個とか直近のとか、値が明確じゃない時に使える。
RANGEは、過去n日とか、決まった値の範囲で集計したい場合に使える。
・PRECEDING
→現在行の前に範囲を広げる
・FOLLOWING
→現在行の後ろに範囲を広げる
BETWEENを使わない場合、現在行が終点となる。
まとめ
最初見た時は一瞬「なんだこれ?」となったが、かなり便利そう。
過去、無駄に複雑に組んだサブクエリとか、 これで行けたんじゃないかってやつありそうな気がする。