【SQL】ウィンドウ関数で前日比とランキングを取得する

Last updated at 2025-09-26Posted at 2025-09-19

はじめに

ここではウィンドウ関数以外の構文に関する解説はなるべく省いています
DBはMySQLを使います
サンプルデータは以下です
- productsテーブル
- categoriesテーブル
- sales_historiesテーブル

要求1: 商品の売上数の前日比をランキングで出したい

行間比較と言われる一般的な使い方です。
ウィンドウ関数が2回登場し、複雑になるので順を追って説明します。

1. 一時テーブルで日付ごとにグループ化し、前日の売上数を取得

WITH
-- 各商品の日別売上数を計算
daily_sales AS (
  SELECT
    p.id,
    p.name,
    DATE(sh.sold_at) AS sale_date,
    COUNT(*) AS daily_sell_count
  FROM products p
  JOIN categories c ON p.category_id = c.id
  JOIN sales_histories sh ON p.id = sh.product_id
  WHERE sh.sold_at BETWEEN
    -- 前々日から前日までの売上履歴を取得
    '2025-08-01 00:00:00' AND
    '2025-08-02 23:59:59'
  GROUP BY
    p.id,
    p.name,
    DATE(sh.sold_at)
)

SELECT
  id,
  name,
  sale_date,
  COALESCE(daily_sell_count, 0) AS daily_sell_count,
  -- 1回目のウィンドウ関数
  COALESCE(
    LAG(daily_sell_count, 1) OVER (
      PARTITION BY id 
      ORDER BY sale_date
    ), 0
  ) AS prev_day_sell_count
FROM daily_sales
ORDER BY
  id,
  sale_date;

実行結果

id	name	sale_date	daily_sell_count	prev_day_sell_count
1	りんご	2025/08/01	2	0
1	りんご	2025/08/02	1	2
2	ぶどう	2025/08/01	1	0
2	ぶどう	2025/08/02	3	1
3	にんじん	2025/08/01	1	0
3	にんじん	2025/08/02	4	1
4	じゃがいも	2025/08/01	1	0
4	じゃがいも	2025/08/02	1	1
5	しゃけ	2025/08/01	1	0

※ 08/03を起点としています
※ 前日分の売上しかない商品もあるので、値がNULLの場合は0となるようCOALESCEで補っています。

処理の順序を追いながら確認しましょう。

id をPARTITION BY句でグループ化 (このグループ化はGROUP BYとは別物です)
ORDER BY句で sale_date 順に並び替え
LAG関数で daily_sell_count を指定
LAG関数のオフセットの値に1が入力されているので、1つ前の行の値を取得し、prev_day_sell_count カラムに格納

このように、ウィンドウ関数を使えば同じ行に前日分の行の値を持ってくることができます。

2. 前日比を取得し、実際にランキング化する

前日分の値を利用し次の一時テーブルで比率を計算、最後にランキング化します。

WITH
-- 各商品の日別売上数を計算
daily_sales AS (
  SELECT
    p.id,
    p.name,
    DATE(sh.sold_at) AS sale_date,
    COUNT(*) AS daily_sell_count
  FROM products p
  JOIN categories c ON p.category_id = c.id
  JOIN sales_histories sh ON p.id = sh.product_id
  WHERE sh.sold_at BETWEEN
    -- 前々日から前日までの売上履歴を取得
    '2025-08-01 00:00:00' AND
    '2025-08-02 23:59:59'
  GROUP BY
    p.id,
    p.name,
    DATE(sh.sold_at)
),

sales_with_lag AS (
  SELECT
    id,
    name,
    sale_date,
    COALESCE(daily_sell_count, 0) AS daily_sell_count,
    -- 1回目のウィンドウ関数
    COALESCE(
      LAG(daily_sell_count, 1) OVER (
        PARTITION BY id 
        ORDER BY sale_date
      ), 0
    ) AS prev_day_sell_count
  FROM daily_sales
  ORDER BY
    id,
    sale_date
),

-- -------------------- ここまで前のクエリと同じ --------------------

-- 前日比を計算
sales_comparison AS (
  SELECT
    id,
    name,
    sale_date,
    daily_sell_count,
    daily_sell_count - prev_day_sell_count AS increase_count,
    -- 前日比の増加率（前日が0の場合は当日の売上数に応じて計算）
    CASE 
      WHEN prev_day_sell_count = 0 AND daily_sell_count > 0 THEN ROUND(daily_sell_count * 100, 2)
      WHEN prev_day_sell_count = 0 AND daily_sell_count = 0 THEN 0.0
      ELSE ROUND((daily_sell_count - prev_day_sell_count) / prev_day_sell_count * 100, 2)
    END AS increase_rate
  FROM sales_with_lag
)

-- 前日比で増加した商品をランキング順にソート
SELECT
  id,
  name,
  sale_date,
  daily_sell_count,
  increase_count,
  increase_rate,
  -- 2回目のウィンドウ関数
  ROW_NUMBER() OVER (ORDER BY increase_rate DESC) as ranking
FROM sales_comparison
WHERE sale_date = DATE('2025-08-02 23:59:59')
ORDER BY increase_rate DESC;

実行結果

id	name	sale_date	daily_sell_count	increase_count	increase_rate	ranking
3	にんじん	2025/08/02	4	3	300.00	1
2	ぶどう	2025/08/02	3	2	200.00	2
4	じゃがいも	2025/08/02	1	0	0.00	3
1	りんご	2025/08/02	1	-1	-50.00	4
5	しゃけ	2025/08/02	0	-1	-100.00	5

ROW_NUMBER() はウィンドウ関数で並び替えたレコードそれぞれに対し、順番に値を付与する関数です。
これでランキングが完成しました！

さて、先ほどグループ化というワードが出てきましたが、GROUP BY句とのとの違いは何でしょうか。

それは、集約の機能がPARTITION BY句には無いことです。
言わばGROUP BY句から集約の機能を除いてグループ分けの機能だけを残した句というわけです。

この機能とROW_NUMBER() を組み合わせることで、柔軟性の高いランキングクエリを書くことができます。

要求2: 果物と野菜の売上数の前日比ランキングをそれぞれベスト2まで出したい

ベスト2までと言いつつそもそも各グループ2品までしかないのですが、実務ではもっと多くの商品があることでしょう。

2回目のウィンドウ関数はこれだけなら正直ORDER BYとLIMITだけで何とかなりそうなので、あまりメリットを感じないかもしれません。しかし、例えば今回のような要求があったらいかがでしょうか。

こんな時にウィンドウ関数の真価が発揮されます。

WITH
-- 各商品の日別売上数を計算
daily_sales AS (
  SELECT
    p.id,
    p.name,
    DATE(sh.sold_at) AS sale_date,
    COUNT(*) AS daily_sell_count,
    c.name AS category_name
  FROM products p
  JOIN categories c ON p.category_id = c.id
  JOIN sales_histories sh ON p.id = sh.product_id
  WHERE sh.sold_at BETWEEN
    -- 前々日から前日までの売上履歴を取得
    '2025-08-01 00:00:00' AND
    '2025-08-02 23:59:59'
    AND c.name IN ('果物', '野菜')
  GROUP BY
    p.id,
    p.name,
    DATE(sh.sold_at),
    c.name
),

sales_with_lag AS (
  SELECT
    id,
    name,
    sale_date,
    category_name,
    COALESCE(daily_sell_count, 0) AS daily_sell_count,
    -- 1回目のウィンドウ関数
    COALESCE(
      LAG(daily_sell_count, 1) OVER (
        PARTITION BY id 
        ORDER BY sale_date
      ), 0
    ) AS prev_day_sell_count
  FROM daily_sales
  ORDER BY
    id,
    sale_date
),

-- 前日比を計算
sales_comparison AS (
  SELECT
    id,
    name,
    sale_date,
    category_name,
    daily_sell_count,
    prev_day_sell_count,
    daily_sell_count - prev_day_sell_count AS increase_count,
    -- 前日比の増加率（前日が0の場合は当日の売上数に応じて計算）
    CASE 
      WHEN prev_day_sell_count = 0 AND daily_sell_count > 0 THEN ROUND(daily_sell_count * 100, 2)
      WHEN prev_day_sell_count = 0 AND daily_sell_count = 0 THEN 0.0
      ELSE ROUND((daily_sell_count - prev_day_sell_count) / prev_day_sell_count * 100, 2)
    END AS increase_rate
  FROM sales_with_lag
),

-- 前日比で増加した商品をランキング順にソート
product_ranking AS (
  SELECT
    id,
    name,
    sale_date,
    category_name,
    daily_sell_count,
    prev_day_sell_count,
    increase_count,
    increase_rate,
    -- 2回目のウィンドウ関数（カテゴリ別にランキング）
    ROW_NUMBER() OVER (PARTITION BY category_name ORDER BY increase_rate DESC) as ranking
  FROM sales_comparison
  WHERE sale_date = DATE('2025-08-02 23:59:59')
  ORDER BY category_name, increase_rate DESC
)

SELECT *
FROM product_ranking
WHERE ranking <= 2;

実行結果

id	name	sale_date	category_name	daily_sell_count	increase_count	increase_rate	ranking
2	ぶどう	2025/08/02	果物	3	2	200.00	1
1	りんご	2025/08/02	果物	1	-1	-50.00	2
3	にんじん	2025/08/02	野菜	4	3	300.00	1
4	じゃがいも	2025/08/02	野菜	1	0	0.00	2

前のクエリでは2回目のウィンドウ関数にPARTITION BY句を使っていませんでしたが、今回は商品の種類ごとにランキングしないといけないため、PARTITION BY句で category_name を指定しています。

PARTITION BY句で category_name ごとにグループ化できたら、
ORDER BY句で並び替え、
ROW_NUMBER() で数値を付与し、ランキング化

しているというわけです。

まとめ

いかがでしょうか。
一見すると複数の句が入り組んで複雑に見えるウィンドウ関数ですが、紐解いていくと案外難しくないことがわかるかと思います。

またウィンドウ関数の応用として、ページ遷移の計測に使うことができます。
これは分析の頻度も高く、非常に有用な使い方です。

ここでは解説いたしませんが、参考になった記事を以下に掲載しておきます。

それでは、ご覧いただきありがとうございました。

参考

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