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

15238 ワード
join Max SQL Window関数 case SQL テキストリンク

初めに

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

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

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

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

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

下記画像の様な出力結果となります
各社員に対して、今のお給料の昇給日とお給料、前回の昇給日とお給料が
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つ前の昇給日が２ となっています

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

順番が3未満のデータだけ取り出す

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 のデータを取出しています

CASE文で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 と名前を付ける

回答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を使って集約する
　⇒　集約しないと下記の様なエラーとなる

参考文献

SQLパズル 第2版~プログラミングが変わる書き方/考え方 | Joe Celko, ミック