CodeZine(コードジン)

特集ページ一覧

CASE式のススメ(前編)

汎用度の高い条件式CASEの代表的な使い方

  • LINEで送る
  • このエントリーをはてなブックマークに追加
2006/06/08 00:00
目次

既存のコード体系を新しい体系に変換して集計

 非定型的な集計を行なう業務では、既存のコード体系を分析用のコード体系に変換して、その新体系の単位で集計したい、という要件が持ち込まれることがあります。

 例えば、県コードは、「1:北海道、2:青森、……47:沖縄」というように振られていますが、これを東北、関東、九州といった地方単位にまとめ、その単位で人口を集計したい場合です。具体的には、次に示す表1の内容を集計し、表2の結果を求めるような場合です。

表1:集計元の表A
県名(pref_name) 人口(population)
徳島 100
香川 200
愛媛 150
高知 200
福岡 300
佐賀 100
長崎 200
東京 400
群馬 50
 
表2:集計結果A
地方名 人口
四国 650
九州 600
その他 450

 こんなとき、皆さんならどうしますか? 「地方コード」という列を持つビューを定義する、というのも一つの方法です。しかしそれだと、集計に使いたいコード体系の数だけ列を追加しなければなりませんし、動的な変更も困難です。

 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に、記述力が貧弱です。具体的には、引数に述語を使った式を取ることができません。当然、サブクエリを作ることもできません。

  • LINEで送る
  • このエントリーをはてなブックマークに追加

修正履歴

  • 2006/06/09 18:43 Page1 評価方法の例文のキャプション「WHERE句」を「WHEN句」に修正

バックナンバー

連載:達人に学ぶSQL

もっと読む

著者プロフィール

  • ミック(ミック)

    日本では、主にBI/DWHの設計からチューニングまでを専門とするデータベースエンジニアとして活動。2018年より米国シリコンバレーに活動拠点を移し、技術調査とビジネス開発に従事している。 主な著書・訳書: 『達人に学ぶSQL徹底指南書 第2版』(2018) 『SQL実践入門』(2015)...

あなたにオススメ

All contents copyright © 2005-2021 Shoeisha Co., Ltd. All rights reserved. ver.1.5