GROUP BYで売上を店舗別・月別に集計する方法

データ分析がはじめての方向けに、SQLの集計の基本であるGROUP BYを、専門用語をかみ砕きながら、実務で使える形で解説します。前の記事のSELECTとWHEREが分かれば、すぐに読めます。

1. 集計とは?

集計とは、たくさんの行を、合計や件数のような「1つの数字」にまとめることです。SQLには、集計のための決まった道具(集計関数)があります。

まずは、GROUP BYを使わずに、全体を1行にまとめてみましょう。

SELECT
  COUNT(*)    AS order_cnt,    -- 注文件数
  SUM(amount) AS total_sales,  -- 売上合計
  AVG(amount) AS avg_amount,   -- 平均金額
  MIN(amount) AS min_amount,   -- 最小金額
  MAX(amount) AS max_amount    -- 最大金額
FROM orders;

結果は、1行だけ返ってきます。「会社全体で」という大きな数字は分かりますが、「どの店舗が強いのか」は分かりません。そこで登場するのがGROUP BYです。

COUNTの3つの書き方

COUNTは、書き方によって数える対象が変わります。

• COUNT(*):行の数を数えます。空欄の行も含みます

• COUNT(列名):その列が空欄ではない行だけを数えます

• COUNT(DISTINCT 列名):重複を除いた、種類の数を数えます。たとえば、購入者数を出したいときは COUNT(DISTINCT customer_id) です

2. 本題:GROUP BYとは?

GROUP BYは、「この項目ごとにグループに分けて、グループごとに集計する」という指定です。注文の伝票を、店舗ごとの箱に仕分けして、箱ごとに合計を出すイメージです。

この記事では、次の orders テーブルを使います。

店舗ごとの売上を出すSQLは、次のとおりです。

SELECT
  store_id,
  COUNT(*)    AS order_cnt,
  SUM(amount) AS total_sales
FROM orders
GROUP BY store_id;

結果は、次のようになります。

店舗1は、4500 + 1600 + 2800 = 8,900円、店舗2は、3200 + 5000 + 3000 = 11,200円です。1つ1つの数字を、手計算で確かめてみると、GROUP BYの動きがよく分かります。

3. SELECTに書ける列のルール

GROUP BYを使うときの、最大のルールがあります。SELECTに書ける列は、次の2種類だけです。

1. GROUP BYに書いた列(例:store_id)

2. 集計関数を使った列(例:SUM(amount))

次のSQLは、エラーになります。

-- エラーになる例
SELECT store_id, order_date, SUM(amount)
FROM orders
GROUP BY store_id;

理由は、店舗1の箱の中に、複数の注文日が入っているためです。「どの日付を表示すればいいのか」を、SQLが決められません。表示したいなら、order_date もGROUP BYに加えるか、MAX(order_date) のように集計関数を使います。

4. 月別に集計する

月ごとに集計するには、日付を「月の初日」にそろえてから、グループ化します。そのための関数は、データベースによって書き方が違います。

PostgreSQLでの例です。

SELECT
  DATE_TRUNC('month', order_date) AS month,
  SUM(amount)                     AS total_sales
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

9月は 4500 + 3200 + 1600 = 9,300円、10月は 5000 + 2800 + 3000 = 10,800円になります。

5. 複数の列でグループ化する(店舗×月)

GROUP BYには、列を複数並べられます。「店舗ごと、かつ月ごと」の集計は、次のように書きます。

SELECT
  store_id,
  DATE_TRUNC('month', order_date) AS month,
  SUM(amount)                     AS total_sales
FROM orders
GROUP BY store_id, DATE_TRUNC('month', order_date)
ORDER BY store_id, month;

このように、グループに使う列を増やすと、結果の行が細かくなることが分かります。

6. WHEREとGROUP BYを組み合わせる