SQL使用CTE填充缺失数据 实现气象数据1分钟粒度致密化

问题核心原因 #

你当前的写法存在两个核心问题，无法满足致密化需求：

• 仅生成了连续时间序列，没有和全量气象站点做笛卡尔积，导致无观测数据的站点不会出现在对应时间点的结果中
• 没有实现 NULL 值的前向填充逻辑，缺失的观测数据无法用最近的历史值补全

调整方案 #

1. 核心逻辑调整 #

首先新增CTE获取所有去重的站点列表，再生成「1分钟时间 × 全量站点」的完整基准表，最后对每个站点分区做最近值填充即可。

2. 调整后SQL代码 #

DECLARE @FromDate DateTime = '2021-01-01 00:00:00.000',
        @ToDate   DateTime = '2021-08-01 00:00:00.000'

;WITH DateCte (DateTime) AS
(
    SELECT @FromDate 
    UNION ALL
    SELECT DATEADD(MINUTE, 1, DateTime)
    FROM DateCte
    WHERE DateTime < @ToDate
),
-- 新增：获取所有需要补全的去重站点列表
SiteCte AS (
    SELECT DISTINCT Name_En
    FROM [ODW].[QMD].[LastObservations]
    WHERE Local_Time >= @FromDate AND Local_Time <= @ToDate
),
-- 新增：生成 时间×站点 的完整致密化基准表
FullBaseCte AS (
    SELECT DateTime, Name_En
    FROM DateCte
    CROSS JOIN SiteCte
)
SELECT
    f.DateTime,
    -- 前向填充最近的观测时间，无历史值则为NULL可根据业务调整
    LAST_VALUE(DATEADD(mi, DATEDIFF(mi, 0, l.Local_Time), 0)) IGNORE NULLS OVER (
        PARTITION BY f.Name_En ORDER BY f.DateTime 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS [Local Time],
    f.Name_En,
    -- 前向填充最近的降雨量，无历史值则为NULL可根据业务调整
    LAST_VALUE(l.Rainfall) IGNORE NULLS OVER (
        PARTITION BY f.Name_En ORDER BY f.DateTime 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS [Value]
FROM FullBaseCte f
LEFT OUTER JOIN [ODW].[QMD].[LastObservations] l 
    ON f.DateTime = DATEADD(mi, DATEDIFF(mi, 0, l.Local_Time), 0) 
    AND l.Name_En = f.Name_En
    AND l.Local_Time >= @FromDate
    AND l.Local_Time <= @ToDate
ORDER BY f.DateTime
OPTION (MaxRecursion 0)

3. 低版本SQL Server兼容写法 #

如果你使用的SQL Server版本低于2022，不支持IGNORE NULLS参数，可以用分组标记法实现前向填充：

-- 前面的DateCte、SiteCte、FullBaseCte逻辑不变，修改外层查询即可
SELECT
    DateTime,
    MAX([Local Time]) OVER (PARTITION BY Name_En, grp) AS [Local Time],
    Name_En,
    MAX(Value) OVER (PARTITION BY Name_En, grp) AS Value
FROM (
    SELECT
        f.DateTime,
        DATEADD(mi, DATEDIFF(mi, 0, l.Local_Time), 0) AS [Local Time],
        f.Name_En,
        l.Rainfall AS [Value],
        -- 给每个非空值及后续的空值分配相同的分组标记
        SUM(CASE WHEN l.Rainfall IS NOT NULL THEN 1 ELSE 0 END) OVER (
            PARTITION BY f.Name_En ORDER BY f.DateTime
        ) AS grp
    FROM FullBaseCte f
    LEFT OUTER JOIN [ODW].[QMD].[LastObservations] l 
        ON f.DateTime = DATEADD(mi, DATEDIFF(mi, 0, l.Local_Time), 0) 
        AND l.Name_En = f.Name_En
        AND l.Local_Time >= @FromDate
        AND l.Local_Time <= @ToDate
) t
ORDER BY DateTime

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