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
版权声明：本文为博主原创文章，转载请附上博文链接！
