hive连续登录问题

现在有一张用户消费信息表，求问连续三天登录用户有多少个，用户连续交易的总额、连续登陆天数、连续登陆开始和结束时间、间隔天数：

user_id bigint comment '广告主id',
daystr comment '日期'
price decimal(10,2) comment '消费金额'

数据预处理：

create table user_log_test (user_id int, daystr string, price decimal(10,2));
insert into user_log_test values
(1, "2019-12-08", 24.23), 
(1, "2019-12-08", 27.32), 
(1, "2019-12-09", 5.63), 
(1, "2019-12-09", 67.36), 
(1, "2019-12-10", 5.69), 
(1, "2019-12-12", 69.85), 
(1, "2019-12-13", 43.86), 
(1, "2019-12-14", 38.42),
(1, "2019-12-15", 69.76),
(1, "2019-12-16", 69.76),
(1, "2019-12-18", 95.15),
(1, "2019-12-19", 15.65),
(1, "2019-12-21", 37.71),
(2, "2019-12-08", 24.23), 
(2, "2019-12-08", 27.32), 
(2, "2019-12-09", 5.63), 
(2, "2019-12-09", 67.36), 
(2, "2019-12-10", 5.69), 
(2, "2019-12-12", 69.85), 
(2, "2019-12-13", 43.86), 
(2, "2019-12-14", 43.18),
(2, "2019-12-15", 69.76),
(2, "2019-12-18", 95.15),
(2, "2019-12-19", 15.65),
(2, "2019-12-21", 37.71),
(3, "2019-12-08", 24.23), 
(3, "2019-12-08", 27.32), 
(3, "2019-12-09", 5.63), 
(3, "2019-12-09", 67.36), 
(3, "2019-12-10", 5.69), 
(3, "2019-12-12", 69.85), 
(3, "2019-12-13", 43.86), 
(3, "2019-12-14", 76.81),
(3, "2019-12-15", 69.76),
(3, "2019-12-16", 69.76),
(3, "2019-12-18", 95.15),
(3, "2019-12-19", 15.65),
(3, "2019-12-21", 37.71);

问题分析：
1.每个用户每天可能有多条记录，需进行聚合操作 t1
2.对用户每天的数据t1进行分用户日期的排序，如果当前日期-排名是同一个日期dt_start，那就是连续排序的
3.对dt_start，user_id进行聚合取count(*)就是连续登录的天数

代码编写：

select user_id 
      ,dt_start    -- 连续登陆的开始日期-1
      ,count(*) as days_cnt  -- 连续登陆的开始日期
      ,min(daystr) as start_date -- 连续登陆的开始日期 与dt_start相差一天
      ,max(daystr) as end_date  -- 连续登陆的结束日期
      ,lag(dt_start, 1, dt_start) over (partition by user_id order by dt_start) as lag_start_dt --用户分组对开始日期做排序，默认为当前开始日期，有差异即为非连续登录，有间隔
      ,datediff(dt_start, lag(dt_start, 1, dt_start) over (partition by user_id order by dt_start)) as interval_day -- 间隔多少天没交易
  from 
      (
       select user_id 
             ,daystr 
             ,price 
             ,row_number() over (partition by user_id order by daystr) as rnk   --用户分组排名
             ,date_sub(daystr, row_number() over (partition by user_id order by daystr)) as dt_start   --同一开始日期即为连续登录
         from 
             (
              --聚合数据
              select user_id 
                    ,daystr 
                    ,sum(price) as price 
                from user_log_test
               group by user_id
                    ,daystr
             ) t_base
      ) t_rnk
 group by user_id
      ,dt_start

执行结果