【MySQL】关于累计去重用户数的计算逻辑

关于累计去重用户数的计算逻辑

当我们手里的数据只有日表，也就是每日的用户维度聚合数据，大致上就是下面这个样子：
这里用直播举例，其他都是类似的，表（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()就可以的到所有日期的累计开播用户数。

---

这里有意思的点，我认为是在统计周期内，只要找到了最早的开播日期，那么后续不管有没有开播，累计开播用户数都会把这个用户计算进去，也就不要再管后续的所有数据，只要每一天是那几个用户最早开播，然后按天去重，就可以了。