SQL徹底指南書の後半問題を解く

概要

達人に学ぶ SQL徹底指南書の後半問題から
分析関数や再帰With句が効果的な問題をピックアップして、解きつつ、
私のSQLの思考法と脳内のイメージを解説します。

目次

第1部 EXISTS述語の使い方
1. テーブルに存在「しない」データを探す (145ページ)(作成中)
2. 素数を求める (157ページ)(作成中)

第2部 SQLで数列を扱う
3. 欠番を全部求める (162ページ)(作成中)
4. 3人なんですけど、座れますか？ (164ページ)(作成中)
5. 3人なんですけど、座れますか？(折り返しを考慮) (166ページ)(作成中)
6. 最大何人まで座れますか？ (168ページ)(作成中)
7. 単調増加と単調減少 (171ページ)(作成中)

第3部 帰ってきたHAVING句
8. 全員、待機中ですか？ (177ページ)(作成中)
9. 一意集合と多重集合 (180ページ)(作成中)
10. 最小の欠番を求める (184ページ)(作成中)
11. 一意集合と多重集合の一般化 (192ページ)(作成中)

解法メモ
9. 一意集合と多重集合  count(*) over(partition by 拠点,資材)
11. 一意集合と多重集合の一般化 dense_rankで座標圧縮してから、count(*) over(partition by 拠点,rn)

リンクメモ
9-65 distinctした結果とpartitioned Anti Join
9-70 単調増加している期間を求める

3. 欠番を全部求める (162ページ)(作成中)

DB2のSQLなので、移植して、ハードコピーも取り直すかも
create table SeqTbl(seq integer);
insert into SeqTbl values(13),(15),(18),(19),(20),(24);

最小値から最大値までの間の欠番を全部求めます。

出力結果
seq
---
 14
 16
 17
 21
 22
 23

下記のように、再帰SQLで最小値から最大値までの間の連番を作成してから、
except allで差集合演算を行うのが分かりやすいと思います。

-- SampleXXXX
with rec(minSeq,maxSeq) as(
select min(seq),max(seq) from SeqTbl
union all
select minSeq+1,maxSeq
  from rec
 where minSeq+1 <= maxSeq)
select minSeq from rec
except all
select seq from SeqTbl;

まず、非再帰項で集約関数のmin関数とmax関数を使って、seqの最小値と最大値を求めつつ、
seqの最小値を持つ行を作成してます。

次に再帰項のwhere句のminSeq+1 <= maxSeqとselect句のminSeq+1,maxSeqによって、
(最小値+1)以上で最大値以下のseqを持つ行を再帰的に作成してます。

ちなみに再帰SQLでは、union allの上にあるselect文を非再帰項と呼び、
union allの下にあるselect文を再帰項と呼びます。

最後にexcept all集合演算を使用して、欠番を全部求めてます。

再帰SQLの脳内のイメージは、下記となります。非再帰項を木の根として木が作成されるイメージです。
13から24までの整数は12個ありますので、木のノードが12個作成されます。



■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

下記のように、分析関数のLead関数で、Seqの昇順で次のSeqを列別名LeadSeqとして求めて、
SeqとLeadSeqとの間に欠番があったら、その欠番を求める。といった方法もあります。

-- SampleXXXX
with rec(Seq,LeadSeq) as(
select Seq+1,LeadSeq
from (select Seq,Lead(Seq) over(order by Seq) as LeadSeq
        from SeqTbl)
 where Seq+1 < LeadSeq
union all
select Seq+1,LeadSeq
  from rec
 where Seq+1 < LeadSeq)
select Seq from rec
order by Seq;

まず、非再帰項のインラインビューで分析関数のLead関数を使って、
seqの昇順で次のseqをLeadSeqとして求めてから、where Seq+1 < LeadSeqを指定して、
seqとLeadSeqとの間に欠番がある行を抽出してます。

次に再帰項のwhere句のSeq+1 < LeadSeqとselect句のSeq+1,LeadSeqによって、
(SeqTblのseq+1)以上でLeadSeq未満の数値を持つ行を再帰的に作成してます。

脳内のイメージ(第1段階)は下記となります。
非再帰項のLead関数を黄緑線でイメージしてます。



脳内のイメージ(最終段階)は下記となります。
非再帰項の where Seq+1 < LeadSeqによる行のフィルタをグレー線でイメージし、
次に、非再帰項を木の根としての木の作成をイメージしてます。