现在有一张用户消费信息表，求问连续三天登录用户有多少个，用户连续交易的总额、连续登陆天数、连续登陆开始和结束时间、间隔天数：
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
执行结果
