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を使わない場合、現在行が終点となる。

まとめ

最初見た時は一瞬「なんだこれ？」となったが、かなり便利そう。
過去、無駄に複雑に組んだサブクエリとか、 これで行けたんじゃないかってやつありそうな気がする。