可視化SQL | 現在の給料と昇給前の給料 を１行で表示する(その５) | INNER JOIN と NOT EXISTS

# 初めに
この問題は、SQLパズル #15 『現在の給料と昇給前の給料 』 を参考にしています
手元に『SQL パズル』があれば問題の詳細が記載されているのでよりわかりやすいです

下記に表示するテーブルとデータを見ると、社員の昇給日と給料は1行づつ追加されていて
複数昇給している社員とまだ昇給していない社員がいます

この問題は、社員のお給料を保持しているテーブルから
`『現在の給料』と『昇給前の給料』を取出して、1行で表示させる問題です`

『その５』はちょっとトリッキーです。
Slaries テーブルを自己結合させて、全体の組合せを作る・・・のは同じですが
そこから、必要の無い行を取り除いています

考え方が面白いSQLです

下記画像の様な出力結果となります
各社員に対して、今のお給料の昇給日とお給料、前回の昇給日とお給料が
1行で表示されています


![rep_1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/6092562e-18e2-dca8-7d2c-bdc81ebd7b2e.png)



**PostgreSQL で動作確認しています**

# テーブル と データ
```SQL:SQL
CREATE TABLE Salaries (
	emp_name CHAR(10) NOT NULL,
	sal_date DATE NOT NULL,
	sal_amt DECIMAL(8,2) NOT NULL,
	PRIMARY KEY (emp_name, sal_date)
)

INSERT INTO Salaries
VALUES ('Tom',   '1996-06-20', 500.00),
       ('Tom',   '1996-08-20', 700.00),
       ('Tom',   '1996-10-20', 800.00),
       ('Tom',   '1996-12-20', 900.00),
       ('Dick',  '1996-06-20', 500.00),
       ('Harry', '1996-07-20', 500.00),
       ('Harry', '1996-09-20', 700.00);
```

▼ Salaries テーブルの中身

![rep_2.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/ab379973-f8b9-f102-3731-bfceb05bfd42.png)

# INNER JOIN を使って集計元になるデータを作る
```SQL:SQL
SELECT S1.*,S2.*
FROM Salaries AS S1 
 INNER JOIN Salaries AS S2 ON S2.emp_name = S1.emp_name
```

▼ 出力(下記画像の左側)

同じ社員名のデータを結合する
Tom： 4行のデータが有るので 4 x 4 = 16 となる
Harry： 2行のデータが有るので 2 x 2 = 4 となる
Dick： 1行だけなので 1 x 1 = 1 となる

![rep_3.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/4fbfb7bd-f263-6b08-fb07-4b8a5f617a3a.png)

# 集計元になるデータを加工する１
```SQL:SQL
SELECT S1.emp_name, 
	   S1.sal_date AS curr_date,
	   S1.sal_amt AS curr_amt,
       CASE WHEN S2.sal_date <> S1.sal_date
            THEN S2.sal_date END AS prev_date,
       CASE WHEN S2.sal_date <> S1.sal_date
            THEN S2.sal_amt  END AS prev_amt
FROM Salaries AS S1 
 INNER JOIN Salaries AS S2 ON S2.emp_name = S1.emp_name
```

▼ 出力

INNER JOIN した組合せには給料データの全組合せが有ります
S1側に『今の給料情報』、S2側に『1つ前の給料情報』が有るとすると
『S1の昇給日』と『S2の昇給日』が同じデータは必要ありません

SELECT文の中で CASE を使って
`『S2.sal_date』と『S1.sal_date』が同じでない時は『S2.sal_date』を表示`
つまり、**『S2.sal_date』と『S1.sal_date』が同じ場合は空白にしている**

～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
つぶやき
元データを作る時にINNER JOIN していますが
結合条件が『社員名』だけなので、Dickの『1つ前の給料』にデータが入ってしまう
これを回避しているのか・・・？と思います
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～


![rep_4.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/13d6e7f4-c851-9621-b7ef-08419fced3d9.png)



# 集計元になるデータを加工する２
```SQL:SQL
SELECT S1.emp_name, 
	   S1.sal_date AS curr_date,
	   S1.sal_amt AS curr_amt,
       CASE WHEN S2.sal_date <> S1.sal_date
            THEN S2.sal_date END AS prev_date,
       CASE WHEN S2.sal_date <> S1.sal_date
            THEN S2.sal_amt  END AS prev_amt,
	  (SELECT array_agg(S4.sal_date) FROM Salaries AS S4 
	   WHERE S4.emp_name = S1.emp_name AND S4.sal_date < S1.sal_date),
	  (SELECT MAX(S4.sal_date) FROM Salaries AS S4 
	   WHERE S4.emp_name = S1.emp_name AND S4.sal_date < S1.sal_date)
FROM Salaries AS S1 
 INNER JOIN Salaries AS S2 ON S2.emp_name = S1.emp_name
```

▼ 出力

SELECT文の中に下記データを追加で取得している
　SELECT array_agg(S4.sal_date) FROM Salaries ・・・
　SELECT MAX(S4.sal_date) FROM Salaries ・・・

array_agg(S4.sal_date)
　⇒　S1.sal_date より前の昇給日を Salaries(S4) テーブルから取出した集合データ
　⇒　WHERE句に S4.sal_date < S1.sal_date が有る
　⇒　**『=』 がついていない事に注目です**
　⇒　昇給日の全組合せから同じ昇給日の組合せがなくなります 

MAX(S4.sal_date)
　⇒　『今の給料の昇給日』より以前の昇給日の集合なので
　　　この中で一番直近の昇給日が、1つ前の昇給日となる


![rep_5.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/194583eb-6307-ee0f-c224-ee2163a0d617.png)



# 集計元になるデータを加工する３
```SQL:SQL
SELECT
  S1.emp_name, 
  S1.sal_date AS curr_date,
  S1.sal_amt AS curr_amt,
  CASE WHEN S2.sal_date <> S1.sal_date
       THEN S2.sal_date END AS prev_date,
  CASE WHEN S2.sal_date <> S1.sal_date
       THEN S2.sal_amt  END AS prev_amt,
  (SELECT array_agg(S4.sal_date) FROM Salaries AS S4 
   WHERE S4.emp_name = S1.emp_name AND S4.sal_date < S1.sal_date),
  COALESCE((SELECT MAX(S4.sal_date) FROM Salaries AS S4 
            WHERE S4.emp_name = S1.emp_name AND S4.sal_date < S1.sal_date),S2.sal_date)
FROM Salaries AS S1 
  INNER JOIN Salaries AS S2 ON S2.emp_name = S1.emp_name
```


MAX(S4.sal_date)がNULLの時は、S2.sal_date が出力されるように修正
　⇒　簡略化すると下記の様なイメージです
　⇒　COALESCE( MAX(S4.sal_date), S2.sal_date )


![rep_6.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/69b23e44-56ad-9c85-aa4b-61925efba180.png)

------

▼ 出力

![rep_7.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/8bbaa066-9e11-c798-4ff8-5fd56a14c71e.png)



# MAX(S4.sal_date) を 結合条件に入れる
```SQL:SQL
SELECT
  S1.emp_name, 
  S1.sal_date AS curr_date,
  S1.sal_amt AS curr_amt,
  CASE WHEN S2.sal_date <> S1.sal_date
       THEN S2.sal_date END AS prev_date,
  CASE WHEN S2.sal_date <> S1.sal_date
       THEN S2.sal_amt  END AS prev_amt,
  COALESCE((SELECT MAX(S4.sal_date) FROM Salaries AS S4 
            WHERE S4.emp_name = S1.emp_name AND S4.sal_date < S1.sal_date),S2.sal_date)
FROM Salaries AS S1 
  INNER JOIN Salaries AS S2 ON S2.emp_name = S1.emp_name
    AND S2.sal_date = COALESCE((SELECT MAX(S4.sal_date) FROM Salaries AS S4 
	                             WHERE S4.emp_name = S1.emp_name AND 
								       S4.sal_date < S1.sal_date),S2.sal_date)
```

上記で MAX(S4.sal_date) の中身を細かく見てきました。
　⇒　**1つ前の昇給日です**
　⇒　ここが空白の場合は S2.sal_date です

INNER JOIN の結合条件に下記条件を追加する
　⇒　S2.sal_date = (SELECT MAX(S4.sal_date) FROM Salaries As S4 ・・・)
　⇒　S2.sal_date は1つ前の給料情報を保持している。
　⇒　この昇給日が同じ日を取出しています


**下記画像の色のついた部分が出力となります**


![rep_8.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/1b25b3fe-3847-f4b7-3b9d-59e07be86eba.png)


-----

▼ 出力

**S1.sal_dateを『今の給料の昇給日』** とした場合
**prev_date が『1つ前の給料の昇給日』** となる組合せの一覧となります

もう一息です・・・

この問題で欲しいのは、『今の給料』と『1つ前の給料』なので
この一覧から **『今の給料』が最新となる行** を取出す必要が有ります


![rep_9.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/30d53b94-a237-0d66-6ceb-5b9fd18cca75.png)


# 集計元になるデータを加工する４
```SQL:SQL
SELECT
  S1.emp_name, 
  S1.sal_date AS curr_date,
  S1.sal_amt AS curr_amt,
  CASE WHEN S2.sal_date <> S1.sal_date
       THEN S2.sal_date END AS prev_date,
  CASE WHEN S2.sal_date <> S1.sal_date
       THEN S2.sal_amt  END AS prev_amt,
  COALESCE((SELECT MAX(S4.sal_date) FROM Salaries AS S4 
            WHERE S4.emp_name = S1.emp_name AND S4.sal_date < S1.sal_date),S2.sal_date),
  (SELECT array_agg(S3.sal_date) FROM Salaries AS S3 
   WHERE S3.emp_name = S1.emp_name AND S3.sal_date > S1.sal_date)
FROM Salaries AS S1 
  INNER JOIN Salaries AS S2 ON S2.emp_name = S1.emp_name
    AND S2.sal_date = COALESCE((SELECT MAX(S4.sal_date) FROM Salaries AS S4 
	                             WHERE S4.emp_name = S1.emp_name AND 
								       S4.sal_date < S1.sal_date),S2.sal_date)
```

▼ 出力

SELECT文の中に array_agg(S3.sal_date) を追加しました
これは、Salaries(S1)とSalaries(S3)を自己結合して
S1.sal_date より大きな昇給日の集合をSalaries(S3)から取り出しています
　⇒　S3.sal_date > S1.sal_date

S1.sal_date が『今の給料の昇給日』なのでここが直近となる行を取出します
　⇒　`対象となる昇給日は S1.sal_date より大きなsal_date が無い行です`
　⇒　色のついた3行。ここの array_agg(S3.sal_date) は空白です
　⇒　**array_agg(S3.sal_date) が存在しないデータ**
　⇒　S1.sal_date が過去日とならない行
　⇒　ココが **S1.sal_date が最新となる昇給日です**


![rep_10.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/61d5cc9c-7a95-8da1-7cf7-8324decb5d7f.png)



# 回答SQL
```SQL:SQL
SELECT
  S1.emp_name, 
  S1.sal_date AS curr_date,
  S1.sal_amt AS curr_amt,
  CASE WHEN S2.sal_date <> S1.sal_date
       THEN S2.sal_date END AS prev_date,
  CASE WHEN S2.sal_date <> S1.sal_date
       THEN S2.sal_amt  END AS prev_amt
FROM Salaries AS S1 
  INNER JOIN Salaries AS S2 ON S2.emp_name = S1.emp_name
    AND S2.sal_date = COALESCE((SELECT MAX(S4.sal_date) FROM Salaries AS S4 
	                             WHERE S4.emp_name = S1.emp_name AND 
								       S4.sal_date < S1.sal_date),S2.sal_date)

WHERE NOT EXISTS(SELECT * FROM Salaries AS S3
                 WHERE S3.emp_name = S1.emp_name AND S3.sal_date > S1.sal_date)		   

ORDER BY S1.emp_name DESC, S1.sal_date
```

▼ 出力

|S1.emp_name|S1.sal_date|S1.sal_amt|S2.sal_date|S2.sal_amt|
|---|---|---|---|---|
|Tom|1996-12-20|900|1996-10-20|800|
|Harry|1996-09-20|700|1996-07-20|500|
|Dick|1996-06-20|500|空白|空白|

WHERE句に条件を追記して
上記で取得した array_agg(S3.sal_date) が存在しない行を取出しています

WHERE NOT EXISTS(SELECT * FROM Salaries AS S3 ・・・・)
　⇒　NOT EXISTS( この中の WHERE句で S1 と S3 を自己結合しています)
　⇒　NOT EXISTS( SELECT の結果が返ってこない行) を取出します


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