SQL中級者になる！（その10/12） - 「大人の教養・知識・気付き」を伸ばすブログ

初めに

　データベースとSQLは現在のデータサイエンスに必須な知識・スキルの1つ。その基礎を

power-of-awareness.com

を基に学んできた。
　この知識をより昇華させて「SQL中級者になりたい！」ため、同じ著者の

達人に学ぶSQL徹底指南書第2版初級者で終わりたくないあなたへ

作者:ミック
翔泳社

Amazon

を参考に、更なるSQLの知識を拡充していこう。

前回

power-of-awareness.com

初めに
前回
10.　SQLで数列を扱う
次回

10.　SQLで数列を扱う

10.1　連番をつくる

　0-9までを格納したテーブル $\mathrm{digits}$ を作った上で、以下を実行すると、1-100までの連番をつくることができる。

CREATE VIEW Sequence (seq) AS
SELECT D1.digit + (D2.digit * 10) + (D3.digit * 100)
FROM Digits D1
CROSS JOIN Digits D2
CROSS JOIN Digits D3
-- 
SELECT seq
FROM Sequence
WHERE seq BETWEEN 1 AND 100
ORDER BY seq

10.2　欠番をすべて求める

　欠番のあるテーブルが存在するとする。このとき欠番を求める。

SeqTbl

$\mathrm{seq}$ (連番)
$1$
$2$
$3$
$4$
$5$
$6$
$7$
$8$
$11$
$12$

-- EXCEPTを用いる例
SELECT seq
FROM Sequence
WHERE seq BETWEEN 1 AND 12
EXCEPT SELECT seq
FROM SeqTbl

-- NOT INを用いる例
SELECT seq
FROM Sequence
WHERE seq BETWEEN 1 AND 12
AND seq NOT IN (SELECT seq FROM SeqTbl)

-- 最大値と最小値を動的に取る例
SELECT seq
FROM Sequence
WHERE seq BETWEEN (SELECT MIN(seq) FROM SeqTbl) AND (SELECT MAX(seq) FROM SeqTbl)
AND seq NOT IN (SELECT seq FROM SeqTbl)

10.3　連番を求める

　連続する3つの番号を求める。

$\mathrm{seat}$ (座席)	$\mathrm{status}$ (状態)
$1$	占
$2$	占
$3$	空
$4$	空
$5$	空
$6$	占
$7$	空
$8$	空
$11$	空
$12$	占
$13$	占
$14$	空
$15$	空

-- :head_cntは連続する数を表すパラメータ
SELECT S1.seat AS start_seat, '~', S2.seat AS end_seat
FROM Seats S1, Seats S2
WHERE S2.seat = S1.seat + (:head_cnt - 1)
AND NOT EXISTS
(SELECT *
 FROM Seats S3
 WHERE S3.seat BETWEEN S1.seat AND S2.seat
 AND S2.status <> '空')


**10.4　折り返しのある数列
　以下のようにラインに変更があり得る連番について3つ連続の連番を取ることにする。当然ながらラインが変更する場合は連番と見なさない。

|*[tex:\mathrm{seat}]</p>(座席)|*[tex:\mathrm{line}]_[tex:\mathrm{id}](行ID)|*[tex:\mathrm{status}]</p>(状態)|
|[tex:1]|[tex:\mathrm{A}]|占|
|[tex:2]|[tex:\mathrm{A}]|占|
|[tex:3]|[tex:\mathrm{A}]|空|
|[tex:4]|[tex:\mathrm{A}]|空|
|[tex:5]|[tex:\mathrm{A}]|空|
|[tex:6]|[tex:\mathrm{B}]|占|
|[tex:7]|[tex:\mathrm{B}]|占|
|[tex:8]|[tex:\mathrm{B}]|空|
|[tex:9]|[tex:\mathrm{B}]|空|
|[tex:10]|[tex:\mathrm{B}]|空|
|[tex:11]|[tex:\mathrm{C}]|空|
|[tex:12]|[tex:\mathrm{C}]|空|
|[tex:13]|[tex:\mathrm{C}]|空|
|[tex:14]|[tex:\mathrm{C}]|占|
|[tex:15]|[tex:\mathrm{C}]|空|

>|sql|
-- NOT EXISTS
SELECT S1.seat AS start_seat, '~', S2.seat AS end_seat
FROM Seats2, Seats2 S2
WHERE S2.seat=S1.seat + (:head_cnt -1)
AND NOT EXISTS
(SELECT *
 FROM Seats2 S3
 WHERE S3 BETWEEN S1.seat AND S2.seat
 AND (S3.status <> '空' OR S3.line_id <> S1.line_id))

-- ウィンドウ関数
SELECT seat, '~', seat + (:head_cnt -1)
FROM (SELECT seta, MAX(seat)
            OVER (PARTITION BY line_id ORDER BY seat
            ROWS BETWEEN (:head_cnt - 1) FOLLOWING
                               AND (:head_cnt - 1) FOLLOWING) AS end_seat
            FROM Seats2
            WHERE status = '空') TMP
WHERE end_seat - seat = (:head_cnt -1)