【SQL 6問】問題形式でCASE式を理解する

# 対象読者

* SELECT文や、WHERE句、論理演算子については理解している
* CASE文がなんとなく、条件分岐するものってのが分かる

# CASE式とは？

* 式とつくため、演算を行う式
    * **式が使用できる箇所ではCASE式が使用できる**
    * 1+1も式
* 条件分岐に使用する
* 単純case式と、検索case式がある
    * 基本的には、検索case式の方が柔軟なので使用することが多いみたい

```sql
CASE WHEN <評価式> THEN <式>
     WHEN <評価式> THEN <式>
     WHEN <評価式> THEN <式>
     ELSE <式>
END
```

# 問題の前に初期データの準備

DBの準備はできている前提で進めていきます

私の環境では、**PostgreSQL 14.4** を使用しています

```$ psql -U postgres``` でDBへ接続

まずは、アイスクリームテーブルを作成します

```sql
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)
```

# ここから問題スタートです

問題の後に、解答を隠して記載してあるので自分で解答を考えることが出来る形式になっています

解答は以下のようなアコーディオンにしています

<details><summary><font color="red">💡 解答を開く 💡</font></summary><div>

ここに解答が書かれている

</div></details>

## 1. 種類(type)に付加情報を付けて出力する

IceCreamテーブルには、`アイスクリーム、アイスミルク、ラクトアイス、氷菓` 4種類のアイスが存在します

実際にもアイスクリームは以下の4種類に分けられるみたいです。

![スクリーンショット 2022-11-03 13.31.46.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/536974/9af064a1-b966-5d3d-2d7d-10f20cbdfd92.png)

では、分かりやすくそれぞれの種類(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文を使用すると以下で出力できます

<details><summary><font color="red">💡 解答を開く 💡</font></summary><div>

解答は、単純CASE式と、検索CASE式で書いた2パターン用意しました

### 単純CASE式
評価したい式(ここで言う、列の事）を記載後は再度、`type`を記載する必要がないです。簡潔にかけることがメリットではありますが、条件に縛りが生まれます

```sql
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の後に毎度条件を書かなければいけませんが、それぞれの条件を細かく使い分けることができます。

```sql
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列`を表しています

</div></details>

## 2. 種類(type)ごとの平均単価価格(price)を列として出力する

行として出力するパターンと、列として出力するパターンがありますがそれぞれで使用するSQL文はもちろん異なります

まずは、**行**として出力するパターンでは今回のテーマのCASE文ではなく、`GRUOP BY`を使用してグループごとの集計を行います

`CASE`文では無いので解答は以下に書いちゃってます

### 行として出力する場合

```
      type      |      avg_price
----------------+----------------------
 アイスクリーム     | 225.0000000000000000
 氷菓            | 75.0000000000000000
 アイスミルク       | 120.0000000000000000
 ラクトアイス       | 110.0000000000000000
(4 rows)
```

SQLは以下です

```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 式を使用します
解答は以下です。

<details><summary><font color="red">💡 解答を開く 💡</font></summary><div>

(単純CASE式)
```sql
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式)
```sql
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;
```

</div></details>

## 3. 値段区分ごとの商品個数を求める

高いアイス・・・150円以上
普通のアイス・・・100円以上、150円未満
安いアイス・・・100円未満

とし、CASE式を使用して列で分類します

分類すると以下の様になります

```
 high_price | middle_price | low_price
------------+--------------+-----------
          2 |            3 |         2
(1 row)
```

<details><summary><font color="red">💡 解答を開く 💡</font></summary><div>

```sql
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;
```

</div></details>

## 4. 一番好きなアイス / そうでないアイス　それぞれの列として人数を集計したテーブルを作成

言葉で説明しきれなかったので実際に作成するものを表で出します

その前に、、、

まずは、100人に聞いた一番好きなアイスクリームを集計した`MostLikeIceCream` テーブルを作成。

```sql
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
```

<details><summary><font color="red">💡 解答を開く 💡</font></summary><div>

```sql
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を使用しています

</div></details>

## 5. 100円以上、140円未満の商品は20%の値上げ。140円以上の商品は10%の値上げをする

`ice_cream`テーブルの更新を行います
「CASE文使わなくても2回UPDATEすれば良いのでは？？」

```sql
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内で条件分岐をする必要がありますね。

解答は以下です

<details><summary><font color="red">💡 解答を開く 💡</font></summary><div>
ここで使用するのがCASE式です。UPDATE内で1度で条件分岐で済みます。
正しくは以下のSQLですね

```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`が出力されてしまうのでここでは必須になります。
</div></details>

## 6. 各商品のキャンペーン月を調べる

今回出力したい情報はこちら

```
      name      | 7月 | 8月 | 9月
----------------+-----+-----+-----
 ハーゲンダッツ    | ×   | ×   | ○
 スーパーカップ    | ○   | ○   | ○
 Coolish        | ○   | ×   | ×
 ガリガリ君        | ○   | ×   | ×
 PARM(パルム)    | ○   | ○   | ×
 雪見だいふく      | ×   | ○   | ○
 sacre(サクレ)   | ×   | ○   | ×
```

まずは、
7~9月の内、アイスクリームのキャンペーンを打った月の情報を持った`campaign_month_of_ice_cream`テーブルを作成します

```sql
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');
```

</div></details>

```
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 を使用する）

<details><summary><font color="red">💡 解答を開く 💡</font></summary><div>

（↓ EXISTS）

```sql
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）

```sql
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`テーブルのサイズが大きくなった場合にパフォーマンス面で効果的です

</div></details>

以上です