SQLのHAVING句で全称量化（Vol.1）

はじめに

馴染みのない単語（全称量化）だと思うので、まずは用語の定義から確認しましょう。

全称量化と存在量化

SQLを支える基礎理論には、集合論と述語理論（predicate logic）があります。

SQLでいう述語とは、戻り値が真理値になる関数のことを言うそうです。

述語論理には、全称量化と存在量化が存在します。

それぞれ、以下のような意味です。

• 全称量化は「すべてのxが条件Pを満たす」
  • 例：すべてのサメは肉食である
• 存在量化は「条件Pを満たすxが(少なくとも1つ)存在する」
  • 例：魚類の中には肉食に分類されるものも存在する

SQLは、全称量化を実装していないそうです。

存在量化の表現を、ド・モルガンの法則を使って、全称量化表現ができるからです。それが NOT EXISTS です。

• 全称量化は
  • 「すべてのxが条件Pを満たす」 = 「条件Pを満たさないxが 存在しない」（これはNOT EXISTSで表現できる）
    • 二重否定で分かりにくいですが、結構使えます。
• 存在量化
  • 「条件Pを満たすxが(少なくとも1つ)存在する」 = 「すべてのxが条件Pを満たさない　わけではない」

今回の例題にて用いるデータ

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

用いるデータ

 CREATE TABLE members (
   id INT NOT NULL PRIMARY KEY auto_increment,
   class TEXT,
   status TEXT
);

INSERT INTO members VALUES
   (1,  'A', 'ready'),
   (2,  'A', 'running'),
   (3,  'A', 'ready'),
   (4,  'B', 'running'),
   (5,  'B', 'dormant'),
   (6,  'C', 'ready'),
   (7,  'C', 'ready'),
   (8,  'C', 'ready'),
   (9,  'D', 'ready'),
   (10, 'E', 'running'),
   (11, 'E', 'dormant'),
   (12, 'E', 'ready');

id	class	status
1	A	ready
2	A	running
3	A	ready
4	B	running
5	B	dormant
6	C	ready
7	C	ready
8	C	ready
9	D	ready
10	E	running
11	E	dormant
12	E	ready

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

今回の例題

membersテーブルには何かしらの行動が開始できるかどうかのステータスがデータ化されています。

この時「クラスの全メンバーが行動可能な状態（ready）　であることがわかるクエリ」を書いてください。

CASE1

まず、全称量化で表現できますよね。日本語にすると

「待機中ではないメンバーが1人もいない」

これをクエリで表現すると

-- 全称文を述語で表現
SELECT class, id
FROM members M1
WHERE NOT EXISTS (
	SELECT *
	FROM members M2
	WHERE M1.class = M2.class AND status <> 'ready'
)

結果は

class	id
C	6
C	7
C	8
D	9

CASE2

HAVING句を使った書き方もできます。

日本語にすると「クラスのすべてのメンバーが行動可能な状態（ready）　である」

-- 全称文を集合で表現
SELECT class
FROM members
GROUP BY class
HAVING COUNT(*) = SUM(CASE WHEN status = 'ready' THEN 1 ELSE 0 END)

結果は

class
C
D

欲しい集合はCとDです。

この2つが持っていて他のクラスが持っていないものは、readyなメンバー数（行数）とそのクラスの集合全体のメンバー数（行数）が一致しているということです。

以下のような4列目のフラグを持たせて、各クラスの全員が1の集合だけ抽出しているイメージです。

id	class	status	flag
1	A	ready	1
2	A	running	0
3	A	ready	1
4	B	running	0
5	B	dormant	0
6	C	ready	1
7	C	ready	1
8	C	ready	1
9	D	ready	1
10	E	running	0
11	E	dormant	0
12	E	ready	1

ちなみに、クエリは以下

SELECT
  *,
  CASE WHEN status = 'ready' THEN 1 ELSE 0 END AS flag
FROM members

これをCASE式で表現しています。

本日は以上です。

次回はCASE 3を見ていきましょう！

参照

120 - 122p

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