おはようございます！愛知に行ってきます！
ちびまる子ちゃんランドに行くのがたのしみです。
この本のサブタイトルが通り、
「初心者で終わりたくない」ので読んでます。
中級者向けですね、わたしにはちょいムズです^^;
ただ、読み込めばいけそうな気がしてます！
今回は、その本の最初の節である「CASE式のススメ」を自分なりにまとめます。
CASE式の種類
– 単純CASE式
– 検索CASE式
「単純CASE式」で書ける =「検索CASE式」でも書ける
-- 単純CASE式 CASE sex WHEN 1 THEN '男' WHEN 2 THEN '女' ELSE 'その他' END
上の単純CASE式を検索CASE式で書くと、以下になります。
-- 検索CASE式 CASE WHEN sex = 1 THEN '男' WHEN sex = 2 THEN '女' ELSE 'その他' END
CASE式の注意点
– CASE式の評価は真になるWHEN句が見つかった時点で打ち切られる。
よって、下位のWHEN句は無視される。
– 各分岐が返すデータの型は統一させる
– 「END」を忘れないようにする。→エラーはでる。
– 「ELSE X」は省略することができる。その場合は、「ELSE NULL」になる。
明示的に「ELSE X」は書くようにした方がいい！
– 「CASE文」ではない。「CASE式」
SELECT と GROUP BY で 同じCASE式を使う
→SELECT句の「AS {別名}」をGROUP BYで流用することができる。
※PostgrSQL, MySQLで使用可
こんなテーブル(pop_tbl)があったとして、
# SELECT * FROM pop_tbl; pref_name | population -----------+------------ 徳島 | 100 香川 | 200 愛媛 | 150 高知 | 200 福岡 | 300 佐賀 | 100 長崎 | 200 東京 | 400 群馬 | 60
[結果] ←こうしたい
region | sum --------+----- その他 | 660 四国 | 650 九州 | 400
別名を流用せずに書くと、こんな感じで長いです。
SELECT
(CASE pref_name
WHEN '徳島' THEN '四国'
WHEN '香川' THEN '四国'
WHEN '愛媛' THEN '四国'
WHEN '高知' THEN '四国'
WHEN '佐賀' THEN '九州'
WHEN '福岡' THEN '九州'
ELSE 'その他' END) AS region
,SUM(population)
FROM pop_tbl
GROUP BY
(CASE pref_name
WHEN '徳島' THEN '四国'
WHEN '香川' THEN '四国'
WHEN '愛媛' THEN '四国'
WHEN '高知' THEN '四国'
WHEN '佐賀' THEN '九州'
WHEN '福岡' THEN '九州'
ELSE 'その他' END);
上記のような長いSQLを以下のように短くすることができます。
SELECT
(CASE pref_name
WHEN '徳島' THEN '四国'
WHEN '香川' THEN '四国'
WHEN '愛媛' THEN '四国'
WHEN '高知' THEN '四国'
WHEN '佐賀' THEN '九州'
WHEN '福岡' THEN '九州'
ELSE 'その他' END) AS region
,SUM(population)
FROM pop_tbl
GROUP BY region;
クロス形式で表にする
こんなテーブル(manju_tbl)があったとして、
# SELECT * FROM manju_tbl; name | anco_id | count --------+---------+------- 太郎 | 1 | 10 太郎 | 2 | 10 二郎 | 1 | 10 二郎 | 2 | 0 小次郎 | 1 | 14 小次郎 | 2 | 8 ※anco_id = 1→「黒あん」、anco_id = 2→「白あん」のイメージです。
[結果] ←こうしたい。
各人がkuroan_count/shiroroan_countを何回ずつ食べたのか1行でみれるようにしたい。
name | kuroan_count | shiroroan_count --------+--------------+----------------- 二郎 | 10 | 0 小次郎 | 14 | 8 太郎 | 10 | 10
こんな感じです。
nameごとに1行にしたいので、GROUP BY nameを忘れない！
SUMで合計するので、ELSE 0にしにしたほうが読みやすいです。
SELECT name ,SUM(CASE WHEN anco_id = 1 THEN count ELSE 0 END) AS kuroan_count ,SUM(CASE WHEN anco_id = 2 THEN count ELSE 0 END) AS shiroroan_count FROM manju_tbl GROUP BY name;
CHECK制約でCASE式を使う
CONSTRAINT {制約名} CHECK ({制約内容})
とし、制約に名前がつけれます。
これにより、エラー発生時に制約名が表示されるので、
どこで引っかかったのかわかりやすくなります。
CREATE TABLEやALTER TABLEで使えます。
今回の例では、
「女性社員の給与は20万円以下である」
という制約をみたすためのテーブル作成時のSQLです。
※この制約のポイントは男性社員に関しては、給与の制約がないことです。
CREATE TABLE Salaries(
name VARCHAR(50),
sex SMALLINT,
salary INT CONSTRAINT Salaries_salary_check CHECK (
CASE WHEN sex = 2
THEN CASE WHEN salary <= 200000
THEN 1 ELSE 0 END
ELSE 1 END = 1
)
);
--すでにテーブル Salariesがあり、カラムsexとカラムsalaryがある前提
ALTER TABLE Salaries ADD CONSTRAINT
Salaries_salary_check CHECK (
CASE WHEN sex = 2
THEN CASE WHEN salary <= 200000
THEN 1 ELSE 0 END
ELSE 1 END = 1
);
データ挿入時に、制約が走ります。
-- 以下３つの INSERT文は 正常に追加されます。
INSERT INTO salaries VALUES('太郎', 1, 220000);
INSERT INTO salaries VALUES('二郎', 1, 190000);
INSERT INTO salaries VALUES('花子', 2, 200000);
-- 制約に違反すると
-- 以下のSQL エラー と表示されます。
-- ERROR: new row for relation "salaries" violates check constraint "salaries_salary_check"
INSERT INTO salaries VALUES('卑弥呼', 2, 220000);
制約名は省略もできます。
・制約名を省略したパターン
CREATE TABLE Salaries2(
name VARCHAR(50),
sex SMALLINT,
salary INT CHECK (
CASE WHEN sex = 2
THEN CASE WHEN salary <= 200000
THEN 1 ELSE 0 END
ELSE 1 END = 1
)
);
--すでにテーブル Salaries2があり、カラムsexとカラムsalaryがある前提
ALTER TABLE Salaries4 ADD CHECK (
CASE WHEN sex = 2
THEN CASE WHEN salary <= 200000
THEN 1 ELSE 0 END
ELSE 1 END = 1
);
・エラーの場合の表記
-- ERROR: new row for relation "salaries2" violates check constraint "salaries2_check"
制約名を省略した場合のエラー時の制約名は、
制約名(デフォルト)： {テーブル名}_check
ってなってますね。
PostgreSQL 9.4.5文書 では、以下のように書いてますね。詳細わかりませんね。
（この方法で制約名を指定しない場合は、システムにより名前が付けられます。）
まとめ
CASE式は、実行時に評価されて１つの値に定まるので、
列名や定数がかける部分であれば、どこにでもかけます！
今回例を出した以外の、UPDATE文や集約関数のなかでもCASE式は使うことができてとても便利です。