求一个某字段连续重复的条数的sql写法

hbb239

引用有位仁兄的数据，问下大虾们


表结构和数据如下
要求：每个电话号码（tel_no），按照时间顺序(call_date_time)，呼叫结果是01(call_result ='01')的连续重复的最大次数。
create table test
(
    tel_no                         varchar2(20) not null,
    call_date_time                 varchar2(20) not null,
    call_result                    varchar2(20) not null
)
/
insert into test values('1300000000', '2008010101', '01');
insert into test values('1300000000', '2008010103', '02');
insert into test values('1300000000', '2008010104', '01');
insert into test values('1300000000', '2008010106', '01');
insert into test values('1300000000', '2008010107', '09');
insert into test values('1333333333', '2008010101', '01');
insert into test values('1333333333', '2008010102', '01');
insert into test values('1333333333', '2008010103', '02');
insert into test values('1333333333', '2008010104', '01');
insert into test values('1333333333', '2008010105', '01');
insert into test values('1333333333', '2008010105', '01');
insert into test values('1333333333', '2008010106', '01');
insert into test values('1333333333', '2008010107', '09');
commit;

13000000000 连续重复出现1回，显示1
13333333333 连续重复出现2回，一回重复2次，一回重复4次，所以显示2
要求结果：
13000000000   1
13333333333   2

要是求连续重复次数超过3次的回数呢？
要求结果：
13333333333    1

[ 本帖最后由 hbb239 于 2010-3-31 16:53 编辑 ]

homeworld80

跌跌撞撞写了一个，期待更好的代码
with a as(select t.*,decode(t.call_result,lag(t.call_result) over(partition by t.tel_no order by t.call_date_time),rownum) c1
from test t),
b as(select a.*,connect_by_isleaf lf from a where c1 is not null connect by c1 = prior c1+1 )
select tel_no,count(distinct c1) from b where lf = 1 group by tel_no

bbls1983

重复的最大次数。
13000000000 连续重复的只有1条，显示1
13333333333 有两条重复记录，一个是重复2次，一个重复4次 显示2
要求结果：
13000000000   1
13333333333   2
怎么和需求不一样啊？我理解错了？

hbb239

原帖由 homeworld80 于 2010-3-31 16:10 发表
跌跌撞撞写了一个，期待更好的代码
with a as(select t.*,decode(t.call_result,lag(t.call_result) over(partition by t.tel_no order by t.call_date_time),rownum) c1
from test t),
b as(select a.*,connect_by_isleaf lf from a where c1 is not null connect by c1 = prior c1+1 )
select tel_no,count(distinct c1) from b where lf = 1 group by tel_no

牛人，正确～！

hbb239

要是求重复超过4次的回数呢？
也就是说结果只显示结果
13333333333   1

nyfor

重复超过2次的也可这么写:
select tel_no, count(0) cnt
  from (select tel_no, call_result,
               lag(call_result) over(partition by tel_no order by call_date_time) r1,
               lead(call_result) over(partition by tel_no order by call_date_time) r2
          from test)
where call_result = '01'
   and nvl(r1, '02') = '02'
   and r2 = '01'
group by tel_no;

hbb239

原帖由 nyfor 于 2010-3-31 16:53 发表
重复超过2次的也可这么写:
select tel_no, count(0) cnt
  from (select tel_no, call_result,
               lag(call_result) over(partition by tel_no order by call_date_time) r1,
               lead(call_result) over(partition by tel_no order by call_date_time) r2
          from test)
where call_result = '01'
   and nvl(r1, '02') = '02'
   and r2 = '01'
group by tel_no;

也对   不过我要重复超过3次的回数呢？

nyfor

重复超过4次的回数,利用上面的笨办法可以写成:
select tel_no, count(0) cnt
  from (select tel_no, call_result,
               lag(call_result) over(partition by tel_no order by call_date_time) r1,
               lead(call_result,1) over(partition by tel_no order by call_date_time) r2,
               lead(call_result,2) over(partition by tel_no order by call_date_time) r3,
               lead(call_result,3) over(partition by tel_no order by call_date_time) r4
          from test)
where call_result = '01'
   and nvl(r1, '02') = '02'
   and r2 = '01' and r3 = '01' and r4 = '01'
group by tel_no;

hbb239

那我要写出重复超过20次的回数不是一大串？
期待更好的办法

nyfor

通用的:
select tel_no, count(times) cnt
  from (select tel_no, r2,
                rn + 1 - lag(rn) over(partition by tel_no order by rn) times
           from (select tel_no, call_result,
                         row_number() over(partition by tel_no order by call_date_time) rn,
                         lag(call_result) over(partition by tel_no order by call_date_time) r1,
                         lead(call_result) over(partition by tel_no order by call_date_time) r2
                    from test)
          where (nvl(r1, '*') = '01' and call_result = '01' and nvl(r2, '*') <> '01')
             or (nvl(r1, '*') <> '01' and call_result = '01' and nvl(r2, '*') = '01'))
where nvl(r2, '*') <> '01' and times >= 4 /* 次数 */
group by tel_no;