CASE WHENを使って、ちょっと複雑な条件分岐と集計をクエリ一発で

こんにちは、さるまりんです🐒

以前の記事では、CASEを使ってシンプルな条件分岐を行う方法をご紹介しました。

今回はそこから進めて、ちょっと複雑な条件分岐と集計関数を組み合わせた応用パターンを見ていきたいと思います。
これであればMySQLでもPostgreSQLでも同じように使えるはずです。(いろんなバージョンを試したわけではないですが)

サンプルデータとして以下のようなstudentsテーブルを想定します。

name	country	department	score
salu	Japan	Math	92
okapi	Japan	Music	75
zebra	USA	Math	88
koala	USA	Music	65

1. CASE WHEN の基本構文

おさらいです。基本形はこうです。

CASE
  WHEN 条件1 THEN 値1
  WHEN 条件2 THEN 値2
  ELSE デフォルト値
END

CASE式はSELECT句や集計関数の引数にも書けます。

2. 複数列を使った条件分岐

countryとscoreの両方を条件に使い、カテゴリを分けてみます。

SELECT
  name,
  CASE
    WHEN country = 'Japan' AND score >= 80 THEN '日本・高得点'
    WHEN country = 'Japan' AND score < 80  THEN '日本・低得点'
    WHEN country <> 'Japan' AND score >= 80 THEN '海外・高得点'
    ELSE '海外・低得点'
  END AS category
FROM students;

こんな風に複数列の組み合わせ条件を1クエリ内に直接書けます。
ビューや集計の前処理として便利かな。

3. 条件付き集計

CASE式は集計関数と組み合わせて、条件ごとの件数や合計を求めてみます。

SELECT
  department,
  SUM(CASE WHEN score >= 80 THEN 1 ELSE 0 END) AS high_scorers,
  SUM(CASE WHEN score < 80  THEN 1 ELSE 0 END) AS low_scorers
FROM students
GROUP BY department;

SUMの中で条件に合えば1、それ以外は0、その結果を合計して件数を出しています。

4. 複数条件＋集計の応用例

例えば、得点をランクA/B/Cに分類して部門別に人数を集計するには、こんな風に書くことができます。

SELECT
  department,
  SUM(CASE WHEN score >= 90 THEN 1 ELSE 0 END) AS rank_a,
  SUM(CASE WHEN score BETWEEN 70 AND 89 THEN 1 ELSE 0 END) AS rank_b,
  SUM(CASE WHEN score < 70 THEN 1 ELSE 0 END) AS rank_c
FROM students
GROUP BY department;

これで1回のGROUP BYでランク別集計が可能になります。