オフグリッドAdvent Calendar 2024

株式会社オフグリッド

SQL　ウィンドウ関数についてまとめる

Last updated at 2024-12-16Posted at 2024-12-16

SQLのウィンドウ関数について、その基本をまとめます。

環境

PostgreSQL 15.6

今回使用したテーブルのCREATEとINSERT文

CREATE_INSERT.sal

CREATE TABLE 売上 (
    売上ID SERIAL PRIMARY KEY,
    売上日 DATE,
    商品名 VARCHAR(50),
    社員名 VARCHAR(50),
    売上金額 INT 
);

INSERT INTO 売上 (売上日, 商品名, 社員名, 売上金額) VALUES
('2024-12-01', 'ノートPC', 'John', 100000),
('2024-12-01', 'スマホ', 'Emily', 150050), 
('2024-12-02', 'ノートPC', 'John', 80075),
('2024-12-02', 'タブレット', 'Michael', 200000),     
('2024-12-03', 'スマホ', 'Emily', 120025),
('2024-12-04', 'ノートPC', 'Michael', 95000),
('2024-12-05', 'タブレット', 'John', 180050),
('2024-12-05', 'スマホ', 'Emily', 130075),   
('2024-12-06', 'ノートPC', 'Michael', 110000),
('2024-12-07', 'タブレット', 'John', 220000),        
('2024-12-08', 'スマホ', 'Emily', 160000),
('2024-12-10', 'ノートPC', 'Michael', 90050),   
('2024-12-11', 'タブレット', 'John', 210025),
('2024-12-11', 'スマホ', 'Emily', 140075),   
('2024-12-13', 'ノートPC', 'Michael', 105000),  
('2024-12-14', 'タブレット', 'John', 175000),
('2024-12-15', 'スマホ', 'Emily', 155000),     
('2024-12-16', 'ノートPC', 'Michael', 98000),
('2024-12-17', 'タブレット', 'John', 190000);

ウィンドウ関数とは

ウィンドウ関数は現在の行に何らかとも関係するテーブル行の集合に渡って計算を行います。これは集約関数により行われる計算の形式と似たようなものです。

通常のクエリでは、複雑になってしまいがちな、行間比較をおこない、行ごとに集計しその結果を各行へ返すことができます。また、ウィンドウ関数を使用せず、同様のデータを取得しようとするとクエリが複雑になってしまいがちです。

ウィンドウ関数の基本的な構文は下記の通りです。

ウィンドウ関数(式) OVER ([PARTITION BY 列リスト] [ORDER BY 列リスト [フレーム句]])

ウィンドウ関数(式)：使用したいウィンドウ関数（例：SUM,AVG,RANK,LAGなど）
PARTITION BY：行をグループ化するための列を指定。この句がない場合、全ての行を一つのグループとして扱う。
OVER()：ウィンドウ関数を使用することの宣言のようなもの
ORDER BY：集計や計算を行う際に、どの順序で行を処理するかを指定(下記のサンプルの場合、売上idの昇順で計算処理を行う)
フレーム句：ウィンドウフレームを定義し、どの行が計算に含まれるかを指定する。
例えば、ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWなどがあります。これは、先頭の行から（UNBOUNDED PRECEDING）から現在行（CURRENT ROW）までを集計対象とするとの指定です。

下記のクエリでは、売上データに対して移動平均を計算しています。

売上額の移動総平均.sql

SELECT
    売上日
    , 商品名
    , 売上金額
    , avg(売上金額) OVER (ORDER BY 売上id ROWS BETWEEN UNBOUNDED PRECEDING  AND CURRENT ROW) AS 移動総平均
    FROM 売上
ORDER BY 売上日

ウィンドウ関数を用いない場合の例として下記のようなクエリが考えられます。

ウィンドウ関数を使わない.sql

SELECT
        s1.売上日
        , s1.商品名
        , s1.売上金額
        , ( 
            SELECT
                    AVG(s2.売上金額) 
                FROM
                    売上 s2 
                WHERE
                    s2.売上id <= s1.売上id
        ) AS 移動総平均 
    FROM
        売上 s1 
    ORDER BY
        s1.売上日

■ それぞれの比較

パフォーマンス: ウィンドウ関数を使用した方が一般的にはパフォーマンスが良いことが多い。
可読性: ウィンドウ関数の方がシンプルで可読性が高い。
柔軟性: サブクエリは特定の条件に基づいて集計を行う際に柔軟性がある。（半面、複雑になってしまう）

主なウィンドウ関数

関数	説明
SUM()	合計値を計算
AVG()	平均値を計算
COUNT()	行数をカウント
MIN()	最小値を取得
MAX()	最大値を取得
ROW_NUMBER()	行番号を付与
RANK()	同じ値に対して同じランクを付与し、次のランクにはスキップが発生
DENSE_RANK()	同じ値に対して同じランクを付与し、次のランクにはスキップが発生しない
LEAD()	次の行の値を取得
LAG()	前の行の値を取得
FIRST_VALUE()	ウィンドウ内で最初の値を取得
LAST_VALUE()	ウィンドウ内で最後の値を取得
NTH_VALUE(,n)	ウィンドウ内でn番目の値を取得

いくつか例をまとめます。

■ RANKの例
売上合計高い順で社員のランク付けを行える。

RANK.sql

SELECT
        社員名
        , sum(売上金額) AS 合計売上額
        , RANK() OVER (ORDER BY SUM(売上金額) DESC) 
    FROM
        売上 
    GROUP BY
        社員名 
    ORDER BY
        合計売上額 DESC

■ LAGの例
社員ごとの前回売上日を取得

LAG.sql

SELECT
        社員名
        , 商品名
        , 売上日
        , LAG(売上日) OVER (PARTITION BY 社員名 ORDER BY 売上日) AS 前回売上日 
    FROM
        売上 
    ORDER BY
        社員名

■ FIRST_VALUEの例
その月の初回販売日を取得

FIRST_VALUE.sql

SELECT
        社員名
        , 商品名
        , 売上日
        , FIRST_VALUE(売上日) OVER (PARTITION BY 社員名 ORDER BY 売上日) AS 初回売上日 
    FROM
        売上 
    ORDER BY
        社員名

フレーム句について

フレーム句の指定は、ウィンドウ関数においてウィンドウ関数の計算が、どの行に対して行われるか制御するために使用される重要な機能です。
フレーム句の基本構文は下記の通りです。

frame_mode frame_start もしくは frame_mode BETWEEN frame_start AND frame_end

frame_startとframe_end の指定方法

キーワード	説明
UNBOUNDED PRECEDING	先頭の行（frame_endでは使えない）
UNBOUNDED FOLLOWING	末尾の行（frame_startでは使えない）
CURRENT ROW	現在行
n PRECEDING	現在行よりn行前、RANGEの場合はn値前
n FOLLOWING	現在行よりn行後、RANGEの場合はn値後

フレームモード

■ ROWS
行数に基づいてフレームを定義します。
例えば、ROWS BETWEEN 1 PRECEDING AND CURRENT ROW は、現在の行とその1行前の行を含むフレームを指定します。

■ RANGE
値に基づいてフレームを定義します。
下記のsample1.sqlでは、「現在行の日付から2日前の日付のデータのみを対象」で最小の売上日を取得します。対象データがない場合は、nullとなります。

sample1.sql

SELECT
        社員名
        , 商品名
        , 売上日
        , MIN(売上日) OVER ( 
                ORDER BY
                    売上日 RANGE BETWEEN INTERVAL '2 day' PRECEDING AND '2 day' PRECEDING
        ) AS "2日前の売上日" 
    FROM
        売上 
    ORDER BY
        売上日

sample2.sqlでは、「現在行の日付から2日前までの範囲」で最小の売上日を取得(現在行の日付とその前の2日間（合計で3日間）の売上日の最大値)となります。

sample2.sql

SELECT
        社員名
        , 商品名
        , 売上日
        , MIN(売上日) OVER ( 
                ORDER BY
                    売上日 RANGE BETWEEN INTERVAL '2 day' PRECEDING AND CURRENT ROW
        )
    FROM
        売上 
    ORDER BY
        売上日

フレーム句がない場合の動作

■ ORDER BYが指定されている場合　
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWが適応
先頭の行から現在行までが対象
■ ORDER BYが省略された場合　
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGが適応
全体（先頭の行から末尾の行）が対象

参考書籍

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

SQL ウィンドウ関数についてまとめる

環境