Hive SQL 案例：用户留存数据
提示
Hive SQL 教程 欢迎使用。提供建议、纠错、催更等加作者微信: gairuo123（备注：sql ）和关注公众号「盖若」ID: gairuo。跟作者学习，请进入 Python学习课程。欢迎关注作者出版的书籍：《深入浅出Pandas》 和 《Python之光》。
用户的留存率可以帮助我们知道产品对于用的粘性。如果留存高就说明我们的产品对于用户是非常有价值的，我们可以从其他方面进行分析以促进用户的消费等行为。
对电商来说用户的留存行为可以分为访问和购买，我们最终是促进用户的购买留存从而提升 LTV，当然其他的一些如打卡、关注、刷礼物等行为也可以分析其留存。
需求
如以下源数据，以天为单，跨天后再来访问为留存，需要知道需要知道指定日期后隔1-14天的留存，如 2020-08-01 天来的访客，之后 1 天、2天、3天...再来人数。
源数据
我们以访问留存为例，数据表位于 dwd.pv_log，以下为其中涉及的部分：
| uuid | ctime | p_day |
|---|---|---|
| 562234 | 2020-05-08 11:09:19.0 | 20200508 |
| 783425 | 2020-05-08 12:09:19.0 | 20200508 |
| 342643 | 2020-05-08 13:09:19.0 | 20200508 |
| 783425 | 2020-05-08 09:09:19.0 | 20200508 |
各字段说明：
- p_day：hive 库以日期分片，下单日期
- uuid：用户访问的设备编号，可能重复，因为用户会访问多次
- ctime：访问浏览时间
思路分析：
- 每个 uuid 和日期去重，形成一个用户一天浏览只有一条数据
- 将上边的数据查询两份，然后左连接起来然后相减，日期差就是间隔天数，用户去重就是人数
编写步骤
先每天去重用户：
SELECT distinct from_unixtime(unix_timestamp(cast(p_day AS string),'yyyyMMdd'),'yyyy-MM-dd') AS f_day,
uid
FROM dwd.order_detail
WHERE p_day >= 20200801
AND p_day <= 20200815
以上两个同样的表拼接后用日期分组并计算日期差值：
SELECT t0.f_day AS day,
datediff(tn.n_day, t0.f_day) AS gap,
count(DISTINCT t0.uid) AS users
FROM
tables -- 两个表拼接
GROUP BY t0.f_day,
tn.n_day
最终加工出数据。
最终代码
SELECT t0.f_day AS day,
datediff(tn.n_day, t0.f_day) AS gap,
count(DISTINCT t0.uid) AS users
FROM
(SELECT distinct from_unixtime(unix_timestamp(cast(p_day AS string),'yyyyMMdd'),'yyyy-MM-dd') AS f_day,
uid
FROM dwd.order_detail
WHERE p_day >= 20200801
AND p_day <= 20200815) AS t0
LEFT JOIN
(SELECT distinct from_unixtime(unix_timestamp(cast(p_day AS string),'yyyyMMdd'),'yyyy-MM-dd') AS n_day,
uid
FROM dwd.order_detail
WHERE p_day >= 20200801
AND p_day <= 20200815) AS tn ON t0.uid = tn.uid
WHERE tn.n_day >= t0.f_day
AND datediff(tn.n_day, t0.f_day) < 15 -- 保险起见加个限制
GROUP BY t0.f_day,
tn.n_day
得到的数据结构及片断为（day日隔gap天的users数）：
| day | gap | users |
|---|---|---|
| 20200801 | 0 | 3243 |
| 20200801 | 2 | 322 |
| 20200801 | 3 | 32 |
| 20200801 | 14 | 3 |
| 20200802 | 1 | 3324 |
| 20200805 | 5 | 334 |
接下来，可以使用 Excel 透视表成最终的格式：
| day | 0 | 1 | 2 | ... |
|---|---|---|---|---|
| 20200801 | 3243 | 213 | 22 | ... |
| 20200802 | 3442 | 654 | 34 | ... |
| 20200803 | 24355 | 362 | 545 | ... |
也可以用 Python 的 Pandas 库的 数据透视 功能来实现：
df.pivot(index='day', columns='gap', values='users')
上表每行为对应日期隔一定天数的用户数，可以让1-14天的人数除以 0 日（当天）的人数产生留存率。
总结
这里的核心点是掌握 datediff 和 from_unixtime 等时间转化函数的使用方法。
更新时间：2021-03-04 10:28:13 标签：hive sql 留存
