本文循序渐进的讲述了大数据领域
用户分析不断优化的过程。借此抛砖引玉，希望读者勇于探索，追求卓越
最近小c同学一直在做用户留存分析。
这天他跟我大倒苦水：接手前同事的留存分析代码不仅可读性差且效率低。
对于他这个追求简单高效的精致程序员来说无疑是无法接受的。
其实前同事的代码不足为怪。
很多人刚开始进行留存分析的时候都是一不小心写出来的代码就成百上千行且效率低，老兵亦然。
那有办法解决小c现在的苦恼吗？嘿嘿，老兵心中暗笑他算是找对人了。
1 留存定义
数据领域的人知道留存分析是最直观的反应用户活跃/参与程度的分析模型之一。
通过分析初始行为的用户中，有多少人会进行后续行为。这是用来衡量产品对用户价值高低的重要方法。
留存分析可以帮助回答以下问题：
-
新用户/老用户一段时间内是否再次使用产品
-
基于某个产品改动，观察是否有人因为新功能改动而延长产品使用天数
-
验证社交产品改进了新注册用户的引导流程，期待改善用户注册后的参与程度
通过观察一段时间的留存，可以帮助运营和产品在一定程度上进行分析决策，制定相应的营销和产品策略。
2 计算留存
2.1 Hive计算模型
不难发现，计算留存就是将当天的数据和之前的数据关联起来取交集。
比如小c手头的需求，需要计算用户在接下来一周每天的留存，看看小c前同事的伪代码实现：
select
t1.uuid
from
dau_table as t1
inner join
dau_table as t2
on t1.uuid = t2.uuid
where t1.imp_date = '2022-01-02'
and t2.imp_date = '2022-01-03'
用这种方式求留存很显然有很多弊端:
-
一次性求
周或月留存就要join多次，代码可读性差 -
很明显当数据量上来之后，sql
性能是一个问题 -
如果要算多天的周留存，代码量又是呈
线性增长
2.2 Clickhouse计算模型
2.2.1 dateDiff函数
在上面我们用了原始join写法，但是存在大量弊端。有没有一个更好的方式来处理呢？
可以选择使用
Clickhouse的dateDiff函数。
这种留存计算方法主要是通过求取用户之前出现的日期与之后出现日期差值，则可以判断该用户是否为某天的N日留存。
若某用户在1号出现，此时该用户则是1号的活跃用户，若该用户2号又出现了，则此时前后两次出现的日期差为1天，则可以确定为该用户为1号的留存用户，以此类推。
select d2.t1,
count(distinct case when diff = 0 then d2.uuid else null end) as activity_cnt,
count(distinct case when diff = 1 then d2.uuid else null end) as after_uuid_cnt_1,
count(distinct case when diff = 2 then d2.uuid else null end) as after_uuid_cnt_2,
count(distinct case when diff = 3 then d2.uuid else null end) as after_uuid_cnt_3,
count(distinct case when diff = 4 then d2.uuid else null end) as after_uuid_cnt_4,
count(distinct case when diff = 5 then d2.uuid else null end) as after_uuid_cnt_5,
count(distinct case when diff = 6 then d2.uuid else null end) as after_uuid_cnt_6,
count(distinct case when diff = 7 then d2.uuid else null end) as after_uuid_cnt_7
from (
-- 这个地方求日期差的写法是clickhouse中的，hive中联合这三个函数from_unixtime，unix_timestamp，datediff进行计算
select *, dateDiff('day', d1.t1, d1.t2) as diff
from (
-- 两表关联，目的获取用户活跃日期的差值
select a.uuid, a.log_date t1, b.log_date t2
from (
SELECT distinct uuid,
toDate(login_time) as log_date
from dau_table
) a
inner join (
SELECT distinct uuid,
toDate(login_time) as log_date
from dau_table
) b
on a.uuid = b.uuid
where a.log_date <= b.log_date
) d1
) d2
group by d2.t1;
上面的计算思路在所有的数据库中都能实现，要比动辄就要left join好多次的逻辑要快很多，在性能和可读性上都有了很大的提升。
我们不难发现，这里还是left join了一次，多于大表来说还是比较影响性能的。
而Clickhouse已经帮我们解决了这个问题，下面就来一睹Clickhouse是如何做到的。
2.2.2 Retention函数
Retention函数是Clickhouse中经常使用的函数，可以说是为留存量身定制的一个func。
该函数将一组条件作为参数，类型为1到32个 UInt8 类型的参数，用来表示事件是否满足特定条件。任何条件都可以指定为参数，除第一个以外，条件成对适用。
例：如果第一个和第二个是真的，第二个结果将是真的，如果第一个和第三个是真的，第三个结果将是真的等等
具体这个函数如何使用，假设我们同样要求最近7天的留存。直接看计算逻辑。
// 为保证易读，这里只算两天
select
toDate(addDays(today(), -8)) as ds,
SUM(r[1]) AS activeAccountNum,
SUM(r[2]) / SUM(r[1]) AS two_day_stay,
SUM(r[3]) / SUM(r[1]) AS three_day_stay
from (
-- begin
WITH toDate(addDays(today(), -8)) AS tt
select
userid,
retention(
toDate(event_time) = tt,
toDate(subtractDays(toDateTime(event_time), 1)) = tt,
toDate(subtractDays(toDateTime(event_time), 2)) = tt,
toDate(subtractDays(toDateTime(event_time), 3)) = tt
) AS r
from orgin_log where imp_date > toDate(addDays(today(), -9))
group by userid
-- end
) group by ds
可能刚开始接触这个函数的同学对这段代码不太理解。我特意在代码中用begin和end把其中一段隔开。
下面我们先来看看被我独立出来的代码的查询结果。
从图中其实就比较好理解。
-
retention在这里实际上就是帮助我们以用户为组根据第一个与第N个条件进行与操作，返回一个数组。若为1，则表示在第N天有留存，若为0，则表示在第N天没有留存。
-
通过整段代码将后面每天的留存用户sum起来与当天的活跃人数相除便可得到用户留存数据。
显然，在海量的数据集下使用ClickHouse自带retention留存函数运行速度更快、更高效。提升了现有技术中用户留存率的计算方式速度慢效率低的问题，进而达到了提高计算速度和计算效率的效果。
到这里我们是否可以鸣金收兵了呢？还没有。
2.2.3 Array&Retention函数
接下来我们考虑这样一种应用场景，以上计算的是某一天的7天留存，如果我们需要查看最近一周每天的7天留存又该如何计算呢。
很直接的想法就是把每天的后7天留存计算出来然后union all起来。可是做个数据分析的都知道使用union all导致代码量剧增，可读性急剧下降。
接下来我们尝试用一种方法不使用union all就能解决上面的问题。
SELECT
fdate,
sum(r[1]) AS play_user,
sum(r[2]) / SUM(r[1]) AS r1,
sum(r[3]) / SUM(r[1]) AS r2
FROM
(
select
fdate,
uid,
retention(account_day = fdate,
account_day = addDays(fdate,1),
account_day = addDays(fdate,2)
) as r
from
(
-- begin
select
arrayJoin(arrayMap(d -> addDays(yesterday(), -d), range(7)) as arr_ftime) as fdate,
account_day ,
uuid as uid
from
(
select
uuid,
toDate(event_time) account_day
from orgin_log where toDate(event_time) >= toDate(addDays(today(),-7)) a ) x
-- end
)
group by fdate, uid
)
group by fdate
同样，我在代码中用begin和end把其中一段隔开。细心的同学很快发现这里与之前主要的差别就在于这里使用了arrayJoin函数。那这个arrayJoin是做什么用的呢。我们先来看一下效果图。
可以看到，arrayJoin函数在这里的作用就是把给每条记录加上7个标签fdate（即从当天往前推7天的日期）。这样说可能不太好理解，我这里画了一张图，相信可以借助这张图可以一目了然
图中能看出来fdate就是一个日期分组标识，与上段代码用with指定一个日期不同，这里通过对fdate的group分组，对每一个fdate取值的日期进行了7天留存计算。
我们不需要进行union all就可以解决上述应用场景，到这里就可以说我们大功告成了。读者可以自取代码举一反三根据实际应用场景进行数据分析。
想获取更多关于数据分析、大数据面试资料，请联系我wx: youlong525
3 总结
说到这里，我们发现我们发现在追求代码简单高效的过程中往往会衍生出意想不到的技术的精进。
所以希望读者跟老兵一起保持这样不断追求完善的热情来对待自己的生活和工作。
当然也希望老兵可以一直陪伴大家～
>>> 欢迎大家添加我的gz号: 大数据兵工厂
扫一扫
1360
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
