skillup

技術ブログ

Database

CASE式のすすめ

投稿日:2016年11月2日 更新日:

SQL実践入門を7割がたぐらい終えたところで同著者の方の「達人に学ぶSQL徹底指南書」を学習しようと思っています。

この方の著書は気づいたら結構読んでましたね。

CASE式での注意ポイント

SQL実践入門でも書かれていましたが、条件分岐の中にはWHERE句でゴリゴリ書くよりもCASE式を使ったほうが有効なパターンがいくつかあります。

そんな使えるCASE式ですが、以下のことに注意。以前にも自分なりにこのエントリーで書いてますので、重複することあるかも。

同一条件で返すデータは統一させる

当たり前ですが、同一条件内で数値と文字列みたいな、そういうのはダメです。

ELSE~ENDの書き忘れ

ELSEは書かなくても動くっぽいですが、書いておいたほうがよいでしょう。ENDは書かないと動かないようです。ただし、本書では明示的に書くようにアドバイスをしています。エラーにならないけど結果が違うというケースが出てしまうからでしょう。

サンプル例

※著者の方のページに全部情報が載っているのでここ見たほうが早いかも(爆)。

http://mickindex.sakura.ne.jp/database/db_support_sinan.html

テーブル名 poptbl

pref_name | population
———–+————

徳島 | 100
香川 | 200
愛媛 | 150
高知 | 200
福岡 | 300
佐賀 | 100
長崎 | 200
東京 | 400
群馬 | 50

よくあるケースとしては上記のようなテーブルで地方ごとに集計したい、といったときでしょう。こういったときに別カラム(地方コードなど)を追加して判別する方法もありますが、こういうときにこそダイレクトにCASE式が使えます。

上記のSQLで下記のような結果を出すことができます。

pref_name | district
———–+———-
徳島 | 四国
香川 | 四国
愛媛 | 四国
高知 | 四国
福岡 | 九州
佐賀 | 九州
長崎 | 九州
東京 | 関東
群馬 | 関東

あとはこれをGROUP BYしてSUMをとればいいのです。

district | sum
———-+—–
四国 | 650
関東 | 450
九州 | 600

カラムを増やさなくても見事にできました。

なおGROUP BYの部分にはSELECT式で作ったカラム名(districtをそのまま使えます。)

この書き方は便利ですが、厳密にいうと非推奨のようでMySQLやPostgresでは動きますが、他のデータベースでは動かないようです。

なお、階級ごとに分けるなんてこともできます。

city_rank | count
———–+——-
小都市 | 3
中都市 | 5
大都市 | 1

また下記のようなテーブル(PopTbl2)があり、

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

これを

pref_name| man | woman
徳島  | 60 | 40
・・・・・・

のように分けたいとします。1回のSQLで。

前段階として、男ならman列に出し、女ならwoman列に出すというのは下記のように書いてあげます。
この場合も下記のようにCASE式を使い、別の列に出力してあげればOKです。

※今回は条件判定をする対象列は一列ですが、2列に出すのでCASE式を2つに書きます。

pref_name | sex | men | women
———–+—–+—–+——-
徳島 | 1 | 60 | 0
徳島 | 2 | 0 | 40

この段階で上記のように出力することができます。

ここでさらに、これをpref_nameでSUMします。

pref_name | man | woman
———–+—–+——-
長崎 | 125 | 125
東京 | 250 | 150
香川 | 100 | 100
高知 | 100 | 100
徳島 | 60 | 40
愛媛 | 100 | 50
福岡 | 100 | 200
佐賀 | 20 | 80

プログラムと近いですが、一気にやらず一つずつ組み立てていくと楽でしょう。

-Database
-

執筆者:


comment

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

関連記事

no image

論理設計のアンチパターン その2

今回は論理設計のアンチパターンの続きです。 今までに比べると何も意図がないというものではなく、パフォーマンスを考えて設計されているようなものが多いです。 ただし、中には絶対に許されないタイプのものもあ …

no image

JPAまとめ

JPAわけわかんねーと思い、触りはじめてから3か月ぐらいたちます。 いまだに細かい部分はよくわかっていないことは多いのですが、全体像というか何となくどういうものかはわかり始めてきたいのでまとめたいと思 …

no image

アンチパターン データ分身の術+DBの不要な連携+バージョンアップ未テスト

今回のアンチパターンは主にデータ設計に関する部分。 Contents1 同一データの使用1.1 デメリット1.2 対策2 DBの不要な連携2.1 デメリット2.2 対策3 サーバーの移行やバージョンア …

no image

JPQLでの算術関数

複雑なJPQLを書いていると、通常のレコードの取り出しだけではなく、合計(SUM)や算出(COUNT)などのいわゆる算術関数を使うことが一般的です。 JPQLでもこれらを通常通り扱うことができます。 …

no image

SQL基礎 条件式はunionよりもcaseで

複雑な条件式があったときにcase式を使うことでパフォーマンスを向上させることができます。 ※一般にunionを使うよりも高速なことが多い。 例1 ある条件により別の列を使いたいとき、 [crayon …