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. 複数列を使った条件分岐

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

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でランク別集計が可能になります。

NULLに注意!

条件がどれにも当てはまらずELSEもない場合、結果はNULLになります。
SUMCOUNTではNULLは無視されるため、件数が0になってしまうことがあります。

なので、必ずELSE 0などのデフォルト値を設定しておきます。

CASE WHENはMySQLでもPostgreSQLでも同じ構文で使えます。SUMなども同じように使えるのでこれらをうまく組み合わせることでちょっと複雑なものもクエリ一発で、できます。
プログラミングって本当にパズルみたいです。うまく組み合わせることで新しい発見があるので楽しいです。

もっといろんなことがひらめく柔らかい頭にできるよう頑張ります。

読んでくださってありがとうございました。
それではまた!