可視化 SQL | HAVING,CASE,COUNT(), 2重否定 を使ってみる

# 初めに
この問題は、SQLパズル #11 作業依頼 を参考にしています

仕事の作業工程が入力されているテーブルから、一定の条件を満たす工程を取出す問題です

ある一定の条件とは
①　step_nbr が 『0』 でかつstep_status が 『C』である
②　同じ workorder_id で step_nbr が 『0』以外の工程の step_status が 『W』である

つまり、step_nbrが 『0』となっている工程のみ完了している仕事を取出す問題です

取出すSQLは10行にも満たない短いものですが、その考え方が面白い。
HAVING や CASE文, COUNT関数 の使いかたの練習にはとてもいい問題です。

#Projects テーブル データ
```SQL:SQL
CREATE TABLE Projects
(
	workorder_id CHAR(5) NOT NULL,
	step_nbr INTEGER NOT NULL CHECK (step_nbr BETWEEN 0 AND 1000),
	step_status CHAR(1) NOT NULL CHECK (step_status IN ('C', 'W')), -- Cは完了、Wは待機
	PRIMARY KEY (workorder_id, step_nbr)
);

INSERT INTO Projects VALUES('AA100', 0, 'C' );
INSERT INTO Projects VALUES('AA100', 1, 'W' );
INSERT INTO Projects VALUES('AA100', 2, 'W' );
INSERT INTO Projects VALUES('AA200', 0, 'W' );
INSERT INTO Projects VALUES('AA200', 1, 'W' );
INSERT INTO Projects VALUES('AA300', 0, 'C' );
INSERT INTO Projects VALUES('AA300', 1, 'C' );
```

このテーブルから、青色の行 workorder_id = AA100, step_nbr = 0 の工程を取出したい

![rep_1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/8f505f6c-4cb4-dd45-47f9-31d8bf77e9e7.png)


# 回答SQL (その１)
```SQL:SQL
SELECT workorder_id
FROM Projects AS P1
WHERE step_nbr = 0 AND step_status = 'C' -- step_nbr が 『0』 かつstep_status が 『C』
   AND 'W' = ALL (SELECT step_status
                  FROM Projects AS P2
                  WHERE step_nbr <> 0 AND P1.workorder_id = P2.workorder_id)
```

▼ 条件に合う工程

|workorder_id|
|---|
|AA100|

---

WHERE句で条件①が記述されている
　⇒step_nbr が 『0』 かつstep_status が 『C』

その下の W = ALL (SELECT step_status FROM ・・・)　の部分ですが
**SELECT で取り出す step_status が全て『W』の時、ALL(TRUE) となります**

■ TRUE の時
W = ALL (W,W,W,W,W,W)

SELECT文で、すべて 『W』 となる時 『TRUE』 が返ってきます

■ FALSE の時
W = ALL (W,**C**,W,W,W,W)

SELECT文で、1つでも 『C』 があると 『FALSE』 が返ってきます

# 回答SQL(その１) の中身を確認する
```SQL:SQL
-- 確認SQL
SELECT P1.*,
	(SELECT array_agg(step_status) FROM Projects AS P2
     WHERE step_nbr <> 0 AND P1.workorder_id = P2.workorder_id) AS a1
FROM Projects AS P1
--WHERE step_nbr = 0 AND step_status = 'C' -- step_nbr が 『0』 かつstep_status が 『C』
```

![rep_2.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/d78124fb-300e-35b1-c8bc-06a4beae8e83.png)

# 回答SQL(その１) の中身を確認する
```SQL:SQL
-- 確認SQL
SELECT P1.*,
	(SELECT array_agg(step_status) FROM Projects AS P2
     WHERE step_nbr <> 0 AND P1.workorder_id = P2.workorder_id) AS a1
FROM Projects AS P1
WHERE step_nbr = 0 AND step_status = 'C' -- step_nbr が 『0』 かつstep_status が 『C』
```

![rep_3.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/96f5cee1-5dcd-7ea3-c034-e03a25dbe312.png)

# 回答SQL (その２)
```SQL:SQL
SELECT workorder_id
FROM Projects
GROUP BY workorder_id
HAVING SUM(CASE WHEN step_nbr <> 0 AND step_status = 'W' THEN 1
                WHEN step_nbr = 0  AND step_status = 'C' THEN 1
                ELSE 0 END) = COUNT(step_nbr);
```
▼ 条件に合う工程

|workorder_id|
|---|
|AA100|

# 回答SQL(その２) の中身を確認する
```SQL:SQL
SELECT P1.*, 
   CASE WHEN P1.step_nbr <> 0 AND P1.step_status = 'W' THEN 1  ---- 条件①
        WHEN P1.step_nbr = 0  AND P1.step_status = 'C' THEN 1  ---- 条件②
        ELSE 0 END AS cnt  ---------------------------------------- 条件③
FROM Projects AS P1
```

条件①：step_nbr が 『0』 以外の時、step_status が 『W』
条件②：step_nbr が 『0』 の時、step_status が 『C』
条件③：条件①、条件②に当てはまらない時は条件③となる

![rep_4.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/4f3e8c7c-933d-ef4c-f684-af0e887cf4a7.png)



# 回答SQL(その２) の中身を確認する
```SQL:SQL
-- 画像右
SELECT workorder_id, COUNT(*),
   SUM(CASE WHEN step_nbr <> 0 AND step_status = 'W' THEN 1
            WHEN step_nbr = 0  AND step_status = 'C' THEN 1
            ELSE 0 END) AS cnt
FROM Projects
GROUP BY workorder_id
```

![rep_5.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/2395ff17-e878-640f-6a06-d050caa04cfe.png)


# 回答SQL (その３)
```SQL:SQL
SELECT workorder_id
FROM Projects
GROUP BY workorder_id
HAVING COUNT(*)
   = COUNT(CASE WHEN step_nbr = 0 AND step_status = 'C'
                THEN 1
                ELSE NULL END)
    + COUNT(CASE WHEN step_nbr <> 0 AND step_status = 'W'
                THEN 1
                ELSE NULL END)
```
▼ 条件に合う工程

|workorder_id|
|---|
|AA100|
----

『その３』は『その２』と微妙に違う・・・が同じ考え方をしている。
この問題を解いた時に、**CASE文やCOUNT関数の使いかたって面白いな・・・** と
思った事を覚えています。『その２』の方が記述が短く、スマートです！！

もう少し説明すると・・・

『その２』では、**COUNT(step_nbr)**と記述しています
これは step_nbr が `NULL の時はカウントしません。`

『その３』では **COUNT(*)**と記述しています
これは集約された行が `NULL もカウントします`

ここでは詳しく書きませんがCOUNT関数の大事な仕様です。
CASE文は頻繁に使用するので色んな使いかたを覚えるのはスキルアップにつながります

![rep_7.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/44ac633a-671c-89de-7a96-ec8b94e2f301.png)


# 回答SQL (その４)
```SQL:SQL
SELECT DISTINCT workorder_id  --データが3つとなるので1つに集約
FROM Projects P1
WHERE NOT EXISTS(
  SELECT step_status FROM Projects P2
  WHERE P1.workorder_id = P2. workorder_id
    AND step_status <> CASE WHEN step_nbr = 0
                            THEN 'C'
                            ELSE 'W' END)
```
▼ 条件に合う工程

|workorder_id|
|---|
|AA100|
----
このSQLは 『SQLパズル』の翻訳者、ミックさんの別解となります。
HAVING を使用せず、**二重否定**で同じ工程を取出しています。

![rep_9.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/4993747d-e8b8-7ff8-e726-4ee083d81d2b.png)


内側の枠内で 
step_nbr が 『0』 の時 **step_status は 『C』 では無い**
step_nbr が 『0』 以外の時の **step_statu は 『W』では無い**

こんな **内側の条件を満たすデータは存在しない(NOT EXISTS)** データを外側で取り出します
　⇒内側の否定を外側で再度否定しています。
　　ここが2重否定の考え方です

内側の条件に当てはまらない、データが存在しない、データを取出す
　　　　　　⇒否定　　　　　　　　　　⇒否定
つまり
内側のデータに当てはまる、データが存在する、データを取出す

**否定を否定すると、肯定になる**・・・考え方です

外側の枠で内側のデータを除外している
　⇒　step_nbr が 『0』 の時 **step_status は 『C』 では無い**
　⇒　このデータが無くなる・・・ので
　⇒　step_nbr  が『0』 の時 step_status は 『C』 が結果的に残る

　⇒　step_nbr が 『0』 以外の時の **step_statu は 『W』では無い**
　⇒　このデータが無くなる・・・ので
　⇒　sstep_nbr  が『0』 以外の時の step_statu は 『W』が結果的に残る


この考え方に慣れるまではピンと来ないかもしれませんが、SQLではよくある記述方法です
`関係除算`とか`二重否定`で検索してみてください


# 参考文献
[SQLパズル 第2版~プログラミングが変わる書き方/考え方 | Joe Celko, ミック](https://www.shoeisha.co.jp/book/detail/9784798114132)