可視化 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: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');
```
■ テーブルの中身
![rep_1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/225631ac-1a8b-fd50-49f8-ff8c2e103d9a.png)

# 確認：comp_date が空白の科目を確認する
```SQL: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 に表示される

![rep_2.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/e0da9bfb-8ac5-9c36-37a7-f6b9dded48d1.png)

# 確認：完了している科目を取出す
```SQL: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 に残っている・・・の方が自然かもしれません

![rep_4.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/06ce9909-4eb6-057e-c9fd-003e1977dc7d.png)


# 回答 : SQL(その１)
```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: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


![rep_1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/c9065323-393f-0c5c-2ef8-b7c1f552eed1.png)

# 確認：COUNT関数で取得できた数
```SQL:SQL
SELECT test_name,
		COUNT(*),
		COUNT(comp_date)
FROM TestResults
GROUP BY test_name
```
▼ 条件に合う科目

![rep_2.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/766e9408-630d-d92f-69e0-47035b819834.png)


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

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

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

# 回答 : SQL(その２)
```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, ミック](https://www.shoeisha.co.jp/book/detail/9784798114132)