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

初めに

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

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

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

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

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

PostgreSQL で動作確認しています

テーブル と データ

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 テーブルの中身

Window 関数を使ってデータを加工する

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つ前の昇給日が２ となっています

LEFT JOINで元となるデータを作る

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 して元データを作ります
結合条件が社員名なので『その６』と同じ様な元データが出来上がります

1つ前の給料情報を取出す

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側 が空白となる

今給料情報を取出す

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 の行を取り出す

回答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, ミック