ウィンドウ関数

別名OLAP(OnLine Analytical Processing)関数。
データベースを使ってリアルタイム(=オンライン)にデータ分析を行うための関数。
ここでのウィンドウは窓ではなく、「範囲」を意味する。

仕様

ある集合に対して、処理を行う。
部分集合に分割されている場合は、全ての部分集合に対して、それぞれ処理を行う。
SELECT句で使われる。
SQLの評価順序についてはこちら

構文

<ウィンドウ関数> OVER
    [PARTITION BY <列リスト>])
    ORDER BY <ソート列リスト>
    [<フレームオプション>]

ウィンドウ関数となる関数

集約関数(SUM, AVG, COUNT, MAX, MIN)
ウィンドウ専用関数(RANK, DENSE_RANK, ROW_NUMBER)

PATITION BY句

元の集合をどのような単位で部分集合に分割するかを決定する。
GROUP BY句と似ているが、集約の機能が無い。
つまり分割した部分集合の要素数を合計すると、元の集合の要素数と一致する。
PARTITION BY句によって区切られたレコードの集合を、ウィンドウと呼ぶ。
ただしPARTITION BYは指定しなくてもエラーとはならない。
もし指定しなかった場合は元の集合に対して、ウィンドウ関数が適用される。

ORDER BY句

分割した部分集合に対して,どのような順序で計算を行うかを指定する。
ウィンドウ関数においてのORDER BY句は表示の順序を決定するわけではない。
そのため、もし表示順序を変更したい場合は、ウィンドウ関数とは別にORDER BY句を指定する(1つのSQLに2つのORDER BY句が存在することになる)。
デフォルトは昇順であるASC。

例

以下のテーブルを利用する。
pop_sex

pref_name	sex	population
東京	1	250
東京	2	250
神奈川	1	200
神奈川	2	100
福岡	1	150
福岡	2	100
埼玉	1	100
埼玉	2	150
長崎	1	20
長崎	2	30
千葉	1	120
千葉	2	130
山形	1	50
山形	2	50

RANK関数

レコードのランキングを算出する。
同順位が存在した場合、後続の順位が飛ぶ。
男女別の県別人口順位を出すことを考える。

SELECT
    pref_name,
    sex, 
    population,
    RANK() OVER
        (PARTITION BY sex
        ORDER BY population DESC)
    AS ranking
FROM
    pop_sex

pref_name	sex	population	ranking
東京	1	250	1
神奈川	1	200	2
福岡	1	150	3
千葉	1	120	4
埼玉	1	100	5
山形	1	50	6
長崎	1	20	7
東京	2	250	1
埼玉	2	150	2
千葉	2	130	3
神奈川	2	100	4
福岡	2	100	4
山形	2	50	6
長崎	2	30	7

DENSE_RANK関数

レコードのランキングを算出する。
同順位が存在した場合、後続の順位が飛ばない。
同様に男女別の県別人口順位を出すことを考える。

SELECT
    pref_name,
    sex, 
    population,
    DENSE_RANK() OVER
        (PARTITION BY sex
        ORDER BY population DESC)
    AS ranking
FROM
    pop_sex

pref_name	sex	population	ranking
東京	1	250	1
神奈川	1	200	2
福岡	1	150	3
千葉	1	120	4
埼玉	1	100	5
山形	1	50	6
長崎	1	20	7
東京	2	250	1
埼玉	2	150	2
千葉	2	130	3
神奈川	2	100	4
福岡	2	100	4
山形	2	50	5
長崎	2	30	6

ROW_NUMBER関数

レコードに一意な番号を付与する。

SELECT
    pref_name,
    sex, 
    population,
    ROW_NUMBER() OVER
        (PARTITION BY sex
        ORDER BY population DESC)
    AS ranking
FROM
    pop_sex

pref_name	sex	population	ranking
東京	1	250	1
神奈川	1	200	2
福岡	1	150	3
千葉	1	120	4
埼玉	1	100	5
山形	1	50	6
長崎	1	20	7
東京	2	250	1
埼玉	2	150	2
千葉	2	130	3
神奈川	2	100	4
福岡	2	100	5
山形	2	50	6
長崎	2	30	7

SUM関数

引数にした列の合計値ではなく、引数にした列の累計を計算する。

SELECT
    pref_name,
    sex, 
    population,
    SUM(population) OVER
        (PARTITION BY sex
        ORDER BY population)
    AS sum
FROM
    pop_sex

pref_name	sex	population	sum
長崎	1	20	20
山形	1	50	70
埼玉	1	100	170
千葉	1	120	290
福岡	1	150	440
神奈川	1	200	640
東京	1	250	890
長崎	2	30	30
山形	2	50	50
福岡	2	100	150
神奈川	2	100	250
千葉	2	130	380
埼玉	2	150	530
東京	2	250	780

フレームオプション

部分集合の中から更に集計範囲を細かく設定できるオプション機能。

ROWS

移動単位を行で設定する

RANGE

移動単位を列の値で設定する
基準となる列はORDER BY句で指定された列

N PRECEDING

カレントレコードに対してNレコード前までを範囲とする

N FOLLOWING

カレントレコードに対してNレコード後までを範囲とする

BETWEEN N1 PRECEDING AND N2 FOLLOWING

カレントレコードに対してN1レコード前からN2レコード後までを範囲とする

UNBOUNDED PRECEDING

無制限に前へ移動する

UNBOUNDED FOLLOWING

無制限に下へ移動する

CURRENT ROW

現在行

例

前後1行ずつのフレームオプションを利用した場合の、男女別の県別人口累計を出すことを考える。
つまり合計3行の合計値を出していく。

SELECT
    pref_name,
    sex, 
    population,
    SUM(population) OVER
        (PARTITION BY sex
        ORDER BY population
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
    AS sum
FROM
    pop_sex

pref_name	sex	population	sum
長崎	1	20	70
山形	1	50	170
埼玉	1	100	270
千葉	1	120	370
福岡	1	150	470
神奈川	1	200	600
東京	1	250	450
長崎	2	30	80
山形	2	50	180
福岡	2	100	250
神奈川	2	100	330
千葉	2	130	380
埼玉	2	150	530
東京	2	250	400

名前付きウィンドウ

1つのSQLにおいて、2か所以上でウィンドウを使用したい場合、名前付きウィンドウを用いることで利用が簡単になる。
以下はウィンドウにWという名前を付けた場合の例

SELECT
    pref_name,
    sex, 
    population,
    SUM(population) OVER W AS sum    
FROM
    pop_sex
WINDOW W AS
    (
    PARTITION BY sex
    ORDER BY population
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    )
;

行間比較

フレームオプションを用いることで、CURRENT ROW以外の行の情報を、CURRENT ROWに持ってくることができる
つまりある行と異なる行で比較(行間比較)ができる
ポイントはウィンドウを1行に限定すること

男女別の県別人口順位と一つ前のレコードとの差分を出す

SELECT
    pref_name,
    sex, 
    population,
    (
    MIN(population) OVER
        (PARTITION BY sex
        ORDER BY population
        ROWS BETWEEN CURRENT ROW AND CURRENT ROW)
    -
    MIN(population) OVER
        (PARTITION BY sex
        ORDER BY population
        ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
    ) as diff
FROM
    pop_sex
;

pref_name	sex	population	diff
東京	1	250	50
神奈川	1	200	50
福岡	1	150	30
千葉	1	120	20
埼玉	1	100	50
山形	1	50	30
長崎	1	20
東京	2	250	100
埼玉	2	150	20
千葉	2	130	30
神奈川	2	100	0
福岡	2	100	50
山形	2	50	20
長崎	2	30