通过比较两个日期列确定实际的TAT(周转时间)

Question

我有一个具有以下结构的表，我试图用它来找出两天之间的TAT(周转时间)。

Appln No        Start Date  End Date
1001009     01-10-15    06-10-15
1001009     02-10-15    04-10-15
1001009     03-10-15    04-10-15
1001009     03-10-15    05-10-15
1001009     04-10-15    07-10-15
1001009     09-10-15    10-10-15
1001009     12-10-15    16-10-15
1001009     14-10-15    17-10-15

从上述样本数据中删除重叠日期后，输出将采用以下格式：

Appln No    Start Date  End Date
1001009     01-10-15    07-10-15
1001009     09-10-15    10-10-15
1001009     12-10-15    17-10-15

由于我是sql的初学者，并且使用oracle sql developer，我发现很难将上面的逻辑写成代码。欢迎对此问题提出任何建议:)

Answer 1

试试这个：

select t1.* from myTable t1
inner join myTable t2
on t2.StartDate > t1.StartDate and t2.StartDate < t1.EndDate

Answer 2

这是一个棘手的查询。您需要通过分配分组id来标识重叠的组。要做到这一点，一种方法是找到重叠的组从哪里开始，然后累积每个记录之间的开始数。

下面假设您的表有一个主键(由于没有更好的名称，所以称为id )。

这提供了聚合以获得您想要的内容的机会：

select ApplnNo, min(start), max(end)
from (select t.*,
             sum(IsGroupStart) over (partition by ApplnNo order by start) as grp
      from (select t.*,
                   (case when exists (select 1
                                      from t t2
                                      where t2.end >= t.start and t2.start <= t.end and
                                            t2.id <> t.id
                                     )
                         then 0 else 1
                    end) as IsGroupStart
            from t
           ) t
      ) t
group by ApplnNo, grp;

这里有一些细微的差别。exists的确切最内层的子查询取决于您如何定义重叠。这包括在开始或结束时有一天的重叠。

Answer 3

这更是一项棘手的任务，因为你不能相信间隔的任何顺序。我通过删除子区间(完全被其他区间覆盖的区间)来攻击它。在此之后，我可以遵循START_DATE定义的顺序，查看下一个感知间隔是否与下一个重叠，并应用标准分组机制。

 with subs as (
 /* first remove all intervals that are subsets of other intervals */
 select * from tst t1
 where NOT exists (select null from tst t2 where t2.start_date < t1.start_date and t1.end_date < t2.end_date)
 ),overlap as (
 select APPLN_NO, START_DATE, END_DATE,
 case when (nvl(lag(END_DATE) over (partition by APPLN_NO order by START_DATE),START_DATE-1)  < START_DATE) then 
      row_number() over (partition by APPLN_NO order by START_DATE) end grp
 from subs),
 overlap2 as (
 select 
 APPLN_NO, START_DATE, END_DATE, GRP,
 last_value(grp ignore nulls) over (partition by APPLN_NO order by START_DATE) as grp2
 from overlap)
 select 
   APPLN_NO,   min(START_DATE) START_DATE, max(END_DATE) END_DATE
 from overlap2
 group by APPLN_NO, grp2
 order by 1,2
 ;

在我的设置中检查查询

 drop table tst ;
 create table tst 
 (appln_no number,
 start_date date,
 end_date date);

 insert into tst values (1001009, to_date('01-10-15','dd-mm-rr'),to_date('06-10-15','dd-mm-rr'));
 insert into tst values (1001009, to_date('02-10-15','dd-mm-rr'),to_date('04-10-15','dd-mm-rr'));
 insert into tst values (1001009, to_date('03-10-15','dd-mm-rr'),to_date('04-10-15','dd-mm-rr'));
 insert into tst values (1001009, to_date('03-10-15','dd-mm-rr'),to_date('05-10-15','dd-mm-rr'));
 insert into tst values (1001009, to_date('04-10-15','dd-mm-rr'),to_date('07-10-15','dd-mm-rr'));
 insert into tst values (1001009, to_date('09-10-15','dd-mm-rr'),to_date('10-10-15','dd-mm-rr'));
 insert into tst values (1001009, to_date('12-10-15','dd-mm-rr'),to_date('16-10-15','dd-mm-rr'));
 insert into tst values (1001009, to_date('13-10-15','dd-mm-rr'),to_date('14-10-15','dd-mm-rr')); /* this is added to make it more interesting */
 insert into tst values (1001009, to_date('15-10-15','dd-mm-rr'),to_date('17-10-15','dd-mm-rr'));

给

    APPLN_NO START_DATE          END_DATE          
 ---------- ------------------- -------------------
    1001009 01.10.2015 00:00:00 07.10.2015 00:00:00 
    1001009 09.10.2015 00:00:00 10.10.2015 00:00:00 
    1001009 12.10.2015 00:00:00 17.10.2015 00:00:00 

不出所料。