Hive SQL 多日留存计算

最新推荐文章于 2024-07-28 06:12:33 发布

Heng_bigdate_yan

最新推荐文章于 2024-07-28 06:12:33 发布

阅读量2k

点赞数

CC 4.0 BY-SA版权

文章标签： 1024程序员节 hive sql 大数据 spark

本文链接：https://blog.youkuaiyun.com/Heng_bigdate_yan/article/details/120935637

本文介绍了如何使用Hive SQL解决多日留存计算的问题，详细阐述了需求、问题分析、求解过程以及结果对比，包括数据准备、计算过程，并验证了预期结果与实际计算结果的一致性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、需求

留存、留存率是用于反映网站、互联网应用或网络游戏等运营情况的统计指标，因此计算留存、留存率是数据分析及数据工程师是常见的需求。对于求单日的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 'ui