SQL Server如何用最后非空值填充透视结果中的空值

问题说明 #

• 源表tasas仅存储经理手动调整汇率的日期记录，未调整日期无对应数据
• 关联日历维度表Dim_Fecha做汇率透视后，无调整日期（如周末、节假日）的汇率字段返回NULL
• 填充规则：空值取同币种上一个最近的非空汇率值，首个有效汇率日期之前的空值保留
• 之前测试代码报错原因：当前使用的SQL Server版本为2008或更早，不支持FIRST_VALUE等高版本窗口偏移函数

实现代码 #

代码完全兼容SQL Server 2008及以上版本，无高版本函数依赖，可直接替换原有逻辑运行：

DECLARE @startdate as date
DECLARE @enddate as date
set @startdate = '20210101'
set @enddate = '20221231'

-- 先生成带空值的透视基础结果
;WITH Pivoted AS (
    SELECT  fecha as Fecha,[US$],[EUR],[ZEL]
    FROM 
    (
        SELECT F.fecha, T.tasa_v, T.co_mone
        FROM DWSTAGING_GML.dbo.Dim_Fecha F
        LEFT JOIN dbo.tasas as T ON CAST(T.fecha AS date) = F.fecha
        WHERE F.fecha BETWEEN @startdate AND @enddate
    ) as SRC
    PIVOT
    (
        AVG(tasa_v) 
        FOR co_mone IN ([US$],[EUR],[ZEL])
    ) as Pivoted
)
-- 累计计数分组后取同组非空值填充
SELECT 
    Fecha,
    CASE WHEN grupo_US > 0 THEN MAX([US$]) OVER (PARTITION BY grupo_US) END AS [US$],
    CASE WHEN grupo_EUR > 0 THEN MAX([EUR]) OVER (PARTITION BY grupo_EUR) END AS [EUR],
    CASE WHEN grupo_ZEL > 0 THEN MAX([ZEL]) OVER (PARTITION BY grupo_ZEL) END AS [ZEL]
FROM (
    SELECT 
        Fecha,
        [US$],
        [EUR],
        [ZEL],
        -- 累计统计截止当前行的非空值数量，连续空值与上一个非空值归为同一组
        COUNT([US$]) OVER (ORDER BY Fecha ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS grupo_US,
        COUNT([EUR]) OVER (ORDER BY Fecha ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS grupo_EUR,
        COUNT([ZEL]) OVER (ORDER BY Fecha ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS grupo_ZEL
    FROM Pivoted
) t
ORDER BY Fecha
GO

逻辑说明 #

• 累计计数分组规则：按日期升序统计截止到当前行的非空汇率数量，遇到非空值时计数+1，连续空值的计数值和上一个非空值保持一致，自动归为同一分组
• 同组内仅存在1个非空汇率值（即该段连续空值需要填充的目标值），用MAX()即可取到该值，不需要使用FIRST_VALUE，从根源规避低版本SQL Server的函数兼容问题
• 组号为0时代表截止到当前行还未出现过有效汇率，直接返回NULL，符合首个有效日期前空值不填充的要求

如果后续升级到SQL Server 2022及以上版本，可以直接用LAST_VALUE([US$]) IGNORE NULLS OVER (ORDER BY Fecha ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)实现更简洁的向前填充，不需要手动编写分组逻辑。

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