SQL技术需求：生成每日30天滚动窗口内历史ID计数报表

核心思路是先构建需要统计的完整日期序列，再对每个日期关联数据，统计其前30天内的唯一ID数量。以下是主流数据库的实现代码：

PostgreSQL 实现 #

WITH date_range AS (
    SELECT generate_series(
        (SELECT MIN(record_date) FROM your_table),
        (SELECT MAX(record_date) FROM your_table),
        INTERVAL '1 day'
    )::DATE AS report_date
)
SELECT
    dr.report_date AS date,
    COUNT(DISTINCT yt.id) AS counts
FROM date_range dr
LEFT JOIN your_table yt
    ON yt.record_date >= dr.report_date - INTERVAL '29 days'
    AND yt.record_date <= dr.report_date
GROUP BY dr.report_date
ORDER BY dr.report_date;

MySQL 实现 #

WITH RECURSIVE date_range AS (
    SELECT (SELECT MIN(record_date) FROM your_table) AS report_date
    UNION ALL
    SELECT DATE_ADD(report_date, INTERVAL 1 DAY)
    FROM date_range
    WHERE report_date < (SELECT MAX(record_date) FROM your_table)
)
SELECT
    dr.report_date AS date,
    COUNT(DISTINCT yt.id) AS counts
FROM date_range dr
LEFT JOIN your_table yt
    ON yt.record_date >= DATE_SUB(dr.report_date, INTERVAL 29 DAY)
    AND yt.record_date <= dr.report_date
GROUP BY dr.report_date
ORDER BY dr.report_date;

关键注意事项 #

• 替换代码中的your_table为实际数据表名，record_date为数据中的日期字段，id为需统计的唯一标识字段
• 若已有独立的日历表，可直接用日历表替代生成的date_range，能大幅提升查询效率
• 针对大数据量表，建议为record_date和id创建联合索引，优化关联查询速度

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