如何用高效MySQL查询统计近一年每日30天内活跃用户数？

核心思路 #

别搞每天单独查一次的笨办法，效率太低。正确的姿势是：一次性生成近一年的所有日期，再结合登录记录的时间覆盖关系——每条登录记录会让用户在登录日之后的29天内都算活跃，然后通过聚合统计每个日期的去重活跃用户数。

步骤1：生成近一年的连续日期 #

首先得有近一年的完整日期序列，哪怕某天没人登录也要显示。不同数据库的写法略有不同：

MySQL/MariaDB #

-- 生成从今天往前364天到今天的所有日期
WITH RECURSIVE date_range AS (
    SELECT CURDATE() - INTERVAL 364 DAY AS active_date
    UNION ALL
    SELECT active_date + INTERVAL 1 DAY
    FROM date_range
    WHERE active_date < CURDATE()
)

PostgreSQL #

-- 生成近一年的连续日期
WITH date_range AS (
    SELECT generate_series(
        CURRENT_DATE - INTERVAL '364 days',
        CURRENT_DATE,
        INTERVAL '1 day'
    )::DATE AS active_date
)

SQL Server #

-- 生成近一年的连续日期
WITH date_range AS (
    SELECT DATEADD(DAY, -364, GETDATE()) AS active_date
    UNION ALL
    SELECT DATEADD(DAY, 1, active_date)
    FROM date_range
    WHERE active_date < GETDATE()
)

步骤2：关联登录记录统计活跃用户 #

接下来把日期序列和登录记录关联，统计每个日期对应的30天窗口内（该日期往前推29天到当天）的去重用户数。这里做了两个关键优化：

通用优化版SQL（以MySQL为例） #

WITH RECURSIVE date_range AS (
    SELECT CURDATE() - INTERVAL 364 DAY AS active_date
    UNION ALL
    SELECT active_date + INTERVAL 1 DAY
    FROM date_range
    WHERE active_date < CURDATE()
),
login_windows AS (
    SELECT 
        user_id,
        date_login AS start_date,
        date_login + INTERVAL 29 DAY AS end_date
    FROM logins
    -- 只保留会影响近一年统计的登录记录，少算没用的数据
    WHERE date_login >= CURDATE() - INTERVAL 364 DAY - INTERVAL 29 DAY
)
SELECT
    dr.active_date,
    COUNT(DISTINCT lw.user_id) AS active_users
FROM date_range dr
LEFT JOIN login_windows lw 
    ON dr.active_date BETWEEN lw.start_date AND lw.end_date
GROUP BY dr.active_date
ORDER BY dr.active_date;

关键优化点 #

• 预过滤登录记录：只留那些会影响近一年日期的登录（比如登录日不早于近一年起始日往前推29天），减少关联的数据量。
• 一次性生成日期序列：避免循环查每一天，一次搞定所有日期。
• 区间关联+去重计数：通过左连接让每个日期匹配到所有覆盖它的登录用户，最后按日期聚合去重，得到当天的活跃数。

再加个性能buff：建索引 #

想要更快？给logins表建个复合索引：

-- MySQL/PostgreSQL通用
CREATE INDEX idx_logins_user_date ON logins(user_id, date_login);

-- SQL Server
CREATE NONCLUSTERED INDEX idx_logins_user_date ON logins(user_id, date_login);

这个索引能快速过滤登录记录，减少查询时的数据扫描范围，速度直接起飞。

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