「大人の教養・知識・気付き」を伸ばすブログ

一流の大人(ビジネスマン、政治家、リーダー…)として知っておきたい、教養・社会動向を意外なところから取り上げ学ぶことで“気付く力”を伸ばすブログです。目下、データ分析・語学に力点を置いています。

MENU

SQL中級者になる!(その10/12)

初めに

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

power-of-awareness.com

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

を参考に、更なる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)

10.5 まとめ

  • \mathrm{SQL}でのデータの扱い方には2通りである。
  • 1つは、順序を無視した集合と見なす方法である。この場合、伝統的な\mathrm{SQL}の集合と述語による考え方に基づいて考える。
  • もう1つはおもにウィンドウ関数による直接的な順序の操作による、順序を持った集合と見なす方法である。
  • \mathrm{SQL}で全称文を記述したい場合、存在分の否定に同地変形して、\mathrm{NOT\ EXISTS}述語を用いる必要がある。
プライバシーポリシー お問い合わせ