SQLで全称量化をHAVINGで表現する場合とNOT EXISTSで表現した場合の違い

はじめに

今回、書くクエリ

step1まで完了しているproject_idが欲しい

2パターン書いていきます。

• 集合レベルの操作を行うという点で EXISTS と HAVING はわりと互換性があるそうです
• 比較して書いていきます

以下のような、工程管理プロジェクトテーブルがあるとします。

各工程の完了しているかどうかの列が status にあります。

id	project_id	step	status
1	AA100	0	completed
2	AA100	1	stand-by
3	AA100	2	stand-by
4	B200	0	stand-by
5	B200	1	stand-by
6	CS300	0	completed
7	CS300	1	completed
8	CS300	2	stand-by
9	CS300	3	stand-by
10	DY400	0	completed
11	DY400	1	completed
12	DY400	2	completed

Schema

CREATE TABLE projects (
  id INT NOT NULL PRIMARY KEY auto_increment,
  project_id TEXT,
  step INT,
  status TEXT
);
    
INSERT INTO projects VALUES
	(1, 'AA100', 0, 'completed'),
	(2, 'AA100', 1, 'stand-by'),
	(3, 'AA100', 2, 'stand-by'),
	(4, 'B200', 0, 'stand-by'),
	(5, 'B200', 1, 'stand-by'),
	(6, 'CS300', 0, 'completed'),
	(7, 'CS300', 1, 'completed'),
	(8, 'CS300', 2, 'stand-by'),
	(9, 'CS300', 3, 'stand-by'),
	(10, 'DY400', 0, 'completed'),
	(11, 'DY400', 1, 'completed'),
	(12, 'DY400', 2, 'completed');

HAVINGを使った集合指向的なクエリ

Query

SELECT project_id
FROM projects
GROUP BY project_id
HAVING COUNT(*) = SUM(CASE WHEN step <= 1 AND status = 'completed' THEN 1
                      WHEN step > 1 AND status = 'stand-by' THEN 1
                      ELSE 0 END)

各プロジェクトごと、

• stepが1以下でcompletedの行数
• stepが1より大きくてでstand-byの行数

を足した場合、グループ全体の行数と一致する。という感じで検索しています。

Results

project_id
CS300

NOT EXISTSを使った場合

まず、全称命題を書きます。

全行（全プロジェクト）について肯定番号が1以下ならばcompletedで、1より大きければstand-byである

これをNOT EXISTSで表現すれば、全称量化的なクエリになります。

Query

SELECT *
FROM projects P1
WHERE NOT EXISTS (
	SELECT status
	FROM projects P2
	WHERE P1.project_id = P2.project_id
    AND status <> CASE WHEN step <= 1 THEN 'completed'
                                      ELSE 'stand-by' END);

Results

id	project_id	step	status
6	CS300	0	completed
7	CS300	1	completed
8	CS300	2	stand-by
9	CS300	3	stand-by

NOT EXISTSを使った場合

デメリット

• 意味がわかりにくい（二重否定なので）

メリット

• パフォーマンスがいい
  • 1行でも満たさない条件があればそこで検索を終了できるので
  • 結合条件でproject_id列のindexも利用できるので、さらに高速化が期待できる
• 結果に含められる情報量が多いこと
  • HAVINGの方は集約されるのでproject_idしかわからない

以上です。

参照

96 - 98p

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