🪟

【SQL】ウィンドウ関数でデータを集計する

2023/06/26に公開

はじめに

上司「資料作成に必要だから、昨年度の部署ごとの単月売上データを出してほしい。あと売り上げの推移がわかるように累計を出してほしい。」

あなたはこう言われた時、どのようにデータを集計しますか？
~~私は部署ごとの 1 年分の売上データを出した後にエクセルに貼り付けて計算します。~~
SQL にはウィンドウ関数という便利な機能があり、上記のデータを一発で取り出すことができます。
この記事ではウィンドウ関数の基本的な使い方と、そのメリットについて解説します。
これまでウィンドウ関数を使ったことがない方は、ぜひ本記事を読んで参考にしていただけますと幸いです。

サンプルデータ

SQL のサンプルデータとして以下のデータを使用します。
データは ChatGPT に作成してもらいました（便利！！）。

CREATE TABLE sales (
    id INT PRIMARY KEY,
    department VARCHAR(50),
    sale_date DATE,
    sales_amount INT
);


INSERT INTO sales (id, department, sale_date, sales_amount) VALUES
(1, '部署A', '2020-04-30', 18000),
(2, '部署B', '2020-04-30', 22000),
(3, '部署A', '2020-05-31', 32000),
(4, '部署B', '2020-05-31', 33000),
(5, '部署A', '2020-06-30', 21000),
(6, '部署B', '2020-06-30', 21000),
(7, '部署A', '2020-07-31', 24000),
(8, '部署B', '2020-07-31', 25000),
(9, '部署A', '2020-08-31', 26000),
(10, '部署B', '2020-08-31', 28000),
(11, '部署A', '2020-09-30', 28000),
(12, '部署B', '2020-09-30', 26000),
(13, '部署A', '2020-10-31', 23000),
(14, '部署B', '2020-10-31', 27000),
(15, '部署A', '2020-11-30', 35000),
(16, '部署B', '2020-11-30', 32000),
(17, '部署A', '2020-12-31', 30000),
(18, '部署B', '2020-12-31', 30000),
(19, '部署A', '2021-01-31', 31000),
(20, '部署B', '2021-01-31', 31000),
(21, '部署A', '2021-02-28', 29000),
(22, '部署B', '2021-02-28', 33000),
(23, '部署A', '2021-03-31', 27000),
(24, '部署B', '2021-03-31', 28000),
(25, '部署A', '2021-04-30', 32000),
(26, '部署B', '2021-04-30', 31000),
(27, '部署A', '2021-05-31', 25000),
(28, '部署B', '2021-05-31', 35000),
(29, '部署A', '2021-06-30', 33000),
(30, '部署B', '2021-06-30', 32000),
(31, '部署A', '2021-07-31', 28000),
(32, '部署B', '2021-07-31', 30000),
(33, '部署A', '2021-08-31', 31000),
(34, '部署B', '2021-08-31', 28000),
(35, '部署A', '2021-09-30', 29000),
(36, '部署B', '2021-09-30', 26000),
(37, '部署A', '2021-10-31', 35000),
(38, '部署B', '2021-10-31', 33000),
(39, '部署A', '2021-11-30', 24000),
(40, '部署B', '2021-11-30', 31000),
(41, '部署A', '2021-12-31', 30000),
(42, '部署B', '2021-12-31', 30000),
(43, '部署A', '2022-01-31', 33000),
(44, '部署B', '2022-01-31', 32000),
(45, '部署A', '2022-02-28', 28000),
(46, '部署B', '2022-02-28', 29000),
(47, '部署A', '2022-03-31', 31000),
(48, '部署B', '2022-03-31', 31000);

以下の記事で、Docker で MySQL の環境構築方法を解説しているので、よければ参考にしてみてください。
記事に記載しているリポジトリに、上記の SQL ファイルも追加しています。

ウィンドウ関数の基本形

ウィンドウ関数の基本形は以下の形になります。

<ウィンドウ関数> OVER ( PARTITION BY <列リスト> ORDER BY <ソート用列リスト> )

ウィンドウ関数

ウィンドウ関数に集約関数(SUM, AVG, COUNT, MAX, MIN)や RANK といった専用の関数を使います。

PARTITION BY

PARTITION BY には集計するグループとなる列を指定します。
これは GROUP BY 句と同様のイメージですが、データは一行に集約されることはなく、もともとある行数分だけ出力されます。
PARTITION BY 句は必須ではなく、もし PARTITION BY 句が無い場合はテーブル全体を集計します。

ORDER BY

ORDER BY 句はデータを出力する順序を指定します。
これは通常の SQL でも使用する ORDER BY 句と同様の働きをし、昇順/降順（asc/desc）を指定することも可能です。

累計を計算する

それでは冒頭で依頼があったデータを抽出します。
SQL は以下のようになります。

SELECT
  department AS '部署',
  sale_date AS '年月',
  sales_amount AS '売上',
  SUM(sales_amount) OVER (PARTITION BY department ORDER BY sale_date) AS '累計売上'
FROM
  sales
WHERE
  sale_date BETWEEN '2021-4-1' AND  '2022-3-31'
ORDER BY
  department,
  sale_date

このように SUM 関数を使うことで売上額の累計を算出することができます。
出力されるデータは次のようになります。

部署	年月日	売上	累計売上
部署 A	2021-04-30	32000	32000
部署 A	2021-05-31	25000	57000
部署 A	2021-06-30	33000	90000
部署 A	2021-07-31	28000	118000
部署 A	2021-08-31	31000	149000
部署 A	2021-09-30	29000	178000
部署 A	2021-10-31	35000	213000
部署 A	2021-11-30	24000	237000
部署 A	2021-12-31	30000	267000
部署 A	2022-01-31	33000	300000
部署 A	2022-02-28	28000	328000
部署 A	2022-03-31	31000	359000
部署 B	2021-04-30	31000	31000
部署 B	2021-05-31	35000	66000
部署 B	2021-06-30	32000	98000
部署 B	2021-07-31	30000	128000
部署 B	2021-08-31	28000	156000
部署 B	2021-09-30	26000	182000
部署 B	2021-10-31	33000	215000
部署 B	2021-11-30	31000	246000
部署 B	2021-12-31	30000	276000
部署 B	2022-01-31	32000	308000
部署 B	2022-02-28	29000	337000
部署 B	2022-03-31	31000	368000

順位をつける

上司「どの月の売り上げが多かったか知りたいから、年度内でその月の売上が何番目に多かったか、順位をつけてほしい。」
提出物に対して追加の要望が出てくるのはよくあることですよね。
エクセルにも rank 関数がありますが、ウィンドウ関数でも rank 関数を使うことで順位を簡単につけることができます。

RANK() OVER (PARTITION BY department ORDER BY sales_amount DESC) AS '売上順位'

このように記述することで、一番売り上げが多かった月を 1 位として順位をつけることができます。

移動平均を計算する

上司「あと、月毎に直近二ヶ月を含めた 3 ヶ月分の平均売り上げも出してほしい。」
注文が増えてきましたがウィンドウ関数では、移動平均を出すことも可能です。
移動平均を求める SQL は次のとおりです。

AVG(sales_amount) OVER (PARTITION BY department ORDER BY sale_date ROWS 2 PRECEDING)

ROWS 2 PRECEDINGとすることで、自分より前の 2 行を含んだ直近 3 行の集計を行います。
また FORROWING を使うと自分より後ろの行を集計に含めることができます。
前後 1 行ずつを集計に含める場合は次のようになります。

ROWS 1 PRECEDING AND 1 FORROWING

前年比を計算する

上司「前年同月比も追加して。」
部下「...」

月毎に順番にデータが並んでいる場合 12 行前（12 ヶ月前）のデータと比較することで前年比を求めることができます。
N 行前のデータを取得するには LAG 関数を用います。
LAG 関数は次のように記述することで N 行前のデータを取得することができます。

LAG(<列リスト>, N)

前年比を出力する SELECT 文は以下のとおりです。

sales_amount / lag(sales_amount, 12) OVER (PARTITION BY department ORDER BY sale_date) * 100

※前年データと比較するので WHERE 句では前年のデータも範囲に含める必要があります。

出力されたデータ

以上の SQL とデータをまとめると次のようになります。
※3 ヶ月平均と前年同月比は前年度のデータも取得する必要があるため、サブクエリで結合しています。

SQL

SELECT
  s2.department AS '部署',
  s2.sale_date AS '年月日',
  s2.sales_amount AS '売上',
  s2.sales_sum AS '累計売上',
  s2.sales_rank AS '売上順位',
  s3.sales_avg AS '3ヶ月平均',
  s3.YoY AS '前年同月比'
FROM
  (
    SELECT
      department,
      sale_date,
      sales_amount,
      SUM(sales_amount) OVER (PARTITION BY department ORDER BY sale_date) AS sales_sum,
      RANK() OVER (PARTITION BY department ORDER BY sales_amount DESC) AS sales_rank
    FROM
      sales s
    WHERE
      sale_date BETWEEN '2021-4-1' AND '2022-3-31'
    order by
      department,
      sale_date
  ) s2
INNER JOIN
  (
    SELECT
      department,
      sale_date,
      ROUND(AVG(sales_amount) OVER (PARTITION BY department ORDER BY sale_date ROWS 2 PRECEDING), 1) AS sales_avg,
      CONCAT(ROUND(sales_amount / LAG(sales_amount, 12) OVER (PARTITION BY department ORDER BY sale_date) * 100, 1),'%') AS YoY
    FROM
      sales
    WHERE
      sale_date BETWEEN '2020-4-1' AND '2022-3-31'
    ORDER BY
      department,
      sale_date
  ) s3
  ON s2.department = s3.department
  AND s2.sale_date = s3.sale_date
WHERE
  s2.sale_date BETWEEN '2021-04-01' AND '2023-03-31'
ORDER BY
  s2.department,
  s2.sale_date;

出力されたデータ

部署	年月日	売上	累計売上	順位	3 ヶ月平均	前年同月比
部署 A	2021-04-30	32000	32000	4	29333.3	177.8%
部署 A	2021-05-31	25000	57000	11	28000.0	78.1%
部署 A	2021-06-30	33000	90000	2	30000.0	157.1%
部署 A	2021-07-31	28000	118000	9	28666.7	116.7%
部署 A	2021-08-31	31000	149000	5	30666.7	119.2%
部署 A	2021-09-30	29000	178000	8	29333.3	103.6%
部署 A	2021-10-31	35000	213000	1	31666.7	152.2%
部署 A	2021-11-30	24000	237000	12	29333.3	68.6%
部署 A	2021-12-31	30000	267000	7	29666.7	100.0%
部署 A	2022-01-31	33000	300000	2	29000.0	106.5%
部署 A	2022-02-28	28000	328000	9	30333.3	96.6%
部署 A	2022-03-31	31000	359000	5	30666.7	114.8%
部署 B	2021-04-30	31000	31000	5	30666.7	140.9%
部署 B	2021-05-31	35000	66000	1	31333.3	106.1%
部署 B	2021-06-30	32000	98000	3	32666.7	152.4%
部署 B	2021-07-31	30000	128000	8	32333.3	120.0%
部署 B	2021-08-31	28000	156000	11	30000.0	100.0%
部署 B	2021-09-30	26000	182000	12	28000.0	100.0%
部署 B	2021-10-31	33000	215000	2	29000.0	122.2%
部署 B	2021-11-30	31000	246000	5	30000.0	96.9%
部署 B	2021-12-31	30000	276000	8	31333.3	100.0%
部署 B	2022-01-31	32000	308000	3	31000.0	103.2%
部署 B	2022-02-28	29000	337000	10	30333.3	87.9%
部署 B	2022-03-31	31000	368000	5	30666.7	110.7%

ウィンドウ関数のなにがうれしいのか？

ウィンドウ関数自体は標準 SQL で規定されていますが、各 RDBMS でサポートされるようになったのは 2000 年代以降であり、MySQL も 8.0 から追加されています。
ウィンドウ関数が登場する以前は、行間の比較を行うような SQL を記述するのは相関サブクエリを使う方法が主流だったようですが、ウィンドウ関数を利用することで以下のようなメリットがあります。

相関サブクエリと比較して、簡潔に記述できるので可読性が高い
テーブルに対するスキャンが一度だけで済むので、パフォーマンスが高い

以上、ウィンドウ関数の解説を行いました。
少しでも参考になりましたら幸いです。

はじめに

サンプルデータ

ウィンドウ関数の基本形

ウィンドウ関数

PARTITION BY

ORDER BY

累計を計算する

順位をつける

移動平均を計算する

前年比を計算する

出力されたデータ

SQL

出力されたデータ

ウィンドウ関数のなにがうれしいのか？

Discussion