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

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

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

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

『その４』は『その２』ととても似ています・・・が人によってアプローチの方法が違います
違う記述で同じ結果を出力するSQLを見るのはとても勉強になると思います

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


![rep_1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/b7b80c8d-e884-831c-75e5-d2f2a173a9a8.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/eb301bb0-d3ec-b9eb-babb-e40c9a7b9b4c.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 した後のイメージを置きます

![rep_3.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/2f922015-3137-8a66-6799-480d06c187dd.png)

ここまでは、『その２』と同じ・・・


# 組合せを作る
```SQL:SQL
SELECT S0.*, S1.*
FROM Salaries AS S0, Salaries AS S1
WHERE S0.emp_name = S1.emp_name AND S0.sal_date <= S1.sal_date
```

▼ 出力(画像の右側)

WHERE句に S0.sal_date <= S1.sal_date　を追加して日にちの組合せを絞り込む
S0.sal_date を今,S1.sal_date を過去の給料昇給日と見た時
**S0.sal_date が過去になってしまう組合せは必要ありません**
　⇒　S0.sal_date より大きな S1.sal_dateを持つ行を取出す
　⇒　テーブル同士を自己結合して組合せを作ります
　⇒　組合せを作る場合よく使う手法です。画像をご確認ください


![rep_5.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/0f777d5c-69d5-3209-76de-bd448771c2b0.png)




# S0.Salaries のデータで GROUP BY
```SQL:SQL
SELECT S0.emp_name AS emp_name, 
       S0.sal_date AS sal_date,
       MAX(S0.sal_amt) AS sal_amt,
       COUNT(*)
FROM Salaries AS S0, Salaries AS S1
WHERE S0.sal_date <= S1.sal_date AND S0.emp_name = S1.emp_name
GROUP BY S0.emp_name, S0.sal_date
```

▼ 出力(画像の左側)


![rep_6.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/8661277b-3044-d5ea-3e68-2ac5964a084b.png)


# COUNT(*) が 2以下のデータを取出す
```SQL:SQL
SELECT S0.emp_name AS emp_name, 
       S0.sal_date AS sal_date,
       MAX(S0.sal_amt) AS sal_amt,
       COUNT(*)
FROM Salaries AS S0, Salaries AS S1
WHERE S0.sal_date <= S1.sal_date AND S0.emp_name = S1.emp_name
GROUP BY S0.emp_name, S0.sal_date
HAVING COUNT(*) <= 2  -- 追加
```

▼ 出力

|emp_name|sal_date|sal_amt|count|
|---|---|---|---|
|Tom|1996-10-20|800|2|
|Tom|1996-12-20|900|1|
|Harry|1996-07-20|500|2|
|Harry|1996-09-20|700|1|
|Dick|1996-06-20|500|1|


![rep_7.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/2e5715ba-70a7-b1c1-343c-06f2c23e4240.png)



なぜ、COUNT(*) の数が2以下のデータをとりだすのか？
Tomの場合、行数が4や3となるデータが存在する。それは昇級した回数が多いからです

この問題では『今の給料』と『1つ前の給料』を取り出したいので
**社員名と昇給日 で GROUP BY した後の COUNT が2以下となる組合せが
『今の給料』と『1つ前の給料』のデータとなります**

この出力結果でほぼデータは取れています・・・が、もう一息です

『今の給料』と『1つ前の給料』が縦に並んでいるので横並びにする必要が有ります
`その為にもう一度 CROSS JOIN して、組み合わせを作ります`


# 組合せを作る２
```SQL:SQL
WITH Salaries2 AS (
  SELECT S0.emp_name AS emp_name, 
         S0.sal_date AS sal_date,
         MAX(S0.sal_amt) AS sal_amt
  FROM Salaries AS S0, Salaries AS S1
  WHERE S0.sal_date <= S1.sal_date AND S0.emp_name = S1.emp_name
  GROUP BY S0.emp_name, S0.sal_date
  HAVING COUNT(*) <= 2
)

SELECT S1.emp_name, S1.sal_date, S1.sal_amt,
       S2.sal_date, S2.sal_amt
FROM Salaries2 AS S1, salaries2 AS S2
WHERE S1.emp_name = S2.emp_name
```

▼ 出力


![rep_8.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/16cae77e-4d43-27bd-b4bd-6cbf582a3439.png)



縦並びに表示されていたお給料を取出したSQLを CTE で定義して( **Salaries2** )
このテーブルデータを CROSS JOIN して組み合わせを作っています

CROSS JOIN しているので各社員の給料情報の全組み合わせがあります
**S1.sal_date を 『今の給料の昇給日』、S2.sal_date を『1つ前の昇給日』とすると**
縦に表示されていた給料情報が、横に並んでいるように見えます

ここから必要な行を取出します


# 給料データの取出し
```SQL:SQL
WITH Salaries2 AS (
  SELECT S0.emp_name AS emp_name, 
         S0.sal_date AS sal_date,
         MAX(S0.sal_amt) AS sal_amt
  FROM Salaries AS S0, Salaries AS S1
  WHERE S0.sal_date <= S1.sal_date AND S0.emp_name = S1.emp_name
  GROUP BY S0.emp_name, S0.sal_date
  HAVING COUNT(*) <= 2
)

SELECT S1.emp_name, S1.sal_date, S1.sal_amt,
       S2.sal_date, S2.sal_amt
FROM Salaries2 AS S1, salaries2 AS S2
WHERE S1.emp_name = S2.emp_name AND S1.sal_date > S2.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|

WHERE条件に、S1.sal_date > S2.sal_date を追加しています
S1.sal_date は『今の給料の昇給日』なので S2.sal_date はそれより過去となる条件です
**『=』 が無いので S1.sal_date と S2.sal_date が同じ日は有りません**

しかし、Dick がいなくなりました・・・

理由は WHERE句の条件が 2つあって **AND となっているので両方を満たす必要が有ります**
S1.emp_name = S2.emp_name AND S1.sal_date > S2.sal_date
　⇒　Dick は この条件を満たしません **S1.sal_date > S2.sal_date**

その為、別途 Dick のデータを取出す必要が有ります

![rep_9.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/e98c712d-509d-9795-21d3-02a7efba7d37.png)



# 回答 SQL
```SQL:SQL
WITH Salaries2 AS (
  SELECT S0.emp_name AS emp_name, 
         S0.sal_date AS sal_date,
         MAX(S0.sal_amt) AS sal_amt
  FROM Salaries AS S0, Salaries AS S1
  WHERE S0.sal_date <= S1.sal_date AND S0.emp_name = S1.emp_name
  GROUP BY S0.emp_name, S0.sal_date
  HAVING COUNT(*) <= 2
)

SELECT S1.emp_name, S1.sal_date, S1.sal_amt,
       S2.sal_date, S2.sal_amt
FROM Salaries2 AS S1, salaries2 AS S2
WHERE S1.emp_name = S2.emp_name AND S1.sal_date > S2.sal_date

UNION ALL  -- Dick の給料情報

SELECT emp_name, MAX(sal_date), MAX(sal_amt), NULL, NULL
FROM Salaries2
GROUP BY emp_name
HAVING COUNT(*) = 1  -- Dick のデータは1行しかなり
```

▼ 出力

|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|空白|空白|

Dick の給料情報を取出すために、Salaries2 の出力結果から
社員名で GROUP BY した結果が1行だけのデータを取出しています

集約する変数を emp_name だけなので
sal_date や sal_amt を MAX関数で表示させています


![rep_10.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/032315d8-ef8f-4f9b-816a-541ef4b108ea.png)




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