【SQL】完全保存版！ウィンドウ関数の書き方まとめ

どうも、hosigakiです！

ウィンドウ関数と聞くと、なんか難しそうで取っつきにくいですよね...。
そこで今日は、分かりやすくウィンドウ関数の書き方をまとめてみました！

ウィンドウ関数の書き方

今回は分かりやすく説明するために、以下の簡単なテーブルを元に解説します！

フレーム分割 ＆ フレームごとにカウント

SELECT
    *,
    COUNT(days) OVER(PARTITION BY days) AS n
FROM
    purchases 
ORDER BY
    id 

例えば id=4 のレコードは、daysが「2023-01-02」のため、フレームは id=3, id=4, id=5 の3行になります。そして、3行ともにdaysの値があるため、カウントして n=3になります。

フレーム分割 ＆ フレームごとに重複除外でカウント

SELECT
    *,
    COUNT(DISTINCT days) OVER(PARTITION BY days) AS n
FROM
    purchases 
ORDER BY
    id 

ひとつ前の書き方でカウントする際の、重複を除外したバージョンです。

例えば id=4 のレコードは、フレームが id=3, id=4, id=5 の3行になりますが、どの行もdaysの値は「2023-01-02」になります。よって、ユニークのdaysの値をカウントし、n=1となります。

並び替え ＆ 開始行から現在行までをフレーム指定 ＆ フレームごとに合計

SELECT
    *,
    SUM(id) OVER(ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS n
FROM
    purchases 
ORDER BY
    id 

こちらの書き方は、レコードをid順に並べた際の、開始行から現在行までがフレームになります。
例えば id=3 の行の場合、フレームは id=1（開始行）, id=2, id=3（現在行）の三行になります。そのため、フレーム内のidの値を合計して、n=6となります。

なお、こちらの書き方には、以下で紹介する省略形があるので、実務だと省略形の方をよく使います。

上記の省略形Ver.

SELECT
    *,
    SUM(id) OVER(ORDER BY id) AS n
FROM
    purchases 
ORDER BY
    id 

上記の省略バージョンです。
書き方を覚えておくと便利です！

全ての行（開始行から最終行まで）をフレーム指定 ＆ 合計

SELECT
    *,
    SUM(id) OVER(ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS n
FROM
    purchases 
ORDER BY
    id 

こちらの書き方は、id=1 の行でも、id=2 の行でも、どの行も同様に、全ての行（開始行である id=1 から 最終行である id=5 までの5行）がフレームになります。そのため、どの行でも全行のidの値が合算されて、n=15 となります。

なお、こちらの書き方には、以下で紹介する省略形があるので、実務だと省略形の方をよく使います。

上記の省略Ver.

SELECT
    *,
    SUM(id) OVER() AS n
FROM
    purchases 
ORDER BY
    id 

上記の省略バージョンです。
書き方を覚えておくと便利です！

並び替え ＆ 一行上から一行下までをフレーム指定 ＆ フレームごとに合計

SELECT
    *,
    SUM(id) OVER(ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS n
FROM
    purchases 
ORDER BY
    id 

レコードをid順に並べた後で、一行上、現在行、一行下の三行がフレームになる例です。
例えば id=2 の行は、id=1（一行上）, id=2（現在行）, id=3（一行下） の三行がフレームになるため、idの合計は9になります。

一方、id=1 の行は、一行上が存在しないため、id=1（現在行）, id=2（一行下） の二行だけがフレームになり、idの合計は3となります。

フレーム分割 ＆ 並び替え ＆ 現在行から一行下までをフレーム指定 ＆ フレームごとに合計

SELECT
    *,
    SUM(id) OVER(PARTITION BY DAYS ORDER BY id ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS n
FROM
    purchases 
ORDER BY
    id 

id=4 の行は、まずdaysが「2023-01-02」である id=3, id=4, id=5 の三行がフレームになります。次にこの三行をid順に並べます。そこからさらに、現在行と一行下がフレームに指定されるため、最終的なフレームは id=4（現在行）, id=5（一行下） の二行となります。よって、id=4 の行は、n=9 となります。

id=5 の行も同様に、まずdaysが「2023-01-02」である id=3, id=4, id=5 の三行がフレームになります。次にこの三行をid順に並べます。ただし、ここからさらに現在行と一行下がフレームに指定されますが、id=5（現在行） の一行下は存在しないため、最終的なフレームは id=5 のみとなります。よって、id=5 の行は、n=5となります。

フレーム分割 ＆ 並び替え ＆ フレームごとに現在行の一行上の値を取得

SELECT
    *,
    LAG(id, 1) OVER(PARTITION BY days ORDER BY id) AS n
FROM
    purchases 
ORDER BY
    id 

id=4 の行は、daysが「2023-01-02」である id=3, id=4, id=5 の三行がフレームになります。次にこの三行をid順に並べます。この時、id=4（現在行）の一行上は、id=3の行なので、n=3 となります。

同様に、id=3 の行は、daysが「2023-01-02」である id=3, id=4, id=5 の三行がフレームになります。ただし、この三行をid順に並べた際、id=3（現在行）の一行上は存在しない（※）ため、 nはNULL となります。

※「id=3 の行の一行上は、id=2の行じゃないの？」と思われるかもしれませんが、id=3 の行のフレームには、id=2 の行が含まれていないため、id=3 の行の一行上は存在しないと見なされます。

フレーム分割 ＆ 並び替え ＆ フレームごとに現在行の一行下の値を取得

SELECT
    *,
    LEAD(id, 1) OVER(PARTITION BY days ORDER BY id) AS n
FROM
    purchases 
ORDER BY
    id 

id=1 の行は、daysが「2023-01-01」である id=1, id=2 の二行がフレームになります。次に、この二行をid順に並べます。この時、id=1（現在行）の一行下は、id=2の行なので、 n=2 となります。

同様に、id=2 の行は、daysが「2023-01-01」である id=1, id=2 の二行がフレームになります。次に、この二行をid順に並べます。ただし、この場合 id=2（現在行）の一行下は存在しない（※）ため、 nはNULL となります。

※「id=2 の行の一行下は、id=3の行じゃないの？」と思われるかもしれませんが、id=2 の行のフレームには、id=3 の行が含まれていないため、id=2 の行の一行下は存在しないと見なされます。

フレーム分割 ＆ 並び替え ＆ フレームごとに順位付け

SELECT
    *,
    RANK() OVER(PARTITION BY days ORDER BY id) AS n
FROM
    purchases 
ORDER BY
    id 

id=2 の行は、daysが「2023-01-01」である id=1, id=2 の二行がフレームになります。この二行をid順に並べ、上から順番をつけていくため、 id=2 の行はn=2となります。

フレーム分割 ＆ 並び替え ＆ フレームごとに開始行の値を取得

SELECT
    *,
    FIRST_VALUE(id) OVER(PARTITION BY days ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS n
FROM
    purchases 
ORDER BY
    id 

id=2 の行は、daysが「2023-01-01」である id=1, id=2 の二行がフレームになります。次に、この二行をid順に並べます。最後に、このフレーム内の開始行は id=1 の行なので、n=1となります、

フレーム分割 ＆ 並び替え ＆ フレームごとに最終行の値を取得

SELECT
    *,
    LAST_VALUE(id) OVER(PARTITION BY days ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS n
FROM
    purchases 
ORDER BY
    id 

id=2 の行は、daysが「2023-01-01」である id=1, id=2 の二行がフレームになります。次に、この二行をid順に並べます。最後に、このフレームの最終行は id=2 の行なので、n=2 となります、

フレーム分割 ＆ 並び替え ＆ フレームごとに2行目の値を取得

SELECT
    *,
    NTH_VALUE(id, 2) OVER(PARTITION BY days ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS n
FROM
    purchases 
ORDER BY
    id 

id=4 の行は、daysが「2023-01-02」である id=3, id=4, id=5 の三行がフレームになります。次に、この三行をid順に並べます。最後に、このフレーム内の一番上から二行目は id=4 の行なので、n=4 となります、

ウィンドウ関数は少し複雑ですが、慣れるとデータ分析がめちゃくちゃ捗るので、ぜひこの機会に習得してはいかがでしょうか？

最後まで読んでいただきありがとうございました。
また次の記事でお会いしましょう(^^)/