Oracleの階層問い合わせ（7）
（複雑な枝切り）

2010/06/04 14:00

ポスト

3. 範囲を過不足なく埋めるかのチェック

　最後に、範囲を過不足なく埋めるかのチェックを扱います。テーブルのデータと出力結果は、下記となります。

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は、過不足なく埋めています。
ID=2は、4と7を余分に埋めています。
ID=3は、3と4を余分に埋めています。
ID=4は、6を埋めてません。
ID=5は、過不足なく埋めています。
ID=6は、7を埋めてません。
ID=7は、過不足なく埋めています。
ID=8は、6と7を余分に埋めています。

出力結果

ID	StaV	EndV
1	2	5
5	5	10
7	6	10

　目視で順番にチェックするように、IDごとにFillSが最小の行から順番にチェックしていけばよいと考えて、下記が答えとなります。このように順番にチェックしていくようなケースで階層問い合わせが使えることがあります。

階層問い合わせを使う方法

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;

　解説すると、まずインラインビューで分析関数を使い、IDごとで最小のFillSと、IDごとの件数を求めてます。次に、start with FillS = all(StaV,MinFillS)でFillSがIDごとで最小でありStaVとも等しい行を根として、階層問い合わせを行ってます。なお、all述語を使ってSQLを少し短くしてます。

　次に、connect by句で、同じIDで（親の行の）FillE+1 = （子の行の）FillSを親子条件としてます。最後にwhere句で、葉であり、EndV = FillEで、レベルがIDごとの件数と一致していれば、StaVからEndVまでの範囲を過不足なく埋めていると判定してます。SQLのイメージは下記となります。IDごとに区切る赤線と、階層問い合わせの木をイメージし、木の葉であるノードに黄緑色を塗ってます。

SQLのイメージ

再帰with句を使う方法

　下記のように再帰with句を使う方法もあります。再帰with句でFillS以上FillE以下の整数を全て用意して、group by ID,StaV,EndVの後に、having句でStaV以上EndV以下の整数を過不足なく持っているかをチェックしてます。

再帰with句を使う方法

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 EndV-StaV+1
      =all(count(*),
           count(distinct case when Val between StaV and EndV
                               then Val end))
order by ID;