HiveSQL题——用户留存数

吵吵叭火

已于 2024-08-08 18:04:20 修改

阅读量687

点赞数 14

分类专栏： # HIve SQL 文章标签：大数据

于 2024-07-28 06:12:33 首次发布

本文链接：https://blog.csdn.net/SHWAITME/article/details/140745550

版权

HIve SQL 专栏收录该内容

25 篇文章 11 订阅

订阅专栏

一、用户留存数

电商业务中需要计算用户留存情况，在时间维度上可以分为用户次日，N日后的留存情况，用留存率表示。留存率计算方式如下：

次日留存率= 基准日新增用户在次日登陆的数量 / 基准日新用户注册数量
三日留存率= 基准日新增用户在第四日登陆的数量 / 基准日新用户注册数量
七日留存率= 基准日新增用户在第八日登陆的数量 / 基准日新用户注册数量

0 问题描述

计算基准日的次日、3日、7日的用户留存率

1 数据准备

创建用户登录表

create table if not exists uid_login
(
    user_id      int comment '用户id',
    login_time   string comment '用户登录时间'
)
    comment '用户登录表（活跃表）';

truncate table uid_login;
INSERT overwrite table uid_login
VALUES 
(1, '2020-01-02 00:02:00'),
(2, '2020-01-02 00:02:00'),
(3, '2020-01-02 00:02:00'),
(1, '2020-01-03 00:02:00'),
(3, '2020-01-03 00:02:00'),
(4, '2020-01-03 00:02:00'),
(6, '2020-01-03 00:02:00'),
(2, '2020-01-04 00:02:00'),
(3, '2020-01-04 00:02:00'),
(5, '2020-01-04 00:02:00'),
(6, '2020-01-04 00:02:00'),
(7, '2020-01-04 00:02:00'),
(1, '2020-01-05 00:02:00'),
(6, '2020-01-05 00:02:00'),
(7, '2020-01-05 00:02:00'),
(4, '2020-01-06 00:02:00'),
(1, '2020-01-07 00:02:00'),
(2, '2020-01-07 00:02:00'),
(6, '2020-01-07 00:02:00'),
(2, '2020-01-08 00:02:00'),
(3, '2020-01-09 00:02:00');

创建用户注册表

create table if not exists uid_reg
(
    user_id      int comment '用户id',
    register_time   string comment '用户注册时间'
)
    comment '用户注册表';

truncate table uid_reg;
INSERT overwrite table uid_reg
VALUES
(1, '2020-01-01 00:01:00'),
(2, '2020-01-01 00:01:00'),
(3, '2020-01-01 00:01:00'),
(4, '2020-01-02 00:01:00'),
(5, '2020-01-03 00:01:00'),
(6, '2020-01-03 00:01:00'),
(7, '2020-01-02 00:01:00');

2 数据分析

with  tmp1 as (
    select  r.user_id as uid ,
        -- 用户注册日期
        date_format(r.register_time,'yyyy-MM-dd') as rt,
        -- 用户登录（活跃）日期
        date_format(l.login_time,'yyyy-MM-dd') as lt,
        datediff( date_format(l.login_time,'yyyy-MM-dd'),date_format(r.register_time,'yyyy-MM-dd')) as  tdiff
from uid_reg  r
left join uid_login as  l
on r.user_id =  l.user_id
where  date_format(l.login_time,'yyyy-MM-dd')
between date_add(date_format(r.register_time,'yyyy-MM-dd'),1 )
and   date_add(date_format(r.register_time,'yyyy-MM-dd'),7 )

)

select rt,
    -- 一般注册表中，每个用户对应一条记录，以防万一，用distinct进行去重
       -- 基准日新增用户数
       count(distinct uid) as cnt ,
       -- 基准日的次日留存率
       count(distinct case when tdiff=1 then uid else null end)/count(distinct uid) as lst1date_rate,
       -- 基准日的3日留存率
       count(distinct case when tdiff=3 then uid else null end)/count(distinct uid) as lst3date_rate,
       --基准日的7日留存率
       count(distinct case when tdiff=7 then uid else null end)/count(distinct uid) as lst7date_rate
from tmp1
group by rt;