hivesql数据处理中两个代表性的连续性问题

1.用户连续3天登录

create table tmp_user_login (
userid int,
time date
) ;
insert into tmp_user_login values (1,‘2020-04-18’),(1,‘2020-04-19’),(1,‘2020-04-20’),(1,‘2020-04-21’),(2,‘2020-04-16’),(2,‘2020-04-17’),(2,‘2020-04-18’),(3,‘2020-04-18’),(4,‘2020-04-18’),(5,‘2020-04-18’),(6,‘2020-04-18’),(7,‘2020-04-18’);

select user_id, count(1) cnt
from (
select
t1.user_id
, t1.login_date
, row_number() over(partition by t1.user_id order by t1.login_date asc) rn
from (select user_id,login_date from tmp_user_login group by user_id,login_date) t1
) t
group by t. user_id, date_sub(login_date, t.rn)
having count(1) >= 3;

2.某一个周期内用户连续4次5秒内下单。
create table tmp_user_order
as
select
user_id,
order_time,
order_time-LAG(order_time,1,‘1970-01-01 00:00:00’) OVER(PARTITION BY cookieid ORDER BY createtime) AS timediff<