対象読者
- SELECT文や、WHERE句、論理演算子については理解している
- CASE文がなんとなく、条件分岐するものってのが分かる
CASE式とは？
- 式とつくため、演算を行う式
- 式が使用できる箇所ではCASE式が使用できる
- 1+1も式
- 条件分岐に使用する
- 単純case式と、検索case式がある
- 基本的には、検索case式の方が柔軟なので使用することが多いみたい
CASE WHEN <評価式> THEN <式>
WHEN <評価式> THEN <式>
WHEN <評価式> THEN <式>
ELSE <式>
END
問題の前に初期データの準備
DBの準備はできている前提で進めていきます
私の環境では、PostgreSQL 14.4 を使用しています
$ psql -U postgres でDBへ接続
まずは、アイスクリームテーブルを作成します
CREATE TABLE ice_cream
(
id INT NOT NULL,
name VARCHAR(100) NOT NULL,
type VARCHAR(32) NOT NULL,
price INT,
delivery_date DATE,
PRIMARY KEY (id)
);
INSERT INTO ice_cream VALUES (1, 'ハーゲンダッツ' ,'アイスクリーム', 300, '2022-09-20');
INSERT INTO ice_cream VALUES (2, 'スーパーカップ' ,'ラクトアイス', 120, '202-08-01');
INSERT INTO ice_cream VALUES (3, 'Coolish' ,'ラクトアイス', 100, '2022-09-20');
INSERT INTO ice_cream VALUES (4, 'ガリガリ君' ,'氷菓', 60, '2022-08-30');
INSERT INTO ice_cream VALUES (5, 'PARM(パルム)' ,'アイスクリーム', 150, '2022-09-20');
INSERT INTO ice_cream VALUES (6, '雪見だいふく' ,'アイスミルク', 120, '2022-09-20');
INSERT INTO ice_cream VALUES (7, 'sacre(サクレ)' ,'氷菓', 90, '2022-08-01');
データのインサートが出来れば以下のようになります
postgres=# SELECT * FROM ice_cream;
id | name | type | price | delivery_date
----+----------------+----------------+-------+---------------
1 | ハーゲンダッツ | アイスクリーム | 300 | 2022-09-20
2 | スーパーカップ | ラクトアイス | 120 | 2022-08-01
3 | Coolish | ラクトアイス | 100 | 2022-09-20
4 | ガリガリ君 | 氷菓 | 60 | 2022-08-30
5 | PARM(パルム) | アイスクリーム | 150 | 2022-09-20
6 | 雪見だいふく | アイスミルク | 120 | 2022-09-20
7 | sacre(サクレ) | 氷菓 | 90 | 2022-08-01
(7 rows)
ここから問題スタートです
問題の後に、解答を隠して記載してあるので自分で解答を考えることが出来る形式になっています
解答は以下のようなアコーディオンにしています
💡 解答を開く 💡
ここに解答が書かれている
1. 種類(type)に付加情報を付けて出力する
IceCreamテーブルには、アイスクリーム、アイスミルク、ラクトアイス、氷菓 4種類のアイスが存在します
実際にもアイスクリームは以下の4種類に分けられるみたいです。
では、分かりやすくそれぞれの種類(type)に付加情報として説明を付けて以下の様に出力してみます
name | type_and_description
----------------+----------------------------------------------------------
ハーゲンダッツ | アイスクリーム(乳固形分15.0％以上 うち乳脂肪分8.0％以上)
スーパーカップ | ラクトアイス(乳固形分3.0％以上)
Coolish | ラクトアイス(乳固形分3.0％以上)
ガリガリ君 | 氷菓それ以外
PARM(パルム) | アイスクリーム(乳固形分15.0％以上 うち乳脂肪分8.0％以上)
雪見だいふく | アイスミルク(乳固形分10.0％以上 うち乳脂肪分3.0％以上)
sacre(サクレ) | 氷菓それ以外
(7 rows)
case文を使用すると以下で出力できます
💡 解答を開く 💡
解答は、単純CASE式と、検索CASE式で書いた2パターン用意しました
単純CASE式
評価したい式(ここで言う、列の事）を記載後は再度、typeを記載する必要がないです。簡潔にかけることがメリットではありますが、条件に縛りが生まれます
SELECT name,
CASE type
WHEN 'アイスクリーム'
THEN type || '(乳固形分15.0％以上 うち乳脂肪分8.0％以上)'
WHEN 'アイスミルク'
THEN type || '(乳固形分10.0％以上 うち乳脂肪分3.0％以上)'
WHEN 'ラクトアイス'
THEN type || '(乳固形分3.0％以上)'
WHEN '氷菓'
THEN type || 'それ以外'
ELSE NULL
END AS type_and_description
FROM ice_cream;
検索CASE式
when の後に評価したい式を記載する。whenの後に毎度条件を書かなければいけませんが、それぞれの条件を細かく使い分けることができます。
SELECT name,
CASE
WHEN type = 'アイスクリーム'
THEN type || '(乳固形分15.0％以上 うち乳脂肪分8.0％以上)'
WHEN type = 'アイスミルク'
THEN type || '(乳固形分10.0％以上 うち乳脂肪分3.0％以上)'
WHEN type = 'ラクトアイス'
THEN type || '(乳固形分3.0％以上)'
WHEN type = '氷菓'
THEN type || 'それ以外'
ELSE NULL
END AS type_and_description
FROM ice_cream;
単純CASE式と、検索CASE式の2種類の解答を用意しました
else ではtype には存在しないものが来た場合にNULLにしています
(ちなみに、elseは書かなくても動きますが、明示的に何が入るか分かるようにNULLでも書いたほうが良いみたいです)
そして、連結演算子の||で文字列の連結をおこなっています
また、case式だけでかなりのボリュームですが、それだけで1列を表しています
2. 種類(type)ごとの平均単価価格(price)を列として出力する
行として出力するパターンと、列として出力するパターンがありますがそれぞれで使用するSQL文はもちろん異なります
まずは、行として出力するパターンでは今回のテーマのCASE文ではなく、GRUOP BYを使用してグループごとの集計を行います
CASE文では無いので解答は以下に書いちゃってます
行として出力する場合
type | avg_price
----------------+----------------------
アイスクリーム | 225.0000000000000000
氷菓 | 75.0000000000000000
アイスミルク | 120.0000000000000000
ラクトアイス | 110.0000000000000000
(4 rows)
SQLは以下です
SELECT type, AVG(price) AS avg_price FROM ice_cream GROUP BY type;
列として出力する場合
avg_price_of_ice_cream | avg_price_of_ice_milk | avg_price_of_racto_ice | avg_price_of_frozen_dessert
------------------------+-----------------------+------------------------+-----------------------------
225.0000000000000000 | 120.0000000000000000 | 110.0000000000000000 | 75.0000000000000000
(1 row)
この時にcase 式を使用します
解答は以下です。
💡 解答を開く 💡
(単純CASE式)
SELECT
AVG(case type when 'アイスクリーム' THEN price end) AS avg_price_of_ice_cream,
AVG(CASE type WHEN 'アイスミルク' THEN price END) AS avg_price_of_ice_milk,
AVG(CASE type WHEN 'ラクトアイス' THEN price END) AS avg_price_of_racto_ice,
AVG(CASE type WHEN '氷菓' THEN price END) AS avg_price_of_frozen_dessert
FROM ice_cream;
(検索CASE式)
SELECT
AVG(CASE WHEN type = 'アイスクリーム' THEN price END) AS avg_price_of_ice_cream,
AVG(CASE WHEN type = 'アイスミルク' THEN price END) AS avg_price_of_ice_milk,
AVG(CASE WHEN type = 'ラクトアイス' THEN price END) AS avg_price_of_racto_ice,
AVG(CASE WHEN type = '氷菓' THEN price END) AS avg_price_of_frozen_dessert
FROM ice_cream;
3. 値段区分ごとの商品個数を求める
高いアイス・・・150円以上
普通のアイス・・・100円以上、150円未満
安いアイス・・・100円未満
とし、CASE式を使用して列で分類します
分類すると以下の様になります
high_price | middle_price | low_price
------------+--------------+-----------
2 | 3 | 2
(1 row)
💡 解答を開く 💡
SELECT
SUM(CASE WHEN price >= 150 THEN 1 ELSE 0 END) AS high_price,
SUM(CASE WHEN price < 150 AND price >= 100 THEN 1 ELSE 0 END) AS middle_price,
SUM(CASE WHEN price < 100 then 1 ELSE 0 END) AS low_price
FROM ice_cream;
4. 一番好きなアイス / そうでないアイス それぞれの列として人数を集計したテーブルを作成
言葉で説明しきれなかったので実際に作成するものを表で出します
その前に、、、
まずは、100人に聞いた一番好きなアイスクリームを集計したMostLikeIceCream テーブルを作成。
CREATE TABLE most_like_ice_cream
(name VARCHAR(100),
is_best BOOLEAN NOT NULL,
population INTEGER NOT NULL,
PRIMARY KEY(name, is_liked));
INSERT INTO most_like_ice_cream VALUES('ハーゲンダッツ', TRUE, 30);
INSERT INTO most_like_ice_cream VALUES('ハーゲンダッツ', FALSE, 70 );
INSERT INTO most_like_ice_cream VALUES('スーパーカップ', TRUE, 30);
INSERT INTO most_like_ice_cream VALUES('スーパーカップ', FALSE, 70);
INSERT INTO most_like_ice_cream VALUES('Coolish', TRUE, 5);
INSERT INTO most_like_ice_cream VALUES('Coolish', FALSE, 95);
INSERT INTO most_like_ice_cream VALUES('ガリガリ君', TRUE, 5);
INSERT INTO most_like_ice_cream VALUES('ガリガリ君', FALSE, 95);
INSERT INTO most_like_ice_cream VALUES('PARM(パルム)', TRUE, 15);
INSERT INTO most_like_ice_cream VALUES('PARM(パルム)', FALSE, 85);
INSERT INTO most_like_ice_cream VALUES('雪見だいふく', TRUE, 13);
INSERT INTO most_like_ice_cream VALUES('雪見だいふく', FALSE, 87);
INSERT INTO most_like_ice_cream VALUES('sacre(サクレ)', TRUE, 2);
INSERT INTO most_like_ice_cream VALUES('sacre(サクレ)', FALSE, 98);
postgres=# SELECT * FROM most_like_ice_cream;
name | is_best | population
----------------+----------+------------
ハーゲンダッツ | t | 30
ハーゲンダッツ | f | 70
スーパーカップ | t | 30
スーパーカップ | f | 70
Coolish | t | 5
Coolish | f | 95
ガリガリ君 | t | 5
ガリガリ君 | f | 95
PARM(パルム) | t | 15
PARM(パルム) | f | 85
雪見だいふく | t | 13
雪見だいふく | f | 87
sacre(サクレ) | t | 2
sacre(サクレ) | f | 98
そして今回出力したいものがこちら
name | best | not_best
----------------+----------+--------------
Coolish | 5 | 95
雪見だいふく | 13 | 87
スーパーカップ | 30 | 70
PARM(パルム) | 15 | 85
sacre(サクレ) | 2 | 98
ハーゲンダッツ | 30 | 70
ガリガリ君 | 5 | 95
💡 解答を開く 💡
SELECT name,
SUM(CASE WHEN is_liked = TRUE THEN population ELSE 0 END) AS best,
SUM(CASE WHEN is_liked = FALSE THEN population ELSE 0 END) AS not_best
FROM most_like_ice_cream GROUP BY name;
ここで重要なのはSUM関数です。
もし、SUM関数と group byなしの場合は以下のようになります
postgres=# SELECT name,CASE WHEN is_liked = TRUE THEN population ELSE 0 END AS best,case WHEN is_liked = FALSE THEN population ELSE 0 END AS not_best FROM most_like_ice_cream;
name | best | not_best
----------------+------+----------
ハーゲンダッツ | 30 | 0
ハーゲンダッツ | 0 | 70
スーパーカップ | 30 | 0
スーパーカップ | 0 | 70
Coolish | 5 | 0
Coolish | 0 | 95
ガリガリ君 | 5 | 0
ガリガリ君 | 0 | 95
PARM(パルム) | 15 | 0
PARM(パルム) | 0 | 85
雪見だいふく | 13 | 0
雪見だいふく | 0 | 87
sacre(サクレ) | 2 | 0
sacre(サクレ) | 0 | 98
case式だけでは、レコードの集約は行われないので元の、most_like_ice_creamテーブルのレコード数と同等のものが出力されます。
そのため、レコードの集約には集約関数であるSUMを使用しています
5. 100円以上、140円未満の商品は20%の値上げ。140円以上の商品は10%の値上げをする
ice_creamテーブルの更新を行います
「CASE文使わなくても2回UPDATEすれば良いのでは？？」
UPDATE ice_cream SET price = price * 1.2 WHERE 100 <= price AND price < 150;
UPDATE ice_cream SET price = price * 1.1 WHERE price >= 150;
これを実行すると結果は以下のようになります。
(元テーブル)
id | name | type | price | delivery_date
----+----------------+----------------+-------+---------------
1 | ハーゲンダッツ | アイスクリーム | 300 | 2022-09-20
2 | スーパーカップ | ラクトアイス | 120 | 2022-08-01
3 | Coolish | ラクトアイス | 100 | 2022-09-20
4 | ガリガリ君 | 氷菓 | 60 | 2022-08-30
5 | PARM(パルム) | アイスクリーム | 150 | 2022-09-20
6 | 雪見だいふく | アイスミルク | 120 | 2022-09-20
7 | sacre(サクレ) | 氷菓 | 90 | 2022-08-01
（更新後）
id | name | type | price | delivery_date
----+----------------+----------------+-------+---------------
4 | ガリガリ君 | 氷菓 | 60 | 2022-08-30
7 | sacre(サクレ) | 氷菓 | 90 | 2022-08-01
3 | Coolish | ラクトアイス | 120 | 2022-09-20
1 | ハーゲンダッツ | アイスクリーム | 330 | 2022-09-20
5 | PARM(パルム) | アイスクリーム | 165 | 2022-09-20
2 | スーパーカップ | ラクトアイス | 158 | 2022-08-01 # ここおかしい
6 | 雪見だいふく | アイスミルク | 158 | 2022-09-20 # ここおかしい
スーパーカップと、雪見だいふくが２度値上げされています。
ここで使用するのがCASE文です。UPDATE内で条件分岐をする必要がありますね。
解答は以下です
💡 解答を開く 💡
ここで使用するのがCASE式です。UPDATE内で1度で条件分岐で済みます。
正しくは以下のSQLですね
UPDATE ice_cream
SET price = CASE WHEN 100 <= price AND price < 140 THEN price * 1.2
WHEN price >= 140 THEN price * 1.1
ELSE price END;
以下が、正しく値上げされたもの
id | name | type | price | delivery_date
----+----------------+----------------+-------+---------------
1 | ハーゲンダッツ | アイスクリーム | 330 | 2022-09-20
2 | スーパーカップ | ラクトアイス | 144 | 2022-08-01
3 | Coolish | ラクトアイス | 120 | 2022-09-20
4 | ガリガリ君 | 氷菓 | 60 | 2022-08-30
5 | PARM(パルム) | アイスクリーム | 165 | 2022-09-20
6 | 雪見だいふく | アイスミルク | 144 | 2022-09-20
7 | sacre(サクレ) | 氷菓 | 90 | 2022-08-01
ELSE priceが無いと、NULLが出力されてしまうのでここでは必須になります。
6. 各商品のキャンペーン月を調べる
今回出力したい情報はこちら
name | 7月 | 8月 | 9月
----------------+-----+-----+-----
ハーゲンダッツ | × | × | ○
スーパーカップ | ○ | ○ | ○
Coolish | ○ | × | ×
ガリガリ君 | ○ | × | ×
PARM(パルム) | ○ | ○ | ×
雪見だいふく | × | ○ | ○
sacre(サクレ) | × | ○ | ×
まずは、
7~9月の内、アイスクリームのキャンペーンを打った月の情報を持ったcampaign_month_of_ice_creamテーブルを作成します
CREATE TABLE campaign_month_of_ice_cream
(
name VARCHAR(100) NOT NULL,
campaign_month VARCHAR(6)
);
INSERT INTO campaign_month_of_ice_cream VALUES ('ハーゲンダッツ', '202209');
INSERT INTO campaign_month_of_ice_cream VALUES ('スーパーカップ', '202207');
INSERT INTO campaign_month_of_ice_cream VALUES ('スーパーカップ', '202208');
INSERT INTO campaign_month_of_ice_cream VALUES ('スーパーカップ', '202209');
INSERT INTO campaign_month_of_ice_cream VALUES ('Coolish', '202207');
INSERT INTO campaign_month_of_ice_cream VALUES ('ガリガリ君', '202207');
INSERT INTO campaign_month_of_ice_cream VALUES ('PARM(パルム)', '202207');
INSERT INTO campaign_month_of_ice_cream VALUES ('PARM(パルム)', '202208');
INSERT INTO campaign_month_of_ice_cream VALUES ('雪見だいふく', '202208');
INSERT INTO campaign_month_of_ice_cream VALUES ('雪見だいふく', '202209');
INSERT INTO campaign_month_of_ice_cream VALUES ('sacre(サクレ)', '202208');
postgres=# SELECT * FROM campaign_month_of_ice_cream;
name | campaign_month
----------------+----------------
ハーゲンダッツ | 202209
スーパーカップ | 202207
スーパーカップ | 202208
スーパーカップ | 202209
Coolish | 202207
ガリガリ君 | 202207
PARM(パルム) | 202207
PARM(パルム) | 202208
雪見だいふく | 202208
雪見だいふく | 202209
sacre(サクレ) | 202208
postgres=# SELECT * FROM ice_cream;
id | name | type | price | delivery_date
----+----------------+----------------+-------+---------------
1 | ハーゲンダッツ | アイスクリーム | 300 | 2022-09-20
2 | スーパーカップ | ラクトアイス | 120 | 2022-08-01
3 | Coolish | ラクトアイス | 100 | 2022-09-20
4 | ガリガリ君 | 氷菓 | 60 | 2022-08-30
5 | PARM(パルム) | アイスクリーム | 150 | 2022-09-20
6 | 雪見だいふく | アイスミルク | 120 | 2022-09-20
7 | sacre(サクレ) | 氷菓 | 90 | 2022-08-01
IceCreamテーブルと、campaign_month_of_ice_creamを使用した case式を使って書いてみてください。
（ヒント：EXISTS もしくは IN を使用する）
💡 解答を開く 💡
（↓ EXISTS）
SELECT
IC.name,
CASE
WHEN EXISTS (SELECT name FROM campaign_month_of_ice_cream CIC WHERE campaign_month = '202207' AND CIC.name = IC.name)
THEN '○' ELSE '×' END AS "7月",
CASE
WHEN EXISTS (SELECT name FROM campaign_month_of_ice_cream CIC WHERE campaign_month = '202208' AND CIC.name = IC.name)
THEN '○' ELSE '×' END AS "8月",
CASE
WHEN EXISTS (SELECT name FROM campaign_month_of_ice_cream CIC WHERE campaign_month = '202209' AND CIC.name = IC.name)
THEN '○' ELSE '×' END AS "9月"
FROM ice_cream IC;
（↓ IN）
SELECT
name,
CASE
WHEN name IN (SELECT name FROM campaign_month_of_ice_cream WHERE campaign_month = '202207')
THEN '○' ELSE '×' END AS "7月",
CASE
WHEN name IN (SELECT name FROM campaign_month_of_ice_cream WHERE campaign_month = '202208')
THEN '○' ELSE '×' END AS "8月",
CASE
WHEN name IN (SELECT name FROM campaign_month_of_ice_cream WHERE campaign_month = '202209')
THEN '○' ELSE '×' END AS "9月"
FROM ice_cream;
どちらでも利用できますが、
EXISTSの方が、サブクエリ内の(name もしくは campaign_month)でインデックスが利用できるのでcampaign_month_of_ice_creamテーブルのサイズが大きくなった場合にパフォーマンス面で効果的です
以上です