Hive SQL面试题-流失回流用户数统计-Toy模板网

这篇具有很好参考价值的文章主要介绍了Hive SQL面试题-流失回流用户数统计。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

流失回流用户数统计

根据用户最后一次登录记录表，统计每天的流失（一段时间未登录平台）用户数量，和回流用户（一段时间未登录平台，但今天重新登录了平台）数量。
执行环境：Hive on Spark

1 统计指标

从用户最后一次登录记录表中统计如下指标，当日流失用户数量、当日回流用户数量

统计周期	指标	说明
最近1日	流失用户数	之前活跃过的用户，最近一段时间未活跃，就称为流失用户。此处要求统计7日前（只包含7日前当天）活跃，但最近7日未活跃的用户总数。
最近1日	回流用户数	之前的活跃用户，一段时间未活跃（流失），今日又活跃了，就称为回流用户。此处要求统计回流用户总数。

2 测试数据

建表

-- user_id: 用户 id
-- last_login_date: 用户上次登录日期
-- dt: 日期分区
create table user_login_log (
    user_id string,
    last_login_date string,
    dt string
);

插入数据

-- 1001 每天登录，普通用户
-- 1002 回流用户，一段时间未登录，然后 2020-06-14 又活跃了
-- 1003 流失用户，从 2020-06-07 之后就没有登录过了
insert into user_login_log values 
('1001', '2020-06-13', '2020-06-13'),
('1001', '2020-06-14', '2020-06-14'),
('1002', '2020-06-01', '2020-06-13'),
('1002', '2020-06-14', '2020-06-14'),
('1003', '2020-06-07', '2020-06-13'),
('1003', '2020-06-07', '2020-06-14');

数据样例

select * from user_login_log;

user_id	last_login_date	dt
1001	2020-06-13	2020-06-13
1002	2020-06-01	2020-06-13
1003	2020-06-07	2020-06-13
1001	2020-06-14	2020-06-14
1002	2020-06-14	2020-06-14
1003	2020-06-07	2020-06-14

3 实现思路

3.1 流失用户

统计当天（2020-06-14）的流失用户，此类用户特征是上次登录日期距当天日期已经达到 7 天

select '2020-06-14' as dt,
    count(*) as user_lost_count
from user_login_log where dt = '2020-06-14'
    and last_login_date = date_sub('2020-06-14', 7);

3.2 回流用户

统计当天（2020-06-14）的回流用户，此类用户的特征为当天（2020-06-14）登陆了平台，同时昨天的还属于流失用户，即前一天（2020-06-13）的最后登录日期在7天之前文章来源地址https://www.toymoban.com/news/detail-403998.html

取出 2020-06-14 登录的用户

select user_id from user_login_log 
where dt = '2020-06-14' 
and last_login_date = '2020-06-14';

取出 2020-06-13 还属于流失用户的用户

-- 此处 last_login_date <= date_sub('2020-06-14', 8) 
-- 表示前一天 date_sub('2020-06-14', 1) 处在流失状态的用户
select user_id from user_login_log
where dt = date_sub('2020-06-14', 1) 
and last_login_date <= date_sub('2020-06-14', 8);

将两份数据 join 起来获得流失用户数

with t1 as (
    select user_id from user_login_log 
    where dt = '2020-06-14' 
    and last_login_date = '2020-06-14'
),t2 as (
    select user_id from user_login_log
    where dt = date_sub('2020-06-14', 1) 
    and last_login_date <= date_sub('2020-06-14', 8)
) select '2020-06-14',count(distinct t1.user_id) 
from t1 inner join t2 on t1.user_id = t2.user_id;

到了这里，关于Hive SQL面试题-流失回流用户数统计的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！