SQL实现日期区间每日匹配记录及用户已读数统计

原有SQL无法实现按日维度统计，核心有两个问题：

• 没有生成查询区间内的连续自然日作为统计基准，无法输出匹配数为0的日期行
• 有效期判断逻辑错误，仅筛选了起止端点符合条件的记录，没有判断每个自然日是否落在记录有效期范围内

基于MySQL 8.0+（支持CTE语法）的实现逻辑如下，可直接替换查询参数运行：

WITH RECURSIVE date_series AS (
    -- 初始化查询起始日期
    SELECT CAST('2022-06-30' AS DATE) AS stat_date
    UNION ALL
    -- 递归生成后续日期直到截止日期
    SELECT DATE_ADD(stat_date, INTERVAL 1 DAY)
    FROM date_series
    WHERE stat_date < '2022-07-07'
)
SELECT
    ds.stat_date AS `统计日期`,
    COUNT(r.id) AS `matches`,
    COUNT(vs.id) AS `already_read_by_me`
FROM date_series ds
-- 关联匹配当日生效的记录
LEFT JOIN records r
    ON ds.stat_date >= r.valid_from
    AND (r.valid_till IS NULL OR ds.stat_date <= r.valid_till)
-- 关联对应用户的已读状态
LEFT JOIN viewstates vs
    ON r.id = vs.record
    AND vs.user = 'X' -- 替换为实际查询的用户ID
GROUP BY ds.stat_date
ORDER BY ds.stat_date ASC;

• 连续日期生成：通过递归CTE构造查询区间内的全部自然日，确保哪怕当日没有匹配记录，也会返回对应日期的统计行，不会出现日期缺漏
• 有效期匹配规则：修正了原有SQL的判断逻辑，只要统计日期大于等于记录生效起始日，且小于等于记录生效截止日（或记录为永久有效），即判定为当日匹配
• 已读数统计：关联已读表时直接过滤目标用户，避免冗余数据关联，统计结果和给出的用户X预期值完全对齐
• 低版本兼容：如果使用不支持递归CTE的数据库版本，可以预先创建一张存储多年连续自然日的通用日历表，用日历表筛选区间日期替换date_series部分即可，核心统计逻辑无需修改

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