GROUP BY: カテゴリ別に集計する

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

この章で学ぶこと

前章では、COUNT や SUM を使って、テーブル全体から1つの指標を作りました。 しかし実務の分析では、全体の数字だけでは足りないことがよくあります。

たとえば、ECサイトで次のような問いに答えるには、カテゴリ別・地域別・ステータス別に集計する必要があります。

• 商品カテゴリごとの売上はいくらか
• 都道府県ごとの顧客数は何人か
• 注文ステータスごとの件数はどう分かれているか
• 顧客ごとの購入回数は何回か

この章では、カテゴリ別に集計するための GROUP BY を学びます。

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

• GROUP BY でカテゴリ別の集計表を作れる
• 集計単位と行の粒度を説明できる
• COUNT, SUM, AVG と GROUP BY を組み合わせられる
• カテゴリ別の比較から分析の問いを作れる

1. GROUP BYは「同じ値ごとにまとめる」

GROUP BY は、指定した列の値が同じ行を1つのグループにまとめます。 そのうえで、各グループごとに集計関数を計算します。

たとえば、注文ステータスごとの件数を数えるSQLです。

SELECT
            status,
            COUNT(*) AS order_count
          FROM orders
          GROUP BY status;
          

このSQLは、completed, cancelled, pending などのステータスごとに注文件数を数えます。

全体の COUNT(*) は1つの値を返します。 GROUP BY status を追加すると、ステータスごとの複数行の集計結果になります。

2. GROUP BYの基本形

GROUP BY は、集計したい単位の列を指定します。

SELECT
            集計したい列,
            集計関数
          FROM テーブル名
          GROUP BY 集計したい列;
          

商品カテゴリごとの商品数を数える例です。

SELECT
            category,
            COUNT(*) AS product_count
          FROM products
          GROUP BY category;
          

このSQLでは、category が同じ商品を1つのグループにまとめ、カテゴリごとの商品数を数えます。

SELECT に表示する通常の列は、原則として GROUP BY にも書くと考えましょう。 集計結果の1行が何を表しているのかを明確にするためです。

3. カテゴリ別の売上を出す

カテゴリ別売上を見るには、注文、注文明細、商品を結びつけてから集計します。 ここでは order_items と products を使います。

SELECT
            p.category,
            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;
          

このSQLは、商品カテゴリごとに販売金額を合計します。 quantity * unit_price は、明細1行の売上金額です。

実務では、「何を合計しているか」を必ず確認します。 orders.total_amount を使うのか、order_items.quantity * unit_price を使うのかで、分析の粒度が変わります。

4. ORDER BYで大きい順に見る

カテゴリ別売上は、金額が大きい順に並べると解釈しやすくなります。

SELECT
            p.category,
            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
          ORDER BY sales DESC;
          

ORDER BY sales DESC によって、売上が大きいカテゴリから順に表示されます。

集計表は、並び替えとセットで使うと発見が増えます。 上位カテゴリを見ることで、売上の中心になっている商品群を把握できます。

5. 複数列でグループ化する

GROUP BY には複数の列を指定できます。 たとえば、都道府県と注文ステータスの組み合わせごとに注文件数を数えます。

SELECT
            c.prefecture,
            o.status,
            COUNT(*) AS order_count
          FROM orders AS o
          JOIN customers AS c
            ON o.customer_id = c.customer_id
          GROUP BY
            c.prefecture,
            o.status
          ORDER BY
            c.prefecture,
            o.status;
          

このSQLの1行は、「ある都道府県の、あるステータスの注文数」を表します。

複数列の GROUP BY は、選んだ列の組み合わせごとに集計するという意味です。 Stage 1で学んだ複数列の DISTINCT と似た考え方です。

6. 集計単位を言葉で説明する

GROUP BY で重要なのは、集計結果の1行が何を表すかです。

たとえば、顧客ごとの購入回数を出すSQLです。

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

この結果の1行は「1人の顧客」を表します。 顧客ごとの購入回数や累計購入金額を見たいときに使います。

7. よくあるミス

GROUP BYしない列をSELECTに入れる

次のSQLは、集計単位が曖昧です。

SELECT
            status,
            order_date,
            COUNT(*) AS order_count
          FROM orders
          GROUP BY status;
          

status ごとにまとめているのに、どの order_date を表示すればよいか決まりません。 日付も含めて集計したいなら、GROUP BY に追加します。

SELECT
            order_date,
            status,
            COUNT(*) AS order_count
          FROM orders
          GROUP BY
            order_date,
            status;
          

集計前の行と集計後の行を混同する

GROUP BY customer_id の結果は、注文1件ごとの表ではありません。 顧客1人ごとの集計表です。

行の意味が変わることを意識しましょう。

実務での使いどころ: 比較できる単位にそろえる

GROUP BYは、全体の数字をカテゴリ別・日別・顧客別などの比較できる単位に分けるために使います。 「売上が下がった」という全体の数字だけでは、どこで下がったのか分かりません。

SELECT
            p.category,
            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
          ORDER BY sales DESC;
          

このSQLはカテゴリ別売上を出します。 GROUP BYを使うときは、結果の1行が何を表すかを必ず言葉で説明します。

GROUP BYは、分析の粒度を決める構文です。 粒度を間違えると、正しいSQLでも意思決定に使いにくい表になります。

ミニ演習

次の分析では、何でGROUP BYすべきか考えてください。

1. 都道府県別の顧客数を見たい。
2. 商品カテゴリ別の売上を見たい。
3. 月別の注文件数を見たい。
4. 顧客ごとの購入回数を見たい。

まとめ

この章のキーメッセージ： GROUP BY は、全体の数字をカテゴリ別・顧客別・地域別の比較に変えるための基本です。集計結果の1行が何を表すのかを、必ず言葉で説明できるようにしましょう。

この章の確認

1. GROUP BY status は、どのような単位で行をまとめますか？
2. 商品カテゴリごとの商品数を数えるSQLを書いてください。
3. 顧客ごとの完了注文件数を出すSQLを書いてください。
4. 複数列で GROUP BY するとき、集計結果の1行は何を表しますか？

関連演習

• SQL入門（GROUP BY）
• SQL入門（集計関数）
• SQL基礎（指標設計と比較分析）