Hive SQL 多日留存计算

目录

一、需求

二、问题分析

1.测试数据情况

2.求解分析

三、求解过程

1.数据准备

2.计算过程

四、正确结果与计算结果对比

1.预期结果

2.计算结果

---

一、需求

        留存、留存率是用于反映网站、互联网应用或网络游戏等运营情况的统计指标，因此计算留存、留存率是数据分析及数据工程师是常见的需求。对于求单日的1日、2日、3日、7日等留存指标，相对较为简单；对于分析一段时间内的留存情况，求一段时间内的各日的1日、2日、3日等留存，较为复杂。

二、问题分析

1.测试数据情况

        测试数据包括三个字段，用户id，用户注册日期，用户登陆日期（活跃日期）。测试数据在天粒度已去重。

2.求解分析

留存：注册用户在N日后的活跃（登录）用户数。

计算留存：

        则要以注册日期为分组基础，对用户id进行count（）汇总；

        同时要以留存天数为分组汇总的字段，对用户id进行count（）汇总；

        留存天数 = 用户登陆日期 - 用户注册日期

故需要以用户注册日期、留存天数为分组字段，最终count（用户id）得到各个日期的用户留存情况。

三、求解过程

1.数据准备

-- 1.建表语句
drop table test_retention;
create table test_retention(
    user_id string
    ,regist_time string 
    ,active_time string
)
partitioned by ( dt string )
;


-- 测试数据插入语句
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table test_retention partition (dt)
    select 'uid_1' as user_id,'2021-08-01' as regist_time,'2021-08-01' as active_time,'2021-08-01' as dt
    union all
    select 'uid_2' as user_id,'2021-08-01' as regist_time, '2021-08-01' as active_time,'2021-08-01' as dt
    union all
    select 'uid_3' as user_id,'2021-08-01' as regist_time, '2021-08-01'  as active_time,'2021-08-01' as dt
    union all
    select 'uid_4' as user_id,'2021-08-01' as regist_time, '2021-08-01' as active_time,'2021-08-01' as dt
    union all
    select 'uid_5' as user_id,'202