如何将MySQL生成的日期列表与未登录用户统计查询结合

你可以将日期生成查询作为主查询，通过关联子查询计算每个月初日期对应的未登录用户数，这并不违反MySQL的子查询限制。

以下是整合后的完整查询：

SELECT 
    month_dates.`Month beginning`,
    (
        SELECT COUNT(DISTINCT id) 
        FROM users 
        WHERE timestamp < month_dates.`Month beginning`
    ) - (
        SELECT COUNT(DISTINCT user_id) 
        FROM logins 
        WHERE timestamp < month_dates.`Month beginning`
    ) AS `Not logged in`
FROM (
    SELECT DATE_FORMAT(date_range.timestamp, "%Y-%m-01") AS `Month beginning`
    FROM (
        SELECT CURDATE() - INTERVAL (a.a + 10*b.a + 100*c.a + 1000*d.a) DAY AS timestamp
        FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS d
    ) date_range
    WHERE date_range.timestamp BETWEEN '2022-02-28' AND NOW() 
    GROUP BY DATE_FORMAT(date_range.timestamp, "%Y-%m-01") 
) month_dates
ORDER BY month_dates.`Month beginning` DESC;

关键说明 #

• 将日期生成逻辑封装为子查询month_dates，生成所有需要统计的月初日期。
• 针对每个Month beginning日期，使用两个关联子查询分别计算：
  1. 该日期前注册的总用户数
  2. 该日期前有登录记录的用户数
• 两者的差值即为截至该月初的未登录用户数。

优化建议（大数据量场景） #

如果用户表和登录表数据量较大，关联子查询可能存在性能瓶颈，可改用预聚合+左连接的方式提升效率：

WITH month_dates AS (
    SELECT DATE_FORMAT(date_range.timestamp, "%Y-%m-01") AS `Month beginning`
    FROM (
        SELECT CURDATE() - INTERVAL (a.a + 10*b.a + 100*c.a + 1000*d.a) DAY AS timestamp
        FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS d
    ) date_range
    WHERE date_range.timestamp BETWEEN '2022-02-28' AND NOW() 
    GROUP BY DATE_FORMAT(date_range.timestamp, "%Y-%m-01")
),
user_counts AS (
    SELECT 
        DATE_FORMAT(u.timestamp, "%Y-%m-01") AS month_start,
        COUNT(DISTINCT u.id) AS total_users
    FROM users u
    GROUP BY month_start
    WITH ROLLUP
),
login_counts AS (
    SELECT 
        DATE_FORMAT(l.timestamp, "%Y-%m-01") AS month_start,
        COUNT(DISTINCT l.user_id) AS logged_in_users
    FROM logins l
    GROUP BY month_start
    WITH ROLLUP
)
SELECT 
    md.`Month beginning`,
    COALESCE(SUM(uc.total_users), 0) - COALESCE(SUM(lc.logged_in_users), 0) AS `Not logged in`
FROM month_dates md
LEFT JOIN user_counts uc ON uc.month_start < md.`Month beginning`
LEFT JOIN login_counts lc ON lc.month_start < md.`Month beginning`
GROUP BY md.`Month beginning`
ORDER BY md.`Month beginning` DESC;

这种方式先预聚合每个月的用户注册和登录数据，再通过左连接累加得到截至每个月初的总数，性能表现更优。

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