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

はじめに

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

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

用いるデータ

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

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

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

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

ベン図

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

Query

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したカラムじゃなくても参照できます）。

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

ベン図

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

Query

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

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を返すことと知られています。

Query

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

本日は以上です。

参照

116 - 119p

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