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

初めに

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

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

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

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

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

CROSS JOIN を使って集計元になるデータを作る

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 した後のイメージを置きます

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

組合せを作る

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を持つ行を取出す
　⇒　テーブル同士を自己結合して組合せを作ります
　⇒　組合せを作る場合よく使う手法です。画像をご確認ください

S0.Salaries のデータで GROUP BY

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

▼ 出力(画像の左側)

COUNT(*) が 2以下のデータを取出す

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

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

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

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

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

組合せを作る２

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

▼ 出力

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

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

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

給料データの取出し

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 のデータを取出す必要が有ります

回答 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関数で表示させています

参考文献

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