1.问题
计算员工日销量大于500元的连续天数。
2.解决
2.1 思路
step1:对全量数据排序,得到序号rn1;
step2:对满足条件的数据排序,得到序号rn2;
step3:求两个序号的差值,即rn1-rn2(若为连续,则为等差,且差值相等);
step4:group by 序号的差值,得到连续天数;
2.2 代码
select
emp_no,
sale,
busi_date,
flag,
-- 计算连续天数
count(rn_diff)over(partition by emp_no, flag, rn_diff) as days
from(
select
emp_no,
sale,
busi_date,
flag,
-- 对sale满足条件的重新排序,并求其与原始排序的差值(若为连续,两次排序之间的差值应该相等)
case when flag=1 then rn1 - row_number()over(partition by emp_no, flag order by busi_date) else null end as rn_diff
from(
select
emp_no,
sale,
busi_date,
case when sale>500 then 1 else 0 end as flag, --条件:sale>500的连续天数
row_number() over(pa