SQL中級者になる！（その01/12）

初めに

　データベースとSQLは現在のデータサイエンスに必須な知識・スキルの1つ。その基礎を

power-of-awareness.com

を基に学んできた。
　この知識をより昇華させて「SQL中級者になりたい！」ため、同じ著者の

達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ

を参考に、更なるSQLの知識を拡充していこう。

1.　CASE式のススメ

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

1.1　CASE式の構文

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

注意点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で行う

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

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制約と複数列への条件関係を定義

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

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

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

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

　式はやを併用できる点が便利な点である。特にとはサブクエリを引数に取れるため、非常に強力である。

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

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

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

まとめ

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

次回

power-of-awareness.com

*1:の規格。