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

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

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

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

『その７』は全く違う方法で該当のデータを取出します
Window 関数を使用します。よくお世話になる便利な機能です
これにより、今までとデータの取出し方がガラッと変わります

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

![rep_1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/b67031d6-750b-a4d3-6dfe-6454ab7a878a.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/1db38aaf-d0dc-f811-9d4e-3d79de7f7ed1.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/d20da0aa-0618-eb69-1475-aa705ecbd71e.png)


# LEFT JOINで元となるデータを作る
```SQL:SQL
WITH salaryRanks(emp_name, sal_date, sal_amt, pos) AS (
  SELECT emp_name,
         sal_date,
         sal_amt,
         RANK() OVER (PARTITION BY emp_name ORDER BY sal_date DESC)
  FROM Salaries
)

SELECT C.emp_name,
	    C.sal_date AS curr_date, -- 今の給料昇給日
		C.sal_amt AS curr_amt,   -- 今の給料
		C.pos,
		P.sal_date AS prev_date, -- 1つ前の給料昇給日
		P.sal_amt AS prev_amt,   -- 1つ前の給料昇
		P.pos
FROM SalaryRanks AS C 
	LEFT JOIN SalaryRanks AS P ON P.emp_name = C.emp_name

ORDER BY C.emp_name DESC,C.sal_date,P.sal_date
```

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

順番をつけたSQLを CTE(salaryRanks) で定義して
salaryRanks に対して salaryRanks を LEFT JOIN して元データを作ります
結合条件が社員名なので『その６』と同じ様な元データが出来上がります


![rep_4.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/84333078-b952-cf63-e871-a2917771353b.png)



# 1つ前の給料情報を取出す
```SQL:SQL
WITH salaryRanks(emp_name, sal_date, sal_amt, pos) AS (
  SELECT emp_name,
         sal_date,
         sal_amt,
         RANK() OVER (PARTITION BY emp_name ORDER BY sal_date DESC)
  FROM Salaries
)

SELECT C.emp_name,
	    C.sal_date AS curr_date, -- 今の給料昇給日
		C.sal_amt AS curr_amt,   -- 今の給料
		C.pos,
		P.sal_date AS prev_date, -- 1つ前の給料昇給日
		P.sal_amt AS prev_amt,   -- 1つ前の給料昇
		P.pos
FROM SalaryRanks AS C 
  LEFT JOIN SalaryRanks AS P ON P.emp_name = C.emp_name AND P.pos = 2
                                                       -- ここを追記
ORDER BY C.emp_name DESC,C.sal_date,P.sal_date
```

▼ 出力(画像左側)

P.pos は sal_date を降順に並べて順番(rank)をつけているので
1番目は直近、2番目は1つ前、3番目は2つ前・・・となります

SalaryRanks AS P の P.pos が2のデータは『1つ前の給料情報』となるので
SalaryRanks AS C に対して LEFT JOIN する時の結合条件に追記してます
　⇒　P.pos = 2 を追記


元データを LEFT JOIN で作成した時に、結合条件を『社員名』で行いました
　⇒　ON P.emp_name = C.emp_name
　⇒　Dick のデータは存在します
　⇒　下記画像の右側(緑色) 

しかし、Dick は 新入社員なので昇給していないので
`P側 に pos = 2 のデータが無い`
その為、**LEFT JOIN すると P側 が空白となる**


![rep_5.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/1200baa7-e85d-60e6-62ec-02e22f2d2a27.png)


# 今給料情報を取出す
```SQL:SQL
WITH salaryRanks(emp_name, sal_date, sal_amt, pos) AS (
  SELECT emp_name,
         sal_date,
         sal_amt,
         RANK() OVER (PARTITION BY emp_name ORDER BY sal_date DESC)
  FROM Salaries
)

SELECT C.emp_name,
	    C.sal_date AS curr_date, -- 今の給料昇給日
		C.sal_amt AS curr_amt,   -- 今の給料
		C.pos,
		P.sal_date AS prev_date, -- 1つ前の給料昇給日
		P.sal_amt AS prev_amt,   -- 1つ前の給料昇
		P.pos
FROM SalaryRanks AS C 
  LEFT JOIN SalaryRanks AS P ON P.emp_name = C.emp_name AND P.pos = 2

WHERE C.pos = 1 -- 追記

ORDER BY C.emp_name DESC,C.sal_date,P.sal_date
```

▼ 出力


SalaryRanks AS C 側が『今の給料』を保持しているので
WHERE句で C.pos が 1 の行を取り出す

![rep_6.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/576a190b-f294-1fe8-eeeb-80d220c1a76d.png)


# 回答SQL
```SQL:SQL
WITH salaryRanks(emp_name, sal_date, sal_amt, pos) AS (
  SELECT emp_name,
         sal_date,
         sal_amt,
         RANK() OVER (PARTITION BY emp_name ORDER BY sal_date DESC)
  FROM Salaries
)

SELECT C.emp_name,
	    C.sal_date AS curr_date, -- 今の給料昇給日
		C.sal_amt AS curr_amt,   -- 今の給料
		P.sal_date AS prev_date, -- 1つ前の給料昇給日
		P.sal_amt AS prev_amt    -- 1つ前の給料昇
FROM SalaryRanks AS C 
  LEFT JOIN SalaryRanks AS P ON P.emp_name = C.emp_name AND P.pos = 2
WHERE C.pos = 1

ORDER BY C.emp_name DESC,C.sal_date,P.sal_date
```

▼ 出力


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


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