关于累计去重用户数的计算逻辑
当我们手里的数据只有日表,也就是每日的用户维度聚合数据,大致上就是下面这个样子:
这里用直播举例,其他都是类似的,表(user_action_day)主键(statistics_day, user_id)
要求的输出结果:
看到这里,开播用户数好算,但是后面的就有点麻了,这里就只讲后面的这玩意儿怎么算出来
有一个比较简单的思路就是用left join >= 来实现,如
SELECT
statistics_day, SUM(IF(live_cnt>0, 1,0)) 开播用户数, SUM(IF(累计>0, 1,0)) 累计开播用户数
FROM (
SELECT
a.statistics_day, a.user_id, a.live_cnt, sum(b.live_cnt) 累计
FROM
user_action_day a
LEFT JOIN user_action_day b
ON a.user_id = b.user_id AND a.statistics_day >= b.statistics_day
WHERE
a.statistics_day BETWEEN '2024-02-01' AND '2024-02-03'
GROUP BY
a.statistics_day, a.user_id
) t
GROUP BY
statistics_day
;
输出结果:
下面分享另一种计算方法,我个人感觉比较有点意思,效率高低另说,或许更适合应用于其他场景,在此仅分享这种思路逻辑
WITH FirstActivityPerUserPerMonth AS (
SELECT
user_id,
MIN(statistics_day) AS first_activity_date
FROM
user_action_day
WHERE
DATE_FORMAT(statistics_day, '%Y-%m') = '2024-02'
AND live_cnt > 0 -- 最早开播
GROUP BY
user_id
),
DistinctUsersPerDay AS (
SELECT
statistics_day,
COUNT(user_id) AS distinct_users
FROM (
SELECT DISTINCT
statistics_day
FROM
user_action_day
WHERE
DATE_FORMAT(statistics_day, '%Y-%m') = '2024-02'
) all_statistics_day
LEFT JOIN FirstActivityPerUserPerMonth
ON all_statistics_day.statistics_day = FirstActivityPerUserPerMonth.first_activity_date
GROUP BY
statistics_day
),
CumulativeUsers AS (
SELECT
du.statistics_day,
SUM(du.distinct_users) OVER (ORDER BY du.statistics_day) AS cumulative_users
FROM
DistinctUsersPerDay du
)
SELECT
CumulativeUsers.statistics_day,
开播用户数,
cumulative_users 累计开播用户数
FROM
CumulativeUsers
LEFT JOIN (
SELECT
statistics_day, sum(if(live_cnt>0,1,0)) 开播用户数
FROM
user_action_day
WHERE
DATE_FORMAT(statistics_day, '%Y-%m') = '2024-02'
GROUP BY
statistics_day
) t
ON CumulativeUsers.statistics_day = t.statistics_day
ORDER BY
CumulativeUsers.statistics_day
结果集如下:
来了,我们拆解一下这里面做了什么
-- FirstActivityPerUserPerMonth
SELECT
user_id,
MIN(statistics_day) AS first_activity_date
FROM
user_action_day
WHERE
DATE_FORMAT(statistics_day, '%Y-%m') = '2024-02'
AND live_cnt > 0 -- 最早开播
GROUP BY
user_id
其实这里做的就是找到每一个用户的最早开播日期
-- DistinctUsersPerDay
SELECT
statistics_day,
COUNT(user_id) AS distinct_users
FROM (
SELECT DISTINCT
statistics_day
FROM
user_action_day
WHERE
DATE_FORMAT(statistics_day, '%Y-%m') = '2024-02'
) all_statistics_day
LEFT JOIN FirstActivityPerUserPerMonth
ON all_statistics_day.statistics_day = FirstActivityPerUserPerMonth.first_activity_date
GROUP BY
statistics_day
DistinctUsersPerDay,执行的内容就是计算每一天的最早开播用户数,由于我们前一部分已经算了用户的最早开播日期,那么基于上一结果集来计算是不会出现重复的,因为一个用户只有一个最早开播日期,引起直接count(user_id)就可以了
这里做了个操作,如果不用所有日期作为主表进行左连接,那么会丢失掉那些不是最早开播日期的日期,简单说就是统计日期少了
-- CumulativeUsers
SELECT
du.statistics_day,
SUM(du.distinct_users) OVER (ORDER BY du.statistics_day) AS cumulative_users
FROM
DistinctUsersPerDay du
CumulativeUsers ,用分窗函数,按照统计时间升序进行分窗,也就是每一个窗都是>=statistics_day的所有数据,那么每一个窗进行sum()就可以的到所有日期的累计开播用户数。
这里有意思的点,我认为是在统计周期内,只要找到了最早的开播日期,那么后续不管有没有开播,累计开播用户数都会把这个用户计算进去,也就不要再管后续的所有数据,只要每一天是那几个用户最早开播,然后按天去重,就可以了。