LoginSignup
6
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@koji_kondoin株式会社NEXT TECHNOLOGY

SQLのウィンドウ関数

Last updated at Posted at 2025-04-25

はじめに

SQLにはデータ分析の幅を拡げる機能としてウィンドウ関数というものが存在しています。
最近、業務で初めて触れる機会があったため、自分が今後も使う気配がするものについての基本事項をまとめていきます。

本記事ではMySQLで確認しています。
MySQLにて確認を行う場合は、ウィンドウ関数に対応している8.0以上のバージョンを利用してください。

また、実行確認にはDB Fiddleというサービスを利用しています。
こちらはブラウザ上で簡単にSQLを実行できるサイトです。

  • 実行日：2025年4月22日
  • MySQLのバージョン：8.4.2
  • 利用ブラウザ：Google Chrome（バージョン 133.0.6943.142）

本記事に書かれていること

ウィンドウ関数とは

ウィンドウ関数は、対象となるテーブルから部分的に切り出したレコードの集合に対して、集計関数や順序関数の処理を適用させる機能です。
ここで言う集計関数とは、MAXやSUMやAVG等、GROUP BY句とともに使って行をまとめて集計操作する関数を指します。ウィンドウ関数では集計関数と同様の計算処理を実行しますが、集計関数と違って単一のレコードにまとめるのではなく、それぞれのレコードごとに処理を実行して結果を返します。
また、順序関数はその名の通り、条件に従って各レコードに順位を付けて返す関数となります。

本記事では「ウィンドウ関数」という呼び方をしておりますが、DBMSや用途によって下記のような呼び方をする場合もあります。

  • 分析関数
  • オンライン分析処理（OLAP）関数

処理の順序

以下がSQL実行時の順序となります。

1．JOIN等の結合処理
2．WHERE句
3．GROUP BY句
4．SELECT句
5．HAVING句
6．ウィンドウ関数 ← ココ
7．ORDER BY句

WHERE句やHAVING句で、ウィンドウ関数の処理結果は利用できないため注意が必要です。
また、ウィンドウ関数で処理したい範囲が決まっていれば、WHERE句の絞り込みで対象のレコードを削減しておくことでクエリのコストが下げられます。

書き方

SELECT句の中で下記のように記述します。

ウィンドウ関数(引数) OVER( [PARTITION BY句] [ORDER BY句] [フレーム句] )
  • OVER句
    テーブルから部分的に切り出したいレコードの範囲を指定します。
    何も指定しない場合はテーブル全体が対象となります。
    　
  • PARTITION BY句
    指定したカラムの値ごとにレコードの部分集合を作ります。
    カラムは複数指定することも可能です。
    　
  • ORDER BY句
    PARTITION BY句で作られたレコードの部分集合の中で、指定した条件にてソートを行います。
    　
  • フレーム句
    ORDER BY句にてソートした結果に対して、どこからどこまでを処理の対象とするかを指定します。指定方法はROWS（レコード数）とRANGE（カラムの値）があります。
    ※ 本記事内の例では使用していません。

サンプルデータ

サンプルとして、店舗ごとの売上が入った簡単なテーブル（SAMPLE_SALES）を作成します。

テーブル作成（SAMPLE_SALES）
CREATE TABLE SAMPLE_SALES (
  SHOP_ID VARCHAR(10), /* 店舗ID */
  SALES_DATE VARCHAR(10), /* 販売日 */
  SALES INT /* 売上 */
);

INSERT INTO SAMPLE_SALES VALUES
	('A0001','2025-04-01',1500),
    ('A0001','2025-04-02',4000),
    ('A0001','2025-04-03',2700),
    ('A0001','2025-04-04',1500),
    ('B0001','2025-04-01',3000),
    ('B0001','2025-04-02',800),
    ('B0001','2025-04-03',1200),
    ('B0001','2025-04-04',5000),
    ('C0001','2025-04-01',4000),
    ('C0001','2025-04-02',3500),
    ('C0001','2025-04-03',4000),
    ('C0001','2025-04-04',6400);

データを投入した結果が下記になります。

データ投入結果（SAMPLE_SALES）
SELECT * FROM SAMPLE_SALES;

| SHOP_ID | SALES_DATE | SALES |
| ------- | ---------- | ----- |
| A0001   | 2025-04-01 | 1500  |
| A0001   | 2025-04-02 | 4000  |
| A0001   | 2025-04-03 | 2700  |
| A0001   | 2025-04-04 | 1500  |
| B0001   | 2025-04-01 | 3000  |
| B0001   | 2025-04-02 | 800   |
| B0001   | 2025-04-03 | 1200  |
| B0001   | 2025-04-04 | 5000  |
| C0001   | 2025-04-01 | 4000  |
| C0001   | 2025-04-02 | 3500  |
| C0001   | 2025-04-03 | 4000  |
| C0001   | 2025-04-04 | 6400  |

MAXとMINの利用例

店舗（SHOP_ID）ごとの売上の最大値（MAX_SALES）と最小値（MIN_SALES）を追加した状態でデータ抽出したい場合、下記のようにMAXとMINが利用できます。

SELECT 
	SHOP_ID,
    SALES_DATE,
    SALES,
    MAX(SALES) OVER(PARTITION BY SHOP_ID) AS MAX_SALES,
    MIN(SALES) OVER(PARTITION BY SHOP_ID) AS MIN_SALES
FROM SAMPLE_SALES;


| SHOP_ID | SALES_DATE | SALES | MAX_SALES | MIN_SALES |
| ------- | ---------- | ----- | --------- | --------- |
| A0001   | 2025-04-01 | 1500  | 4000      | 1500      |
| A0001   | 2025-04-02 | 4000  | 4000      | 1500      |
| A0001   | 2025-04-03 | 2700  | 4000      | 1500      |
| A0001   | 2025-04-04 | 1500  | 4000      | 1500      |
| B0001   | 2025-04-01 | 3000  | 5000      | 800       |
| B0001   | 2025-04-02 | 800   | 5000      | 800       |
| B0001   | 2025-04-03 | 1200  | 5000      | 800       |
| B0001   | 2025-04-04 | 5000  | 5000      | 800       |
| C0001   | 2025-04-01 | 4000  | 6400      | 3500      |
| C0001   | 2025-04-02 | 3500  | 6400      | 3500      |
| C0001   | 2025-04-03 | 4000  | 6400      | 3500      |
| C0001   | 2025-04-04 | 6400  | 6400      | 3500      |

SUMの利用例

全体の売上合計（SUM_ALL_SALES）や各店舗（SHOP_ID）ごとの売上合計（SUM_SHOP_SALES）を追加した状態でデータ抽出したい場合、下記のようにSUMが利用できます。
全体の売上合計は、OVER句に何も指定しないことで出しています。

SELECT 
	SHOP_ID,
    SALES_DATE,
    SALES,
    SUM(SALES) OVER() AS SUM_ALL_SALES,
    SUM(SALES) OVER(PARTITION BY SHOP_ID) AS SUM_SHOP_SALES
FROM SAMPLE_SALES;


| SHOP_ID | SALES_DATE | SALES | SUM_ALL_SALES | SUM_SHOP_SALES |
| ------- | ---------- | ----- | ------------- | -------------- |
| A0001   | 2025-04-01 | 1500  | 37600         | 9700           |
| A0001   | 2025-04-02 | 4000  | 37600         | 9700           |
| A0001   | 2025-04-03 | 2700  | 37600         | 9700           |
| A0001   | 2025-04-04 | 1500  | 37600         | 9700           |
| B0001   | 2025-04-01 | 3000  | 37600         | 10000          |
| B0001   | 2025-04-02 | 800   | 37600         | 10000          |
| B0001   | 2025-04-03 | 1200  | 37600         | 10000          |
| B0001   | 2025-04-04 | 5000  | 37600         | 10000          |
| C0001   | 2025-04-01 | 4000  | 37600         | 17900          |
| C0001   | 2025-04-02 | 3500  | 37600         | 17900          |
| C0001   | 2025-04-03 | 4000  | 37600         | 17900          |
| C0001   | 2025-04-04 | 6400  | 37600         | 17900          |

AVGの利用例

各店舗（SHOP_ID）ごとの売上平均（AVG_SHOP_SALES）や各販売日（SALES_DATE）ごとの売上平均（AVG_DATE_SALES）を追加した状態でデータ抽出したい場合、下記のようにAVGが利用できます。
この例では、ROUNDを利用して小数点以下を丸めて表示しています。

SELECT 
	SHOP_ID,
    SALES_DATE,
    SALES,
    ROUND(AVG(SALES) OVER(PARTITION BY SHOP_ID)) AS AVG_SHOP_SALES,
    ROUND(AVG(SALES) OVER(PARTITION BY SALES_DATE)) AS AVG_DATE_SALES
FROM SAMPLE_SALES;


| SHOP_ID | SALES_DATE | SALES | AVG_SHOP_SALES | AVG_DATE_SALES |
| ------- | ---------- | ----- | -------------- | -------------- |
| A0001   | 2025-04-01 | 1500  | 2425           | 2833           |
| B0001   | 2025-04-01 | 3000  | 2500           | 2833           |
| C0001   | 2025-04-01 | 4000  | 4475           | 2833           |
| A0001   | 2025-04-02 | 4000  | 2425           | 2767           |
| B0001   | 2025-04-02 | 800   | 2500           | 2767           |
| C0001   | 2025-04-02 | 3500  | 4475           | 2767           |
| A0001   | 2025-04-03 | 2700  | 2425           | 2633           |
| B0001   | 2025-04-03 | 1200  | 2500           | 2633           |
| C0001   | 2025-04-03 | 4000  | 4475           | 2633           |
| A0001   | 2025-04-04 | 1500  | 2425           | 4300           |
| B0001   | 2025-04-04 | 5000  | 2500           | 4300           |
| C0001   | 2025-04-04 | 6400  | 4475           | 4300           |

順位付けの例

各店舗（SHOP_ID）ごとに売上（SALES）の高い順に順位付けをしてデータ抽出したい場合、下記のようにRANKやDENSE_RANKやROW_NUMBERが利用できます。

SELECT 
	SHOP_ID,
    SALES_DATE,
    SALES,
    RANK() OVER(PARTITION BY SHOP_ID ORDER BY SALES DESC) AS RANK_SHOP_SALES,
    DENSE_RANK() OVER(PARTITION BY SHOP_ID ORDER BY SALES DESC) AS DENSE_RANK_SHOP_SALES,
    ROW_NUMBER() OVER(PARTITION BY SHOP_ID ORDER BY SALES DESC) AS ROW_SHOP_SALES
FROM SAMPLE_SALES;

| SHOP_ID | SALES_DATE | SALES | RANK_SHOP_SALES | DENSE_RANK_SHOP_SALES | ROW_SHOP_SALES |
| ------- | ---------- | ----- | --------------- | --------------------- | -------------- |
| A0001   | 2025-04-02 | 4000  | 1               | 1                     | 1              |
| A0001   | 2025-04-03 | 2700  | 2               | 2                     | 2              |
| A0001   | 2025-04-01 | 1500  | 3               | 3                     | 3              |
| A0001   | 2025-04-04 | 1500  | 3               | 3                     | 4              |
| B0001   | 2025-04-04 | 5000  | 1               | 1                     | 1              |
| B0001   | 2025-04-01 | 3000  | 2               | 2                     | 2              |
| B0001   | 2025-04-03 | 1200  | 3               | 3                     | 3              |
| B0001   | 2025-04-02 | 800   | 4               | 4                     | 4              |
| C0001   | 2025-04-04 | 6400  | 1               | 1                     | 1              |
| C0001   | 2025-04-01 | 4000  | 2               | 2                     | 2              |
| C0001   | 2025-04-03 | 4000  | 2               | 2                     | 3              |
| C0001   | 2025-04-02 | 3500  | 4               | 3                     | 4              |

RANKとDENSE_RANKには次のような違いがあります。

  • RANK：同じ値の結果に同じ順位が付けられた分だけ、次の結果までの間に順位の抜けができる
  • DENSE_RANK：同じ値の結果に同じ順位がどれだけ付けられても、次の結果にはその次の順位が付く（抜けが発生しない）

ROW_NUMBERはRANKと違って同じ番号が付くことは無いため、ソートの条件を整えることでデータが一意となるように順位を付けることができます。

どのように順位付けをしたいかで状況に応じて使い分けましょう。

ウィンドウに名前を付ける

OVER句で指定する範囲は、WINDOW句を利用して名前を付けることもできます。
WINDOW句は次のように記述します。

書き方
WINDOW ウィンドウ名 AS ( [PARTITION BY句] [ORDER BY句] )

下記の例のように複数のウィンドウ関数を同じ範囲指定で利用する際には、WINDOW句で名前を付けておくことで記述が少しスッキリするだけでなく、変更が発生した場合も1箇所の修正で済みます。

① ウィンドウ名を付けない場合
SELECT 
	SHOP_ID,
    SALES_DATE,
    SALES,
    MAX(SALES) OVER(PARTITION BY SHOP_ID) AS MAX_SALES,
    MIN(SALES) OVER(PARTITION BY SHOP_ID) AS MIN_SALES,
    SUM(SALES) OVER(PARTITION BY SHOP_ID) AS SUM_SHOP_SALES,
    ROUND(AVG(SALES) OVER(PARTITION BY SHOP_ID)) AS AVG_SHOP_SALES,
    ROW_NUMBER() OVER(PARTITION BY SHOP_ID ORDER BY SALES DESC) AS ROW_SHOP_SALES
FROM SAMPLE_SALES;


| SHOP_ID | SALES_DATE | SALES | MAX_SALES | MIN_SALES | SUM_SHOP_SALES | AVG_SHOP_SALES | ROW_SHOP_SALES |
| ------- | ---------- | ----- | --------- | --------- | -------------- | -------------- | -------------- |
| A0001   | 2025-04-02 | 4000  | 4000      | 1500      | 9700           | 2425           | 1              |
| A0001   | 2025-04-03 | 2700  | 4000      | 1500      | 9700           | 2425           | 2              |
| A0001   | 2025-04-01 | 1500  | 4000      | 1500      | 9700           | 2425           | 3              |
| A0001   | 2025-04-04 | 1500  | 4000      | 1500      | 9700           | 2425           | 4              |
| B0001   | 2025-04-04 | 5000  | 5000      | 800       | 10000          | 2500           | 1              |
| B0001   | 2025-04-01 | 3000  | 5000      | 800       | 10000          | 2500           | 2              |
| B0001   | 2025-04-03 | 1200  | 5000      | 800       | 10000          | 2500           | 3              |
| B0001   | 2025-04-02 | 800   | 5000      | 800       | 10000          | 2500           | 4              |
| C0001   | 2025-04-04 | 6400  | 6400      | 3500      | 17900          | 4475           | 1              |
| C0001   | 2025-04-01 | 4000  | 6400      | 3500      | 17900          | 4475           | 2              |
| C0001   | 2025-04-03 | 4000  | 6400      | 3500      | 17900          | 4475           | 3              |
| C0001   | 2025-04-02 | 3500  | 6400      | 3500      | 17900          | 4475           | 4              |
② ウィンドウ名を付けてみた場合
SELECT 
	SHOP_ID,
    SALES_DATE,
    SALES,
    MAX(SALES) OVER(WIN01) AS MAX_SALES,
    MIN(SALES) OVER(WIN01) AS MIN_SALES,
    SUM(SALES) OVER(WIN01) AS SUM_SHOP_SALES,
    ROUND(AVG(SALES) OVER(WIN01)) AS AVG_SHOP_SALES,
    ROW_NUMBER() OVER(WIN01 ORDER BY SALES DESC) AS ROW_SHOP_SALES
FROM SAMPLE_SALES
WINDOW WIN01 AS (PARTITION BY SHOP_ID);


| SHOP_ID | SALES_DATE | SALES | MAX_SALES | MIN_SALES | SUM_SHOP_SALES | AVG_SHOP_SALES | ROW_SHOP_SALES |
| ------- | ---------- | ----- | --------- | --------- | -------------- | -------------- | -------------- |
| A0001   | 2025-04-02 | 4000  | 4000      | 1500      | 9700           | 2425           | 1              |
| A0001   | 2025-04-03 | 2700  | 4000      | 1500      | 9700           | 2425           | 2              |
| A0001   | 2025-04-01 | 1500  | 4000      | 1500      | 9700           | 2425           | 3              |
| A0001   | 2025-04-04 | 1500  | 4000      | 1500      | 9700           | 2425           | 4              |
| B0001   | 2025-04-04 | 5000  | 5000      | 800       | 10000          | 2500           | 1              |
| B0001   | 2025-04-01 | 3000  | 5000      | 800       | 10000          | 2500           | 2              |
| B0001   | 2025-04-03 | 1200  | 5000      | 800       | 10000          | 2500           | 3              |
| B0001   | 2025-04-02 | 800   | 5000      | 800       | 10000          | 2500           | 4              |
| C0001   | 2025-04-04 | 6400  | 6400      | 3500      | 17900          | 4475           | 1              |
| C0001   | 2025-04-01 | 4000  | 6400      | 3500      | 17900          | 4475           | 2              |
| C0001   | 2025-04-03 | 4000  | 6400      | 3500      | 17900          | 4475           | 3              |
| C0001   | 2025-04-02 | 3500  | 6400      | 3500      | 17900          | 4475           | 4              |

上記では各店舗（SHOP_ID）ごとのデータ抽出を実行していますが、各販売日（SALES_DATE）ごとの抽出に変更が必要になったとします。
そのような場合には、WIN01の中のPARTITION BY句で指定しているカラムを、SHOP_IDからSALES_DATEへ変えて実行するだけで修正完了です。

③ ウィンドウ（WIN01）の範囲指定を変更してみた場合
SELECT 
	SHOP_ID,
    SALES_DATE,
    SALES,
    MAX(SALES) OVER(WIN01) AS MAX_SALES,
    MIN(SALES) OVER(WIN01) AS MIN_SALES,
    SUM(SALES) OVER(WIN01) AS SUM_SHOP_SALES,
    ROUND(AVG(SALES) OVER(WIN01)) AS AVG_SHOP_SALES,
    ROW_NUMBER() OVER(WIN01 ORDER BY SALES DESC) AS ROW_SHOP_SALES
FROM SAMPLE_SALES
WINDOW WIN01 AS (PARTITION BY SALES_DATE);


| SHOP_ID | SALES_DATE | SALES | MAX_SALES | MIN_SALES | SUM_SHOP_SALES | AVG_SHOP_SALES | ROW_SHOP_SALES |
| ------- | ---------- | ----- | --------- | --------- | -------------- | -------------- | -------------- |
| C0001   | 2025-04-01 | 4000  | 4000      | 1500      | 8500           | 2833           | 1              |
| B0001   | 2025-04-01 | 3000  | 4000      | 1500      | 8500           | 2833           | 2              |
| A0001   | 2025-04-01 | 1500  | 4000      | 1500      | 8500           | 2833           | 3              |
| A0001   | 2025-04-02 | 4000  | 4000      | 800       | 8300           | 2767           | 1              |
| C0001   | 2025-04-02 | 3500  | 4000      | 800       | 8300           | 2767           | 2              |
| B0001   | 2025-04-02 | 800   | 4000      | 800       | 8300           | 2767           | 3              |
| C0001   | 2025-04-03 | 4000  | 4000      | 1200      | 7900           | 2633           | 1              |
| A0001   | 2025-04-03 | 2700  | 4000      | 1200      | 7900           | 2633           | 2              |
| B0001   | 2025-04-03 | 1200  | 4000      | 1200      | 7900           | 2633           | 3              |
| C0001   | 2025-04-04 | 6400  | 6400      | 1500      | 12900          | 4300           | 1              |
| B0001   | 2025-04-04 | 5000  | 6400      | 1500      | 12900          | 4300           | 2              |
| A0001   | 2025-04-04 | 1500  | 6400      | 1500      | 12900          | 4300           | 3              |

まとめ

簡単にではありますが、SQLのウィンドウ関数についてまとめてみました。
本記事に書かれている内容はほんの一部ですので、他にどんな機能や使い方があるのか、
ウィンドウ関数の全貌について興味があるという方は、各DBMSの公式ドキュメント等も併せてご確認くださると幸いです。

参考資料

6
3
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
Sign upLogin
6
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?