ウィンドウ関数
別名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 |