可視化 SQL | NULL データの賢い使い方 と テーブル JOIN


# 初めに
この問題は、SQLパズル #12 訴訟の進行状況 を参考にしています
手元に『SQL パズル』があれば問題の詳細が記載されているのでよりわかりやすいです

この問題は裁判の進行状況を確認する問題です

`『可視化 SQL | 複雑なサブクエリ と テーブル JOIN』` と **同じ内容**
・・・ですが、アプローチが違います

裁判は３つあって、claim_id が 『10』『20』『30』 としています
この記事では 裁判10、裁判20、裁判30　としています 
各裁判には被告人がいて、それぞれ進行状況が違います

取出したいデータは 同じ claim_id の裁判で、進行状況が一番遅い状態の裁判となります

![err0.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/030a62e3-f47d-0e82-581a-356df9c9da46.png)

PostgreSQL で動作確認しています

#テーブル と データ
```SQL:SQL
-- 裁判の原告の一覧
CREATE TABLE Claims(
	claim_id integer,
	patient_name text
);

INSERT INTO Claims VALUES(10,'Smith');
INSERT INTO Claims VALUES(20,'Johns');
INSERT INTO Claims VALUES(30,'Brown');

-- 裁判の被告人の一覧
CREATE TABLE Defendants(
	claim_id integer,
	defendant_name text
);

INSERT INTO Defendants VALUES(10,'Johnson');
INSERT INTO Defendants VALUES(10,'Mayer');
INSERT INTO Defendants VALUES(10,'Dow');
INSERT INTO Defendants VALUES(20,'Baker');
INSERT INTO Defendants VALUES(20,'Mayer');
INSERT INTO Defendants VALUES(30,'Johnson');

-- 裁判の進行状況が確認できるデータ
CREATE TABLE LegalEvents(
	claim_id integer,
	defendant_name text,
	claim_status text,
	change_date text
);

INSERT INTO LegalEvents VALUES(10,'Johnson','AP','1994-01-01');
INSERT INTO LegalEvents VALUES(10,'Johnson','OR','1994-02-01');
INSERT INTO LegalEvents VALUES(10,'Johnson','SF','1994-03-01');
INSERT INTO LegalEvents VALUES(10,'Johnson','CL','1994-04-01');
INSERT INTO LegalEvents VALUES(10,'Mayer','AP','1994-01-01');
INSERT INTO LegalEvents VALUES(10,'Mayer','OR','1994-02-01');
INSERT INTO LegalEvents VALUES(10,'Mayer','SF','1994-03-01');
INSERT INTO LegalEvents VALUES(10,'Dow','AP','1994-01-01');
INSERT INTO LegalEvents VALUES(10,'Dow','OR','1994-02-01');
INSERT INTO LegalEvents VALUES(20,'Mayer','AP','1994-01-01');
INSERT INTO LegalEvents VALUES(20,'Mayer','OR','1994-02-01');
INSERT INTO LegalEvents VALUES(20,'Baker','AP','1994-01-01');
INSERT INTO LegalEvents VALUES(30,'Johnson','AP','1994-01-01');

-- 進行状況は4段階に分かれている
CREATE TABLE ClaimStatusCodes(
	claim_status text,
	claim_status_desc text,
	claim_seq integer
);

INSERT INTO ClaimStatusCodes VALUES('AP','Awaiting review panel',1);
INSERT INTO ClaimStatusCodes VALUES('OR','Panel opinion rendered',2);
INSERT INTO ClaimStatusCodes VALUES('SF','Suit filed',3);
INSERT INTO ClaimStatusCodes VALUES('CL','Closed',4);
```

▼ テーブルデータ

![rep_1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/027a99e5-896c-48ac-21ac-19e83997db6a.png)

#回答 SQL
```SQL:SQL
SELECT C1.claim_id, C1.patient_name,
   CASE MIN(S1.claim_seq) WHEN 2 THEN 'AP'
                          WHEN 3 THEN 'OR'
                          WHEN 4 THEN 'SF'
                          ELSE 'C1' END
FROM
(
	(
		Claims AS C1
		INNER JOIN
		Defendants AS D1 ON C1.claim_id = D1.claim_id
	) CROSS JOIN ClaimStatusCodes AS S1
)
LEFT OUTER JOIN
	LegalEvents AS E1 ON C1.claim_id  = E1.claim_id
		AND D1.defendant_name = E1.defendant_name
		AND S1.claim_status = E1.Claim_status

WHERE E1.claim_id IS NULL
GROUP BY C1.claim_id, C1.patient_name
```

▼ 出力結果は 『複雑なサブクエリ と テーブル JOIN』 と同じです

この回答SQLは全テーブルを順番に結合しています
『Claims』 と 『Defendants』 を INNER JOIN
　　⇒　『ClaimStatusCodes』 を CROSS JOIN
　　　⇒　『LegalEvents』 を LEFT JOIN

本に記載されているSQLは記述が簡略化されています(JOIN部分)
すこし手直しして、上から順番に解説していきます

# 最初の INNER JOIN
```SQL:SQL
SELECT C1.*, D1.*
FROM Claims AS C1
INNER JOIN Defendants AS D1 ON C1.claim_id = D1.claim_id
```

▼画像左側が出力です

原告が起こした『裁判 10』『裁判 20』『裁判 30』で
訴えられている被告人の組合せを INNER JOIN で作成している

![rep_2.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/c90c4ddf-bc8a-1aa0-e03b-de5ffdb629af.png)

# 次の CORSS JOIN
```SQL:SQL
SELECT C1.*, D1.*,S1.*
FROM Claims AS C1
INNER JOIN Defendants AS D1 ON C1.claim_id = D1.claim_id
CROSS JOIN ClaimStatusCodes AS S1
```

▼画像左側が出力です

最初の INNER JOIN したでーたに、『ClaimStatusCodes』テーブルを CROSS JOIN します

これは、『裁判 10』『裁判 20』『裁判 30』には複数の被告人がいて
同じ裁判でも被告人によって進行状況が違います

ClaimStatusCodes が持っている進捗状況を示す1 ~ 4 番の claim_seq を
各被告の裁判に結合します
　⇒　全ての裁判の全ての進行状況が分かる元データを作ります

![rep_3.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/e340f3a4-fd21-7cbd-7b4c-02bd59209981.png)

# 最後の LEFT JOIN
```SQL:SQL
SELECT a1.*,E1.*
FROM
(
	SELECT C1.*, D1.defendant_name,S1.*
	FROM Claims AS C1
	INNER JOIN Defendants AS D1 ON C1.claim_id = D1.claim_id
	CROSS JOIN ClaimStatusCodes AS S1
) AS a1
LEFT JOIN LegalEvents AS E1 ON a1.claim_id = E1.claim_id
  AND a1.defendant_name = E1.defendant_name
  AND a1.claim_status = E1.Claim_status
```

▼ 『次のCROSS JOIN』に 現在進行している裁判データ(LegalEvents)を結合します

結合する時の条件は
　　[1] 同じ claim_id
　　　　⇒　同じ裁判を紐づける
　　[2] 同じ defendant_name
　　　　⇒　同じ裁判で、同じ被告人を紐づける
　　[3] 同じ claim_status
　　　　⇒　同じ裁判で、同じ被告人で、裁判の進行状況を紐づける

結合の条件が3つありますので、結合の後の行数は『次のCROSS JOIN』と同じです
元データに対して、進行中の裁判状況を紐づけしています
ここから空白のデータのある行は、まだ完了していないステータスだと見てわかります

![rep_4.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/cd085428-7747-55f2-b2ae-59d7e4047fce.png)


# NULL となっているデータの取出し
```SQL:SQL
SELECT a1.*,E1.*
FROM
(
	SELECT C1.*, D1.defendant_name,S1.*
	FROM Claims AS C1
	INNER JOIN Defendants AS D1 ON C1.claim_id = D1.claim_id
	CROSS JOIN ClaimStatusCodes AS S1
) AS a1
LEFT JOIN LegalEvents AS E1 ON a1.claim_id = E1.claim_id
  AND a1.defendant_name = E1.defendant_name
  AND a1.claim_status = E1.Claim_status
WHERE E1.claim_id IS NULL ------------- この条件を追加
```

▼ 画像右側が出力結果

![rep_5.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/b018028b-db71-df27-bf27-6631a633d754.png)

# 回答 SQL
```SQL:SQL
SELECT a1.claim_id,
       a1.patient_name,
		array_agg(a1.claim_seq),
		MIN(a1.claim_seq),
		CASE MIN(a1.claim_seq) WHEN 2 THEN 'AP'
                               WHEN 3 THEN 'OR'
                               WHEN 4 THEN 'SF'
                               ELSE 'CL' END AS claim_status
FROM
(
	SELECT C1.*, D1.defendant_name,S1.*
	FROM Claims AS C1
	INNER JOIN Defendants AS D1 ON C1.claim_id = D1.claim_id
	CROSS JOIN ClaimStatusCodes AS S1
) AS a1
LEFT JOIN LegalEvents AS E1 ON a1.claim_id = E1.claim_id
   AND a1.defendant_name = E1.defendant_name
   AND a1.claim_status = E1.Claim_status
WHERE E1.claim_id IS NULL

GROUP BY a1.claim_id, a1.patient_name
```

▼ 出力結果

|claim_id|patient_name|array_agg|min|claim_status|
|---|---|---|---|---|
|10|Smith|{4,3,4}|3|OR|
|20|Johns|{2,3,4,3,4}|2|AP|
|30|Brown|{2,3,4}|2|AP|

『裁判10』の中で一番遅い進行の遅いステータスは OR
『裁判20』の中で一番遅い進行の遅いステータスは AP
『裁判30』は被告人が一人なので、ステータスは AP

空白のデータの一覧に対して、 claim_id と patient_name で GROUP BY しています
これは各裁判での進行状況を集計する為です。

`GROUP BY claim_id だけでもいいかと思いますが
おそらく原告の名前もそのまま使いたかったのかと思います`

取出したいのは、各裁判での空白データの中で、一番小さい claim_seq を持つ行です
　⇒　GROUP BY を追加して、同じ裁判内で最小の claim_seq を取出す
　⇒　NULL となっている行はまだ完了していないステータスです
　⇒　このNULLデータで一番小さいステータスの1つ上が直近のステータスとなります
　⇒　**この処理を CASE文 で対応しています**


![rep_6.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/66f2b7bd-2417-f0e3-5709-6d7f525f01cb.png)


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