Zenn
📊

SQL 分析関数(window関数)について図解整理する

に公開
SQL
SQL Server
window関数
mssql
分析関数
tech

はじめに

個人的に分析関数はかなり便利と考えてますが、周りでは使用している人が少ない(?)ため、布教も含めて本記事の作成にいたります。

分析関数とは

分析関数（ウィンドウ関数とも呼ばれる）とは、SQLでデータを分析するために利用する関数です。行のグループに対して値を計算し、各行に対して1つの結果を返します。

(AI 検索結果より引用)

ざっくり私の理解でが、FROM句に指定したデータの塊に対して、グループ(パーティション)を区切ってサブクエリとか記述せずになんだかんだ操作できる、便利君という認識です。

分析関数(window関数)の構成要素概要

<分析関数>(<対象列>) OVER (
    [PARTITION BY <区切り列>]
    [ORDER BY <並び替え列>]
    [ROWS/RANGE BETWEEN <開始> AND <終了>]
)
要素 説明
分析関数 実行する関数（例：SUM、ROW_NUMBER、LAG など）
OVER句 分析関数を行単位で適用するために必要な句です。
PARTITION BY データを論理的なグループに分割して関数を適用します。指定しなければ全体を1グループとして扱います。
ORDER BY 各グループ内での順序を定義します。ROW_NUMBER() や LAG() などはこれが必須です。
ROWS/RANGE BETWEEN ウィンドウの範囲（開始〜終了の行）を指定します。省略時は「UNBOUNDED PRECEDING〜CURRENT ROW」等が自動で適用されます。

環境

  • SQL Server 2022
    (本記事ではSQL Server ですが、他のRDBでも考え方は同じで、使えるはず。。)
  • 本記事でのSQL操作はa5m2を使用します。

テスト用テーブル

以下のようなSAMPLE_TB1テーブルがあったとします。
主キーはIDなどがセオリーですが、サンプルの便宜上、名称としています。

-- テーブル作成
CREATE TABLE [dbo].SAMPLE_TB1 (
  PROCESS_NAME varchar(128) NOT NULL 
  ,PROCESS_GROUP_NAME varchar(128) NOT NULL 
  ,IS_COMPLETE BIT NOT NULL DEFAULT 0
  ,PLANNED_QUANTITY INT NOT NULL DEFAULT 0
  ,ACTUAL_QUANTITY INT NOT NULL DEFAULT 0
  , PRIMARY KEY (PROCESS_NAME,PROCESS_GROUP_NAME)
);
-- テストデータのインサート
INSERT INTO [dbo].SAMPLE_TB1(PROCESS_NAME,PROCESS_GROUP_NAME,IS_COMPLETE,PLANNED_QUANTITY,ACTUAL_QUANTITY) VALUES ('工程1','工程グループ1',1,6,6);
INSERT INTO [dbo].SAMPLE_TB1(PROCESS_NAME,PROCESS_GROUP_NAME,IS_COMPLETE,PLANNED_QUANTITY,ACTUAL_QUANTITY) VALUES ('工程2','工程グループ1',0,6,3);
INSERT INTO [dbo].SAMPLE_TB1(PROCESS_NAME,PROCESS_GROUP_NAME,IS_COMPLETE,PLANNED_QUANTITY,ACTUAL_QUANTITY) VALUES ('工程3','工程グループ1',0,6,0);
INSERT INTO [dbo].SAMPLE_TB1(PROCESS_NAME,PROCESS_GROUP_NAME,IS_COMPLETE,PLANNED_QUANTITY,ACTUAL_QUANTITY) VALUES ('工程1','工程グループ2',1,6,6);
INSERT INTO [dbo].SAMPLE_TB1(PROCESS_NAME,PROCESS_GROUP_NAME,IS_COMPLETE,PLANNED_QUANTITY,ACTUAL_QUANTITY) VALUES ('工程2','工程グループ2',1,6,6);
INSERT INTO [dbo].SAMPLE_TB1(PROCESS_NAME,PROCESS_GROUP_NAME,IS_COMPLETE,PLANNED_QUANTITY,ACTUAL_QUANTITY) VALUES ('工程3','工程グループ2',0,9,3);
INSERT INTO [dbo].SAMPLE_TB1(PROCESS_NAME,PROCESS_GROUP_NAME,IS_COMPLETE,PLANNED_QUANTITY,ACTUAL_QUANTITY) VALUES ('工程4','工程グループ2',0,9,0);
INSERT INTO [dbo].SAMPLE_TB1(PROCESS_NAME,PROCESS_GROUP_NAME,IS_COMPLETE,PLANNED_QUANTITY,ACTUAL_QUANTITY) VALUES ('工程1','工程グループ3',1,9,9);
INSERT INTO [dbo].SAMPLE_TB1(PROCESS_NAME,PROCESS_GROUP_NAME,IS_COMPLETE,PLANNED_QUANTITY,ACTUAL_QUANTITY) VALUES ('工程2','工程グループ3',1,12,12);

テーブル定義

テーブル値

データの取得方針

以下のような列をSELECTするケースを考えます。

  1. すべての工程が完了している工程グループをフラグ1として取得する(IS_GROUP_COMPLETE)
  2. 工程グループごとの生産実績合計を取得する(GROUP_ACTUAL_QUANTITY)
  3. 工程グループ内での前工程との生産実績の差分を抽出します(DIFF_ACTUAL_QUANTITY)

分析関数(window関数)を使用してデータを取得する

分析関数では、PARTITION BY をどのように指定するか、が重要！と勝手に思ってます。
PARTITION BY は、データセットを指定した列でグループ化し、各グループごとにウィンドウ関数を計算します。
これにより、全体の集計ではなく、グループ単位で集計や計算を行うことができます。
本記事では、説明が複雑になってしまうので、1つの列に対してPARTITION BYを指定しているのみですが、複数の列を指定することも可能です。

早速ですが、以下は実行するSELECT文です。

SELECT
    PROCESS_NAME 
    , PROCESS_GROUP_NAME  
    , IS_COMPLETE
    , PLANNED_QUANTITY
    , ACTUAL_QUANTITY

    -- 1. すべての工程が完了している工程グループをフラグ1として取得する(IS_GROUP_COMPLETE)
    , MIN(CAST(IS_COMPLETE AS INT)) OVER (PARTITION BY PROCESS_GROUP_NAME) AS IS_GROUP_COMPLETE

    -- 2. 工程グループごとの生産実績合計を取得する(GROUP_ACTUAL_QUANTITY)
    , SUM(ACTUAL_QUANTITY) OVER (PARTITION BY PROCESS_GROUP_NAME) AS GROUP_ACTUAL_QUANTITY

    -- 3. 工程グループ内での前工程との生産実績の差分を抽出します(DIFF_ACTUAL_QUANTITY)
    , ACTUAL_QUANTITY 
      - LAG(ACTUAL_QUANTITY) OVER 
        (PARTITION BY PROCESS_GROUP_NAME ORDER BY PROCESS_GROUP_NAME ,PROCESS_NAME)
      AS DIFF_ACTUAL_QUANTITY 
FROM
    [dbo].SAMPLE_TB1 
ORDER BY 
    PROCESS_GROUP_NAME 
    ,PROCESS_NAME
;

実行結果：

次項で順に図解します。

図解

  1. すべての工程が完了している工程グループをフラグ1として取得する(IS_GROUP_COMPLETE)
    MIN(CAST(IS_COMPLETE AS INT)) OVER (PARTITION BY PROCESS_GROUP_NAME) AS IS_GROUP_COMPLETE
    これだけならWHERE句やGROUP BYの指定でなんとかなるんですが、パフォーマンスを意識した場合やサブクエリをあまり作りたくない場合は分析関数を利用したほうが良いと考えます。
    グループ内のIS_COMPLETE最小値を取得し、抽出しています。

  1. 工程グループごとの生産実績合計を取得する(GROUP_ACTUAL_QUANTITY)
    SUM(ACTUAL_QUANTITY) OVER (PARTITION BY PROCESS_GROUP_NAME) AS GROUP_ACTUAL_QUANTITY

こちらもGROUP BYを指定しずらい場合によく使用します。
グループ内のACTUAL_QUANTITY合計値を抽出しています。

  1. 工程グループ内での前工程との生産実績の差分を抽出します(DIFF_ACTUAL_QUANTITY)
    ACTUAL_QUANTITY - LAG(ACTUAL_QUANTITY) OVER (PARTITION BY PROCESS_GROUP_NAME ORDER BY PROCESS_GROUP_NAME ,PROCESS_NAME) AS DIFF_ACTUAL_QUANTITY

分析関数ならではの集計計算です。LAG関数でグループ内の前レコードを取得し、現在レコードから減算します。このときOVER句内で ORDER BYを指定し、前レコードがずれないように指定してます。

よく使用する分析関数(window関数)例

以下はT-SQLでよく使用する分析関数です。他のSQLでも似たような関数は存在するはずですが、、ご参考まで。

分析関数(window関数) 説明
ROW_NUMBER() 各行に連番を付け、指定した順序で番号を付与します。
RANK() 順位を付け、同じ値があれば同順位となり、順位に空きが発生します。
DENSE_RANK() 順位を付け、同じ値があれば同順位となりますが、順位に空きは発生しません。
NTILE() データを指定した数のグループに分け、それぞれに番号を付けます。
SUM() ウィンドウ内の合計値を計算します。
AVG() ウィンドウ内の平均値を計算します。
MIN() ウィンドウ内の最小値を計算します。
MAX() ウィンドウ内の最大値を計算します。
LAG() 現在の行の前の行の値を取得します。
LEAD() 現在の行の次の行の値を取得します。
FIRST_VALUE() ウィンドウ内の最初の値を取得します。
LAST_VALUE() ウィンドウ内の最後の値を取得します。
COUNT() ウィンドウ内の行数をカウントします。

まとめ

分析関数(window関数)は便利です。積極的に使ってほしいです。特にGROUP BYでサブクエリ作成が必要になった場合など、まずは分析関数でなんとなならないかを検討したほうが後のメンテナンス性も良いかと感じます。
何かのお役に立てれば幸いです。

参考

  • a5m2

https://a5m2.mmatsubara.com/

  • 分析関数（ウィンドウ関数）がよくわからんかったのでまとめた

https://www.niandc.co.jp/tech/20220613_2187/

  • Qiita 分析関数（ウインドウ関数）をわかりやすく説明してみた

https://qiita.com/tlokweng/items/fc13dc30cc1aa28231c5

  • Microsoft Learn 集計関数 (Transact-SQL)

https://learn.microsoft.com/ja-jp/sql/t-sql/functions/aggregate-functions-transact-sql?view=sql-server-ver16

  • Microsoft Learn 分析関数 (Transact-SQL)

https://learn.microsoft.com/ja-jp/sql/t-sql/functions/analytic-functions-transact-sql?view=sql-server-ver16

Discussion