可視化 SQL | 複雑なサブクエリ と テーブル JOIN

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

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

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

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

![err0.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/5d0bce5e-ccb4-a016-9870-df2ec0164a73.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/c200b718-315a-d739-7c5d-e1a5f5d48f5a.png)



#回答 SQL
```SQL:SQL
-- 見やすい様にサブクエリをずらしています
SELECT C1.claim_id, C1.patient_name, S1.claim_status, S1.claim_seq
FROM Claims AS C1, ClaimStatusCodes AS S1
WHERE S1.claim_seq IN ( SELECT MIN(S2.claim_seq)
						FROM ClaimStatusCodes AS S2
						WHERE S2.claim_seq IN ( SELECT MAX(S3.claim_seq)
												FROM LegalEvents AS E1,ClaimStatusCodes AS S3
												WHERE E1.claim_status = S3.claim_status AND E1.claim_id = C1.claim_id
												GROUP BY E1.defendant_name
											  )
					  );
```

まずは、回答を見てみます
このSQLは一体何をしているのでしょうか？

直訳すると・・・

サブクエリ[1]で各被告人の最新の進行状況を取得しています
次のサブクエリ[2]で、一番進行の遅い裁判を取出しています

それほど長いSQLではないので、SQLの読解にはとてもいい問題だと思います
それでは、少しづつ紐解いていきます・・・

サブクエリ[1]、サブクエリ[2]、最後に外側の SELECT文を解説せつします
**SQL は基本、内側から外側へ読んでいきます**

![rep_2.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/0fe87e6d-420f-ff99-9a5f-c0b475a9aa38.png)


#サブクエリ[1] step1
```SQL:SQL
SELECT C1.*, S1.*
FROM Claims AS C1, ClaimStatusCodes AS S1
-- 取出したいデータを絞り込む
-- WHERE S1.claim_seq IN (SELECT MIN(S2.claim_seq)
--                         FROM ClaimStatusCodes AS S2
--                          ・・・・・
```

回答のSQLを見ると、上記のSQLのが元となるデータになり
WHERE句にサブクエリを使うことで、必要なデータを取出しています

Claims と ClaimStatusCodes  を CROSS JOIN している
このデータは 原告が起こしている裁判『10』『20』『30』 と`裁判の進行状況の全組合せ`
　⇒　この組合せのどれか？が回答になります
　⇒　裁判10、裁判20、裁判30 から条件に合うデータを各1つづつ取出します

![rep_3.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/7b7000e1-0631-918b-b37b-3a690c591c8a.png)


#サブクエリ[1] step2
```SQL:SQL
SELECT E1.*, S3.claim_status, S3.claim_seq
FROM LegalEvents AS E1,ClaimStatusCodes AS S3
WHERE E1.claim_status = S3.claim_status
```

それぞれの裁判で訴えられている被告人と
彼らの裁判の進行状況をまとめたテーブルデータ

LegalEvents テーブルには 裁判の進行状況を数字で表すデータを持っていません
ClaimStatusCodes から進行状況が判断できる数字を結合しています
　⇒　数字が大きい程、裁判は進行している

裁判10では3人の被告人がいます
裁判20では2人の被告人がいます
裁判30では1人の被告人がいます

同じ裁判でも、被告人によって進行状況が違います
この問題は、同じ裁判で一番進行状況が遅い裁判を探すので・・・
　⇒　★ マークが取出したい裁判データです


![rep_4.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/33ce6ac7-d2a7-adaf-b780-0ed7153557bb.png)


#サブクエリ[1] step3
```SQL:SQL
SELECT C1.claim_id, C1.patient_name, S1.claim_status,S1.claim_seq,
  ( SELECT array_agg(S3.claim_seq) 
     FROM LegalEvents AS E1,ClaimStatusCodes AS S3
      WHERE E1.claim_status = S3.claim_status AND E1.claim_id = C1.claim_id ),

  ( SELECT MAX(S3.claim_seq)
     FROM LegalEvents AS E1,ClaimStatusCodes AS S3
      WHERE E1.claim_status = S3.claim_status AND E1.claim_id = C1.claim_id ) AS a1
FROM Claims AS C1, ClaimStatusCodes AS S1
```

上記のクエリを細かく見てみます。サブクエリ[1] step2 をSELECT文で使用しています。
`サブクエリ[1]で は WHERE句で MAX(S3.claim_seq) を サブクエリ[2] へ渡しています`
　⇒　**MAX(S3.claim_seq) は何か？を SELECT文で確認します**
　⇒　下記画像の左側がこのSQLの結果となります

これは、LegalEvents テーブルにある被告人の裁判の進行状況と
ClaimStatusCodes テーブルにある 裁判の進行状況の数字を CROSS JOIN したデータから
進行状況の数字(claim_seq)を全部取得しています
　⇒　ピンクの点線データ(画像右)を取出します
　⇒　このデータを array_agg(S3.claim_seq) とします(画像左)
　⇒　条件(黄色)に同じ claim_id を指定しているので同じ裁判の数字だけ取込ます

裁判10には3人の被告がいるので、3人全員の進行状況を表す数字を取出しています
　⇒　ここから一番大きい数字(進行が進んでいる裁判)を取出す
　⇒　画像左の紫色の部分です(画像左)
　⇒　これを MAX(S3.claim_seq) とします

![rep_6.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/257a3ac3-1250-6917-e3c6-6901f7799d79.png)


だいぶ全体像が見えてきたか・・・と思いますが、**この状態ではまだ不足が有ります**
上記の SQL を修正してその中身を確認していきます


#サブクエリ[1] step4
```SQL:SQL
SELECT C1.claim_id, C1.patient_name, S1.claim_status,
  (	
      SELECT array_agg(b1.max_seq)
      FROM
      ( -- サブクエリ[1]
          SELECT MAX(S3.claim_seq) AS max_seq
          FROM LegalEvents AS E1,ClaimStatusCodes AS S3
          WHERE E1.claim_status = S3.claim_status AND E1.claim_id = C1.claim_id 
          GROUP BY E1.defendant_name
      ) AS b1
  ) AS c1

FROM Claims AS C1, ClaimStatusCodes AS S1
```

上記SQLで追加したのは `GROUP BY E1.defendant_name` です

GROUP BY を追加した理由は

例えば裁判10の場合、被告人が(Dow,Johonson,Mayer)の3人がいます
それぞれ裁判の進行状況が異なるので個別に確認する必要があります

GROUP BYを追加したことで ClaimStatusCodes から取り出す claim_seq が変わります
　⇒　**このデータをサブクエリ[2] へ渡しています**

裁判10の場合
　⇒　Dow, Johnson, Mayer がいるので各々の裁判の進行状況の最大値を取出します
　⇒　2, 4, 3
　⇒　サブクエリ[2] へ渡す

裁判20の場合
　⇒　Baker, Mayer がいるので各々の裁判の進行状況の最大値を取出します
　⇒　1, 2
　⇒　サブクエリ[2] へ渡す

裁判30の場合
　⇒　Johnson がいるので彼の裁判の進行状況の最大値を取出します
　⇒　1
　⇒　サブクエリ[2] へ渡す

**array_agg( MAX(S3.claim_seq) ) と記述すると エラーとなった・・・ので**
確認SQLがサブクエリになっています


![rep_8.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/a53196c2-79e8-7a10-0f70-1b3577052c3a.png)


#サブクエリ[2]
```SQL:SQL
SELECT C1.claim_id, C1.patient_name, S1.claim_status,
  ( -- サブクエリ[2]
      SELECT array_agg(S2.claim_seq)
      FROM ClaimStatusCodes AS S2
      WHERE S2.claim_seq IN 
      (
            SELECT MAX(S3.claim_seq) AS max_seq
            FROM LegalEvents AS E1,ClaimStatusCodes AS S3
            WHERE E1.claim_status = S3.claim_status AND E1.claim_id = C1.claim_id 
            GROUP BY E1.defendant_name
      )
  ) AS c2,
  ( -- サブクエリ[2]
      SELECT MIN(S2.claim_seq)
      FROM ClaimStatusCodes AS S2
      WHERE S2.claim_seq IN 
      (
            SELECT MAX(S3.claim_seq) AS max_seq
            FROM LegalEvents AS E1,ClaimStatusCodes AS S3
            WHERE E1.claim_status = S3.claim_status AND E1.claim_id = C1.claim_id 
            GROUP BY E1.defendant_name
      )
  ) AS c3

FROM Claims AS C1, ClaimStatusCodes AS S1
```

サブクエリ[1] からもらったデータを サブクエリ[2] で何をしているか？
SELECT文の中で確認していきます
**同じ様なSELECT文が2つ**あります、それぞれを **c2,c3** としています


c2：サブクエリ[1]の MAX値を サブクエリ[2]へ渡している
　⇒　MAX(S3.claim_seq) と同じ数字(claim_seq)を ClaimStatusCodes から取出す
　⇒　array_agg(S2.claim_seq)

c3：サブクエリ[1]の MAX値を サブクエリ[2]へ渡している
　⇒　MAX(S3.claim_seq) と同じ数字の中から一番小さい(claim_seq)を ClaimStatusCodes から取出す
　⇒　MIN(S2.claim_seq)
　⇒　**この数字が一番最初の WHERE句の条件となり、最終的なデータを取出す**


![rep_10.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/5f8ab422-f630-a9ee-8be2-bb51b89139a0.png)


#回答 SQL
```SQL:SQL
-- 見やすい様にサブクエリをずらしています
SELECT C1.claim_id, C1.patient_name, S1.claim_status, S1.claim_seq
FROM Claims AS C1, ClaimStatusCodes AS S1
WHERE S1.claim_seq IN ( SELECT MIN(S2.claim_seq)
						FROM ClaimStatusCodes AS S2
						WHERE S2.claim_seq IN ( SELECT MAX(S3.claim_seq)
												FROM LegalEvents AS E1,ClaimStatusCodes AS S3
												WHERE E1.claim_status = S3.claim_status AND E1.claim_id = C1.claim_id
												GROUP BY E1.defendant_name
											  )
					  );
```

▼ 最終的に欲しいデータ

|claim_id|patient_name|claim_status|claim_seq|
|---|---|---|---|
|10|Smith|OR|2|
|20|Johns|AP|1|
|30|Brown|AP|1|


![rep_11.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/61c3e73c-68bf-37af-004d-6e5c46670e13.png)


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