可視化 SQL | パズル#9  席あいてますか？その２

# 初めに
この問題は、SQLパズル #9
『席空いてますか』を解説します
手元に『SQL パズル』があればわかりやすいです

前回パズル#9 の記事を公開しました
こちらでは
並んでいる数字の中から
<font color="Blue">歯抜けの番号を探す</font>・・でした

https://qiita.com/hidesroom/items/de01259d2a866c0dbc21


今回は同じパズルで別のSQLを書いてみました
やりたい事は
<font color="Blue">歯抜けの番号を前の番号で埋める</font>・・です
下記右側がそのイメージです

![pic_1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/63ce0aff-3fdb-4a70-8c4e-596f03f5fded.png)


:::note
【手法】
①空白番号を見つける
②空白番号から見て、直近の空白ではない番号を見つける
③この番号で空白を埋める
:::


> SQLの書き方のテクニックの1つとして
> 見ていただけると幸いです

**PostgreSQLで動作確認してます**

# Restaurant Table & Data
```SQL:SQL
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)を作る
```SQL:SQL
-- 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までの連続する番号を
再帰処理で作成します
再帰処理は下記記事をご確認ください

https://qiita.com/hidesroom/items/fc24cd5b1799e6f3d7aa


# Baseとなるテーブルを作成
```SQL:SQL
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 となるテーブル
　⇒ 今後このテーブルにデータを結合していきます

▼出力
![pic_2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/2e773d47-6589-48d7-893e-810bd5ed6cf0.png)

# Joinしたデータを加工する①
```SQL: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
)

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
```
 ▼出力
![pic_4.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/5f16536b-5099-4cc8-a35b-600f1494037a.png)
 
SELECT句に ARRAY_AGG()と MAX()を追加

> ここでの作業は
> 空白行(NULL)の場合は
> その前にある数字で埋める・・為の前処理です

**ARRAY_AGG(c2.seq)**
　⇒ c1.seq より小さいseqを別のseq_1から取出す
　⇒ 数字の集合
　⇒ MAX値を取る為の確認用
**MAX(c2.seq)**
　⇒ c1.seq より小さいseqは複数ある
　⇒ ここからMAX値を取る
　⇒ <font color="red">赤文字</font>が空白を埋める数字です
 


# Joinしたデータを加工する②
```SQL: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
)

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句で <font color="deeppink">c1.set IS NULL</font> を追加

> 空白に番号を持つデータができました
> これを次に使用します

▼出力(画像右側) 
![pic_5.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/173614df-ebde-4db8-8a42-bb755b631d5b.png)


# Joinしたデータを加工する③
```SQL: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.seq,d1.seat,d2.seq,d2.seq_max
FROM seq_1 d1
LEFT JOIN seq_2 d2 ON d2.seq = d1.seq
```

▼出力(画像右側)
![pic_6.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/b64f3039-b375-4b94-a4d0-b6956fdbec69.png)


`Baseとなるテーブルを作成` で加工したデータ
　⇒ 上の画像左(黄色)
　　 着席してる番号 & 1~20まで持ってるデータ
　⇒ <font color="orange">seq_1</font>
`Joinしたデータを加工する⓶` で加工したデータ
　⇒ 上の画像真ん中(白色)
　⇒ 空白番号に直前の番号を持たせた
　⇒ <font color="blue">seq_2</font>

seq_1とseq_2を**LEFT JOIN**する

> SELECT d1.seq,d1.seat,d2.seq,d2.seq_max
> FROM <font color="orange">seq_1</font> d1
> LEFT JOIN <font color="blue">seq_2</font> d2 ON d2.seq = d1.seq

**LEFT JOIN**が重要な所です
　⇒ 上の画像右
　⇒ 黄色の部分は元データに数字がある
　⇒ 青色の部分は元データに数字がない
 　　**しかし**、今は数字がある(赤色文字)
 　　⇒ <font color="deeppink">LEFT JOIN でこのデータを結合しました</font>

後は、最後のSELECT句を修正するだけです

# 回答SQL
```SQL: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
```
▼出力(画像右側)
![pic_7.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/2c550dd4-42a7-4fb7-9767-0256826471a9.png)


SELECT句を修正してます
　⇒ d1.seat,COALESCE(d1.seat,d2.seq_max) seq2
　⇒ <font color="orange">d1.seat</font>がNULLの時、<font color="blue">d2.seq_max</font>を表示


>  COALESCE() は使う機会が多いです
>
> 表示したい列のデータがNULLの場合
> 別の列のデータを表示させる
> 空白表示となるのを避ける場合によく使います


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