oracle 数据遍历差计算问题

[路遥迢]

例子数据
create table LEAD_TABLE
(
CASEID VARCHAR2(10),
STEPID VARCHAR2(10),
ACTIONDATE DATE
)
insert into LEAD_TABLE values('Case1','Step1',to_date('20161101','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step2',to_date('20161103','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step3',to_date('20161104','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step4',to_date('20161105','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step5',to_date('20161107','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step4',to_date('20161108','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step6',to_date('20161112','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step1',to_date('20161201','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case2','Step2',to_date('20161202','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case2','Step3',to_date('20161203','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case2','Step3',to_date('20161205','yyyy-mm-dd'));
commit;

有个需求，我想算出以CASEID分组，ACTIONDATE 排序，并以各自分组第一条的ACTIONDATE 为时间基准，获得以后所有该分组时间差>=两天的数据
想要的结果如下，有没办法？

('Case1','Step1',to_date('20161101','yyyy-mm-dd'));基准，要
('Case1','Step2',to_date('20161103','yyyy-mm-dd')); 这条与上一条差2天，要
('Case1','Step3',to_date('20161104','yyyy-mm-dd'));这条与上一条差1天，不要
('Case1','Step4',to_date('20161105','yyyy-mm-dd'));这条与上一条差1天，，但与上上一条差2天，要
('Case1','Step5',to_date('20161107','yyyy-mm-dd')); 这条与上一条差2天，要
('Case1','Step4',to_date('20161108','yyyy-mm-dd'));这条与上一条差1天，不要
('Case1','Step6',to_date('20161112','yyyy-mm-dd'));这条与上上一条差5天，要
('Case1','Step1',to_date('20161201','yyyy-mm-dd'));这条与上上一条差18天，要

('Case2','Step2',to_date('20161202','yyyy-mm-dd'));基准，要
('Case2','Step3',to_date('20161203','yyyy-mm-dd'));这条与上一条差1天，不要
('Case2','Step3',to_date('20161205','yyyy-mm-dd'));这条与上一条差2天，，但与上上一条差3天，要

[ckc]

这么复杂，用pl/sql或者外部语言处理吧，sql搞这个就算能搞也很麻烦的 另外，to_date('20161202','yyyy-mm-dd')应该是to_date('20161202','yyyymmdd')吧

[卖水果的net]

楼主研究下 lead 和 lag 这两个函数，可以实现你的需求；

[jdsnhan]

select caseid,stepid,actiondate from 
(select caseid,stepid,actiondate,lag(actiondate,1) over(partition by caseid order by actiondate) ldate from lead_table)
where actiondate - ldate >=2 or ldate is null

不过，('Case1','Step4',to_date('20161105','yyyy-mm-dd'));这条与上一条差1天，，但与上上一条差2天，要 这句不满足，为啥还比较上上一条记录呢 ('Case1','Step4',to_date('20161108','yyyy-mm-dd'));这条与上一条差1天，不要 这条又为什么不比较上上一条记录呢

[路遥迢]

引用 3 楼 jdsnhan 的回复:

select caseid,stepid,actiondate from 
(select caseid,stepid,actiondate,lag(actiondate,1) over(partition by caseid order by actiondate) ldate from lead_table)
where actiondate - ldate >=2 or ldate is null

不过，('Case1','Step4',to_date('20161105','yyyy-mm-dd'));这条与上一条差1天，，但与上上一条差2天，要 这句不满足，为啥还比较上上一条记录呢 ('Case1','Step4',to_date('20161108','yyyy-mm-dd'));这条与上一条差1天，不要 这条又为什么不比较上上一条记录呢

因为('Case1','Step4',to_date('20161105','yyyy-mm-dd'))的上一条不要，所以他的要比的是上上条 而('Case1','Step4',to_date('20161108','yyyy-mm-dd'));上一条是要的，所以他要比就是上一条而不是上上条

[jdsnhan]

看你的数据是否有规律性，比如统一分组内不会用相同的actiondate，如果成立，那就可以取actiondate - ldate >=2 和 actiondate - ldate = 1 为偶数个时的数据