需求：根据用户起止日期统计平台每日用户数量

已知平台内每位用户的使用起始日期（START）和结束日期（END），需要统计指定时间段内平台的每日用户数量（即当日处于使用周期内的用户总数）。

现有用户数据 #

期望统计结果 #

解决方案（以SQL为例） #

核心思路是先生成目标时间段内的所有日期，再逐个日期统计符合START ≤ 日期 ≤ END条件的用户数。

方法1：使用递归CTE生成日期序列（适用于MySQL 8.0+、PostgreSQL、SQL Server等支持CTE的数据库）

-- 生成日期范围（从用户最早的起始日期到最晚的结束日期）
WITH date_range AS (
    SELECT MIN(START) AS date FROM users
    UNION ALL
    SELECT DATE_ADD(date, INTERVAL 1 DAY) FROM date_range
    WHERE date < (SELECT MAX(END) FROM users)
)
SELECT 
    dr.date AS DATE,
    COUNT(u.ID) AS NUMBER
FROM date_range dr
LEFT JOIN users u 
    ON dr.date BETWEEN u.START AND u.END
GROUP BY dr.date
ORDER BY dr.date;

方法2：使用数字表生成日期序列（兼容低版本数据库）

如果数据库不支持递归CTE，可预先创建一个存储连续整数的数字表，再通过整数计算生成日期：

-- 假设已有数字表numbers，包含从0开始的连续整数
SELECT 
    DATE_ADD((SELECT MIN(START) FROM users), INTERVAL n.num DAY) AS DATE,
    COUNT(u.ID) AS NUMBER
FROM numbers n
LEFT JOIN users u 
    ON DATE_ADD((SELECT MIN(START) FROM users), INTERVAL n.num DAY) BETWEEN u.START AND u.END
WHERE DATE_ADD((SELECT MIN(START) FROM users), INTERVAL n.num DAY) <= (SELECT MAX(END) FROM users)
GROUP BY DATE_ADD((SELECT MIN(START) FROM users), INTERVAL n.num DAY)
ORDER BY DATE;

结果说明 #

执行上述SQL后，会生成目标时间段内的每日日期，以及对应日期的用户数量，与期望结果完全匹配。

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