「大人の教養・知識・気付き」を伸ばすブログ

一流の大人(ビジネスマン、政治家、リーダー…)として知っておきたい、教養・社会動向を意外なところから取り上げ学ぶことで“気付く力”を伸ばすブログです。目下、データ分析・語学に力点を置いています。

MENU

SQL中級者になる!(その01/X)

1. CASE式のススメ

 \mathrm{CASE}式は\mathrm{SQL}-92*1より取り入れら主要な\mathrm{DBMS}では問題ない句利用できる。しかしその真価が良く理解されておらず、利用されなかったりその簡易版の式が使われたりしてきた。しかし\mathrm{CASE}式に習熟すると\mathrm{SQL}の利用の幅がぐっと広がり、また汎用性も確保できる。

1.1 CASE式の構文

 \mathrm{CASE}式には単純\mathrm{CASE}式と検索\mathrm{CASE}式の2つがある。後者の方が汎用的(前者は後者で同じものを書ける。)なので後者のみを扱うことにする。なお\mathrm{CASE}式の評価は真になる\mathrm{WHEN}句が見つかった時点で打ち切られてそれ以後の\mathrm{WHEN}句は無視される短絡評価を採用している。無用の混乱を避けるべく、\mathrm{WHEN}句は排他的に記述するのが望ましい。

注意点1 各分岐が返すデータ型は統一する。
注意点2 ENDは省略できない。
注意点3 ELSE句は想定外の動作を避けるべく明記すべきである。
-- 単純CASE式
CASE sex
   WHEN '1' THEN ''
   WHEN '2' THEN ''
ELSE 'その他' END

-- 検索CASE式
CASE
   WHEN sex= '1' THEN ''
   WHEN sex='2' THEN ''
ELSE 'その他' END

1.2 CASE式のメリット

1.2.1 既存コード体系を新体系で集計する

 非定型的な集計を行う場合に既存のコード体系を別のコード体系に変換してその新体系の単位で集計したいときに効力を及ぼす。これはたとえば都道府県別に集計されたデータを地方(関東、関西など)レベルに集計し直して総計を出したい場合が相当する。

-- イメージ例
SELECT CASE PREF_NAME
    WHEN '' THEN '四国'
    WHEN '' THEN '四国'
    WHEN '' THEN '四国'
    WHEN '' THEN '四国'
    WHEN '' THEN '九州'
    WHEN '' THEN '九州'
    WHEN '' THEN '九州'
             ELSE 'その他' END AS district,
    SUM(population)
FROM PopTbl
GROUP BY district
1.2.2 異なる条件の集計を1つのSQLで行う

 この手法のメリットは\mathrm{SQL}を二次元表の形式に成型できる点にある。

SELECT PREF_NAME,
             SUM(CASE WHEN SEX ='1' THEN population ELSE 0 END) AS CNT_M
             SUM(CASE WHEN SEX ='2' THEN population ELSE 0 END) AS CNT_F
FROM PopTbl2
GROUP BY PREF_NAME
1.2.3 CHECK制約と複数列への条件関係を定義

 \mathrm{CASE}式は\mathrm{CHECK}制約と相性が非常に良い。条件法(\mathrm{CHECK}制約)は論理積よりも緩い制約を掛けることができるため、より柔軟なクエリを書くことができる。

CONSTRAINT check_slary CHECK
(CASE WHEN SEX = '2'
           THEN CASE WHEN salary <= 200000
                               THEN 1 ELSE 0 END
  ELSE 1 END = 1)
1.2.4 条件を分岐させたUPDATE

 数値型の列に対して現在の値を判定対象として別の値へ変える場合に、\mathrm{UPDATE}の条件が複数に分岐する場合、\mathrm{CASE}式を用いると便利である。


1.2.5 テーブル同士のマッチング

 \mathrm{CASE}式は\mathrm{BETWEEN}\mathrm{LIKE}を併用できる点が便利な点である。特に\mathrm{IN}\mathrm{EXISTS}はサブクエリを引数に取れるため、非常に強力である。

1.2.6 CASE式の中で集約関数を用いる

 \mathrm{CASE}式の中に更に\mathrm{CASE}を書いた入れ子構造にすることで、条件分岐を\mathrm{SELECT}文で実行することができる。

SELECT STD_ID
             CASE WHEN COUNT(*)=1
                       THEN MAX(CLUB_ID)

             ELSE MAX(CASE WHEN MAIN_CLUB_FLG='Y'
                                        THEN CLUB_ID
                               ELSE NULL END) END AS MAIN_CLUB
FROM StudentClub
GROUP BY STD_ID

まとめ

  1. \mathrm{GROUP\ BY}句で\mathrm{CASE}式を用いることで集約単位となるコードや階級を柔軟に設定できる。これは非定形的な集計に有効である。
  2. 集約関数の中に使うことで行持ちから列持ちへの水平展開も簡単にできる。
  3. 集約関数を条件式に組み込むことで\mathrm{HAVING}句を使わずにクエリをまとめられる。
  4. 実装依存の関数より表現力が強力かつ汎用性も高まる。
  5. 以上ができるのは\mathrm{CASE}式が式であるからである。
  6. \mathrm{CASE}式を駆使することで複数の\mathrm{SQL}分をつにまとめられ、可読性もパフォーマンスも向上する。

*1:\mathrm{SQL}の規格。

プライバシーポリシー お問い合わせ