青の統計学-DS Playground-

ウィンドウ関数: ランキングと累積を見る

Stage 4 — 第2章 | データ分析基礎カリキュラム 推定学習時間:35〜45分 | 難易度:★★★★☆


この章で学ぶこと

通常の集計では、GROUP BY によって複数行を1行にまとめます。 一方で実務では、明細行を残したまま「順位」「累計」「前回との差」を見たい場面があります。

この章では、行を残したまま計算できる ウィンドウ関数 を学びます。

この章を終えると、こんなことができるようになります:

  • ROW_NUMBER で順位をつけられる
  • SUM(...) OVER で累計を計算できる
  • LAG で前回注文との差を見られる
  • PARTITION BY と ORDER BY の役割を説明できる

1. GROUP BYとウィンドウ関数の違い

GROUP BY は、行をまとめるための構文です。 顧客ごとの売上を出すと、顧客1人につき1行になります。

SELECT
            customer_id,
            SUM(total_amount) AS total_sales
          FROM orders
          WHERE status = 'completed'
          GROUP BY customer_id;
          

一方、ウィンドウ関数は元の行を残したまま、周辺の行を使って計算します。 注文1件ごとの行を残しながら、顧客内での注文順や累計金額を出せます。


2. ROW_NUMBERで注文順をつける

ROW_NUMBER は、指定した並び順で連番をつける関数です。 次のSQLは、顧客ごとに注文日の古い順で注文番号をつけます。

SELECT
            customer_id,
            order_id,
            order_date,
            total_amount,
            ROW_NUMBER() OVER (
              PARTITION BY customer_id
              ORDER BY order_date ASC
            ) AS order_number
          FROM orders
          WHERE status = 'completed';
          
句 役割
PARTITION BY customer_id 顧客ごとに分ける
ORDER BY order_date ASC 顧客内で注文日順に並べる
ROW_NUMBER() 並び順に連番をつける

「初回注文だけを見る」「2回目注文までの日数を見る」といった分析の入口になります。


3. 累計売上を見る

SUM は集計関数ですが、OVER と組み合わせると累計を作れます。

SELECT
            customer_id,
            order_id,
            order_date,
            total_amount,
            SUM(total_amount) OVER (
              PARTITION BY customer_id
              ORDER BY order_date ASC
            ) AS cumulative_sales
          FROM orders
          WHERE status = 'completed';
          

このSQLでは、顧客ごとに注文日順で売上が足し上げられます。

たとえば、ある顧客の注文が3件ある場合、1行目は初回注文金額、2行目は1回目と2回目の合計、3行目は3回分の合計になります。


4. LAGで前回注文と比較する

LAG は、前の行の値を参照する関数です。 顧客ごとに、前回注文日を取り出す例を見ます。

SELECT
            customer_id,
            order_id,
            order_date,
            LAG(order_date) OVER (
              PARTITION BY customer_id
              ORDER BY order_date ASC
            ) AS previous_order_date
          FROM orders
          WHERE status = 'completed';
          

前回注文日が分かると、注文間隔の分析につながります。

SELECT
            customer_id,
            order_id,
            order_date,
            total_amount,
            LAG(total_amount) OVER (
              PARTITION BY customer_id
              ORDER BY order_date ASC
            ) AS previous_amount
          FROM orders
          WHERE status = 'completed';
          

前回金額との差を見れば、購入単価が上がっているのか、下がっているのかを確認できます。


5. 実務での使いどころ

ウィンドウ関数は、時系列や顧客行動の分析でよく使います。

見たいこと 使う関数の例
顧客ごとの初回注文 ROW_NUMBER()
顧客別の累計購入金額 SUM(...) OVER
前回注文との差 LAG()
商品カテゴリ内の売上順位 RANK()

たとえば商品カテゴリごとの売上順位を出す場合は、商品別に集計した後でランキングをつけます。

WITH product_sales AS (
            SELECT
              p.category,
              p.product_id,
              p.product_name,
              SUM(oi.quantity * oi.unit_price) AS sales
            FROM order_items AS oi
            JOIN products AS p
              ON oi.product_id = p.product_id
            GROUP BY p.category, p.product_id, p.product_name
          )
          SELECT
            category,
            product_name,
            sales,
            RANK() OVER (
              PARTITION BY category
              ORDER BY sales DESC
            ) AS category_rank
          FROM product_sales;
          

実務での使いどころ: 行を残したまま順位や前回値を見る

ウィンドウ関数は、GROUP BYのように行をまとめず、元の行を残したまま順位・累計・前回値を計算できます。 実務では、顧客ごとの初回注文、商品ランキング、前回購入からの日数などでよく使います。

SELECT
            customer_id,
            order_id,
            order_date,
            ROW_NUMBER() OVER (
              PARTITION BY customer_id
              ORDER BY order_date
            ) AS order_number
          FROM orders;
          

このSQLでは、顧客ごとに注文順を付けています。 PARTITION BY は「どの単位で分けるか」、ORDER BY は「どの順番で見るか」を決めます。

使いどころ 関数の例
初回注文を探す ROW_NUMBER()
売上ランキングを作る RANK()
累計売上を見る SUM() OVER
前回注文と比べる LAG()

ウィンドウ関数は便利ですが、並び順を間違えると意味が変わります。 「何の中で、どの順番で計算しているか」を必ず確認しましょう。

ミニ演習

次の分析で使うウィンドウ関数や考え方を選んでください。

  1. 顧客ごとの初回注文を探す。
  2. 月別売上に累計売上を付ける。
  3. 商品カテゴリ内で売上順位を付ける。
  4. PARTITION BY を書き忘れると何が起きるか説明する。

まとめ

構文 意味 例
OVER ウィンドウ関数の範囲を指定する SUM(amount) OVER (...)
PARTITION BY 計算するグループを分ける PARTITION BY customer_id
ORDER BY グループ内の順序を決める ORDER BY order_date
LAG 前の行を参照する LAG(total_amount)

この章のキーメッセージ: ウィンドウ関数は、明細行を残したまま順位や累計を計算するための実務的なSQLです。PARTITION BY で区切り、ORDER BY で順序を決める感覚を身につけましょう。


この章の確認

  1. GROUP BY とウィンドウ関数の違いを説明してください。
  2. 顧客ごとに注文順をつけるには、どの関数を使いますか?
  3. PARTITION BY customer_id は何を意味しますか?
  4. 前回注文金額を取り出すSQLの考え方を説明してください。

関連演習