8-29 Partitioned Inner Joinもどき

SQLパズル

NoTable
  ID  No
----  --
CAT1   1
CAT1   2
CAT1   3
CAT1   5
CAT1   7
CAT2   1
CAT2   3
CAT2   4
CAT2   6

IDごとの、最小のNoから最大のNoまでの間の歯抜けのNoを出力する。

出力結果
  ID  No
----  --
CAT1   4
CAT1   6
CAT2   2
CAT2   5

データ作成スクリプト

create table NoTable(ID,No) as
select 'CAT1', 1 from dual union all
select 'CAT1', 2 from dual union all
select 'CAT1', 3 from dual union all
select 'CAT1', 5 from dual union all
select 'CAT1', 7 from dual union all
select 'CAT2', 1 from dual union all
select 'CAT2', 3 from dual union all
select 'CAT2', 4 from dual union all
select 'CAT2', 6 from dual;

SQL

--■■■Partitioned Outer Joinを使う方法(10g以降)■■■
select b.ID,a.Counter as No
  from (select RowNum as Counter from dict) a
  Left Join (select ID,No,
             max(No) over(partition by ID) as maxNo
             from NoTable) b
  partition by (b.ID,b.maxNo)
    on (a.Counter = b.No)
 where b.No is null
   and a.Counter < b.maxNo;

--■■■Partitioned Outer Joinを使わない方法■■■
select a.ID,b.Counter
from (select ID,max(No) as maxNo
        from NoTable
      group by ID) a,
     (select RowNum as Counter from dict) b
 where a.maxNo >= b.Counter
   and not exists(select 1 from NoTable c
                   where c.ID = a.ID
                     and c.No = b.Counter)
order by a.ID,b.Counter;

--■■■再帰with句を使う方法(11gR2以降)■■■
with rec(ID,No,LeadNo) as(
select ID,No+1,LeadNo
  from (select ID,No,
        Lead(No) over(partition by ID order by No) as LeadNo
        from NoTable)
 where No+1 < LeadNo
union all
select ID,No+1,LeadNo
  from rec
 where No+1 < LeadNo)
select * from rec;

解説

Partitioned Outer Joinを使う方法では、
実質、外部結合してからwhere句でフィルタをかけてるだけですが
Partitioned Outer Joinによく似た
Partitioned Inner Joinと考えていいでしょう。

日本語の用語だと、
パーティション化された外部結合によく似た
パーティション化された内部結合と考えていいでしょう。

Find missing sequences by category(英語)

3-34 Partitioned Outer Join
10-241 Partitioned Anti Joinもどき

---