连续登陆问题案例
在电商、物流和银行可能经常会遇到这样的需求：统计用户连续交易的总额、连续登陆天数、连续登陆开始和结束时间、间隔天数等
数据：
注意：每个用户每天可能会有多条记录
amount表示金额
id datestr amount
1,2019-02-08,6214.23
1,2019-02-08,6247.32
1,2019-02-09,85.63
1,2019-02-09,967.36
1,2019-02-10,85.69
1,2019-02-12,769.85
1,2019-02-13,943.86
1,2019-02-14,538.42
1,2019-02-15,369.76
1,2019-02-16,369.76
1,2019-02-18,795.15
1,2019-02-19,715.65
1,2019-02-21,537.71
2,2019-02-08,6214.23
2,2019-02-08,6247.32
2,2019-02-09,85.63
2,2019-02-09,967.36
2,2019-02-10,85.69
2,2019-02-12,769.85
2,2019-02-13,943.86
2,2019-02-14,943.18
2,2019-02-15,369.76
2,2019-02-18,795.15
2,2019-02-19,715.65
2,2019-02-21,537.71
3,2019-02-08,6214.23
3,2019-02-08,6247.32
3,2019-02-09,85.63
3,2019-02-09,967.36
3,2019-02-10,85.69
3,2019-02-12,769.85
3,2019-02-13,943.86
3,2019-02-14,276.81
3,2019-02-15,369.76
3,2019-02-16,369.76
3,2019-02-18,795.15
3,2019-02-19,715.65
3,2019-02-21,537.71
建表语句
create table deal_tb(
id string
,datestr string
,amount string
)row format delimited fields terminated by ',';
计算逻辑
-
统计连续交易，日期不能重复
先按用户和日期分组求和，使每个用户每天只有一条数据
select id
,datestr
,sum(amount) as sum_amount
from deal_tb
group by id,datestr;
//执行结果
1 2019-02-08 12461.55
1 2019-02-09 1052.99
1 2019-02-10 85.69
1 2019-02-12 769.85
1 2019-02-13 943.86
1 2019-02-14 538.42
1 2019-02-15 369.76
1 2019-02-16 369.76
1 2019-02-18 795.15
1 2019-02-19 715.65
1 2019-02-21 537.71
2 2019-02-08 12461.55
2 2019-02-09 1052.99
2 2019-02-10 85.69
2 2019-02-12 769.85
2 2019-02-13 943.86
2 2019-02-14 943.18
2 2019-02-15 369.76
2 2019-02-18 795.15
2 2019-02-19 715.65
2 2019-02-21 537.71
3 2019-02-08 12461.55
3 2019-02-09 1052.99
3 2019-02-10 85.69
3 2019-02-12 769.85
3 2019-02-13 943.86
3 2019-02-14 276.81
3 2019-02-15 369.76
3 2019-02-16 369.76
3 2019-02-18 795.15
3 2019-02-19 715.65
3 2019-02-21 537.71
-
想要求连续交易总额，日期要转换为一样的，才能进行分组求和
根据用户ID分组，使用row_number()给日期编号，从上到下1、2、3...，
select t1.id ,t1.datestr ,t1.sum_amount ,row_number() over(partition by id order by datestr) as rn from ( select id ,datestr ,sum(amount) as sum_amount from deal_tb group by id,datestr ) t1； //执行结果 1 2019-02-08 12461.55 1 1 2019-02-09 1052.99 2 1 2019-02-10 85.69 3 1 2019-02-12 769.85 4 1 2019-02-13 943.86 5 1 2019-02-14 538.42 6 1 2019-02-15 369.76 7 1 2019-02-16 369.76 8 1 2019-02-18 795.15 9 1 2019-02-19 715.65 10 1 2019-02-21 537.71 11 2 2019-02-08 12461.55 1 2 2019-02-09 1052.99 2 2 2019-02-10 85.69 3 2 2019-02-12 769.85 4 2 2019-02-13 943.86 5 2 2019-02-14 943.18 6 2 2019-02-15 369.76 7 2 2019-02-18 795.15 8 2 2019-02-19 715.65 9 2 2019-02-21 537.71 10 3 2019-02-08 12461.55 1 3 2019-02-09 1052.99 2 3 2019-02-10 85.69 3 3 2019-02-12 769.85 4 3 2019-02-13 943.86 5 3 2019-02-14 276.81 6 3 2019-02-15 369.76 7 3 2019-02-16 369.76 8 3 2019-02-18 795.15 9 3 2019-02-19 715.65 10 3 2019-02-21 537.71 11 -
如果是在同一个连续登录的区间，用日期减去编号，那么结果都一样
日期减少函数：data_sun(String_startdate,int_days) select tt1.id ,tt1.datestr ,tt1.sum_amount ,date_sub(tt1.datestr,rn) as grp from( select t1.id ,t1.datestr ,t1.sum_amount ,row_number() over(partition by id order by datestr) as rn from ( select id ,datestr ,sum(amount) as sum_amount from deal_tb group by id,datestr ) t1 ) tt1; //执行结果 1 2019-02-08 12461.55 2019-02-07 1 2019-02-09 1052.99 2019-02-07 1 2019-02-10 85.69 2019-02-07 1 2019-02-12 769.85 2019-02-08 1 2019-02-13 943.86 2019-02-08 1 2019-02-14 538.42 2019-02-08 1 2019-02-15 369.76 2019-02-08 1 2019-02-16 369.76 2019-02-08 1 2019-02-18 795.15 2019-02-09 1 2019-02-19 715.65 2019-02-09 1 2019-02-21 537.71 2019-02-10 2 2019-02-08 12461.55 2019-02-07 2 2019-02-09 1052.99 2019-02-07 2 2019-02-10 85.69 2019-02-07 2 2019-02-12 769.85 2019-02-08 2 2019-02-13 943.86 2019-02-08 2 2019-02-14 943.18 2019-02-08 2 2019-02-15 369.76 2019-02-08 2 2019-02-18 795.15 2019-02-10 2 2019-02-19 715.65 2019-02-10 2 2019-02-21 537.71 2019-02-11 3 2019-02-08 12461.55 2019-02-07 3 2019-02-09 1052.99 2019-02-07 3 2019-02-10 85.69 2019-02-07 3 2019-02-12 769.85 2019-02-08 3 2019-02-13 943.86 2019-02-08 3 2019-02-14 276.81 2019-02-08 3 2019-02-15 369.76 2019-02-08 3 2019-02-16 369.76 2019-02-08 3 2019-02-18 795.15 2019-02-09 3 2019-02-19 715.65 2019-02-09 3 2019-02-21 537.71 2019-02-10 //由结果可以看出来，在连续登录区间日期相等 -
根据上面已完成的数据，统计用户连续交易的总额、连续登陆天数、连续登陆开始和结束时间、间隔天数
连续交易总额
round(sum(ttt1.sum_amount),2) as sum_amount //round()取两位小数连续登录天数
count(ttt1.datestr) as days连续登录开始、结束时间
min(ttt1.datestr) as start_date max(ttt1.datestr) as end_date间隔天数=下一条的开始时间—上一条的结束时间
上一条的结束时间，需要用到窗口函数lag() lag(max(ttt1.datestr),1) over (partition by ttt1.id order by ttt1.grp) 间隔天数，需要使用到日期函数datediff() datediff(min(ttt1.datestr),lag(max(ttt1.datestr),1) over (partition by ttt1.id order by ttt1.grp)) as interval_days最终代码
select ttt1.id
,ttt1.grp
,round(sum(ttt1.sum_amount),2) as sum_amount
,count(ttt1.datestr) as days
,min(ttt1.datestr) as start_date
,max(ttt1.datestr) as end_date
,datediff(min(ttt1.datestr),lag(max(ttt1.datestr),1) over (partition by ttt1.id order by ttt1.grp))
from(
select tt1.id
,tt1.datestr
,tt1.sum_amount
,date_sub(tt1.datestr,rn) as grp
from(
select t1.id
,t1.datestr
,t1.sum_amount
,row_number() over(partition by id order by datestr) as rn
from (
select id
,datestr
,sum(amount) as sum_amount
from deal_tb
group by id,datestr
) t1
) tt1
) ttt1 group by ttt1.id,ttt1.grp
;
- 最终结果
# id 连续登录日期 连续交易总额 连续登录天数 开始时间 结束时间 间隔天数
1 2019-02-07 13600.23 3 2019-02-08 2019-02-10 NULL
1 2019-02-08 2991.65 5 2019-02-12 2019-02-16 2
1 2019-02-09 1510.8 2 2019-02-18 2019-02-19 2
1 2019-02-10 537.71 1 2019-02-21 2019-02-21 2
2 2019-02-07 13600.23 3 2019-02-08 2019-02-10 NULL
2 2019-02-08 3026.65 4 2019-02-12 2019-02-15 2
2 2019-02-10 1510.8 2 2019-02-18 2019-02-19 3
2 2019-02-11 537.71 1 2019-02-21 2019-02-21 2
3 2019-02-07 13600.23 3 2019-02-08 2019-02-10 NULL
3 2019-02-08 2730.04 5 2019-02-12 2019-02-16 2
3 2019-02-09 1510.8 2 2019-02-18 2019-02-19 2
3 2019-02-10 537.71 1 2019-02-21 2019-02-21 2