SQL时间序列带限制前向填充实现：适配15分钟粒度需求

要实现类似pandas ffill(limit=3)的SQL逻辑，核心是先补全完整的15分钟时间轴，再计算每个缺失点与上一个有效数据的间隔，限制仅填充间隔≤3的时段（对应1小时内的缺失）。以下是分步实现方案：

步骤1：生成完整的15分钟时间序列 #

首先构建覆盖所有目标时段的15分钟粒度时间轴，确保没有缺失的时间点（以SQL Server语法为例）：

WITH TimeSeries AS (
    SELECT DATEADD(MINUTE, 15 * n, (SELECT MIN([dlvrystartutc]) FROM T1)) AS datetime
    FROM (
        SELECT TOP (DATEDIFF(MINUTE, (SELECT MIN([dlvrystartutc]) FROM T1), (SELECT MAX([dlvrystartutc]) FROM T1)) / 15 + 1)
               n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
        FROM sys.all_columns
    ) AS nums
),

步骤2：关联原始小时数据 #

将原始小时粒度的数据左连接到时间序列上，把每个小时的import_h值映射到该小时的4个15分钟时间点：

RawDataWithTime AS (
    SELECT 
        ts.datetime,
        t.import_h
    FROM TimeSeries ts
    LEFT JOIN T1 t ON DATEADD(HOUR, DATEDIFF(HOUR, 0, ts.datetime), 0) = t.[dlvrystartutc]
),

步骤3：计算与上一个有效数据的间隔 #

用窗口函数标记每个时间点的上一个非空import_h，并计算两者之间的15分钟间隔数：

GapCalculation AS (
    SELECT 
        datetime,
        import_h,
        LAST_VALUE(import_h) OVER (ORDER BY datetime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS last_valid_import,
        ROW_NUMBER() OVER (PARTITION BY grp ORDER BY datetime) - 1 AS gap_count
    FROM (
        SELECT 
            datetime,
            import_h,
            COUNT(import_h) OVER (ORDER BY datetime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS grp
        FROM RawDataWithTime
    ) AS grouped
)

步骤4：应用填充限制 #

仅保留间隔≤3的填充值（对应最多填充3个15分钟间隔，即1小时），超过的设为NULL：

SELECT 
    datetime,
    import_h,
    CASE WHEN gap_count <= 3 THEN last_valid_import ELSE NULL END AS ff_import_h
FROM GapCalculation
ORDER BY datetime;

关键逻辑说明 #

• 时间序列生成：确保所有15分钟时间点都被覆盖，避免原始数据缺失导致的填充断档
• 间隔计算：通过分组和行号计算，精准控制填充的最大间隔（3个15分钟=1小时）
• 限制填充：用CASE语句过滤超过限制的填充，实现与ffill(limit=3)完全一致的效果

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