初めに
データベースとSQLは現在のデータサイエンスに必須な知識・スキルの1つ。その基礎を
を基に学んできた。
この知識をより昇華させて「SQL中級者になりたい!」ため、同じ著者の
を参考に、更なる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
まとめ
句で
式を用いることで集約単位となるコードや階級を柔軟に設定できる。これは非定形的な集計に有効である。
- 集約関数の中に使うことで行持ちから列持ちへの水平展開も簡単にできる。
- 集約関数を条件式に組み込むことで
句を使わずにクエリをまとめられる。
- 実装依存の関数より表現力が強力かつ汎用性も高まる。
- 以上ができるのは
式が式であるからである。
式を駆使することで複数の
分をつにまとめられ、可読性もパフォーマンスも向上する。
*1:の規格。