可視化 SQL | HAVING,CASE,EXISTS を使ってみる

初めに

この問題は、SQLパズル #20 テスト結果 を参考にしています
パズルの詳細はこちらの本をご確認ください

TestResults には科目毎の test_step をもっています。test_step は科目毎に違います。
test_step が2つの科目もあれば、3つの科目もあります。
test_step が完了すると comp_date に完了日が入ります

科目には、『Reading』『Math』『Chemistry』『Music』が有ります
全ての test_step が完了している科目を SQL で取り出します

テーブル と データ

SQL

CREATE TABLE TestResults(
	test_name CHAR(20) NOT NULL,
	test_step INTEGER NOT NULL,
	comp_date DATE, -- NULLは未完了を意味する
	PRIMARY KEY (test_name, test_step)
);

INSERT INTO TestResults VALUES('Reading', 1, '2006-03-10');
INSERT INTO TestResults VALUES('Reading', 2, '2006-03-12');
INSERT INTO TestResults VALUES('Math', 1, NULL);
INSERT INTO TestResults VALUES('Math', 2, '2006-03-12');
INSERT INTO TestResults VALUES('Chemistry', 1, '2006-03-08');
INSERT INTO TestResults VALUES('Chemistry', 2, '2006-03-12');
INSERT INTO TestResults VALUES('Chemistry', 3, '2006-03-15');
INSERT INTO TestResults VALUES('Music', 1, '2006-03-10');
INSERT INTO TestResults VALUES('Music', 2, NULL);
INSERT INTO TestResults VALUES('Music', 3, '2006-03-14');

■ テーブルの中身

確認：comp_date が空白の科目を確認する

SQL

-- a1に科目を取出している
SELECT T1.*,
  (SELECT test_name FROM TestResults AS T2 
   WHERE T1.test_name = T2.test_name AND T2.comp_date IS NULL) AS a1
FROM TestResults AS T1

TestResultsを自己結合して、条件に同じ test_name を使っているので
各科目の comp_date に NULL が入っていれば、その科目が a1 に表示される

確認：完了している科目を取出す

SQL

-- 上記SQLの結果となる科目ではない・・・科目
SELECT T1.*
FROM TestResults AS T1
WHERE NOT EXISTS (
	SELECT * FROM TestResults AS T2
	WHERE T1.test_name = T2.test_name AND T2.comp_date IS NULL
)

▼ 条件に合う科目

test_name	test_step	comp_date
Reading	1	2006-03-10
Reading	2	2006-03-12
Chemistry	1	2006-03-08
Chemistry	2	2006-03-12
Chemistry	3	2006-03-15

取出したい科目は全test_step が完了している科目。
言い換えると先程確認した、『完了していない科目』 以外の科目 となります

NOT EXISTS() で 『Math』 と 『Music』 以外を定義しています

直訳すると、『Math』 と 『Music』が存在しない科目
『Reading』 と 『Chemistry』 が取り出したい科目になります

『Reading』 と 『Chemistry』を取出す・・・と言うより
必要ない科目を取り除くと
『Reading』 と 『Chemistry』が TestResults に残っている・・・の方が自然かもしれません

回答 : SQL(その１)

SQL

SELECT DISTINCT test_name -- 複数あるので1だけ取り出す
FROM TestResults AS T1
WHERE NOT EXISTS (
	SELECT * FROM TestResults AS T2
	WHERE T1.test_name = T2.test_name AND T2.comp_date IS NULL
)

▼ 条件に合う科目

test_name
Reading
Chemistry

確認：別解(その２) COUNT()関数の特性を利用する

SQL

SELECT T1.*,
	(SELECT array_agg(comp_date) FROM TestResults AS T2 
	 WHERE T1.test_name = T2.test_name),
	 
	(SELECT COUNT(*) FROM TestResults AS T2 
	 WHERE T1.test_name = T2.test_name) AS a2,
	 
	(SELECT COUNT(comp_date) FROM TestResults AS T2 
	 WHERE T1.test_name = T2.test_name) AS a3
	 
FROM TestResults AS T1

▼ 出力

count()関数は、count(*) と count(フィールド) では、場合によって結果が異なります

count(*) は NULL となる行もカウント
　⇒　a2
count(フィールド) は フィールドがNULLの時はカウントしません
　⇒　a3

確認：COUNT関数で取得できた数

SQL

SELECT test_name,
		COUNT(*),
		COUNT(comp_date)
FROM TestResults
GROUP BY test_name

▼ 条件に合う科目

全test_step が完了している場合、comp_date には日にちが入っている(NULL ではない)
その為、count(*) の数と count(comp_date) の数は同じになるはず

しかし、全test_step が完了していない場合、 count(comp_date) は NULL をカウントしないので
count(*) の数と count(comp_date) の数が同じにならない

この特性を利用して、全test_step が完了している科目を見つける

回答 : SQL(その２)

SQL

SELECT test_name
FROM TestResults
GROUP BY test_name  -- test_name で集約して
HAVING COUNT(*) = COUNT(comp_date)  --count関数の数が一致しているデータを取出す

▼ 条件に合う科目

test_name
Reading
Chemistry

参考文献

SQLパズル 第2版~プログラミングが変わる書き方/考え方 | Joe Celko, ミック