10-292 クロスジョインして、minusで差集合演算

SQLパズル

daysTable          SinceTable
 ID  日付           ID  開始日
---  ----------    ---  ----------
001  2009-04-13    001  2009-04-12
001  2009-04-14    002  2009-04-13
001  2009-04-16    123  2009-04-13
002  2009-04-13
002  2009-04-16
123  2009-04-14

IDごとで、SinceTableの開始日から、
2009-04-16までの欠損日を求める。

出力結果
ID   欠損日
---  --------
001  09-04-12
001  09-04-15
002  09-04-14
002  09-04-15
123  09-04-13
123  09-04-15
123  09-04-16

データ作成スクリプト

create table daysTable(ID,日付) as
select '001',date '2009-04-13' from dual union
select '001',date '2009-04-14' from dual union
select '001',date '2009-04-16' from dual union
select '002',date '2009-04-13' from dual union
select '002',date '2009-04-16' from dual union
select '123',date '2009-04-14' from dual;

create table SinceTable(ID,開始日) as
select '001',date '2009-04-12' from dual union
select '002',date '2009-04-13' from dual union
select '123',date '2009-04-13' from dual;

SQL

select a.ID,a.開始日-1+b.rn as 欠損日
  from SinceTable a Join (select RowNum as rn from dict) b
    on (a.開始日-1+b.rn <= date '2009-04-16')
 minus
select ID,日付 from daysTable
order by ID,欠損日;

解説

クロスジョインで行を増やしつつ、組み合わせを求めて、
minusで差集合演算を行ってます。

---