SQL如何不使用for循环查询2021年滚动30天区间记录数

不需要使用for循环，通过公共表表达式（CTE）生成所有滚动窗口的起始日期后，做范围关联聚合即可实现需求，执行效率远高于逐行循环。

• 先生成2021-01-01至2021-12-01的连续日期序列，作为每个30天滚动窗口的起始日，刚好覆盖你伪代码中335次循环的范围
• 将日期序列与目标表做左关联，匹配落在对应时间区间内的记录，保证无数据的窗口也会返回计数0
• 按起始日期分组统计记录数，按日期排序输出即可

PostgreSQL 版本实现 #

WITH date_series AS (
    SELECT generate_series(
        '2021-01-01'::date,
        '2021-12-01'::date,
        '1 day'::interval
    )::date AS start_date
)
SELECT
    ds.start_date,
    COUNT(t.application_date) AS _count
FROM date_series ds
LEFT JOIN _table t
    ON t.application_date >= ds.start_date
    AND t.application_date <= ds.start_date + INTERVAL '30 days'
    -- 如果不需要统计2022年的数据，打开下一行注释
    -- AND t.application_date <= '2021-12-31'
GROUP BY ds.start_date
ORDER BY ds.start_date;

MySQL 8.0+ 版本实现 #

WITH RECURSIVE date_series AS (
    SELECT '2021-01-01' AS start_date
    UNION ALL
    SELECT DATE_ADD(start_date, INTERVAL 1 DAY)
    FROM date_series
    WHERE start_date < '2021-12-01'
)
SELECT
    ds.start_date,
    COUNT(t.application_date) AS _count
FROM date_series ds
LEFT JOIN _table t
    ON t.application_date >= ds.start_date
    AND t.application_date <= DATE_ADD(ds.start_date, INTERVAL 30 DAY)
    -- 如果不需要统计2022年的数据，打开下一行注释
    -- AND t.application_date <= '2021-12-31'
GROUP BY ds.start_date
ORDER BY ds.start_date;

• 不要用INNER JOIN替换LEFT JOIN，否则没有任何记录的时间窗口会直接缺失，不会返回_count=0的结果
• 如果application_date字段带时分秒精度，建议将结束条件改为t.application_date < ds.start_date + INTERVAL '30 days'，避免时间精度问题导致漏算/多算临界时间点的记录
• 可以根据业务口径调整日期间隔：如果需要统计包含起始日共30天的窗口，将间隔改为29天即可

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