SQLのCASE文とGROUP BYを効果的に組み合わせることで、複雑な集計や条件付きのデータ処理が可能になります。これにより、ビジネスインテリジェンスやデータ分析において、データの洞察力を高めることができます。本記事では、CASE文とGROUP BYの基本的な使い方から、具体的な実例や応用例までを詳しく紹介し、分類して行をまとめる方法と、SUMやCOUNTの内側でCASEを使う条件付き集計の違いを解説します。NULL、境界値、GROUP BYでの別名の扱いも確認します。
CASE文の基本構文
CASE文は、SQLクエリ内で条件に応じて異なる値を返すための条件式です。最初に真になったWHENの結果を返し、どれにも一致せずELSEもなければNULLになります。THENとELSEの返り値はDBMSが共通の型に解決できるようにそろえます。基本的な構文は以下の通りです。conditionやresultなどは説明用のプレースホルダーで、そのまま実行するSQLではありません。
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE resultN
END例：基本的なCASE文
以下は、CASE文を使用してテーブルの値に基づいてカテゴリを割り当てる例です。
SELECT
product_name,
CASE
WHEN price IS NULL THEN 'Unknown'
WHEN price < 100 THEN 'Cheap'
WHEN price BETWEEN 100 AND 500 THEN 'Moderate'
ELSE 'Expensive'
END AS price_category
FROM products;このクエリは、productsテーブルから各商品の価格に基づいて「Cheap」「Moderate」「Expensive」のカテゴリを割り当てて表示します。
CASE文は、条件によって異なる値を返すため、データの分類やカスタム集計を行う際に非常に便利です。次のセクションでは、GROUP BYの基本構文について説明します。
GROUP BYの基本構文
GROUP BYは、SQLでデータを特定の列でグループ化し、集計関数と組み合わせて使用するための構文です。基本的な構文は以下の通りです。
SELECT
column1,
aggregate_function(column2)
FROM
table_name
GROUP BY
column1;例：基本的なGROUP BY
以下は、salesテーブルのデータを商品ごとにグループ化し、各商品の総売上を計算する例です。
SELECT
product_name,
SUM(sales_amount) AS total_sales
FROM
sales
GROUP BY
product_name;このクエリは、salesテーブルから各商品の売上額を合計し、商品ごとの総売上を表示します。
GROUP BY句は、データの集計や統計分析を行う際に非常に重要です。次のセクションでは、CASE文とGROUP BYを組み合わせて条件別に集計を行う方法について説明します。
検証用データ：境界値とNULLを含む8件
以下のsalesの例は、この小さなデータで結果を追えるようにしています。既存の業務テーブルへ実行せず、同名テーブルのない検証用データベースを使ってください。DDLと複数行INSERTはSQLite、PostgreSQL、MySQL、SQL Serverで利用する基本形です。DBMSや既存スキーマに応じて型・日付リテラル・INSERT構文を調整します。
CREATE TABLE sales (
sale_id INTEGER PRIMARY KEY,
product_name VARCHAR(20),
region VARCHAR(10),
price INTEGER,
sales_amount INTEGER,
sale_date DATE
);
INSERT INTO sales VALUES
(1, 'A', 'East', 99, 900, '2024-01-05'),
(2, 'B', 'East', 100, 1000, '2024-01-12'),
(3, 'C', 'East', 500, 5000, '2024-01-20'),
(4, 'D', 'West', 501, 6000, '2024-02-01'),
(5, 'E', 'West', NULL, 200, '2024-02-02'),
(6, 'F', 'West', 50, NULL, '2024-02-03'),
(7, 'G', 'East', 100, 1000, '2024-02-05'),
(8, 'H', 'East', 0, 0, '2024-01-22');priceは価格、sales_amountは1明細の売上額です。数量から自動計算する列ではありません。NULLの売上額は不明、0は既知のゼロとして区別します。冒頭のproductsの例は構文説明用の別テーブルで、このsalesデータとは別です。
CASE文とGROUP BYの組み合わせ方
CASE文とGROUP BYを組み合わせることで、特定の条件に基づいた集計を行うことができます。これにより、複雑な条件に基づくデータ分析や集計が容易になります。
例：条件別にデータを集計する
以下の例では、売上データを基に、価格帯ごとの売上額を集計します。価格がNULLの行は高価格帯ではなくUnknownへ分類します。100以上500以下はModerateで、500を超える値がExpensiveです。負の価格を許可しない場合は、別途データ制約や不正値の分類を設けてください。
SELECT
CASE
WHEN price IS NULL THEN 'Unknown'
WHEN price < 100 THEN 'Cheap'
WHEN price BETWEEN 100 AND 500 THEN 'Moderate'
ELSE 'Expensive'
END AS price_category,
SUM(sales_amount) AS total_sales
FROM
sales
GROUP BY
CASE
WHEN price IS NULL THEN 'Unknown'
WHEN price < 100 THEN 'Cheap'
WHEN price BETWEEN 100 AND 500 THEN 'Moderate'
ELSE 'Expensive'
END;このクエリでは、salesテーブルの価格帯ごとに売上額を集計しています。CASE文で価格帯を定義し、それをGROUP BY句でグループ化することで、各価格帯の総売上を計算しています。
ポイント
- CASE文をGROUP BY句と一緒に使用することで、データの分類と集計を一度に行うことができます。
- SELECTとGROUP BYに同じCASE式を書く方法のほか、CTEや派生テーブルで分類列を作り、外側でその列を集計する方法があります。同じSELECTで付けた別名をGROUP BYに書けるかはDBMSによって異なります。
この方法を使うことで、複雑な条件付きの集計を効率的に実行できます。次のセクションでは、実際の売上データを使用した具体的な例を紹介します。
実例：売上データの条件別集計
ここでは、売上データを使用してCASE文とGROUP BYを組み合わせた条件別集計の具体例を紹介します。
例：月ごとの売上データをカテゴリ別に集計
以下はMySQL用です。DATE_FORMATとGROUP BYでのSELECT別名の利用を含むため、そのまま全DBMS共通にはなりません。カテゴリは「各明細の売上額」で決め、その後に月別合計を求めます。「月の合計額をLow/Medium/Highに分類」する処理とは異なります。
SELECT
DATE_FORMAT(sale_date, '%Y-%m') AS sale_month,
CASE
WHEN sales_amount IS NULL THEN 'Unknown'
WHEN sales_amount < 1000 THEN 'Low'
WHEN sales_amount BETWEEN 1000 AND 5000 THEN 'Medium'
ELSE 'High'
END AS sales_category,
SUM(sales_amount) AS total_sales
FROM
sales
GROUP BY
sale_month,
sales_category
ORDER BY
sale_month,
sales_category;このクエリは、salesテーブルのデータを使用して、上の8件に対する期待結果は次のとおりです。ORDER BYのカテゴリ名は文字列順なので、Low→Medium→Highの業務上の順序にはなりません。希望の順序には別の数値ソートキーを用意します。
sale_month | sales_category | total_sales
2024-01 | Low | 900
2024-01 | Medium | 6000
2024-02 | High | 6000
2024-02 | Low | 200
2024-02 | Medium | 1000
2024-02 | Unknown | NULLポイント
DATE_FORMAT関数を使用して日付を月単位にフォーマットしています。- CASE文を使用して、売上額に基づいて「Low」「Medium」「High」のカテゴリを割り当てています。
- GROUP BY句で月ごと、カテゴリごとにデータをグループ化し、売上額の合計を計算しています。
このように、CASE文とGROUP BYを組み合わせることで、複数の条件に基づく詳細なデータ集計を行うことができます。次のセクションでは、さらに複雑な条件を扱う応用例について説明します。
応用例：複数条件の処理
CASE文とGROUP BYを組み合わせることで、さらに複雑な条件を扱うことも可能です。ここでは、複数の条件を組み合わせた応用例を紹介します。
例：地域と価格帯別の売上集計
以下のクエリでは、売上データを地域別および価格帯別にグループ化し、集計します。
SELECT
region,
CASE
WHEN price IS NULL THEN 'Unknown'
WHEN price < 100 THEN 'Cheap'
WHEN price BETWEEN 100 AND 500 THEN 'Moderate'
ELSE 'Expensive'
END AS price_category,
SUM(sales_amount) AS total_sales
FROM
sales
GROUP BY
region,
CASE
WHEN price IS NULL THEN 'Unknown'
WHEN price < 100 THEN 'Cheap'
WHEN price BETWEEN 100 AND 500 THEN 'Moderate'
ELSE 'Expensive'
END
ORDER BY
region,
price_category;このクエリでは、salesテーブルから地域と価格帯ごとに売上額を集計し、上の8件では次の結果になります。SUMはNULLを集計から除外するため、金額不明の行があれば件数も併記して確認してください。
region | price_category | total_sales
East | Cheap | 900
East | Moderate | 7000
West | Cheap | NULL
West | Expensive | 6000
West | Unknown | 200複数のCASE文を使用した例
さらに、複数のCASE式を使用して、異なる基準でデータを分類できます。以下もMySQL用の別名を使ったGROUP BYです。SQL Serverなどへ移す場合は、後述のCTE方式で分類列を先に作成してください。
SELECT
region,
CASE
WHEN price IS NULL THEN 'Unknown'
WHEN price < 100 THEN 'Cheap'
WHEN price BETWEEN 100 AND 500 THEN 'Moderate'
ELSE 'Expensive'
END AS price_category,
CASE
WHEN sales_amount IS NULL THEN 'Unknown'
WHEN sales_amount < 1000 THEN 'Low Sales'
WHEN sales_amount BETWEEN 1000 AND 5000 THEN 'Medium Sales'
ELSE 'High Sales'
END AS sales_volume,
COUNT(*) AS number_of_sales
FROM
sales
GROUP BY
region,
price_category,
sales_volume
ORDER BY
region,
price_category,
sales_volume;このクエリは、地域と価格帯および明細の売上金額帯（sales_volumeという別名）に基づいてデータを分類し、各分類の売上件数を集計します。
ポイント
- 複数のCASE文を使用することで、複数の基準に基づいてデータを分類できます。
- これにより、詳細なデータ分析や特定の条件に基づいたレポート作成が可能です。
このように、CASE文とGROUP BYの組み合わせは、複雑なデータ集計や分析を行う際に非常に強力なツールとなります。次のセクションでは、これらのテクニックを効果的に使用するためのポイントと注意点をまとめます。
効果的な使用方法と注意点
CASE文とGROUP BYを組み合わせて使用する際の効果的な方法と注意点をいくつか紹介します。
効果的な使用方法
- 条件付き集計: CASE文を使用して、特定の条件に基づく集計を行うことで、詳細なデータ分析が可能になります。例えば、売上データを価格帯別に分類し、それぞれの合計を計算するなど。
- データの分類: GROUP BYと組み合わせることで、データを複数のカテゴリに分けて集計できます。これにより、異なる視点からデータを分析することができます。
- カスタムレポートの作成: 複雑な条件を使用してカスタムレポートを作成する際に、CASE文とGROUP BYの組み合わせが非常に有効です。
注意点
- パフォーマンスの考慮: 複雑なCASE文や多くの条件を使用すると、クエリのパフォーマンスが低下する可能性があります。適切なインデックスを使用して、クエリの効率を最適化することが重要です。
- 可読性の確保: 複雑なCASE文は可読性が低下するため、コメントを追加してクエリの意図を明確にすることが望ましいです。また、必要に応じてサブクエリやCTE（Common Table Expressions）を使用して、クエリを分割して読みやすくすることも考慮してください。
- データの一貫性: GROUP BYを使用する際には、集計されるデータが一貫していることを確認してください。異なる基準での集計が混在すると、意図しない結果が得られることがあります。
例：パフォーマンス向上のためのインデックス作成
次は検討候補のインデックス定義です。実行するとデータベースのスキーマを変更するため、権限・既存の重複インデックス・書き込み負荷を確認し、検証環境で実行計画を比較してから判断します。作成すれば必ず速くなるという意味ではありません。
CREATE INDEX idx_sales_region_price ON sales(region, price);このインデックスの先頭列はregionです。全件のCASE分類・集計を行うクエリでは効果が小さい場合もあり、CASE式による分類と元のprice列の索引は同一ではありません。行数・絞り込み条件・統計情報・DBMSによって実行計画が変わるため、速度向上は測定して確認します。CTEへの書き換えも、それ自体で高速化を保証しません。
GROUP BYの別名エラーを避ける：CTEで分類してから集計
MySQLはGROUP BYでSELECT別名を使えますが、SQL Serverでは同じSELECTリストで定義した別名をGROUP BYへ書けません。移植性と保守性を考えるなら、次のようにCTEで分類列を作り、外側から通常の列として参照します。MySQLでCTEを使うには8.0以降が必要です。
WITH classified AS (
SELECT region, sales_amount,
CASE
WHEN price IS NULL THEN 'Unknown'
WHEN price < 100 THEN 'Cheap'
WHEN price <= 500 THEN 'Moderate'
ELSE 'Expensive'
END AS price_category
FROM sales
)
SELECT price_category, COUNT(*) AS row_count,
COUNT(sales_amount) AS known_amount_count,
SUM(sales_amount) AS total_sales
FROM classified
GROUP BY price_category
ORDER BY price_category;期待結果はCheapが3行・金額既知2行・合計900、Expensiveが1行・既知1行・6000、Moderateが3行・既知3行・7000、Unknownが1行・既知1行・200です。COUNT(*)とCOUNT(sales_amount)の差により、Cheapの中に金額不明の行が1件あることも分かります。
条件付き集計：カテゴリを列として並べる
価格帯ごとに行を分けたいときはGROUP BY CASE、地域ごとに1行を残して条件別の数値を列に並べたいときはSUM(CASE…)やCOUNT(CASE…)を使います。次は地域別にModerateの件数・売上額と価格不明の件数を出します。
SELECT region,
SUM(CASE WHEN price BETWEEN 100 AND 500 THEN 1 ELSE 0 END) AS moderate_count,
COUNT(CASE WHEN price BETWEEN 100 AND 500 THEN 1 END) AS moderate_count2,
SUM(CASE WHEN price BETWEEN 100 AND 500 THEN sales_amount ELSE 0 END) AS moderate_sales,
SUM(CASE WHEN price IS NULL THEN 1 ELSE 0 END) AS unknown_price_count
FROM sales
GROUP BY region
ORDER BY region;Eastはmoderate_count=3、moderate_count2=3、moderate_sales=7000、unknown_price_count=0。Westは順に0、0、0、1です。SUMのELSE 0は「条件外の行を0として加算する」指定で、条件内の金額NULLを自動で既知の0に直す指定ではありません。不明金額の扱いは業務ルールに合わせて別途決めます。
COUNTのELSE 0は条件外の行まで数える
SELECT COUNT(*) AS all_rows,
COUNT(CASE WHEN price BETWEEN 100 AND 500 THEN 1 END) AS matching_rows,
COUNT(CASE WHEN price BETWEEN 100 AND 500 THEN 1 ELSE 0 END) AS wrong_count
FROM sales;結果はall_rows=8、matching_rows=3、wrong_count=8です。COUNT(式)は非NULLを数えるため、0も数えます。件数を数えるならCOUNT(CASE WHEN … THEN 1 END)か、SUM(CASE WHEN … THEN 1 ELSE 0 END)を使います。
行を分類するCASEと、集計結果を分類するCASEは違う
SELECT region, SUM(sales_amount) AS total_sales,
CASE
WHEN SUM(sales_amount) IS NULL THEN 'Unknown'
WHEN SUM(sales_amount) < 1000 THEN 'Low'
WHEN SUM(sales_amount) <= 5000 THEN 'Medium'
ELSE 'High'
END AS regional_sales_category
FROM sales
GROUP BY region
HAVING SUM(sales_amount) >= 6000
ORDER BY region;この例は地域別合計を計算した後、その合計を分類します。East=7900、West=6200で、両方がHighです。一方、先の月別例は各明細をLow/Medium/Highへ分類してから合算します。WHEREは集計前の行、HAVINGは集計後のグループを絞り込みます。CASE内にSUMを書いているこの式を、そのままGROUP BYへ移すことはできません。
0件・NULL・並び順・JOINで確認すること
SELECT COUNT(*) AS row_count,
SUM(CASE WHEN price < 100 THEN 1 ELSE 0 END) AS sum_count,
COALESCE(SUM(CASE WHEN price < 100 THEN 1 ELSE 0 END), 0) AS sum_count_or_zero
FROM sales
WHERE sale_id < 0;該当行がないためrow_count=0、sum_count=NULL、sum_count_or_zero=0です。SUMは入力0件でNULLを返します。COALESCEで0にするかは要件次第です。またGROUP BY付きのクエリで元データに存在しないカテゴリは、自動で「0件の行」として生成されません。全カテゴリを表示するにはカテゴリ一覧を用意してLEFT JOINするなどの設計が必要です。
GROUP BYは表示順を保証しません。ORDER BYで順序を指定します。明細をJOINしてから集計する場合は1対多・多対多で行数が増えて合計が重複しないかを確認し、必要ならJOIN前に適切な粒度で集計します。CASEの分類基準を増やす際も、同じラベルへ異なる条件をまとめてよいかを確認してください。
参考資料
まとめ
CASE文とGROUP BYを組み合わせることで、SQLで複雑な条件付き集計やデータ分析が可能になります。この記事では、基本構文から実例、応用例、そして効果的な使用方法と注意点までを解説しました。これらの技術を活用することで、データの洞察力を高め、ビジネスインテリジェンスの向上に貢献できます。データベースのパフォーマンスを最適化しつつ、必要なデータを効率的に抽出し、分析するための強力なツールとして、SQLを最大限に活用しましょう。
コメント