多表统计每日活跃用户（去重）SQL查询方案咨询

需求背景 #

需基于users与subscriptions表，整合user_video_plays、forum_posts、forum_post_replies三个用户行为表，统计近30天内的日活数据，核心要求是同一用户单日多次行为仅计1次。

现有单表关联的查询语句如下：

SELECT
    DATE_FORMAT(user_video_plays.created_at, '%Y-%m-%d') AS date,
    count(*)
FROM
    `users`
    INNER JOIN `subscriptions` ON `users`.`id` = `subscriptions`.`user_id`
    LEFT JOIN `user_video_plays` ON `users`.`id` = `user_video_plays`.`user_id`
WHERE
    `users`.`deleted_at` IS NULL
    AND `subscriptions`.`chargebee_status` <> 'cancelled'
    AND `user_video_plays`.`created_at` BETWEEN '2022-10-01 00:00:00' AND '2022-10-31 23:59:59'
GROUP BY
    DATE_FORMAT(user_video_plays.created_at, '%Y-%m-%d')

解决方案 #

核心思路 #

先将三个行为表中的用户行为按「用户ID+日期」去重，合并成统一的活跃用户日期数据集，再与有效用户（未删除、订阅未取消）关联，最后按日期统计去重后的用户数。

完整SQL语句 #

SELECT
    active_dates.date,
    COUNT(DISTINCT active_dates.user_id) AS dau
FROM
    (
        -- 提取视频播放表中去重的用户-日期记录
        SELECT
            user_id,
            DATE_FORMAT(created_at, '%Y-%m-%d') AS date
        FROM user_video_plays
        WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
        GROUP BY user_id, DATE_FORMAT(created_at, '%Y-%m-%d')
        
        UNION
        
        -- 提取论坛发帖表中去重的用户-日期记录
        SELECT
            user_id,
            DATE_FORMAT(created_at, '%Y-%m-%d') AS date
        FROM forum_posts
        WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
        GROUP BY user_id, DATE_FORMAT(created_at, '%Y-%m-%d')
        
        UNION
        
        -- 提取论坛回帖表中去重的用户-日期记录
        SELECT
            user_id,
            DATE_FORMAT(created_at, '%Y-%m-%d') AS date
        FROM forum_post_replies
        WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
        GROUP BY user_id, DATE_FORMAT(created_at, '%Y-%m-%d')
    ) AS active_dates
INNER JOIN users ON active_dates.user_id = users.id
INNER JOIN subscriptions ON users.id = subscriptions.user_id
WHERE
    users.deleted_at IS NULL
    AND subscriptions.chargebee_status <> 'cancelled'
GROUP BY active_dates.date
ORDER BY active_dates.date;

关键说明 #

• UNION自动去重：跨表的同一用户单日行为会被合并，确保每个用户单日仅保留一条记录
• 前置筛选：先过滤近30天的行为数据，减少后续关联计算的数据量
• 有效用户过滤：通过关联users和subscriptions表，排除已删除用户和订阅已取消的用户
• 固定日期范围适配：如果需要指定具体日期（如示例中的2022-10-01至2022-10-31），可将DATE_SUB(CURDATE(), INTERVAL 30 DAY)替换为起始日期，并添加AND created_at <= '2022-10-31 23:59:59'的结束条件

内容的提问来源于stack exchange，提问作者unstuckable