【Hive】连续几日的计算逻辑

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(partition by emp_no order by busi_date) as raw_rn --原始排序
    -- 生成测试数据			
		from(
			select 
			'a' as emp_no,
			(random()*1000)::decimal(20,2) as sale,
			generate_series('2019-01-01', '2019-01-05'::date, '1 day')::date as busi_date
			union all 
			select 
			'b' as emp_no,
			(random()*1000)::decimal(20,2) as sale,
			generate_series('2019-01-01', '2019-01-05'::date, '1 day')::date as busi_date
		) as sale_table
	) as t1
) as t2
order by emp_no, busi_date