如何用MySQL递归CTE补全时间序列中的数据缺口？

针对百万级数据量的场景，递归CTE的逐行生成方式会导致性能瓶颈，推荐使用日期维度表+区间范围连接的方案，这是大数据量下最优的实现方式，具体步骤如下：

核心思路 #

1. 先为每个状态变更记录计算其生效时间区间：即从变更日期开始，到下一次变更日期的前一天结束（无后续变更则到统计截止日）。
2. 用预先生成的日期维度表，与状态区间表做范围连接，一次性补全所有日期的状态记录。
3. 按日期和状态聚合统计数量。

具体实现（以PostgreSQL为例） #

步骤1：生成状态生效区间 #

使用LEAD窗口函数获取每个请求的下一次变更日期，计算出当前状态的结束日期：

WITH request_status_periods AS (
    SELECT 
        request_id,
        to_status AS current_status,
        request_date AS start_date,
        -- 下一次变更日期减1天作为当前状态的结束日，无后续变更则用当前日期
        COALESCE(LEAD(request_date) OVER (PARTITION BY request_id ORDER BY request_date) - INTERVAL '1 day', CURRENT_DATE) AS end_date
    FROM request_status_changes
)

步骤2：生成日期维度表 #

如果没有现成的日期维度表，用generate_series快速生成所需日期范围：

, date_dim AS (
    SELECT generate_series('2023-01-01'::DATE, CURRENT_DATE, '1 day') AS report_date
)

步骤3：范围连接并聚合统计 #

通过区间匹配补全每日状态数据，再按日期和状态计数：

SELECT 
    dd.report_date,
    rsp.current_status,
    COUNT(DISTINCT rsp.request_id) AS status_count
FROM date_dim dd
JOIN request_status_periods rsp 
    ON dd.report_date BETWEEN rsp.start_date AND rsp.end_date
GROUP BY dd.report_date, rsp.current_status
ORDER BY dd.report_date, rsp.current_status;

MySQL适配方案（无generate_series） #

如果使用MySQL，可通过数字辅助表生成日期维度，避免递归CTE：

WITH nums AS (
    SELECT 0 AS n 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
),
date_dim AS (
    SELECT 
        DATE_ADD('2023-01-01', INTERVAL (n1.n * 1000 + n2.n * 100 + n3.n * 10 + n4.n) DAY) AS report_date
    FROM nums n1, nums n2, nums n3, nums n4
    HAVING report_date <= CURRENT_DATE
),
request_status_periods AS (
    SELECT 
        request_id,
        to_status AS current_status,
        request_date AS start_date,
        COALESCE(DATE_SUB(LEAD(request_date) OVER (PARTITION BY request_id ORDER BY request_date), INTERVAL 1 DAY), CURDATE()) AS end_date
    FROM request_status_changes
)
SELECT 
    dd.report_date,
    rsp.current_status,
    COUNT(DISTINCT rsp.request_id) AS status_count
FROM date_dim dd
JOIN request_status_periods rsp 
    ON dd.report_date BETWEEN rsp.start_date AND rsp.end_date
GROUP BY dd.report_date, rsp.current_status
ORDER BY dd.report_date, rsp.current_status;

性能优化建议 #

• 给request_status_changes表建立联合索引(request_id, request_date)，大幅提升LEAD窗口函数的执行效率。
• 如果日期维度表是常驻表，为report_date建立主键或唯一索引，加速范围连接。
• 若确认同一请求在同一天不会有多次状态变更，可将COUNT(DISTINCT rsp.request_id)改为COUNT(*)，进一步提升聚合速度。

方案对比 #

递归CTE是对每个请求的缺失日期逐行生成，百万级数据下会产生海量中间结果，导致内存和IO压力剧增；而区间范围连接是基于数据库的区间匹配优化，利用索引快速定位匹配的状态区间，中间数据量小，执行效率提升数倍甚至数十倍，完全适配百万级数据规模。

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