提取连续6个月电量为0的用户

[封尘-莫若]

数据格式大概是这样，我想取到右边的结果，这个sql该怎么写

[封尘-莫若]

还有跨年的情况，需要日期做转换

[sxq129601]

感觉你这个问题和我10年前问的问题一模一样，参考下吧：https://bbs.csdn.net/topics/310263176

[封尘-莫若]

引用 4 楼 sxq129601 的回复:

感觉你这个问题和我10年前问的问题一模一样，参考下吧：https://bbs.csdn.net/topics/310263176

你那个我之前看了，但是涉及到突然断档的时候就不行了，这中情况不好过滤掉

[nayi_224]

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
;

[AHUA1001]

SELECT * FROM (
SELECT T.用户,T.月份 开始月份,
MONTHS_BETWEEN(TO_DATE(T.TTT,'YYYYMM'),TO_DATE(T.月份,'YYYYMM')) 持续月份 FROM (
SELECT T1.用户,T1.月份,T1.电量,
(SELECT T2.月份 FROM TABLE_NAME T2 WHERE T2.用户=T1.用户 AND T2.月份>T1.月份 AND T2.电量<>0) TTT
FROM TABLE_NAME T1) T WHERE T.电量 = 0) T WHERE 持续月份 = 6
最后的 持续月份 = 6，查询6个月没有交费的。
要是改成持续月份 = 10，就是10个月没有交费的。
没有测试，不知道会不会有问题。

[AHUA1001]

改了一点东西
SELECT * FROM (
SELECT T.用户,T.月份 开始月份,
MONTHS_BETWEEN(TO_DATE(T.TTT,'YYYYMM'),TO_DATE(T.月份,'YYYYMM'))+1 持续月份 FROM (
SELECT T1.用户,T1.月份,T1.电量,
(SELECT T2.月份 FROM TABLE_NAME T2 WHERE T2.用户=T1.用户 AND T2.月份>T1.月份 AND T2.电量<>0) TTT
FROM TABLE_NAME T1) T WHERE T.电量 = 0) T WHERE 持续月份 = 6
最后的 持续月份 = 6，查询6个月没有交费的。
要是改成持续月份 = 10，就是10个月没有交费的。
没有测试，不知道会不会有问题。

[shpery]

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;

[ora_rookie]

这个问题在oracle中使用rownum可以很简单的解决。

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

[ora_rookie]

附上表结构和数据

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');

[ora_rookie]

在oracle中，求连续月份就用rownum； 在连续月分中求中断就用lag over

[gioloe]

with ta as ( select month , nvl(months_between ( to_date(lead(month , 6)over(order by month),'yyyymm')， -6 to_date(month,'yyyymm') ) ,99) mc, sum(fee)over( order by month rows between current row and 6 following ) sum_f -6 from table1 ) select * from ta where mc = 6 and sum_f = 0