初めに
この問題は、SQLパズル #9
『席空いてますか』を解説します
手元に『SQL パズル』があればわかりやすいです
前回パズル#9 の記事を公開しました
こちらでは
並んでいる数字の中から
歯抜けの番号を探す・・でした
今回は同じパズルで別のSQLを書いてみました
やりたい事は
歯抜けの番号を前の番号で埋める・・です
下記右側がそのイメージです
【手法】
①空白番号を見つける
②空白番号から見て、直近の空白ではない番号を見つける
③この番号で空白を埋める
SQLの書き方のテクニックの1つとして
見ていただけると幸いです
PostgreSQLで動作確認してます
Restaurant Table & Data
CREATE TABLE Restaurant(
seat integer -- 着席してる席番号
);
INSERT INTO Restaurant VALUES(1);
INSERT INTO Restaurant VALUES(2);
INSERT INTO Restaurant VALUES(3);
INSERT INTO Restaurant VALUES(8);
INSERT INTO Restaurant VALUES(9);
INSERT INTO Restaurant VALUES(10);
INSERT INTO Restaurant VALUES(16);
INSERT INTO Restaurant VALUES(18);
INSERT INTO Restaurant VALUES(19);
連続する番号(1~20)を作る
-- 1 から 20 までの連続する番号
WITH RECURSIVE seq_0 AS (
SELECT 1 AS seq
UNION ALL
SELECT seq + 1 FROM seq_0 WHERE seq < 20
)
SELECT * FROM seq_0
-- Output
-- 1
-- 2
-- ・
-- ・
-- 20
１から20までの連続する番号を
再帰処理で作成します
再帰処理は下記記事をご確認ください
Baseとなるテーブルを作成
WITH RECURSIVE seq_0 AS (
SELECT 1 AS seq
UNION ALL
SELECT seq + 1 FROM seq_0 WHERE seq < 20
)
SELECT b1.seq, b2.seat
FROM seq_0 b1
LEFT JOIN Restaurant b2 ON b1.seq = b2.seat
ORDER BY b1.seq
連番にRestaurantテーブルをJoinする
⇒ base となるテーブル
⇒ 今後このテーブルにデータを結合していきます
▼出力
Joinしたデータを加工する①
WITH RECURSIVE seq_0 AS (
SELECT 1 AS seq
UNION ALL
SELECT seq + 1 FROM seq_0 WHERE seq < 20
)
,seq_1 AS (
SELECT b1.seq, b2.seat
FROM seq_0 b1
LEFT JOIN Restaurant b2 ON b1.seq = b2.seat
ORDER BY b1.seq
)
SELECT c1.*
,(SELECT ARRAY_AGG(c2.seq) FROM seq_1 c2 WHERE c2.seq<c1.seq AND c2.seat IS NOT NULL)
,(SELECT MAX(c2.seq) FROM seq_1 c2 WHERE c2.seq<c1.seq AND c2.seat IS NOT NULL)
FROM seq_1 c1
ORDER BY c1.seq
▼出力
SELECT句に ARRAY_AGG()と MAX()を追加
ここでの作業は
空白行(NULL)の場合は
その前にある数字で埋める・・為の前処理です
ARRAY_AGG(c2.seq)
⇒ c1.seq より小さいseqを別のseq_1から取出す
⇒ 数字の集合
⇒ MAX値を取る為の確認用
MAX(c2.seq)
⇒ c1.seq より小さいseqは複数ある
⇒ ここからMAX値を取る
⇒ 赤文字が空白を埋める数字です
Joinしたデータを加工する②
WITH RECURSIVE seq_0 AS (
SELECT 1 AS seq
UNION ALL
SELECT seq + 1 FROM seq_0 WHERE seq < 20
)
,seq_1 AS (
SELECT b1.seq, b2.seat
FROM seq_0 b1
LEFT JOIN Restaurant b2 ON b1.seq = b2.seat
ORDER BY b1.seq
)
SELECT c1.*
,(SELECT ARRAY_AGG(c2.seq) FROM seq_1 c2 WHERE c2.seq<c1.seq AND c2.seat IS NOT NULL)
,(SELECT MAX(c2.seq) FROM seq_1 c2 WHERE c2.seq<c1.seq AND c2.seat IS NOT NULL)
FROM seq_1 c1
WHERE c1.seat IS NULL -- 追加
ORDER BY c1.seq
seatが空白の番号を取出したいので
WHERE句で c1.set IS NULL を追加
空白に番号を持つデータができました
これを次に使用します
▼出力(画像右側)
Joinしたデータを加工する③
WITH RECURSIVE seq_0 AS (
SELECT 1 AS seq
UNION ALL
SELECT seq + 1 FROM seq_0 WHERE seq < 20
)
,seq_1 AS (
SELECT b1.seq, b2.seat
FROM seq_0 b1
LEFT JOIN Restaurant b2 ON b1.seq = b2.seat
ORDER BY b1.seq
)
,seq_2 AS (
SELECT c1.*
-- ,(SELECT ARRAY_AGG(c2.seq) FROM seq_1 c2 WHERE c2.seq<c1.seq AND c2.seat IS NOT NULL)
,(SELECT MAX(c2.seq) FROM seq_1 c2 WHERE c2.seq<c1.seq AND c2.seat IS NOT NULL) seq_max
FROM seq_1 c1
WHERE c1.seat IS NULL
)
SELECT d1.seq,d1.seat,d2.seq,d2.seq_max
FROM seq_1 d1
LEFT JOIN seq_2 d2 ON d2.seq = d1.seq
▼出力(画像右側)
Baseとなるテーブルを作成 で加工したデータ
⇒ 上の画像左(黄色)
着席してる番号 & 1~20まで持ってるデータ
⇒ seq_1
Joinしたデータを加工する⓶ で加工したデータ
⇒ 上の画像真ん中(白色)
⇒ 空白番号に直前の番号を持たせた
⇒ seq_2
seq_1とseq_2をLEFT JOINする
SELECT d1.seq,d1.seat,d2.seq,d2.seq_max
FROM seq_1 d1
LEFT JOIN seq_2 d2 ON d2.seq = d1.seq
LEFT JOINが重要な所です
⇒ 上の画像右
⇒ 黄色の部分は元データに数字がある
⇒ 青色の部分は元データに数字がない
しかし、今は数字がある(赤色文字)
⇒ LEFT JOIN でこのデータを結合しました
後は、最後のSELECT句を修正するだけです
回答SQL
WITH RECURSIVE seq_0 AS (
SELECT 1 AS seq
UNION ALL
SELECT seq + 1 FROM seq_0 WHERE seq < 20
)
,seq_1 AS (
SELECT b1.seq, b2.seat
FROM seq_0 b1
LEFT JOIN Restaurant b2 ON b1.seq = b2.seat
ORDER BY b1.seq
)
,seq_2 AS (
SELECT c1.*
-- ,(SELECT ARRAY_AGG(c2.seq) FROM seq_1 c2 WHERE c2.seq<c1.seq AND c2.seat IS NOT NULL)
,(SELECT MAX(c2.seq) FROM seq_1 c2 WHERE c2.seq<c1.seq AND c2.seat IS NOT NULL) seq_max
FROM seq_1 c1
WHERE c1.seat IS NULL
)
SELECT d1.seat,COALESCE(d1.seat,d2.seq_max) seq2 -- 修正
FROM seq_1 d1
LEFT JOIN seq_2 d2 ON d2.seq = d1.seq
▼出力(画像右側)
SELECT句を修正してます
⇒ d1.seat,COALESCE(d1.seat,d2.seq_max) seq2
⇒ d1.seatがNULLの時、d2.seq_maxを表示
COALESCE() は使う機会が多いです
表示したい列のデータがNULLの場合
別の列のデータを表示させる
空白表示となるのを避ける場合によく使います