LoginSignup
0
0

ウィンドウ関数を理解してSQLマスターになろう

Posted at

ウィンドウ関数

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

仕様

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

構文

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

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

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

PATITION BY句

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

ORDER BY句

image.png
分割した部分集合に対して,どのような順序で計算を行うかを指定する。
ウィンドウ関数においての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
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0