http://topic.csdn.net/u/20090923/13/baaa34b4-184d-4805-b6d4-747ec0845fe6.html
曾经我发过一个这样的帖子  
还是这样的一个问题 还是时间的连续判断 
只不过是和上一个点不一样 
现在的条件是这样的 
在连续登记的每年内,登记45天中间断不超过1天。只间断1天应考虑为管理性质,而不算作性质不同的等级空缺。
现在把问题简单化 就是在2005-01-01 至2006-01-01 之间判断连续。
这个连续的条件是什么呢!就是登记45天中 间断不超过1天,说白了就是登记45天之间只能有一天的断档。
举例说明:就是说这个人从2005-01-01 登记 到2005-02-15 是登记45天,但是在这45天之内只允许有一天的间断。比如说他登记的时间是这样的 从2005-01-01 开始登记 到2005-01-15 结束 然后又从 2005-01-16开始 到2005-01-25 结束 然后又从 2005-01-27 开始到 2005-02-15 结束  这个人登记的45天之内(从2005-01-01 到 2005-02-15)就只有一天是间断的。
就是一个这样的过程,现在需要得到的就是都有哪些人符合这个条件。
我现在在和大家说一下这个要查询的表结构。
有这样的一个表hist 表里有三个字段  一个是mbrid (就是人的id) 一个是effdat (登记开始的时间) 一个是expdat(登记结束时间) 一个人可能有多次登记 ,而且登记的时间可能是包含的关系。也可能是不连续的
下面是表中的一些数据。    MBRID EFFDAT EXPDAT
100000 2005-1-1 2005-3-31
100000 2005-1-1 2005-12-31
100000 2005-4-1 2005-12-31
100000 2005-7-1 2005-12-31
100000 2006-1-1 2006-3-31
100000 2006-1-1 2006-12-31
100000 1997-1-1 1997-3-16
100000 1997-3-16 1997-12-31
100000 1998-1-1 1998-12-31
100000 1999-1-1 1999-12-31
100000 1999-7-1 2000-1-1
100000 2000-1-1 2000-12-31
100000 2000-4-1 2001-1-1
100000 2000-7-1 2001-1-1
100000 2001-1-1 2001-6-30
100000 2001-4-1 2002-1-1
100000 2002-1-1 2002-5-18
100000 2002-4-1 2002-5-18
100000 2002-5-19 2002-12-31
100000 2003-1-1 2003-3-31
100000 2003-1-1 2003-12-31
100000 2003-4-1 2003-12-31
100000 2004-1-1 2004-3-31
100000 2004-1-1 2004-12-31
100000 2004-4-1 2004-12-31
1000001 2003-7-1 2003-12-31
1000001 2003-7-1 2004-3-31
1000001 2004-1-1 2004-6-30
1000001 2004-4-1 2004-6-30
1000001 2004-7-1 2004-8-15
1000001 2005-6-1 2005-6-30
1000001 2005-7-1 2005-12-31
1000001 2005-7-1 2006-3-31
1000001 2005-7-1 2006-6-30
1000001 2006-7-1 2006-12-31
1000001 1997-3-1 1997-7-1
1000001 1998-1-1 1998-7-1
1000001 1998-7-1 1998-12-31
1000001 1999-1-1 1999-7-1
1000001 2002-1-1 2002-6-30
1000001 2002-4-1 2002-6-30
1000001 2002-7-1 2002-12-31
1000001 2002-7-1 2003-3-31
1000001 2003-1-1 2003-6-30
1000001 2003-4-1 2003-6-30
1000002 1997-1-1 1997-7-1
1000002 1997-7-1 1997-12-31
1000002 1998-1-1 1998-7-1
1000002 1998-7-1 1998-12-1
1000002 1999-1-1 1999-7-1
1000002 1999-7-1 2000-6-30
1000002 2000-1-1 2000-7-1
1000002 2000-4-1 2000-7-1
1000002 2000-7-1 2001-6-30
1000002 2001-4-1 2001-7-1
1000002 2001-7-1 2002-2-1
1000002 2002-1-1 2002-1-31
1000004 1997-7-1 1997-12-31
1000004 1998-1-1 1998-7-1
1000004 1998-12-1 1998-12-31
1000004 1999-7-1 2000-6-30
1000004 2000-7-1 2001-6-30
1000004 2001-4-1 2001-7-1
1000004 2001-7-1 2002-3-31
1000004 2002-1-1 2002-6-30
1000004 2002-4-1 2002-6-30
1000004 2002-7-1 2002-12-31
1000004 2002-7-1 2003-3-31
1000004 2003-1-1 2003-6-30
1000004 2003-4-1 2003-6-30
1000004 2003-7-1 2003-12-31
1000004 2003-7-1 2004-3-31
1000004 2004-1-1 2004-6-30
1000004 2004-4-1 2004-6-30
1000004 2004-7-1 2004-12-31
1000004 2004-7-1 2005-1-31
1000004 2005-1-1 2005-1-31

解决方案 »

  1.   

    45天?
    像上面的示例中有这么条记录
    100000    2001-1-1    2001-6-30
    明显超过45天。可是在这一年里,如果有另一次登记时间和这一次的间隔超过一天,那么100000在2001年是连续还是不连续?
    要查出的是连续的id和年份还是不连续的?楼主能否贴上测试数据的sql脚本语句(可用pl/sql dev导出)..一条一条插入太麻烦了
      

  2.   


    values ('1000009', to_date('01-01-1997', 'dd-mm-yyyy'), to_date('01-07-1997', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000009', to_date('01-07-1997', 'dd-mm-yyyy'), to_date('01-09-1997', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000009', to_date('01-09-1997', 'dd-mm-yyyy'), to_date('31-12-1997', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000009', to_date('01-01-1998', 'dd-mm-yyyy'), to_date('01-06-1998', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000009', to_date('01-06-1998', 'dd-mm-yyyy'), to_date('01-07-1998', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000009', to_date('01-07-1998', 'dd-mm-yyyy'), to_date('01-12-1998', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000009', to_date('01-12-1998', 'dd-mm-yyyy'), to_date('31-12-1998', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000009', to_date('01-01-1999', 'dd-mm-yyyy'), to_date('01-07-1999', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000009', to_date('01-07-2000', 'dd-mm-yyyy'), to_date('30-06-2001', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000009', to_date('01-04-2001', 'dd-mm-yyyy'), to_date('01-07-2001', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000009', to_date('01-01-2002', 'dd-mm-yyyy'), to_date('30-06-2002', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000009', to_date('01-04-2002', 'dd-mm-yyyy'), to_date('30-06-2002', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000009', to_date('01-07-2002', 'dd-mm-yyyy'), to_date('31-07-2002', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('100001', to_date('01-04-2001', 'dd-mm-yyyy'), to_date('01-01-2002', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('100001', to_date('01-01-2002', 'dd-mm-yyyy'), to_date('31-12-2002', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('100001', to_date('01-04-2002', 'dd-mm-yyyy'), to_date('31-12-2002', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('100001', to_date('01-01-2003', 'dd-mm-yyyy'), to_date('31-03-2003', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('100001', to_date('01-01-2003', 'dd-mm-yyyy'), to_date('31-12-2003', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('100001', to_date('01-04-2003', 'dd-mm-yyyy'), to_date('31-12-2003', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('100001', to_date('01-01-2004', 'dd-mm-yyyy'), to_date('31-03-2004', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('100001', to_date('01-01-2004', 'dd-mm-yyyy'), to_date('31-12-2004', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('100001', to_date('01-04-2004', 'dd-mm-yyyy'), to_date('31-12-2004', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('100001', to_date('01-01-2005', 'dd-mm-yyyy'), to_date('31-03-2005', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('100001', to_date('01-01-2005', 'dd-mm-yyyy'), to_date('30-09-2005', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('100001', to_date('01-04-2005', 'dd-mm-yyyy'), to_date('30-09-2005', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('100001', to_date('01-07-2005', 'dd-mm-yyyy'), to_date('30-09-2005', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('100001', to_date('01-10-2005', 'dd-mm-yyyy'), to_date('31-12-2005', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('100001', to_date('01-01-2006', 'dd-mm-yyyy'), to_date('31-03-2006', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('100001', to_date('01-01-2006', 'dd-mm-yyyy'), to_date('14-04-2006', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('100001', to_date('15-04-2006', 'dd-mm-yyyy'), to_date('30-04-2006', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('100001', to_date('01-05-2006', 'dd-mm-yyyy'), to_date('26-09-2006', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('100001', to_date('27-09-2006', 'dd-mm-yyyy'), to_date('31-12-2006', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000011', to_date('01-01-1997', 'dd-mm-yyyy'), to_date('01-07-1997', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000011', to_date('01-01-1999', 'dd-mm-yyyy'), to_date('01-10-1999', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000011', to_date('01-07-1999', 'dd-mm-yyyy'), to_date('01-10-1999', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000011', to_date('01-10-1999', 'dd-mm-yyyy'), to_date('30-06-2000', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000011', to_date('01-01-2000', 'dd-mm-yyyy'), to_date('01-10-2000', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000011', to_date('01-04-2000', 'dd-mm-yyyy'), to_date('01-10-2000', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000011', to_date('01-07-2000', 'dd-mm-yyyy'), to_date('01-10-2000', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000011', to_date('01-10-2000', 'dd-mm-yyyy'), to_date('30-06-2001', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000011', to_date('01-04-2001', 'dd-mm-yyyy'), to_date('01-10-2001', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000011', to_date('01-10-2001', 'dd-mm-yyyy'), to_date('01-01-2002', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000011', to_date('01-01-2002', 'dd-mm-yyyy'), to_date('28-02-2002', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000011', to_date('01-10-2002', 'dd-mm-yyyy'), to_date('31-12-2002', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000011', to_date('01-10-2002', 'dd-mm-yyyy'), to_date('31-03-2003', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000011', to_date('01-01-2003', 'dd-mm-yyyy'), to_date('30-06-2003', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000011', to_date('01-04-2003', 'dd-mm-yyyy'), to_date('30-06-2003', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000011', to_date('01-07-2003', 'dd-mm-yyyy'), to_date('31-12-2003', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000011', to_date('01-07-2003', 'dd-mm-yyyy'), to_date('31-03-2004', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000011', to_date('01-01-2004', 'dd-mm-yyyy'), to_date('30-06-2004', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000011', to_date('01-04-2004', 'dd-mm-yyyy'), to_date('30-06-2004', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000011', to_date('01-07-2004', 'dd-mm-yyyy'), to_date('31-12-2004', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000011', to_date('01-07-2004', 'dd-mm-yyyy'), to_date('31-03-2005', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000011', to_date('01-01-2005', 'dd-mm-yyyy'), to_date('30-06-2005', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000011', to_date('01-04-2005', 'dd-mm-yyyy'), to_date('30-06-2005', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000011', to_date('01-07-2005', 'dd-mm-yyyy'), to_date('30-11-2005', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000011', to_date('01-02-2002', 'dd-mm-yyyy'), to_date('14-04-2002', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000011', to_date('01-04-2002', 'dd-mm-yyyy'), to_date('14-04-2002', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000011', to_date('15-04-2002', 'dd-mm-yyyy'), to_date('31-08-2002', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000011', to_date('01-11-2005', 'dd-mm-yyyy'), to_date('31-12-2005', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000011', to_date('01-11-2005', 'dd-mm-yyyy'), to_date('31-03-2006', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000011', to_date('01-11-2005', 'dd-mm-yyyy'), to_date('30-06-2006', 'dd-mm-yyyy'));
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
    values ('1000011', to_date('01-07-2006', 'dd-mm-yyyy'), to_date('31-12-2006', 'dd-mm-yyyy'));
      

  3.   


    脚本的第一句缺一行 不好意思
    insert into TEST_TETS (MBRID, EFFDAT, EXPDAT)
      

  4.   

    那你看看怎么写吧  我实在是不会了……
    对了 我这还有你上个帖子 也就是那个连续判断的那个的代码select mbrid from(  
    select mbrid,sum(case when effdat>lg+1 then 1 else 0 end)s,min(effdat)mi,max(expdat)ma 
    from( 
    select mbrid,effdat,expdat, 
    lag(expdat)over(partition by mbrid order by expdat)lg from (select * from mbr_hist where effdat <= to_date('2006-01-01','yyyy-mm-dd') and expdat >= to_date('2005-01-01','yyyy-mm-dd'))) 
    where  effdat <=to_date('2006-01-01','yyyy-mm-dd') and expdat>=to_date('2005-01-01','yyyy-mm-dd')
    group by mbrid) 
    where s=0 
    and mi <=to_date('2005-01-01','yyyy-mm-dd')
    and ma>=to_date('2005-12-31','yyyy-mm-dd')
    这个是你以前写的那个代码…… 你看看对这个有没有什么作用
      

  5.   

    看看这个
    select distinct mbrid from(
      select mbrid,expdat,expdat-effdat a,effdat-lag(effdat)over(partition by mbrid order by effdat)b from
        (
          select mbrid,effdat,lead(effdat)over(partition by mbrid order by effdat)expdat from(
          select mbrid,effdat from test_teTs where effdat between date'2005-1-1' and date'2005-12-31'
          union
          select mbrid,expdat from test_teTs where effdat between date'2005-1-1' and date'2005-12-31'))a
        where expdat is not null
          and not exists(select 1 from test_teTs where
              a.mbrid=mbrid and a.effdat>=effdat and a.expdat<=expdat
              and effdat between date'2005-1-1' and date'2005-12-31')
        and expdat-effdat>1)
    where a>2 or b<45
      

  6.   

    啊  那我就明白了 咱们俩说的是一个意思但是我感觉这个写法是不正确的啊  首先这个人必须在我现在所说的2005-1-1 到 2006-1-1的登记啊
    我刚才弄了部分的数据进行测试 有这样的数据    
    MBRID EFFDAT EXPDAT
    1000002 2001-4-1 2001-7-1
    1000002 2001-7-1 2002-2-1
    1000002 2002-1-1 2002-1-31
    但是这个程序没有检索出来啊? 正常这个人在2005-1-1 到 2006-1-1之内都没有登记,就更不用说连续了
    你看是错误出在了什么地方啊
      

  7.   

    上面查的是2005年中有登记且不连续的id
    可以根据全部用户id列表找出2005年有记录且不在上面查询结果中的id,就是有登记且连续的吧
    我想想有没有办法优化下
      

  8.   

    先这么试试吧
    select distinct mbrid from test_tets t where not exists(
        select 1 from(
          select mbrid,expdat,expdat-effdat a,effdat-lag(effdat)over(partition by mbrid order by effdat)b from
            (
              select mbrid,effdat,lead(effdat)over(partition by mbrid order by effdat)expdat from(
              select mbrid,effdat from test_teTs where effdat between date'2005-1-1' and date'2005-12-31'
              union
              select mbrid,expdat from test_teTs where effdat between date'2005-1-1' and date'2005-12-31'))a
            where expdat is not null
              and not exists(select 1 from test_teTs where
                  a.mbrid=mbrid and a.effdat>=effdat and a.expdat<=expdat
                  and effdat between date'2005-1-1' and date'2005-12-31')
            and expdat-effdat>1)
        where a>2 or b<45
          and mbrid=t.mbrid)
      and effdat between date'2005-1-1' and date'2005-12-31'
      

  9.   

    这个好像也不是很正确 
    因为
    MBRID EFFDAT EXPDAT
    -32169 2005-6-14 2005-8-31
    -32169 2005-7-1 2005-8-31
    -32169 2005-9-1 2005-12-31
    -32169 2006-1-1 2006-3-31
    -32169 2006-1-1 2006-8-31
    -32169 2006-9-1 2006-9-30
    -32169 2006-10-1 2006-12-31 
    这样的数据也出现在了查询的结果中  但是他不是合格的啊
    现在查询的结果必须是从2005-1-1 到2006-1-1 之间都是连续的!就是从一月一号 到十二月三十一号都是连续的啊?
      

  10.   

    就是说和上个帖一样,连续的时间段必须包含整个2005年?
    select  mbrid from test_tets t where not exists(
        select 1 from(
          select mbrid,expdat,expdat-effdat a,effdat-lag(effdat)over(partition by mbrid order by effdat)b from
            (
              select mbrid,effdat,lead(effdat)over(partition by mbrid order by effdat)expdat from(
              select mbrid,effdat from test_teTs where effdat<=date'2006-1-1' and expdat>=date'2005-1-1'
              union
              select mbrid,expdat from test_teTs where effdat<=date'2006-1-1' and expdat>=date'2005-1-1'))a
            where expdat is not null
              and not exists(select 1 from test_teTs where
                  a.mbrid=mbrid and a.effdat>=effdat and a.expdat<=expdat
                  and effdat<=date'2006-1-1' and expdat>=date'2005-1-1')
            and expdat-effdat>1)
        where a>2 or b<45
          and mbrid=t.mbrid)
      and effdat<=date'2006-1-1' and expdat>=date'2005-1-1'
    group by mbrid
    having max(expdat)>=date'2005-12-31' and min(effdat)<=date'2005-1-1'
      

  11.   

    。。
    [email protected]
    发到我邮箱吧
      

  12.   

    现在的这个执行效率太低了……
    你看一下 我的表里面有15969835条数据
    下面这个是执行计划SELECT STATEMENT, GOAL = ALL_ROWS Cost=312148008697 Cardinality=2460 Bytes=51660 Time=3745776105
     FILTER
      HASH GROUP BY Cost=312148008697 Cardinality=2460 Bytes=51660 Time=3745776105
       FILTER
        TABLE ACCESS FULL Object owner=ZJHIT Object name=MBR_HIST Cost=17222 Cardinality=2876408 Bytes=60404568 Time=207
        VIEW Object owner=ZJHIT Cost=109118 Cardinality=287641 Bytes=4889897 Time=1310
         WINDOW BUFFER Cost=109118 Cardinality=287641 Bytes=6615743 Time=1310
          FILTER
           VIEW Object owner=ZJHIT Cost=101630 Cardinality=5752816 Bytes=132314768 Time=1220
            WINDOW SORT Cost=101630 Cardinality=5752816 Bytes=80539424 Time=1220
             VIEW Object owner=ZJHIT Cost=72317 Cardinality=5752816 Bytes=80539424 Time=868
              SORT UNIQUE Cost=72317 Cardinality=5752816 Bytes=120809136 Time=868
               UNION-ALL
                TABLE ACCESS FULL Object owner=ZJHIT Object name=MBR_HIST Cost=17167 Cardinality=2876408 Bytes=60404568 Time=207
                TABLE ACCESS FULL Object owner=ZJHIT Object name=MBR_HIST Cost=17167 Cardinality=2876408 Bytes=60404568 Time=207
           TABLE ACCESS BY INDEX ROWID Object owner=ZJHIT Object name=MBR_HIST Cost=7488 Cardinality=1 Bytes=21 Time=90
            INDEX RANGE SCAN Object owner=ZJHIT Object name=HIST2 Cost=390 Cardinality=28746 Time=5
    效率太低了  看看怎么还能优化一下啊?现在的效率太低……
      

  13.   

    嗯,这个效率不高,我再想办法
    上面测试数据不出结果问题应该在这里:a>2 or b<45外面忘记加一层括号了(a>2 or b<45)
      

  14.   

    试试这个
    select mbrid from(
        select mbrid,effdat,expdat,case flag1 when 0 then 
             effdat-lag(effdat)over(partition by mbrid,flag1 order by effdat) end bt from(
            select mbrid,effdat,expdat
              ,min(
                case when effdat<=date'2005-1-1' or exists (select 1 from TEST_MBR_HIST
                           where mbrid=a.mbrid and effdat<a.effdat and expdat>a.effdat-2) then 1 --连续
                   when exists (select 1 from test_mbr_hist
                           where mbrid=a.mbrid and effdat<a.effdat and expdat=a.effdat-2) then 0 --隔2天
                   else -1 --cut
                           end )over(partition by mbrid)flag,
              case when effdat-lag(expdat)over(partition by mbrid order by effdat)=2 then 0 end flag1
            from (
              select mbrid,effdat,max(expdat)expdat from TEST_MBR_HIST 
                where effdat<date'2006-1-1'
                  and expdat>=date'2005-1-1'
                group by mbrid ,effdat)a
        )where flag>=0)
    group by mbrid having (min(bt)>45 or min(bt) is null)and max(expdat)>=date'2005-12-31'
      

  15.   

    千万级的表
    你可以将(
              select mbrid,effdat,max(expdat)expdat from TEST_MBR_HIST 
                where effdat<date'2006-1-1'
                  and expdat>=date'2005-1-1'
                group by mbrid ,effdat)a
    这个结果先提取出来放在临时表里
    再根据这个临时表来进行后续的查询
    create global temporary table a on commit preserve rows as 
      select mbrid,effdat,max(expdat)expdat from TEST_MBR_HIST 
                where effdat<date'2006-1-1'
                  and expdat>=date'2005-1-1'
                group by mbrid ,effdat;
      

  16.   

    connect by 的写法
    select mbrid from(
      select mbrid,expdat,case when flag=2 then effdat-lag( effdat)over(partition by mbrid,flag order by effdat) end flag1 from (  select mbrid,effdat,expdat,effdat-prior expdat flag from(    select mbrid,effdat,expdat,row_number()over(partition by mbrid order by effdat,expdat)rn from(
           select mbrid,effdat,max(expdat)expdat from TEST_MBR_HIST
          where effdat<date'2006-1-1' and expdat>date'2005-1-1'
          group by mbrid,effdat))t
          
      start with rn=1 and effdat<=date'2005-1-1'  connect by rn>prior rn
           and mbrid=prior mbrid
           and expdat>prior expdat
           and effdat<=prior expdat+2))
           
    group by mbrid
      having max(expdat)>=date'2005-12-31'
        and (min(flag1)>=45 or min(flag1) is null)
      

  17.   

    wildwave的写法很不错,比我开始想到的loop though 365天要好很多。赞一个。性能问题是因为sql用了很多嵌套,我估计mbrid上应该是有索引的,但是这个列传不到最低层的UNION部分,所以导致了全表扫描,这在性能方面是很大的负面影响。我写了一个function,把mbrid传进去,就能使用索引了。最好建立一个mbrid, effdat, expdate的组合索引,那就连表都不用读了。为了保证在2005/2006时间段内根本没有记录的mbrid也能查到,我在UNION里加入了起始和结束时间。CREATE OR REPLACE FUNCTION date_gap_exists (
       p_mbrid        VARCHAR2
     , p_start_date   DATE
     , p_end_date     DATE)
       RETURN VARCHAR2 IS
       w_ret   VARCHAR2 (1);
    BEGIN
       SELECT 'Y'
         INTO w_ret
         FROM (SELECT expdat, expdat - effdat a
                    , effdat - LAG (effdat) OVER (ORDER BY effdat) b
                 FROM (SELECT effdat, LEAD (effdat) OVER (ORDER BY effdat) expdat
                         FROM (SELECT effdat
                                 FROM test_tets
                                WHERE effdat <= p_end_date
                                  AND expdat >= p_start_date
                                  AND mbrid = p_mbrid
                               UNION
                               SELECT expdat
                                 FROM test_tets
                                WHERE effdat <= p_end_date
                                  AND expdat >= p_start_date
                                  AND mbrid = p_mbrid
                               UNION
                               SELECT p_start_date
                                 FROM DUAL
                               UNION
                               SELECT p_end_date
                                 FROM DUAL)) a
                WHERE expdat IS NOT NULL
                  AND NOT EXISTS (
                         SELECT 1
                           FROM test_tets
                          WHERE mbrid = p_mbrid
                            AND a.effdat >= effdat
                            AND a.expdat <= expdat
                            AND effdat <= p_end_date
                            AND expdat >= p_start_date)
                  AND expdat - effdat > 1)
        WHERE (a > 2 OR b < 45);
       RETURN w_ret;
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
          RETURN 'N';
    END date_gap_exists;
    /查询时使用如下,test_mbrid是我建的一个测试表,包含所有mbridSELECT mbrid
         , date_gap_exists (mbrid
                         , TO_DATE ('2005-01-01', 'YYYY-MM-DD')
                         , TO_DATE ('2006-01-01', 'YYYY-MM-DD')) check_date_gap
      FROM test_mbrid;
      

  18.   


    你好,这个函数好像是有问题,当我执行我的测试数据的时候。有这样的一组数据EFFDAT EXPDAT MBRID
    2006-2-16 2006-3-31 -36730
    2006-4-1 2006-8-6 -36730
    2006-11-1 2006-12-31 -36730
    当这个函数执行这个mbrid的时候就报出实际返回的行数超出请求的行数。你看看是怎么回事啊?
      

  19.   

    测试的时间段是 TO_DATE ('2006-01-01','YYYY-MM-DD'),TO_DATE ('2007-01-01','YYYY-MM-DD')
    它为什么返回了两个‘Y’啊? 是什么问题啊?
      

  20.   

    我忘了加上控制“实际返回的行数超出请求的行数“的一行CREATE OR REPLACE FUNCTION date_gap_exists (
       p_mbrid        VARCHAR2
     , p_start_date   DATE
     , p_end_date     DATE)
       RETURN VARCHAR2 IS
       w_ret   VARCHAR2 (1);
    BEGIN
       SELECT 'Y'
         INTO w_ret
         FROM (SELECT expdat, expdat - effdat a
                    , effdat - LAG (effdat) OVER (ORDER BY effdat) b
                 FROM (SELECT effdat, LEAD (effdat) OVER (ORDER BY effdat) expdat
                         FROM (SELECT effdat
                                 FROM test_tets
                                WHERE effdat <= p_end_date
                                  AND expdat >= p_start_date
                                  AND mbrid = p_mbrid
                               UNION
                               SELECT expdat
                                 FROM test_tets
                                WHERE effdat <= p_end_date
                                  AND expdat >= p_start_date
                                  AND mbrid = p_mbrid
                               UNION
                               SELECT p_start_date
                                 FROM DUAL
                               UNION
                               SELECT p_end_date
                                 FROM DUAL)) a
                WHERE expdat IS NOT NULL
                  AND NOT EXISTS (
                         SELECT 1
                           FROM test_tets
                          WHERE mbrid = p_mbrid
                            AND a.effdat >= effdat
                            AND a.expdat <= expdat
                            AND effdat <= p_end_date
                            AND expdat >= p_start_date)
                  AND expdat - effdat > 1)
        WHERE (a > 2 OR b < 45)
          AND rownum = 1;  -- 加上此行避免重复
       RETURN w_ret;
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
          RETURN 'N';
    END date_gap_exists;
    /
      

  21.   

    现在还是有点不对劲啊? 加上rownum = 1 确实是没有错误了
    但是我的测试数据EFFDAT    EXPDAT    MBRID
    2006-2-16    2006-3-31    -36730
    2006-4-1    2006-8-6    -36730
    2006-11-1    2006-12-31    -36730
    在06-07年期间返回的也是Y 就是说经过判断这个也是连续的了……