タグ: SQL

おはようございます！愛知に行ってきます！
ちびまる子ちゃんランドに行くのがたのしみです。

https://amzn.to/3vcFDbG

この本のサブタイトルが通り、
「初心者で終わりたくない」ので読んでます。
中級者向けですね、わたしにはちょいムズです^^;
ただ、読み込めばいけそうな気がしてます！

 

今回は、その本の最初の節である「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式は使うことができてとても便利です。