可視化SQL | 現在の給料と昇給前の給料 を１行で表示する(その８) | Window 関数

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

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

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

『その８』は『その７』と同じで Window 関数を使用します。
CASE文を使って、順番をつけられたデータを判定しています

最後に取出すデータは同じですが、その過程は人それぞれです
色んな方法を見る事はいい勉強になります

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

![rep_1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/6163181f-e493-09da-7b02-a4637facdf76.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/d1034cc8-ba94-6d49-34d7-a132de6c7b93.png)

# Window 関数を使ってデータを加工する
```SQL:SQL
SELECT emp_name,
       sal_date,
       sal_amt,
       RANK() OVER (PARTITION BY emp_name ORDER BY sal_date DESC)
FROM Salaries
```

▼ 出力

Window関数の一つ RANK関数を使って、データに順番をつけます

PARTITION BY emp_name ・・・は GROUP BY em_name と見てください
　⇒　データを社員名で分割するイメージです
　⇒　詳細は割愛させていただきます

分割されたデータを昇給日の降順でソートしたデータに対して
1 -> 2 -> 3 -> 4 と順番( RANK )をつけます
直近の昇給日が１で、1つ前の昇給日が２ となっています

![rep_3.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/28d6df9d-b8d6-6064-82a9-75da63d0fce8.png)

・・・ここまでは『その７』と同じです


# 順番が3未満のデータだけ取り出す
```SQL:SQL
SELECT S1.*
FROM 
(
  SELECT emp_name,
         sal_date,
         sal_amt,
         RANK() OVER (PARTITION BY emp_name ORDER BY sal_date DESC)
  FROM Salaries
) AS S1 (emp_name, sal_date, sal_amt, pos)
WHERE pos < 3 -- 番号が3未満だけを取出す
```

▼ 出力(画像右側)

お給料情報は昇給日が新しい順番に1番から番号が振られています
『今の給料』と『1つ前の給料』が取出したいデータなので
この番号(pos)が 1 と 2 のデータを取出しています


![rep_4.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/80e85b6e-b45d-6727-a9a7-d96957639dfd.png)

# CASE文でSQLを加工する
```SQL:SQL
SELECT S1.*,
  CASE WHEN pos = 1 THEN sal_date ELSE NULL END AS curr_date,
  CASE WHEN pos = 1 THEN sal_amt  ELSE NULL END AS curr_amt,
  CASE WHEN pos = 2 THEN sal_date ELSE NULL END AS prev_date,
  CASE WHEN pos = 2 THEN sal_amt  ELSE NULL END AS prev_amt
FROM 
(
  SELECT emp_name,
         sal_date,
         sal_amt,
         RANK() OVER (PARTITION BY emp_name ORDER BY sal_date DESC) FROM Salaries
) AS S1 (emp_name, sal_date, sal_amt, pos)
WHERE pos < 3

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

▼ 出力

先頭の SELECT文の中に CASE文を使って項目を追加しています
『今の給料情報』と『1つ前の給料情報』が
**縦に並んでいるのを横に並ぶように加工しています**

昇給日を見ると
posが1の時に sal_date を表示させて curr_date と名前を付ける
posが2の時に sal_date を表示させて prev_date と名前を付ける



![rep_6.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/60eb9e7a-9675-3938-619f-aaf02888b8e7.png)


# 回答SQL
```SQL:SQL
SELECT S1.emp_name,
  MAX(CASE WHEN pos = 1 THEN sal_date ELSE NULL END) AS curr_date,
  MAX(CASE WHEN pos = 1 THEN sal_amt  ELSE NULL END) AS curr_amt,
  MAX(CASE WHEN pos = 2 THEN sal_date ELSE NULL END) AS prev_date,
  MAX(CASE WHEN pos = 2 THEN sal_amt  ELSE NULL END) AS prev_amt
FROM 
(
  SELECT emp_name,
         sal_date,
         sal_amt,
         RANK() OVER (PARTITION BY emp_name ORDER BY sal_date DESC) FROM Salaries
) AS S1 (emp_name, sal_date, sal_amt, pos)
WHERE pos < 3
GROUP BY S1.emp_name -- 追加

ORDER BY S1.emp_name DESC
```

▼ 出力

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

社員名(emp_name) で GROUP BY して1人1行表示にしている
データが集約されているので、CASE分で追加した項目も MAXを使って集約する
　⇒　**集約しないと下記の様なエラーとなる**

![rep_8.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/7f898dbc-5324-6fc9-952e-f31911f1eb6b.png)



![rep_9.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/2000e072-dfc9-0b11-978a-0cfd7a9a852e.png)

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