SQLのCASE WHEN文：複雑な条件分岐を使いこなす応用テクニック
CASE WHEN文の基本：条件に応じた値の割り当て
SQLを学習していると、特定の条件に基づいてデータを分類したり、値を変更したりする必要に迫られることがあります。
例えば、顧客の購入金額に応じて「一般」「プレミアム」「VIP」といったランクを付けたい場合や、テストの点数に応じて「優」「良」「可」といった評価を付けたい場合などです。
このような、条件分岐を伴うデータ操作に非常に役立つのが CASE WHEN 文です。
CASE WHEN 文は、SQLの標準機能として提供されており、IF-THEN-ELSE のような条件分岐をSQLクエリ内で実現できます。
まずは、最も基本的な CASE WHEN 文の構文を見てみましょう。
SELECT
column1,
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE result_else
END AS new_column_name
FROM
your_table;
この構文では、CASE キーワードの後に、WHEN と THEN のペアを複数記述できます。
各 WHEN 節では、特定の条件 (condition1, condition2 など) を指定します。
もしその条件が真 (true) であれば、対応する THEN 節の result1 や result2 が返されます。
どの WHEN 条件にも一致しなかった場合は、ELSE 節で指定された result_else が返されます。
ELSE 節は省略可能ですが、省略した場合でどの WHEN 条件にも一致しない場合は NULL が返されることに注意が必要です。
そして、END キーワードで CASE 文を閉じ、AS キーワードで新しい列の名前 (new_column_name) を付けます。
では、具体的な例を見てみましょう。
ある「orders」テーブルがあり、amount 列に注文金額が格納されているとします。
この amount に基づいて、注文のステータスを「低」「中」「高」に分類したい場合、以下のようなクエリが考えられます。
SELECT
order_id,
amount,
CASE
WHEN amount < 1000 THEN '低'
WHEN amount >= 1000 AND amount < 5000 THEN '中'
WHEN amount >= 5000 THEN '高'
ELSE '不明'
END AS amount_status
FROM
orders;
このクエリでは、amount が1000未満なら「低」、1000以上5000未満なら「中」、5000以上なら「高」というステータスを割り当てています。
もし amount が NULL の場合は、ELSE 節で指定した「不明」が返されます。
このように、CASE WHEN 文を使えば、複雑な条件分岐をSQLクエリ内で直接記述し、データを整形することが可能です。
これは、レポート作成やデータ分析の初期段階で、データを分かりやすい形に加工する際に非常に役立ちます。
観察
CASE WHEN 文は、IF 文や switch 文に似ていますね。
しかし、SQLの CASE WHEN 文は、単に値を返すだけでなく、集計関数と組み合わせることで、さらに強力な分析が可能になります。
その点については、後ほど詳しく見ていきましょう。
CASE WHEN文と集計関数の組み合わせ
CASE WHEN 文の真価は、集計関数と組み合わせることで発揮されます。
例えば、特定の条件を満たすレコードの数を数えたい場合や、条件ごとに合計値を計算したい場合などに、COUNT() や SUM() といった集計関数と CASE WHEN 文を組み合わせることができます。
これにより、単一のクエリで複雑な集計処理を実行することが可能になり、データ分析の効率が飛躍的に向上します。
条件付きカウント (Conditional Counting)
特定の条件を満たすレコードの数を数えたい場合、COUNT() 関数と CASE WHEN 文を組み合わせます。
例えば、先ほどの orders テーブルで、ステータスが「高」の注文がいくつあるかを数えたいとします。
SELECT
COUNT(CASE WHEN amount >= 5000 THEN 1 ELSE NULL END) AS high_amount_orders_count
FROM
orders;
このクエリでは、CASE WHEN amount >= 5000 THEN 1 ELSE NULL END の部分が、条件を満たす場合に 1 を、満たさない場合に NULL を返します。
COUNT() 関数は NULL をカウントしないため、結果として amount >= 5000 を満たすレコードの数だけがカウントされます。
ELSE NULL を省略しても、CASE 文の条件に一致しない場合はデフォルトで NULL が返されるため、同じ結果になります。
SELECT
COUNT(CASE WHEN amount >= 5000 THEN 1 END) AS high_amount_orders_count
FROM
orders;
このテクニックは、複数の条件でグループ化してそれぞれの件数を取得したい場合にも応用できます。
例えば、注文金額のステータス（「低」「中」「高」）ごとの件数を一度に取得したい場合です。
SELECT
SUM(CASE WHEN amount < 1000 THEN 1 ELSE 0 END) AS low_amount_count,
SUM(CASE WHEN amount >= 1000 AND amount < 5000 THEN 1 ELSE 0 END) AS medium_amount_count,
SUM(CASE WHEN amount >= 5000 THEN 1 ELSE 0 END) AS high_amount_count
FROM
orders;
ここでは SUM() 関数を使っています。
CASE WHEN で条件を満たす場合に 1 を、それ以外の場合に 0 を返すようにし、それを SUM() で合計することで、各条件に合致するレコード数をカウントしています。
ELSE 0 とすることで、NULL 値が存在する場合でも意図したカウントが可能です。
逡巡
COUNT(CASE WHEN ...) と SUM(CASE WHEN ...) のどちらを使うべきか、迷うかもしれません。
COUNT() を使う場合は、条件に合致した行に 1 を、それ以外に NULL を返します。COUNT() は NULL を無視するため、条件に合致した行数だけを数えます。
SUM() を使う場合は、条件に合致した行に 1 を、それ以外に 0 を返します。SUM() は 0 も合計するため、結果として条件に合致した行数になります。
どちらの方法でも同じ結果が得られますが、SUM(CASE WHEN ... THEN 1 ELSE 0 END) の方が、NULL の扱いを意識せずに済むため、より直感的で安全な場合が多いでしょう。
条件付き集計 (Conditional Aggregation)
特定の条件を満たすレコードの合計値や平均値などを計算したい場合も、CASE WHEN 文が活躍します。
例えば、orders テーブルで、ステータスが「高」の注文の合計金額を計算したいとします。
SELECT
SUM(CASE WHEN amount >= 5000 THEN amount ELSE 0 END) AS total_high_amount
FROM
orders;
このクエリでは、amount が5000以上の場合はその amount を、それ以外の場合は 0 を SUM() 関数に渡しています。
これにより、5000以上の注文金額のみが合計されます。
ELSE 0 を指定することで、NULL 値がある場合でも、その行は合計に含まれず、意図した集計結果が得られます。
また、条件ごとの平均値を計算することも可能です。
例えば、注文金額が1000円未満の注文と、1000円以上の注文の平均金額を比較したい場合です。
SELECT
AVG(CASE WHEN amount < 1000 THEN amount ELSE NULL END) AS avg_low_amount,
AVG(CASE WHEN amount >= 1000 THEN amount ELSE NULL END) AS avg_high_amount
FROM
orders;
ここでは、AVG() 関数と CASE WHEN 文を組み合わせています。
CASE WHEN で条件に合致する amount を返し、合致しない場合は NULL を返しています。
AVG() 関数は NULL を無視するため、それぞれの条件に合致するレコードの amount だけが平均計算の対象となります。
断定
CASE WHEN 文と集計関数を組み合わせることで、単一のクエリで複数の条件に基づいた集計を行うことができます。
これは、ピボットテーブルのような集計表を作成する際に非常に強力な手法です。
例えば、商品カテゴリごとの売上合計を、CASE WHEN を使って列方向に展開することも可能です。
SELECT
SUM(CASE WHEN category = 'Electronics' THEN amount ELSE 0 END) AS electronics_sales,
SUM(CASE WHEN category = 'Books' THEN amount ELSE 0 END) AS books_sales,
SUM(CASE WHEN category = 'Clothing' THEN amount ELSE 0 END) AS clothing_sales
FROM
products_sales;
このように、CASE WHEN 文は、データを集計し、分析しやすい形に整形するための柔軟で強力なツールなのです。
CASE WHEN文におけるNULL値の扱いと応用
CASE WHEN 文を使いこなす上で、NULL 値の扱いは非常に重要です。
SQLにおいて NULL は、値が存在しないことを示す特別な値であり、比較演算子 (=, <, >) とは異なる振る舞いをします。
NULL は、どのような値とも等しくなく、また大小比較もできません。
NULL = NULL は FALSE になり、NULL < 100 も FALSE になります。
CASE WHEN 文で NULL をどのように扱うかによって、クエリの結果が大きく変わってきます。
NULL値の判定
NULL 値を判定するには、等価演算子 (=) ではなく、IS NULL または IS NOT NULL を使用します。
例えば、orders テーブルで amount が NULL の注文を抽出したい場合は、以下のように記述します。
SELECT
order_id,
amount
FROM
orders
WHERE
amount IS NULL;
CASE WHEN 文の中でも、この IS NULL を使って NULL 値を条件として指定できます。
SELECT
order_id,
CASE
WHEN amount IS NULL THEN '未定義'
WHEN amount < 1000 THEN '低'
ELSE 'その他'
END AS amount_status
FROM
orders;
この例では、amount が NULL の場合は「未定義」と表示し、そうでなければ金額に基づいて「低」または「その他」と表示します。
再観察
CASE WHEN 文の ELSE 節は、どの WHEN 条件にも一致しなかった場合に実行されます。
もし WHEN 条件で NULL を明示的に扱わない場合、amount が NULL のレコードは、ELSE 節の処理を受けることになります。
これは意図しない結果につながる可能性があるため、NULL 値の存在を考慮して CASE WHEN 文を記述することが重要です。
NULLを0として扱う
集計関数と CASE WHEN 文を組み合わせて NULL を 0 として扱いたい場面はよくあります。
例えば、SUM() 関数は NULL を無視しますが、0 として扱いたい場合があります。
先ほどの例で、amount が NULL の場合も 0 として合計に含めたい場合は、ELSE 0 を明示的に指定します。
SELECT
SUM(CASE WHEN amount IS NULL THEN 0 ELSE amount END) AS total_amount_including_null_as_zero
FROM
orders;
このクエリは、amount が NULL であれば 0 を返し、そうでなければ amount そのものを返します。
SUM() 関数はこの結果を合計するため、結果的に NULL が 0 として扱われた合計値が得られます。
結論
CASE WHEN 文は、SQLにおける条件分岐の強力なツールです。
基本的な値の割り当てから、集計関数との組み合わせによる高度なデータ集計、そして NULL 値の慎重な扱いまで、その応用範囲は非常に広いです。
これらのテクニックを習得することで、より複雑で実践的なデータ分析が可能になります。
CASE WHEN 文を使いこなすことは、SQLを使ったデータ操作の幅を大きく広げる鍵となるでしょう。
SQLの CASE WHEN 文について、基本的な使い方から集計関数との組み合わせ、NULL値の扱いまでを解説しました。
これらの知識を定着させるために、ぜひ次の練習問題に挑戦してみてください。