流失回流用户数统计
根据用户最后一次登录记录表,统计每天的流失(一段时间未登录平台)用户数量,和回流用户(一段时间未登录平台,但今天重新登录了平台)数量。
执行环境: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 天文章来源:https://www.toymoban.com/news/detail-403998.html
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模板网!