hive中统计用户连续交易的总额、连续登陆天数、连续登陆开始和结束时间、间隔天数

在电商、物流和银行可能经常会遇到这样的需求：统计用户连续交易的总额、连续登陆天数、连续登陆开始和结束时间、间隔天数等，那今天就来聊聊这些需求在hive中的实现方法，注意：我用的hive版本是3.0的版本，低版本可能有些函数或语法不支持会报错。

先创建测试表

create table deal_tb (id int, daystr varchar(10), amount decimal(10,2));

再插入模拟数据

insert into deal_tb values
(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);

注意：每个用户每天可能会有多条记录

所以我们先按用户和日期分组求和，使每个用户每天只有一条数据

select 
 id, daystr, sum(amount) amount
from
 deal_tb
group by
 id, daystr;

OK
id	daystr	amount
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
Time taken: 43.875 seconds, Fetched: 32 row(s)

根据用户ID分组按日期排序，将日期和分组序号相减得到连续登陆的开始日期(from_day)，如果开始日期相同说明连续登陆

-- 根据用户ID分组按日期排序，将日期和分组序号相减得到连续登陆的开始日期，如果开始日期相同说明连续登陆
select
id, daystr, amount, date_sub(daystr, row_number() over(partition by id order by daystr)) from_day -- 日期减去分区排序的序号，如果新日期相等，则连续登陆
from
(
-- 按用户和日期分区求和，使每个用户每天只有一条数据
select 
 id, daystr, sum(amount) amount
from
 deal_tb
group by
 id, daystr
) a;

OK
id	daystr	amount	from_day
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
Time taken: 85.875 seconds, Fetched: 32 row(s)

统计用户连续交易的总额、连续登陆天数、连续登陆开始和结束时间、间隔天数

select
 id, 
 case when count(1) >= 3 then sum(amount) else -100 end sum_amount, -- 连续大于三天的交易总额，不满足的赋值为-100
 min(daystr) start_date, -- 连续登陆的开始时间
 max(daystr) end_date, -- 连续登陆的结束时间
 count(1) continuous_day, -- 连续登陆的天数
 datediff(from_day, lag(from_day, 1, from_day) over(partition by id order by from_day)) interval_day-- 间隔多少天没交易
from
(
-- 根据用户ID分组按日期排序，将日期和分组序号相减得到连续登陆的开始日期，如果开始日期相同说明连续登陆
select
id, daystr, amount, date_sub(daystr, row_number() over(partition by id order by daystr)) from_day -- 日期减去分区排序的序号，如果新日期相等，则连续登陆
from
(
-- 按用户和日期分区求和，使每个用户每天只有一条数据
select 
 id, daystr, sum(amount) amount
from
 deal_tb
group by
 id, daystr
) a
) b
group by
id, from_day;

OK
id	sum_amount	start_date	end_date	continuous_day	interval_day
1	13600.23	2019-02-08	2019-02-10	3	0
1	2991.65	2019-02-12	2019-02-16	5	1
1	-100.00	2019-02-18	2019-02-19	2	1
1	-100.00	2019-02-21	2019-02-21	1	1
2	13600.23	2019-02-08	2019-02-10	3	0
2	3026.65	2019-02-12	2019-02-15	4	1
2	-100.00	2019-02-18	2019-02-19	2	2
2	-100.00	2019-02-21	2019-02-21	1	1
3	13600.23	2019-02-08	2019-02-10	3	0
3	2730.04	2019-02-12	2019-02-16	5	1
3	-100.00	2019-02-18	2019-02-19	2	1
3	-100.00	2019-02-21	2019-02-21	1	1
Time taken: 87.16 seconds, Fetched: 12 row(s)