select
e.electri_quantity_w 电量,
e.electri_fee_month 时间,
max(e.electri_fee_month) over(partition by e.user_id order by e.electri_fee_month rows between current row and 6 following) 最大时间
from be_user b,
where b.user_id = e.user_id
and nvl(e.electri_quantity_w, 0) = 0
) t1
where t1.最大时间 >= t1.时间 + 6我写的,但有漏洞。有些不是连续也查出来了。请达人请教要求连续6个月(e.electri_fee_month) 电量(e.electri_quantity_w)为0的记录,怎么查出。
e.electri_quantity_w 电量,
e.electri_fee_month 时间,
max(e.electri_fee_month) over(partition by e.user_id order by e.electri_fee_month rows between current row and 6 following) 最大时间
from be_user b,
where b.user_id = e.user_id
and nvl(e.electri_quantity_w, 0) = 0
) t1
where t1.最大时间 >= t1.时间 + 6我写的,但有漏洞。有些不是连续也查出来了。请达人请教要求连续6个月(e.electri_fee_month) 电量(e.electri_quantity_w)为0的记录,怎么查出。
select
e.electri_quantity_w 电量,
e.electri_fee_month 时间,
max(e.electri_fee_month) over(partition by e.user_id order by e.electri_fee_month rows between current row and 6 following) 最大时间
from be_user b,
ec_quantity_history e
where b.user_id = e.user_id
and nvl(e.electri_quantity_w, 0) = 0
) t1
where t1.最大时间 >= t1.时间 + 6
之前的没写全
为什么用这个表示六个月?where t1.最大时间 >= t1.时间 + 6
我对分析函数不熟。请赐教谢谢
select b.user_id,b.username,e.ct 持续时间,mi 起始月份,ma 终止月份 from be_user b inner join (
select user_id,count(1) ct,max(electri_fee_month)ma,min(electri_fee_month)mi from(
select a.*,
row_number()over(partition by user_id order by electri_fee_month)rn
from ec_quantity_history a where electri_quantity_w is null or electri_quantity_w=0)
group by user_id,add_months(to_date(electri_fee_month,'YYYY-MM'),-rn)) e
on b.user_id=e.user_id
where e.ct>=6
看看是不是你想要的结果