3,491
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE DL (USR VARCHAR2(10), MON VARCHAR2(20) , DL VARCHAR2(10));
insert into dl (USR, MON, DL)
values ('a', '202001', '0');
insert into dl (USR, MON, DL)
values ('a', '202002', '0');
insert into dl (USR, MON, DL)
values ('a', '202003', '0');
insert into dl (USR, MON, DL)
values ('a', '202004', '0');
insert into dl (USR, MON, DL)
values ('a', '202005', '0');
insert into dl (USR, MON, DL)
values ('a', '202006', '0');
insert into dl (USR, MON, DL)
values ('a', '202008', '0');
insert into dl (USR, MON, DL)
values ('b', '202002', '0');
insert into dl (USR, MON, DL)
values ('b', '202005', '0');
insert into dl (USR, MON, DL)
values ('b', '202006', '0');
insert into dl (USR, MON, DL)
values ('b', '202007', '0');
insert into dl (USR, MON, DL)
values ('b', '202008', '0');
insert into dl (USR, MON, DL)
values ('b', '202009', '0');
insert into dl (USR, MON, DL)
values ('b', '202010', '0');
with a as
(SELECT usr,
add_months(to_date(mon, 'yyyyMM'), -rnum),
count(1) as co,
min(mon) as begin_mon
FROM (SELECT USR, Mon, rownum as rnum
FROM (SELECT USR, MON FROM DL WHERE DL = '0' ORDER BY USR, MON))
group by usr, add_months(to_date(mon, 'yyyyMM'), -rnum))
SELECT usr, begin_mon FROM a WHERE co = 6
with userdata as
(select 'a' username, to_date('202001', 'yyyyMM') monthdate, 0 ele
from dual
union all
select 'a' username, to_date('202002', 'yyyyMM') monthdate, 0 ele
from dual
union all
select 'a' username, to_date('202003', 'yyyyMM') monthdate, 0 ele
from dual
union all
select 'a' username, to_date('202004', 'yyyyMM') monthdate, 0 ele
from dual
union all
select 'a' username, to_date('202005', 'yyyyMM') monthdate, 0 ele
from dual
union all
select 'a' username, to_date('202006', 'yyyyMM') monthdate, 0 ele
from dual
union all
select 'a' username, to_date('202007', 'yyyyMM') monthdate, 2 ele
from dual
union all
select 'a' username, to_date('202008', 'yyyyMM') monthdate, 0 ele
from dual
union all
select 'b' username, to_date('202002', 'yyyyMM') monthdate, 0 ele
from dual
union all
select 'b' username, to_date('202005', 'yyyyMM') monthdate, 0 ele
from dual
union all
select 'b' username, to_date('202006', 'yyyyMM') monthdate, 0 ele
from dual
union all
select 'b' username, to_date('202007', 'yyyyMM') monthdate, 0 ele
from dual
union all
select 'b' username, to_date('202008', 'yyyyMM') monthdate, 0 ele
from dual
union all
select 'b' username, to_date('202009', 'yyyyMM') monthdate, 0 ele
from dual
union all
select 'b' username, to_date('202010', 'yyyyMM') monthdate, 0 ele
from dual
union all
select 'c' username, to_date('202008', 'yyyyMM') monthdate, 0 ele
from dual
union all
select 'c' username, to_date('202009', 'yyyyMM') monthdate, 0 ele
from dual
union all
select 'c' username, to_date('202010', 'yyyyMM') monthdate, 0 ele
from dual
union all
select 'c' username, to_date('202011', 'yyyyMM') monthdate, 0 ele
from dual
union all
select 'c' username, to_date('202012', 'yyyyMM') monthdate, 0 ele
from dual
union all
select 'c' username, to_date('202101', 'yyyyMM') monthdate, 0 ele
from dual
)
select username 用户,
to_char(min(monthdate), 'yyyyMM') 开始月份,
to_char(max(monthdate), 'yyyyMM') 结束月份,
months_between(max(monthdate),min(monthdate))+1 持续月份
from userdata t
start with not exists (select 1
from userdata
where username = t.username
and monthdate = add_months(t.monthdate, -1)
and ele = 0)
connect by prior username = username
and prior monthdate = add_months(monthdate, -1)
and ele = 0
group by username, connect_by_root monthdate
having count (1) = 6
order by 1, 2;
with tab1 as (
select 'a' a, to_date('201912', 'yyyymm') dat, 0 dd from dual
union all select 'a' a, to_date('202001', 'yyyymm') dat, 0 c from dual
union all select 'a' a, to_date('202002', 'yyyymm') dat, 0 c from dual
union all select 'a' a, to_date('202003', 'yyyymm') dat, 0 c from dual
union all select 'a' a, to_date('202004', 'yyyymm') dat, 0 c from dual
union all select 'a' a, to_date('202005', 'yyyymm') dat, 0 c from dual
union all select 'a' a, to_date('202008', 'yyyymm') dat, 0 c from dual
union all select 'a' a, to_date('202009', 'yyyymm') dat, 0 c from dual
union all select 'b' a, to_date('202008', 'yyyymm') dat, 0 c from dual
union all select 'b' a, to_date('202009', 'yyyymm') dat, 0 c from dual
)
, tab2 as (
select t1.*,
case when lag(t1.dat) over(partition by t1.a order by t1.dat) = add_months(t1.dat, -1) then 0 else 1 end lg
from tab1 t1
where t1.dd = 0
)
, tab3 as (
select t1.*,
sum(t1.lg) over(partition by t1.a order by t1.dat) sm
from tab2 t1
)
select t1.a,
min(t1.dat)
from tab3 t1
group by t1.a, t1.sm
having count(1) = 6
;