CASE式で特定の条件を満たす集合を定義する練習をした

## はじめに

HAVING句や、CASE式で特定の条件を満たす集合のクエリを書いていきましょう。

実行結果はこちらで確認できます。

https://www.db-fiddle.com/

## 用いるデータ

```sql
CREATE TABLE members (
  id INT NOT NULL PRIMARY KEY auto_increment,
  class TEXT,
  gender TEXT,
  score INT
);

INSERT INTO members VALUES
   (1,  'A', 'men', 100),
   (2,  'A', 'women', 100),
   (3,  'A', 'women', 49),
   (4,  'A', 'men', 30),
   (5,  'B', 'women', 100),
   (6,  'B', 'men', 92),
   (7,  'B', 'men', 80),
   (8,  'B', 'men', 80),
   (9,  'B', 'women', 10),
   (10, 'C', 'men', 92),
   (11, 'C', 'men', 80),
   (12, 'C', 'women', 21),
   (13, 'D', 'women', 100),
   (14, 'D', 'women', 0),
   (15, 'D', 'women', 0);
```


| id  | class | gender | score |
| --- | ----- | ------ | ----- |
| 1   | A     | men    | 100   |
| 2   | A     | women  | 100   |
| 3   | A     | women  | 49    |
| 4   | A     | men    | 30    |
| 5   | B     | women  | 100   |
| 6   | B     | men    | 92    |
| 7   | B     | men    | 80    |
| 8   | B     | men    | 80    |
| 9   | B     | women  | 10    |
| 10  | C     | men    | 92    |
| 11  | C     | men    | 80    |
| 12  | C     | women  | 21    |
| 13  | D     | women  | 100   |
| 14  | D     | women  | 0     |
| 15  | D     | women  | 0     |


このデータを用いた例題を解いていきましょう

https://qiita.com/ryosuketter/items/0031f14b5f3fc1ded050

この記事にあるようにベン図を書きながらクエリを書くのがいいかと思います。

## 例題1「クラスの75%以上の生徒が80点以上のクラスを抽出せよ」

ベン図

クラスAだけをベン図にすると、以下のような感じです。

![IMG_820A51DF08E6-1.jpeg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/14191/bc3a8653-9432-579c-f5ab-547d5586c3f6.jpeg)

Query

```sql
SELECT *
FROM members
GROUP BY class
HAVING COUNT(*) * 0.75 <= SUM(
  CASE
    WHEN score >= 80
    THEN 1 
    ELSE 0 END);
```

Results

| id  | class | gender | score |
| --- | ----- | ------ | ----- |
| 5   | B     | women  | 100   |


ただし、このクエリはMySQL（v5.6）までしか使えないでしょう（デフォルトの設定しかしていなければ）。

なぜならば、MySQL(v5.7)からはデフォで`ONLY_FULL_GROUP_BY`オプションが有効になっているからです。

有効の場合、GROUP BYするカラムだけしか参照できなくなります（無効だと、GROUP BYしたカラムじゃなくても参照できます）。

https://dev.mysql.com/doc/refman/8.0/ja/group-by-handling.html

## 例題2「50点以上のスコアを取った生徒のうち男子の数が女子よりも多いクラスを抽出せよ」

ベン図

クラスA（50点以上のスコアを取った生徒のうち男子の数が女子よりも少ない場合）と
B（50点以上のスコアを取った生徒のうち男子の数が女子よりも多い場合）だけをベン図にすると、以下のような感じです。

![IMG_090BC3631E53-1.jpeg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/14191/af05d8e6-f426-e834-77e1-5c4ed4dab17c.jpeg)

Query

```sql
SELECT class
FROM members
GROUP BY class
HAVING 
  SUM(CASE WHEN score >= 50 AND gender = 'men' THEN 1 ELSE 0 END) >
  SUM(CASE WHEN score >= 50 AND gender = 'women' THEN 1 ELSE 0 END)
```

Results

| class |
| ----- |
| B     |
| C     |


## 例題3「女子の平均点が、男子の平均点よりも高いクラスを抽出せよ」

ベン図

クラスAだけをベン図にすると、以下のような感じです。

Query

```sql
SELECT *
FROM members
GROUP BY class
HAVING
  AVG(CASE WHEN gender = 'men' THEN score ELSE 0 END)
  < AVG(CASE WHEN gender = 'women' THEN score ELSE 0 END)
```

Results

| id  | class | gender | score |
| --- | ----- | ------ | ----- |
| 1   | A     | men    | 100   |
| 13  | D     | women  | 100   |

Dクラスは女子しかいません。上のクエリでは男子は0として返されるので、最終的な演算結果は

```
0 < 33.3（(100+0+0)/3）
```

がTRUEになります。それでも、間違いでは無いのですが空集合に対する平均は未定義であるべきでしょう。

SQL標準では、空集合にAVG関数を通すと、NULLを返すことと知られています。

https://www.firebirdsql.org/file/documentation/html/en/firebirddocs/nullguide/firebird-null-guide.html#nullguide-aggrfunc

Query

```sql
SELECT *
FROM members
GROUP BY class
HAVING
  AVG(CASE WHEN gender = 'men' THEN score ELSE NULL END)
  < AVG(CASE WHEN gender = 'women' THEN score ELSE NULL END)
```

これで、Dクラスの男子の平均はNULLになります。女子の平均点によらず集計対象外になりますし、こちらの方が、通常のAVG関数とも一致しますし、いいですよね。

Results

| id  | class | gender | score |
| --- | ----- | ------ | ----- |
| 1   | A     | men    | 100   |

本日は以上です。

## 参照

https://www.amazon.co.jp/dp/4798157821/

116 - 119p

## アウトプット100本ノック実施中

https://qiita.com/ryosuketter/items/713474dbd009870e4103