可視化SQL | 現在の給料と昇給前の給料 を１行で表示する(その６) | LEFT JOIN と MAX

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

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

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

『その６』は元となるデータ結合に LEFT JOIN を使用し
最後に WHERE句で『現在の給料』と『昇給前の給料』を抽出しています。

～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
つぶやき

これまで『その２』～『その６』までデータの取出し方が違う手法を
解説してきました。SQLパズルに複数の回答が有るので・・・

同じ回答を取得するのに、違うアプローチで実現させるのは
スキルアップにとても有意義だと思っています。

自分の引き出しが増える・・・と言いますか
ここぞ！！ という時に役に立ちます
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～

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

![rep_1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/f7713af1-631c-939e-9484-5af350cdd1e1.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/adeaf5b0-9c37-94e6-0e77-4f9559a39d5f.png)

# LEFT JOIN を使って集計元になるデータを作る
```SQL:SQL
SELECT 
  S0.emp_name,
  S0.sal_date AS curr_date, --- 今の給料昇給日
  S0.sal_amt AS curr_amt, ----- 今の給料
  S1.sal_date AS prev_date, --- 1つ前の給料昇給日
  S1.sal_amt AS prev_amt ------ 1つ前の給料
FROM Salaries AS S0
  LEFT JOIN Salaries AS S1 ON S0.emp_name = S1.emp_name
```

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

『その５』でINNER JOINした時と同じ出力となっています
S0とS1は同じテーブルなので、S0の社員が持つ行数とS1の社員の持つ行数は同じ
結合する条件は同じ社員同士・・・なので
見た目には CROSS JOIN や INNER JOIN しているのと同じ結果となります

同じ社員名のデータを結合する
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/71894d6b-a094-3d3b-e5dc-3eb839f776ab.png)

# 条件追加してデータを絞込む
```SQL:SQL
SELECT 
  S0.emp_name,
  S0.sal_date AS curr_date, --- 今の給料昇給日
  S0.sal_amt AS curr_amt, ----- 今の給料
  S1.sal_date AS prev_date, --- 1つ前の給料昇給日
  S1.sal_amt AS prev_amt ------ 1つ前の給料
FROM Salaries AS S0
  LEFT JOIN Salaries AS S1 ON S0.emp_name = S1.emp_name 
     AND S0.sal_date > S1.sal_date -- 条件追加
```

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

S0.sal_date > S1.sal_date を条件に追加して出力

Tomの場合、どのように LEFT JOIN されるかをイメージ図で示します


![rep_4.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/624264c8-ca8c-c4bf-ac64-0501c631ffc0.png)



# 今の給料の昇給日
```SQL:SQL
WITH SalaryHistory AS (
  SELECT 
     S0.emp_name,
     S0.sal_date AS curr_date, --- 今の給料昇給日
     S0.sal_amt AS curr_amt, ----- 今の給料
     S1.sal_date AS prev_date, --- 1つ前の給料昇給日
     S1.sal_amt AS prev_amt ------ 1つ前の給料
  FROM Salaries AS S0
     LEFT JOIN Salaries AS S1 ON S0.emp_name = S1.emp_name 
        AND S0.sal_date > S1.sal_date
)

SELECT 
  S3.emp_name,
  S3.curr_date,  ---- 今の給料昇給日
  S3.curr_amt, ------ 今の給料
  S3.prev_date, ----- 1つ前の給料昇給日
  S3.prev_amt, ------ 1つ前の給料
  (SELECT array_agg(curr_date) FROM SalaryHistory AS S1 
     WHERE S3.emp_name = S1.emp_name),
  (SELECT MAX(curr_date) FROM SalaryHistory AS S1
      WHERE S3.emp_name = S1.emp_name)
FROM SalaryHistory AS S3
```

LEFT JOIN を使って取出した元データのSQLを CTE(SalaryHistory) として
SELECT文の中に array_agg(curr_date) と MAX(curr_date) を追加

array_agg(curr_date)はSalaryHistoryを自己結合して
同じ社員の全昇給日の集合を取出しています

MAX(curr_date)は全昇給日の中で最新の昇給日を取出している
　⇒　つまり、**今の給料の昇給日**

▼出力


![rep_5.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/318e1185-31b1-8bac-5936-0cab2db05a1a.png)



# 1つ前の給料の昇給日
```SQL:SQL
WITH SalaryHistory AS (
  SELECT 
     S0.emp_name,
     S0.sal_date AS curr_date, --- 今の給料昇給日
     S0.sal_amt AS curr_amt, ----- 今の給料
     S1.sal_date AS prev_date, --- 1つ前の給料昇給日
     S1.sal_amt AS prev_amt ------ 1つ前の給料
  FROM Salaries AS S0
     LEFT JOIN Salaries AS S1 ON S0.emp_name = S1.emp_name 
        AND S0.sal_date > S1.sal_date
)

SELECT 
  S3.emp_name,
  S3.curr_date,  ---- 今の給料昇給日
  S3.curr_amt, ------ 今の給料
  S3.prev_date, ----- 1つ前の給料昇給日
  S3.prev_amt, ------ 1つ前の給料
  (SELECT array_agg(prev_date) FROM SalaryHistory AS S1 
     WHERE S3.emp_name = S1.emp_name),
  (SELECT MAX(prev_date) FROM SalaryHistory AS S1
      WHERE S3.emp_name = S1.emp_name)
FROM SalaryHistory AS S3
```

LEFT JOIN を使って取出した元データのSQLを CTE(SalaryHistory) として
SELECT文の中に array_agg(prev_date) と MAX(prev_date) を追加

array_agg(prev_date)はSalaryHistoryを自己結合して
同じ社員の S0.sal_date より小さい昇給日の集合を取出している
　⇒　今のお給料の昇給日を含まない、過去の昇給日

MAX(prev_date)は『過去の昇給日』の中で最新の昇給日を取出している
　⇒　つまり、**1つ前の給料の昇給日**


▼出力

![rep_6.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/7fe8c880-659d-b6ed-cf2a-61ff6c8ac9df.png)


『今の給料の昇給日』と『1つ前の給料の昇給日』が取得できました
取出したい給料情報(行)を下記に示します


# 取出したい給料情報
```SQL:SQL
WITH SalaryHistory AS (
  SELECT 
     S0.emp_name,
     S0.sal_date AS curr_date, --- 今の給料昇給日
     S0.sal_amt AS curr_amt, ----- 今の給料
     S1.sal_date AS prev_date, --- 1つ前の給料昇給日
     S1.sal_amt AS prev_amt ------ 1つ前の給料
  FROM Salaries AS S0
     LEFT JOIN Salaries AS S1 ON S0.emp_name = S1.emp_name 
        AND S0.sal_date > S1.sal_date
)

SELECT
  S3.emp_name,
  S3.curr_date,
  S3.curr_amt,
  S3.prev_date,
  S3.prev_amt,
  (SELECT MAX(curr_date) FROM SalaryHistory AS S1
      WHERE S3.emp_name = S1.emp_name), ---- 今の給料昇給日
  (SELECT MAX(prev_date) FROM SalaryHistory AS S1
      WHERE S3.emp_name = S1.emp_name) ----- 1つ前の給料昇給日
FROM SalaryHistory AS S3
```

▼　出力

![rep_7.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/7012d978-d12c-3c07-829f-6aea51809194.png)




# Tom と Harry を取出す
```SQL:SQL
WITH SalaryHistory AS (
  SELECT 
     S0.emp_name,
     S0.sal_date AS curr_date, --- 今の給料昇給日
     S0.sal_amt AS curr_amt, ----- 今の給料
     S1.sal_date AS prev_date, --- 1つ前の給料昇給日
     S1.sal_amt AS prev_amt ------ 1つ前の給料
  FROM Salaries AS S0
     LEFT JOIN Salaries AS S1 ON S0.emp_name = S1.emp_name 
        AND S0.sal_date > S1.sal_date
)

SELECT 
  S3.emp_name,
  S3.curr_date,
  S3.curr_amt,
  S3.prev_date,
  S3.prev_amt,
  (SELECT MAX(curr_date) FROM SalaryHistory AS S1
      WHERE S3.emp_name = S1.emp_name), ---- 今の給料昇給日
  (SELECT MAX(prev_date) FROM SalaryHistory AS S1
      WHERE S3.emp_name = S1.emp_name) ----- 1つ前の給料昇給日

FROM SalaryHistory AS S3
WHERE 
  S3.curr_date = (SELECT MAX(curr_date) FROM SalaryHistory AS S1
                  WHERE S3.emp_name = S1.emp_name) AND -- 今の給料昇給日
  S3.prev_date = (SELECT MAX(prev_date) FROM SalaryHistory AS S1
                   WHERE S3.emp_name = S1.emp_name) --- 1つ前の給料昇給日
```

▼ 出力

WHERE句に下記条件[1],[2]を追加して、SalaryHistory(S3) からデータを取出す

[1] 『今の給料の昇給日』が MAX(curr_date)と同じになる行
　⇒　S3.curr_date = (SELECT MAX(curr_date) FROM ・・・

[2] 『1つ前の給料の昇給日』が MAX(prev_date)と同じになる行
　⇒　S3.prev_date = (SELECT MAX(prev_date) FROM ・・・


![rep_8.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/544e1aaa-6ea7-0ffa-5853-fdecd6fcbf48.png)


Dickのデータが取得できないのは、**WHERE句の条件がANDなので、条件を満たしません**
　⇒　S3.curr_date =  MAX(curr_date) AND S3.prev_date = MAX(prev_date)
　⇒　Dickには **MAX(prev_date) が存在しません**

その為、WHERE句の条件を修正する必要が有ります
　⇒　Dickの場合、S3.prev_date が NULLなので下記の様な条件に修正します
　⇒　S3.prev_date = MAX(prev_date)  `OR S3.prev_date IS NULL`


# 回答SQL
```SQL:SQL
WITH SalaryHistory AS (
  SELECT
     S0.emp_name,
     S0.sal_date AS curr_date, --- 今の給料昇給日
     S0.sal_amt AS curr_amt, ----- 今の給料
     S1.sal_date AS prev_date, --- 1つ前の給料昇給日
     S1.sal_amt AS prev_amt ------ 1つ前の給料
  FROM Salaries AS S0
     LEFT JOIN Salaries AS S1 ON S0.emp_name = S1.emp_name 
        AND S0.sal_date > S1.sal_date
)

SELECT
  S3.emp_name,
  S3.curr_date,
  S3.curr_amt,
  S3.prev_date,
  S3.prev_amt,
  (SELECT MAX(curr_date) FROM SalaryHistory AS S1
      WHERE S3.emp_name = S1.emp_name), ---- 今の給料昇給日
  (SELECT MAX(prev_date) FROM SalaryHistory AS S1
      WHERE S3.emp_name = S1.emp_name) ----- 1つ前の給料昇給日

FROM SalaryHistory AS S3
WHERE
  S3.curr_date = (SELECT MAX(curr_date) FROM SalaryHistory AS S1
                  WHERE S3.emp_name = S1.emp_name) AND -- 今の給料昇給日
 (S3.prev_date = (SELECT MAX(prev_date) FROM SalaryHistory AS S1
                   WHERE S3.emp_name = S1.emp_name) OR S3.prev_date IS NULL)
                                                   -- OR 上記を追記
```

▼ 出力

|emp_name|curr_date|curr_amt|prev_date|prev_amt|max|max|
|---|---|---|---|---|---|---|
|Tom|1996-12-20|900|1996-10-20|800|1996-12-20|1996-10-20|
|Harry|1996-09-20|700|1996-07-20|500|1996-09-20|1996-07-20|
|Dick|1996-06-20|500|空白|空白|1996-06-20|空白|


WHERE句に『1つ前の昇給日』が無い場合も含める・・・記述を追記
　⇒　S3.prev_date IS NULL
　⇒　Dick の行が取得できる

![rep_9.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/dd5b648c-d153-2d1a-18ad-4a1289ac9306.png)


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