初めに
この問題は、SQLパズル #12 訴訟の進行状況 を参考にしています
手元に『SQL パズル』があれば問題の詳細が記載されているのでよりわかりやすいです
この問題は裁判の進行状況を確認する問題です
『可視化 SQL | 複雑なサブクエリ と テーブル JOIN』 と 同じ内容
・・・ですが、アプローチが違います
裁判は３つあって、claim_id が 『10』『20』『30』 としています
この記事では 裁判10、裁判20、裁判30 としています
各裁判には被告人がいて、それぞれ進行状況が違います
取出したいデータは 同じ claim_id の裁判で、進行状況が一番遅い状態の裁判となります
PostgreSQL で動作確認しています
テーブル と データ
-- 裁判の原告の一覧
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);
▼ テーブルデータ
回答 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
SELECT C1.*, D1.*
FROM Claims AS C1
INNER JOIN Defendants AS D1 ON C1.claim_id = D1.claim_id
▼画像左側が出力です
原告が起こした『裁判 10』『裁判 20』『裁判 30』で
訴えられている被告人の組合せを INNER JOIN で作成している
次の CORSS JOIN
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 を
各被告の裁判に結合します
⇒ 全ての裁判の全ての進行状況が分かる元データを作ります
最後の LEFT JOIN
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』と同じです
元データに対して、進行中の裁判状況を紐づけしています
ここから空白のデータのある行は、まだ完了していないステータスだと見てわかります
NULL となっているデータの取出し
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 ------------- この条件を追加
▼ 画像右側が出力結果
回答 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文 で対応しています
