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

在电商、物流和银行可能经常会遇到这样的需求：统计用户连续交易的总额、连续登陆天数、连续登陆开始和结束时间、间隔天数等，那今天就来聊聊这些需求在hive中的实现方法

先创建测试表

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

 

1再插入模拟数据

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
);

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)

 

---------------------
作者：浮云6363
来源：CSDN
原文：https://blog.csdn.net/lz6363/article/details/87209532
版权声明：本文为博主原创文章，转载请附上博文链接！

转载于:https://www.cnblogs.com/kingyoung/p/10469187.html