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

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';
          

「初回注文だけを見る」「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. 実務での使いどころ

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

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

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 は「どの順番で見るか」を決めます。

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

ミニ演習

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

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

まとめ

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

この章の確認

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

関連演習

• SQL集計の基礎
• SQL集計（GROUP BY）
• SQL結合の基礎