遇到一个比较有难度的问题

Question

案例
============================================
select * from t1;        C1 C2
---------- --------
      1001 20091130
      1001 20091231
      1001 20100131
      1001 20100331
      1001 20100630
      1001 20100731
      1001 20100831
      1001 20100930
      1001 20101031
      1001 20101130
      1001 20101231
      1002 20091130
      1002 20091231
      1002 20100231
===================================
要求取出按照c1字段分组,每个分组最长连续月数
即
c1      最长连续月数
1001         7
1002         2---------------------------------------------------
本人水平有限只能写成这样select c1,rn,c2
from (
select 
c1,
c2,
rank() over(partition by c1 order by c2) rn 
from t1
)
model
partition by (c1)
dimension by (rn)
measures(c2)
(
c2[for rn from 1 to 11 increment 1]=case 
when
months_between(to_date(substr(c2[cv()+1],0,6),'yyyymm'),to_date(substr(c2[cv()],0,6),'yyyymm')) =1 then
1
end
);
        C1         RN C2
---------- ---------- --------
      1001          1 1
      1001          2 1
      1001          3
      1001          4
      1001          5 1
      1001          6 1
      1001          7 1
      1001          8 1
      1001          9 1
      1001         10 1
      1001         11
      1002          1 1
      1002          2
      1002          3
      1002          4
      1002          5
      1002          6
      1002          7
      1002          8
      1002          9
      1002         10
      1002         11求高人指点

Answer 1

with t as(
select '1001' c1,to_date('20091130','yyyymmdd') c2 from dual
union all
select '1001',to_date('20091231','yyyymmdd') from dual
union all
select '1001',to_date('20100131','yyyymmdd') from dual
union all
select '1001',to_date('20100331','yyyymmdd') from dual
union all
select '1001',to_date('20100630','yyyymmdd') from dual
union all
select '1001',to_date('20100731','yyyymmdd') from dual
union all
select '1001',to_date('20100831','yyyymmdd') from dual
union all
select '1001',to_date('20100930','yyyymmdd') from dual
union all
select '1001',to_date('20101031','yyyymmdd') from dual
union all
select '1001',to_date('20101130','yyyymmdd') from dual
union all
select '1001',to_date('20101231','yyyymmdd') from dual
union all
select '1002',to_date('20091130','yyyymmdd') from dual
union all
select '1002',to_date('20091231','yyyymmdd') from dual
union all
select '1002',to_date('20100228','yyyymmdd') from dual
)
select c1, max(c)
  from (select c1, c3, count(1) c
          from (select c1,
                       to_char(add_months(c2,
                                          -row_number()
                                          over(partition by c1 order by c2)),
                               'yyyymm') c3
                  from t)
         group by c1, c3)
 group by c1
C1       MAX(C)
---- ----------
1001          7
1002          2

Answer 2

附上我测试修改后的sql语句,比牛人繁杂了点但容易理解一点
select c1,max(c5) from(
select c1,c4,count(1) c5 from ( 
select c1,to_char(add_months(to_date(substr(c2,0,6),'yyyymm'),-c3),'yyyymmdd') c4,c2,c3 from (
select c1,c2,row_number() over(partition by c1 order by c2) c3 from t1))
group by c1,c4)
group by c1;
多谢曾钦龙指点