遇到一个比较有难度的问题
案例
============================================
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
求高人指点
[最优解释]
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
group by c1,c4)
group by c1;
多谢曾钦龙指点
