在电商、物流和银行可能经常会遇到这样的需求:统计用户连续交易的总额、连续登陆天数、连续登陆开始和结束时间、间隔天数等,那今天就来聊聊这些需求在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)