如何在SQL中实现指定天数的Forward Fill（前向填充）逻辑

刚好之前处理过类似的时序数据补全需求，我来给你拆解下SQL实现的思路和代码，完全贴合你说的「最多填充n天前向值，超出缺口不处理」的逻辑：

核心思路 #

要实现这个需求，关键是先补全每个Product_type的完整日期序列，然后给每个缺失日期标记它距离最近的前一个有效数据的天数，最后判断这个天数是否小于等于n，再决定是否填充。

完整SQL实现（以PostgreSQL为例，n=2） #

假设你的表名为product_metrics，我们用CTE分步实现：

WITH date_range AS (
    -- 第一步：生成原数据覆盖的所有连续日期
    SELECT generate_series(
        (SELECT MIN(date) FROM product_metrics),
        (SELECT MAX(date) FROM product_metrics),
        '1 day'::interval
    ) AS full_date
),
product_full_dates AS (
    -- 第二步：给每个Product_type匹配所有日期，确保无遗漏
    SELECT DISTINCT pm.Product_type, dr.full_date AS date
    FROM product_metrics pm
    CROSS JOIN date_range dr
),
joined_raw_data AS (
    -- 第三步：关联原数据，标记出缺失metric的日期
    SELECT 
        pfd.Product_type,
        pfd.date,
        pm.metric
    FROM product_full_dates pfd
    LEFT JOIN product_metrics pm
        ON pfd.Product_type = pm.Product_type
        AND pfd.date = pm.date
),
grouped_with_gaps AS (
    -- 第四步：给每个有效数据为起点的连续区间分组，计算缺失天数
    SELECT 
        Product_type,
        date,
        metric,
        -- 每当遇到非空metric，生成新的分组ID
        SUM(CASE WHEN metric IS NOT NULL THEN 1 ELSE 0 END) 
            OVER (PARTITION BY Product_type ORDER BY date) AS gap_group_id,
        -- 计算组内的行号，用来判断距离有效数据的天数
        ROW_NUMBER() OVER (PARTITION BY Product_type, gap_group_id ORDER BY date) AS days_since_last_valid
    FROM joined_raw_data
)
-- 第五步：根据天数判断是否填充，超出n天则保留null
SELECT 
    Product_type,
    date,
    CASE 
        WHEN metric IS NOT NULL THEN metric
        -- 这里的2就是你要设置的最大填充天数n
        WHEN days_since_last_valid - 1 <= 2 THEN LAST_VALUE(metric) OVER (PARTITION BY Product_type, gap_group_id ORDER BY date)
        ELSE NULL
    END AS metric_filled
FROM grouped_with_gaps
ORDER BY Product_type, date;

代码解释 #

1. date_range：生成原数据中最早到最晚的所有连续日期，解决原数据日期缺失的问题。
2. product_full_dates：通过笛卡尔积，让每个Product_type都拥有完整的日期序列，确保没有日期遗漏。
3. joined_raw_data：左连接原数据，把存在的metric保留，缺失的标记为null。
4. grouped_with_gaps：用窗口函数把每个有效数据和后续的缺失日期划分为一个分组，同时计算每个缺失日期距离上一个有效数据的天数（days_since_last_valid - 1就是间隔天数）。
5. 最后一步的CASE判断：如果是有效数据直接保留；如果是缺失数据且间隔天数≤n，就用LAST_VALUE取分组内的有效metric填充；超出n天的缺口则保留null。

其他数据库适配说明 #

如果用MySQL/SQL Server这类没有generate_series的数据库，需要用递归CTE生成日期序列：

• MySQL版本的日期生成CTE：

WITH RECURSIVE date_range AS (
    SELECT MIN(date) AS full_date FROM product_metrics
    UNION ALL
    SELECT DATE_ADD(full_date, INTERVAL 1 DAY)
    FROM date_range
    WHERE full_date < (SELECT MAX(date) FROM product_metrics)
)

• SQL Server版本的日期生成CTE：

WITH date_range AS (
    SELECT MIN(date) AS full_date FROM product_metrics
    UNION ALL
    SELECT DATEADD(DAY, 1, full_date)
    FROM date_range
    WHERE full_date < (SELECT MAX(date) FROM product_metrics)
)

验证你的示例数据 #

用你给出的示例数据测试，最终结果会和预期一致：

• 2019-10-05、2019-10-06会被填充为5（距离2019-10-04的间隔分别是1、2天，≤n=2）
• 2019-10-07至2019-10-15的metric_filled为null（间隔超过2天）
• 2019-10-16及之后的有效数据正常保留

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