MySQLのCASE式+GROUP BYで行を列に変換するクロス集計

black laptop computer turned-on displaying source code on table SQL

MySQLには他のDBMS（Oracle、SQL Server等）にあるPIVOT構文が存在しません。「月ごとの商品カテゴリ別売上をひとつの行に並べたい」「日付ごとにステータス別注文件数を横展開したい」——こういったクロス集計（ピボット）の要件は、CASE式とGROUP BYを組み合わせることで実現できます。PostgreSQLやSQLiteでも同じ手法が使えます。この記事では基本のSUM+CASE式パターンから始め、COUNT版の書き方・合計列の追加、CASE式が行う中間変換の仕組み、そして「ELSE 0 と ELSE NULLどちらが正しいか」「カテゴリが動的に変わる場合はどうするか」といった実務上の疑問まで、コピペして試せるSQLとともに解説します。

このシナリオで考える

月別・商品カテゴリ別の売上テーブルを使います。1行が「月×カテゴリ」の組み合わせになっている縦持ち形式のデータです。

CREATE TABLE sales (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  sale_month VARCHAR(7),   -- 例: '2024-01'
  category   VARCHAR(20),  -- 'food', 'drink', 'misc'
  amount     INT NOT NULL
);

INSERT INTO sales (sale_month, category, amount) VALUES
  ('2024-01', 'food',  12000),
  ('2024-01', 'drink',  5000),
  ('2024-01', 'misc',   3000),
  ('2024-02', 'food',  15000),
  ('2024-02', 'drink',  6500),
  ('2024-02', 'misc',   2000),
  ('2024-03', 'food',  11000),
  ('2024-03', 'drink',  4000),
  ('2024-03', 'misc',   4500);

このデータをそのままSELECTすると9行返ってきます。これを「1月に1行、food・drink・miscを列に展開した3行」に変換するのが目標です。

SUM+CASE式で行を列に変換する

縦持ちを横持ちに変換する基本パターンは次のとおりです。

SELECT
  sale_month,
  SUM(CASE WHEN category = 'food'  THEN amount ELSE 0 END) AS food,
  SUM(CASE WHEN category = 'drink' THEN amount ELSE 0 END) AS drink,
  SUM(CASE WHEN category = 'misc'  THEN amount ELSE 0 END) AS misc
FROM sales
GROUP BY sale_month
ORDER BY sale_month;

実行結果:

sale_month food drink misc
2024-01 12000 5000 3000
2024-02 15000 6500 2000
2024-03 11000 4000 4500

動作の仕組みを順番に確認します。

  1. CASE式がカテゴリで振り分ける：CASE WHEN category = 'food' THEN amount ELSE 0 END は、その行のcategoryが’food’ならamountの値を、それ以外は0を返します
  2. GROUP BYがsale_monthでまとめる：同じ月の行がひとつのグループになります
  3. SUM()がグループ内を合計する：グループ内でfood列の値が[12000, 0, 0]になる場合、SUM()は12000を返します

各列のSUM(CASE …)が独立して動くことがポイントです。3カテゴリなら3つ書き、5カテゴリなら5つ書く形になります。全カテゴリの合計列を追加したい場合は、SUM(amount)を加えるだけです。

SELECT
  sale_month,
  SUM(CASE WHEN category = 'food'  THEN amount ELSE 0 END) AS food,
  SUM(CASE WHEN category = 'drink' THEN amount ELSE 0 END) AS drink,
  SUM(CASE WHEN category = 'misc'  THEN amount ELSE 0 END) AS misc,
  SUM(amount) AS total  -- カテゴリを問わず全合計
FROM sales
GROUP BY sale_month
ORDER BY sale_month;

CASE式が行う中間変換を確認する

パターンの意味が分かりにくい場合は、GROUP BYをかける前の「CASE式の中間結果」を先に確認すると理解が深まります。

-- GROUP BY を外してCASE式の動きだけ見る
SELECT
  sale_month,
  category,
  amount,
  CASE WHEN category = 'food'  THEN amount ELSE 0 END AS food_val,
  CASE WHEN category = 'drink' THEN amount ELSE 0 END AS drink_val,
  CASE WHEN category = 'misc'  THEN amount ELSE 0 END AS misc_val
FROM sales
ORDER BY sale_month, category;

実行結果（一部）:

sale_month category amount food_val drink_val misc_val
2024-01 drink 5000 0 5000 0
2024-01 food 12000 12000 0 0
2024-01 misc 3000 0 0 3000
2024-02 drink 6500 0 6500 0
2024-02 food 15000 15000 0 0
2024-02 misc 2000 0 0 2000

この中間データにGROUP BY sale_monthでSUM()をかけると、各月の列ごとの合計が求まります。「CASE式で値を振り分け、SUM()で折りたたむ」という2ステップで横持ちへの変換が成立しています。

COUNT+CASEで件数を横展開する

金額ではなく件数を横展開したい場合はCOUNT+CASEを使います。日別に注文ステータスの件数を並べる例で確認します。

CREATE TABLE orders (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  order_date DATE,
  status     VARCHAR(20)  -- 'completed', 'pending', 'cancelled'
);

INSERT INTO orders (order_date, status) VALUES
  ('2024-01-10', 'completed'),
  ('2024-01-10', 'completed'),
  ('2024-01-10', 'pending'),
  ('2024-01-11', 'completed'),
  ('2024-01-11', 'cancelled'),
  ('2024-01-11', 'pending');

SELECT
  order_date,
  COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed,
  COUNT(CASE WHEN status = 'pending'   THEN 1 END) AS pending,
  COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled
FROM orders
GROUP BY order_date
ORDER BY order_date;

実行結果:

order_date completed pending cancelled
2024-01-10 2 1 0
2024-01-11 1 1 1

COUNT()はNULLを無視するため、ELSEを省略することで「条件に一致しない行はカウントしない」という動作になります。ELSE NULLと書いても結果は同じです。

SUM・COUNT・AVGとCASEの組み合わせを整理します。

集計目的 推奨構文 ELSEの扱い
金額・数量の合計 SUM(CASE WHEN … THEN 値 ELSE 0 END) ELSE 0 で明示
件数カウント COUNT(CASE WHEN … THEN 1 END) ELSE省略（NULLを無視）
カテゴリ別の平均 AVG(CASE WHEN … THEN 値 END) ELSE省略が必須

AVGでELSE 0 を書くと「対象外の行を0として平均に含める」ため、カテゴリ別の平均単価を求める場面では結果が低く出てしまいます。ELSEを省略してNULLを返すほうが正しい挙動です。

よくある落とし穴 / 注意点

ELSE 0 と ELSE NULL で結果は変わるか

SUM+CASEに限っていえば、ELSE 0・ELSE NULL・ELSE省略の集計結果はすべて同じです。SUMはNULLを無視して加算するため、NULLと0は数値集計の観点では等価になります。

ただし後段の処理には影響します。「対象カテゴリの売上が1件もない月」にSUMが0を返すかNULLを返すかで、アプリ側のNULLチェックやCSV出力の値が変わることがあります。表示上の0とNULLを区別したい場合はELSE 0 を明示するか、COALESCE(SUM(...), 0)で後処理するのが安全です。

動的なカテゴリ値には対応できない

CASE+GROUP BYのアプローチは、クエリを書く時点でカテゴリの種類が確定していることを前提とします。カテゴリが増えるたびにSQLを書き直す必要があり、実行時に初めてカテゴリ値が決まる場合には対応できません。

動的カテゴリへの対処法は2通りあります。

  • アプリ側で動的SQL生成：カテゴリ一覧を先にSELECTで取得し、プログラムでCASE句を組み立ててからクエリを実行する
  • ストアドプロシージャで動的SQL：PREPARE/EXECUTE構文で動的クエリを生成・実行する

カテゴリが固定または変更頻度が低い集計であれば、CASE+GROUP BYでシンプルに書けます。

categoryにNULLが含まれる場合

category列にNULLが入っている行は、どのCASE WHEN category = '...'にも一致しません。NULLとの等値比較は常にUNKNOWNになるためです。NULLをひとつの区分として集計に含めたい場合は、WHEN category IS NULL THEN amountの句を追加してください。

まとめ

MySQLでクロス集計（横持ち変換）を実現するには、SUM(CASE WHEN ... THEN 値 ELSE 0 END)とGROUP BYの組み合わせが基本パターンです。件数の横展開にはCOUNT(CASE WHEN ... THEN 1 END)を使い、平均を出す場合はELSEを省略します。カテゴリが事前に確定している集計ならこの方法でシンプルに対応でき、合計列の追加もSUM(amount)を1列加えるだけです。

参考リンク

アイキャッチ画像: Photo by Jantine Doornbos on Unsplash

タイトルとURLをコピーしました