既存のコード体系を新しい体系に変換して集計
非定型的な集計を行なう業務では、既存のコード体系を分析用のコード体系に変換して、その新体系の単位で集計したい、という要件が持ち込まれることがあります。
例えば、県コードは、「1:北海道、2:青森、……47:沖縄」というように振られていますが、これを東北、関東、九州といった地方単位にまとめ、その単位で人口を集計したい場合です。具体的には、次に示す表1の内容を集計し、表2の結果を求めるような場合です。
表1：集計元の表A
| 県名（pref_name） | 人口（population） |
| 徳島 | 100 |
| 香川 | 200 |
| 愛媛 | 150 |
| 高知 | 200 |
| 福岡 | 300 |
| 佐賀 | 100 |
| 長崎 | 200 |
| 東京 | 400 |
| 群馬 | 50 |
こんなとき、皆さんならどうしますか？ 「地方コード」という列を持つビューを定義する、というのも一つの方法です。しかしそれだと、集計に使いたいコード体系の数だけ列を追加しなければなりませんし、動的な変更も困難です。
CASE式を使うと次のような一つのSQLで取出しが可能です。ここでは分かりやすくするために、コードの代わりに県名（pref_name）をGROUP BYのキーに使います。
サンプル1
SELECT SUM(population),
CASE pref_name
WHEN '徳島県' THEN '四国'
WHEN '香川県' THEN '四国'
WHEN '愛媛県' THEN '四国'
WHEN '高知県' THEN '四国'
WHEN '福岡県' THEN '九州'
WHEN '佐賀県' THEN '九州'
WHEN '長崎県' THEN '九州'
ELSE 'その他' END
FROM Table_A
GROUP BY CASE pref_name
WHEN '徳島県' THEN '四国'
WHEN '香川県' THEN '四国'
WHEN '愛媛県' THEN '四国'
WHEN '高知県' THEN '四国'
WHEN '福岡県' THEN '九州'
WHEN '佐賀県' THEN '九州'
WHEN '長崎県' THEN '九州'
ELSE 'その他' END;
豪快にGROUP BY句にSELECT句のCASE式をコピーしてあげるのがポイントです。単純に「GROUP BY pref_name」と変換前の列を指定すると、正しい結果が得られないので注意してください。
また、同様の考え方で、数値型の列を適当な階級体系に振り分けて集計することも可能です。例えば、所得階級（salary_class）ごとのレコード数を調べたい場合は、次のようなSQLになります。
サンプル2
SELECT
CASE WHEN salary <= 500 THEN '1'
WHEN salary > 500 AND salary <= 600 THEN '2'
WHEN salary > 600 AND salary <= 800 THEN '3'
WHEN salary > 800 AND salary <= 1000 THEN '4'
ELSE NULL END salary_class,
COUNT(*)
FROM Table_A
GROUP BY
CASE WHEN salary <= 500 THEN '1'
WHEN salary > 500 AND salary <= 600 THEN '2'
WHEN salary > 600 AND salary <= 800 THEN '3'
WHEN salary > 800 AND salary <= 1000 THEN '4'
ELSE NULL END;
異なる条件の集計を1つのSQLで行なう
異なる条件の集計は、CASE式の使い方として有名なものの一つです。例えば、先の県別人口を保持するテーブルに、性別列を付け加えたテーブルから、男女別・県別の人数の合計を求める、というケースを考えます。具体的には、次に示す表3の内容を集計し、表4の結果を求めるような場合です。
表3：集計元の表B
| 県名（pref_name） | 性別（sex） | 人口（population） |
| 徳島 | 1 | 60 |
| 徳島 | 2 | 40 |
| 香川 | 1 | 100 |
| 香川 | 2 | 100 |
| 愛媛 | 1 | 100 |
| 愛媛 | 2 | 50 |
| 高知 | 1 | 100 |
| 高知 | 2 | 100 |
| 福岡 | 1 | 100 |
| 福岡 | 2 | 200 |
| 佐賀 | 1 | 20 |
| 佐賀 | 2 | 80 |
| 長崎 | 1 | 125 |
| 長崎 | 2 | 125 |
| 東京 | 1 | 250 |
| 東京 | 2 | 150 |
表4：集計結果B
| 県名 | 男 | 女 |
| 徳島 | 60 | 40 |
| 香川 | 100 | 100 |
| 愛媛 | 100 | 50 |
| 高知 | 100 | 100 |
| 福岡 | 100 | 200 |
| 佐賀 | 20 | 80 |
| 長崎 | 125 | 125 |
| 東京 | 250 | 150 |
普通は次のように、WHERE句で条件分岐させて、2回SQLを発行します。
サンプル3
--男性の人口
SELECT pref_name,
COUNT(*)
FROM Table_A
WHERE sex = '1'
GROUP BY pref_name;
--女性の人口
SELECT pref_name,
COUNT(*)
FROM Table_A
WHERE sex = '2'
GROUP BY pref_name;
UNIONを使えば1つのSQLにできますが、コストは減りませんし、SQLも無駄に長くなります。一方、CASE式を使えば、次のような1つのSQLで済みます。
サンプル4
SELECT pref_name,
SUM( CASE WHEN sex = '1' THEN
population ELSE 0 END), --男性の人口
SUM( CASE WHEN sex = '2' THEN
population ELSE 0 END) --女性の人口
FROM Table_A
GROUP BY pref_name;
性別が男性'1'のレコードと女性'2'の人口列を、それぞれ合計しているわけです。いわば「行持ち」のデータから「列持ち」に水平展開しているのです。集約関数であれば、SUMに限らずCOUNTでもAVGでも同様に使えます。
このトリックの重宝するところは、SQLの結果を二次元表の形に整形できることです。単純にGROUP BYで集約しただけだと、その後、ホスト言語やExcelなどのアプリケーション上でクロス表の形に整形しなければなりません。しかし、サンプル4を見ると、表側が県名、表頭が性別という、既に二次元表の形式で結果が出力されることが分かります。これは集計表を作るときに非常に便利な機能です。この技をスローガン的に表現するならば、
WHERE句で条件分岐させるのは素人のやること。プロはSELECT句で分岐させる。
ということです。使い勝手の良い技なので、多用してください。
DECODE関数の弱点
DECODE関数が
CASE式に比べて劣っていると思う点は、次の4つです。
- 第1に、Oracleの方言なので互換性がありません。
- 第2に、分岐の数が127に制限されています（引数の上限数は255ですが、一つの分岐を表現するのに2つの引数を要します）。
- 第3に、分岐の数が増えるとソースが非常に読みづらくなります。
- 第4に、記述力が貧弱です。具体的には、引数に述語を使った式を取ることができません。当然、サブクエリを作ることもできません。