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

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

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

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

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


![rep_1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/da880a70-5405-c7fb-ceff-10574d5173ae.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/131d1f3e-e72b-5e49-57b6-da04f2243def.png)



# CROSS JOIN を使って集計元になるデータを作る
```SQL:SQL
SELECT S0.*, S1.*
FROM Salaries AS S0, Salaries AS S1
WHERE S0.emp_name = S1.emp_name
```
Salaries テーブルには3人の社員のデータが有り、全部で7件登録されています
`Salaries テーブルを自己結合して、全組合せのデータを作ります`
条件の WHERE句で、同じ社員名を結合する条件を追加しています

Tomは4件のデータが有るので 4 x 4 = 16件
Marryは2件のデータが有るので 2 x 2 = 4件
Dickは1件しかデータが無いので 1 x 1 = 1件

下記に Salaries テーブルを CROSS JOIN した後のイメージを置きます
この結合したデータから、**S0から今の給料、S2から1つ前の給料を取出します**


![rep_4.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/7f696c2f-d732-033e-3e66-fcc9032c0041.png)


----

![rep_5.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/6754edd1-f9b8-32d1-23b5-eb08b8b6d603.png)



Tom の場合、今のお給料は MAX(S0.sal_date) で 1996-12-20 とわかります
上のイメージと同じ処理をすると1つ前の昇給日は 1996-10-20 となります

これらの処理をSQLで行っていきます


# 上記 SQL を加工する
```SQL:SQL
SELECT S0.*, S1.sal_date,
 (SELECT array_agg(S2.sal_date) FROM Salaries AS S2 WHERE S0.emp_name = S2.emp_name),
 (SELECT array_agg(S3.sal_date) FROM Salaries AS S3 WHERE S0.emp_name = S3.emp_name 
                                                      AND S0.sal_date > S3.sal_date)
FROM Salaries AS S0, Salaries AS S1
WHERE S0.emp_name = S1.emp_name
```

`array_agg(S2.sal_date)` 取得時に S0.emp_name = S2.emp_nameが条件としてある
　⇒　S0 に存在する同じ社員の昇給日を S2 から取り出す
　⇒　**Salaries テーブルに有る各社員の全昇給日が有る**
　⇒　ここから、今のお給料の昇給日を取出す

`array_agg(S3.sal_date)` は array_agg(S2.sal_date)取得した時の条件にもう１つ条件を追加
　⇒　条件は S0.sal_date > S3.sal_date
　⇒　**S0.sal_date より小さい昇給日の集合(前の全昇給日)** を S3 から取り出す
　⇒　ここから、1つ前の昇給日を取出す


![rep_6.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/0e58591e-b669-04d7-96e6-970c6fb7c8bd.png)



# 上記 SQL を加工する
```SQL:SQL
SELECT S0.*, S1.sal_date,
 (SELECT array_agg(S2.sal_date) FROM Salaries AS S2 WHERE S0.emp_name = S2.emp_name),
 (SELECT MAX(S2.sal_date) FROM Salaries AS S2 WHERE S0.emp_name = S2.emp_name),

 (SELECT array_agg(S3.sal_date) FROM Salaries AS S3 WHERE S0.emp_name = S3.emp_name 
                                                    AND S0.sal_date > S3.sal_date),
 (SELECT MAX(S3.sal_date) FROM Salaries AS S3 WHERE S0.emp_name = S3.emp_name 
                                                    AND S0.sal_date > S3.sal_date)
FROM Salaries AS S0, Salaries AS S1
WHERE S0.emp_name = S1.emp_name
```

MAX(S2.sal_date) と MAX(S3.sal_date) を追加した

array_agg(S2.sal_date) は各社員の全昇給日の集合データ
　⇒　MAX(S2.sal_date) は集合の中で一番大きな日
　⇒　**直近の昇給日となる**

array_agg(S3.sal_date) は各社員の`今の給料より前`の全昇給日の集合データ
　⇒　MAX(S3.sal_date) は集合の中で一番大きな日
　⇒　**1つ前の昇給日となる**

つまり **MAX(S2.sal_date) と MAX(S3.sal_date) の組合せを持つ行を取出す事になる**


![rep_7.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/185b32fc-7bbd-ed4a-8ca3-3ecfcb6500a8.png)



# 上記 SQL を加工する
```SQL:SQL
SELECT S0.*, S1.sal_date,S1.sal_amt,
 (SELECT MAX(S2.sal_date) FROM Salaries AS S2 WHERE S0.emp_name = S2.emp_name),
 (SELECT MAX(S3.sal_date) FROM Salaries AS S3 WHERE S0.emp_name = S3.emp_name 
                                                    AND S0.sal_date > S3.sal_date)
FROM Salaries AS S0, Salaries AS S1
WHERE S0.emp_name = S1.emp_name
  AND S0.sal_date = (SELECT MAX(S2.sal_date) FROM Salaries AS S2 
                     WHERE S0.emp_name = S2.emp_name)
  AND S1.sal_date = (SELECT MAX(S3.sal_date) FROM Salaries AS S3 
                     WHERE S0.emp_name = S3.emp_name AND S0.sal_date > S3.sal_date)
```

▼ 出力 (ここには Dick は表示されない)

|emp_name|S0.sal_date|sal_amt|S1.sal_date|sal_amt|MAX(S2.sal_date)|MAX(S3.sal_date)|
|---|---|---|---|---|---|---|
|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|

『今の給料』と『1つ前の給料』を1行で取り出すには・・・
上記で言いましたが・・・**S0から今の給料、S2から1つ前の給料を取出します**

**上記SQLを加工する・・・で取得したMAX値をWHERE句で使用します**

Salaries テーブルを CROSS JOIN したデータに
WHERE句で条件を追加する
　　S0.sal_date = MAX(S2.sal_date)  <--- 条件１：今の給料
　　S1.sal_date = MAX(S3.sal_date)  <--- 条件２：前の給料


■ 条件１：今の給料の昇給日
　⇒　S0.sal_date から直近の昇給日を取る
　⇒　MAX(S2.sal_date) には直近の昇給日を持っている
　⇒　`S0.sal_date と同じ MAX(S2.sal_date) が直近の昇給日となる`
　⇒　S0.sal_date = (SELECT MAX(S2.sal_date) FROM Salaries AS S2 ・・・)

■ 条件２：1つ前の給料の昇給日
　⇒　S1.sal_date から1つ前の昇給日を取る
　⇒　MAX(S3.sal_date) には1つ前の昇給日を持っている
　⇒　`S1.sal_date と同じ MAX(S3.sal_date) が1つ前の昇給日となる`
　⇒　S1.sal_date = (SELECT MAX(S3.sal_date) FROM Salaries AS S3 ・・・)

条件１と条件２を持つ行を CROSS JOIN したテーブルから取得すると
Tom と Marry の『今の給料』と『前の給料』が取得できる

**このSQLでは Dick の給料が表示できない・・・**

その理由は、`昇給していないので、1つ前の昇給日MAX(S3.sal_date)がない`
その為、WHERE句の2つ目の条件を満たさない
　⇒　S1.sal_date = (SELECT MAX(S3.sal_date) FROM Salaries AS S3 ・・・)
　⇒　`この条件は AND 条件なので、必須条件です`

**Dick の給料は別のSQLで取込む必要が有ります**

![rep_8.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/b4fe1e10-0e19-57d1-faa7-5df9c1328787.png)


------

Dick の給料情報を取出す

![rep_10.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/b8993845-5f89-518d-c678-729de7adeff2.png)


# 回答 SQL
```SQL:SQL
SELECT S0.emp_name, S0.sal_date, S0.sal_amt, S1.sal_date, S1.sal_amt
FROM Salaries AS S0, Salaries AS S1
WHERE S0.emp_name = S1.emp_name
  AND S0.sal_date = (SELECT MAX(S2.sal_date) FROM Salaries AS S2 
                     WHERE S0.emp_name = S2.emp_name)
  AND S1.sal_date = (SELECT MAX(S3.sal_date) FROM Salaries AS S3 
                     WHERE S0.emp_name = S3.emp_name AND S0.sal_date > S3.sal_date)
UNION ALL

SELECT S4.emp_name, MAX(S4.sal_date), MAX(S4.sal_amt),NULL, NULL
FROM Salaries AS S4
GROUP BY S4.emp_name
HAVING COUNT(*) = 1
```

▼ 結果

UNION ALL を使って Dick の給料情報を結合する

|emp_name|S0.sal_date|S0.sal_amt|S1.sal_date|S1.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|NULL|NULL|


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