oracle用笛卡尔积找连续日期中残缺的数据

[思路转自]SQL查询案例：利用笛卡尔积寻找连续日期中残缺的数据_苦苦的潜行者的博客-CSDN博客_笛卡尔积查找缺少

[区别]本篇文章是在oracle 11g条件下测试可执行。

[背景]关于笛卡尔积解决残缺数据的应用资料，口述多，代码少，查阅了很久才找到相关文章，无oracle数据库下的操作的文章。

---

--创建表格

CREATE TABLE TestDataCheck (
id varchar2(40), --设备ID
dates date --日期
)

--插入数据

INSERT INTO TestDataCheck VALUES ('设备1', to_date('2010-10-01','yyyy-mm-dd'));
INSERT INTO TestDataCheck VALUES ('设备1', to_date('2010-10-02','yyyy-mm-dd'));
INSERT INTO TestDataCheck VALUES ('设备1', to_date('2010-10-03','yyyy-mm-dd'));
INSERT INTO TestDataCheck VALUES ('设备1', to_date('2010-10-04','yyyy-mm-dd'));
INSERT INTO TestDataCheck VALUES ('设备1', to_date('2010-10-06','yyyy-mm-dd'));
INSERT INTO TestDataCheck VALUES ('设备1', to_date('2010-10-07','yyyy-mm-dd'));
INSERT INTO TestDataCheck VALUES ('设备1', to_date('2010-10-08','yyyy-mm-dd'));
INSERT INTO TestDataCheck VALUES ('设备1', to_date('2010-10-09','yyyy-mm-dd'));
INSERT INTO TestDataCheck VALUES ('设备2', to_date('2010-10-01','yyyy-mm-dd'));
INSERT INTO TestDataCheck VALUES ('设备2', to_date('2010-10-02','yyyy-mm-dd'));
INSERT INTO TestDataCheck VALUES ('设备2', to_date('2010-10-03','yyyy-mm-dd'));
INSERT INTO TestDataCheck VALUES ('设备2', to_date('2010-10-07','yyyy-mm-dd'));
INSERT INTO TestDataCheck VALUES ('设备2', to_date('2010-10-08','yyyy-mm-dd'));
INSERT INTO TestDataCheck VALUES ('设备2', to_date('2010-10-09','yyyy-mm-dd'));
INSERT INTO TestDataCheck VALUES ('设备2', to_date('2010-10-10','yyyy-mm-dd'));

select * from TestDataCheck;

/*
drop table testdatacheck;
*/

【要求】

取得所有设备，在2010-10-01至2010-10-10之间，缺少的记录

【思路】

首先需要一个10天的日期序列

然后，需要一个10天与设备的排列组合

最后，用排列组合与目标表关联检索缺少的数据。

--创建日期序列

暂时用1-10来存储日期的变化。最后与主表关联的时候，再作日期的处理。

create table no_test(no number);

--插入数据

insert into no_test values(1);
insert into no_test values(2);
insert into no_test values(3);
insert into no_test values(4);
insert into no_test values(5);
insert into no_test values(6);
insert into no_test values(7);
insert into no_test values(8);
insert into no_test values(9);
insert into no_test values(10);

select * from no_test;

/*
展示内容如下
NO
-----
1
2
3
4
5
6
7
8
9
10
*/

--日期、设备排列组合

select * from 
(select distinct id from TestDataCheck) a,
no_test b
order by no;

/*
展示内容如下
id    no
---------
设备1 1
设备2 1
设备1 2
设备2 2
设备1 3
设备2 3
设备1 4
设备2 4
设备1 5
设备2 5
设备1 6
设备2 6
设备1 7
设备2 7
设备1 8
设备2 8
设备1 9
设备2 9
设备1 10
设备2 10


*/

--排列组合与目标表关联

   select * 
     from (select *  
             from (    select distinct id 
                         from TestDataCheck) a, no_test b 
                     order by no
           ) aa       --日期、设备排列组合
left join (select id
                , to_number(to_char(dates, 'dd')) datess 
             from TestDataCheck
          ) bb    --TestDataCheck 的 date 字段 保留‘日’
       on aa.id=bb.id and aa.no=bb.datess
    where bb.id is null  --筛选有残缺的数据

--展示结果

     ID      NO     ID      DATES
1    设备2    4        
2    设备1    5        
3    设备2    5        
4    设备2    6        
5    设备1    10