10-324 範囲を過不足なく埋めるかのチェック

SQLパズル

FillTable
ID  StaV  EndV  FillS  FillE
--  ----  ----  -----  -----
 1     2     5      2      3
 1     2     5      4      5
 2     3     6      3      4
 2     3     6      4      7
 3     4     7      3      4
 3     4     7      4      7
 4     4     8      4      5
 4     4     8      7      8
 5     5    10      5      6
 5     5    10      7      8
 5     5    10      9     10
 6     5    11      5      6
 6     5    11      8      9
 6     5    11     10     11
 7     6    10      6     10
 8     8    13      6     13

以下のロジックで、IDごとで、StaVからEndVまでを過不足なく埋めていないIDを出力する。

ID=1 is OK. Case 2-5 filled with 2-3 and 4-5.
ID=2 is NG. Case 3-6 filled with 3-4 and 4-7, point 4-4 is fileld more than once, point 7-7 is not needed.
ID=3 is NG. Case 4-7 filled with 3-4 and 4-7, too much filled in point 4-4 and not needed point 3-3
ID=4 is NG. Case 4-8 filled with 4-5 and 7-8, missing is point 6-6
ID=5 is OK. Case 5-10 filled with 5-6 and 7-8 and 9-10
ID=6 is NG. Case 5-11 filled with 5-6 and 8-9 and 10-11, missing is point 7-7.
ID=7 is OK. Case 6-10 filled with 6-10.
ID=8 is NG. Case 8-13 filled with 6-13 but not needed point 6-7.

出力結果
ID  StaV  EndV
--  ----  ----
 2     3     6
 3     4     7
 4     4     8
 6     5    11
 8     8    13

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table FillTable(ID,StaV,EndV,FillS,FillE) as
select 1,2, 5, 2, 3 from dual union all
select 1,2, 5, 4, 5 from dual union all
select 2,3, 6, 3, 4 from dual union all
select 2,3, 6, 4, 7 from dual union all
select 3,4, 7, 3, 4 from dual union all
select 3,4, 7, 4, 7 from dual union all
select 4,4, 8, 4, 5 from dual union all
select 4,4, 8, 7, 8 from dual union all
select 5,5,10, 5, 6 from dual union all
select 5,5,10, 7, 8 from dual union all
select 5,5,10, 9,10 from dual union all
select 6,5,11, 5, 6 from dual union all
select 6,5,11, 8, 9 from dual union all
select 6,5,11,10,11 from dual union all
select 7,6,10, 6,10 from dual union all
select 8,8,13, 6,13 from dual;

SQL

--■■■階層問い合わせを使う方法(10g以降)■■■
select ID,StaV,EndV from FillTable
minus
select ID,StaV,EndV
from (select ID,StaV,EndV,FillS,FillE,
      min(FillS) over(partition by ID) as MinFillS,
      count(*) over(partition by ID) as cnt
      from FillTable)
where connect_by_IsLeaf = 1
  and EndV = FillE
  and Level = cnt
start with FillS = all(StaV,MinFillS)
connect by nocycle prior ID = ID
               and prior FillE+1 = FillS;

--■■■再帰with句を使う方法(11gR2以降)■■■
with rec(ID,StaV,EndV,FillS,FillE,Val) as(
select ID,StaV,EndV,FillS,FillE,FillS
  from FillTable
union all
select ID,StaV,EndV,FillS,FillE,Val+1
  from rec
 where Val+1 <= FillE)
select ID,StaV,EndV
  from rec
group by ID,StaV,EndV
having not(EndV-StaV+1
          =all(count(*),
           count(distinct case when Val between StaV and EndV
                               then Val end)))
order by ID;

解説

階層問い合わせを行って、葉となったノードのレベルなどをチェックしてます。
脳内で木をイメージするのに苦労しました。

再帰with句を使う方法もあります。
9-62 製品の対象年齢の範囲

---