在SQL Server中回溯获取车辆最新有效价格及对应日期

这个需求其实很常见——处理带NULL的时间序列数据，需要向前填充最近的有效值并获取每辆车的最新记录。结合你给出的SQL Server表变量场景，这里提供几个实用的解决方案：

方案1：使用窗口函数 LAST_VALUE（SQL Server 2012+） #

利用LAST_VALUE窗口函数按车辆分区、日期排序，自动为每条记录填充之前最近的非NULL价格，再筛选出每辆车的最新记录即可。

WITH FilledPrices AS (
    SELECT 
        car,
        PricingDate,
        -- 向前填充最近的非NULL价格
        LAST_VALUE(price) OVER (
            PARTITION BY car 
            ORDER BY PricingDate 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS EffectivePrice,
        -- 标记每辆车的最新日期记录
        ROW_NUMBER() OVER (
            PARTITION BY car 
            ORDER BY PricingDate DESC
        ) AS rn
    FROM @CarPrices
)
SELECT 
    car,
    PricingDate AS LatestDate,
    EffectivePrice
FROM FilledPrices
WHERE rn = 1;

解释 #

• LAST_VALUE会在每个车辆的分区内，从最早的记录到当前行，抓取最后一个非NULL的价格值，实现“向前填充”。
• ROW_NUMBER()按日期倒序编号，每辆车的rn=1就是最新的那条记录，直接筛选即可得到目标结果。

方案2：使用OUTER APPLY操作符（更直观） #

先获取每辆车的最新日期，再针对每个车辆和它的最新日期，反向查找最近的非NULL价格记录，逻辑更清晰，适合新手理解。

SELECT 
    c.car,
    c.LatestDate,
    p.price AS EffectivePrice
FROM (
    -- 第一步：获取每辆车的最新日期
    SELECT 
        car,
        MAX(PricingDate) AS LatestDate
    FROM @CarPrices
    GROUP BY car
) c
OUTER APPLY (
    -- 第二步：为每个车辆找最新日期之前（含）的最后一个有效价格
    SELECT TOP 1 price
    FROM @CarPrices cp
    WHERE cp.car = c.car 
      AND cp.PricingDate <= c.LatestDate
      AND cp.price IS NOT NULL
    ORDER BY cp.PricingDate DESC
) p;

解释 #

• 子查询c先聚合得到每辆车的最新日期；
• OUTER APPLY会为c中的每一行（每个车辆+最新日期）执行一次子查询，找到最近的有效价格，即使某辆车全是NULL记录（示例中没有这种情况），也会返回NULL，符合预期。

方案3：使用CTE+ROW_NUMBER定位有效价格 #

通过两次CTE，先筛选出所有有效价格记录并排序，再关联到所有记录上，最后取最新日期的结果，适合需要更灵活扩展的场景。

WITH ValidPrices AS (
    -- 筛选所有非NULL价格记录，按日期倒序编号
    SELECT 
        car,
        price,
        PricingDate,
        ROW_NUMBER() OVER (PARTITION BY car ORDER BY PricingDate DESC) AS rn_valid
    FROM @CarPrices
    WHERE price IS NOT NULL
),
AllRecords AS (
    -- 关联所有记录和对应的最新有效价格
    SELECT 
        cp.car,
        cp.PricingDate,
        vp.price AS EffectivePrice,
        ROW_NUMBER() OVER (PARTITION BY cp.car ORDER BY cp.PricingDate DESC) AS rn_latest
    FROM @CarPrices cp
    LEFT JOIN ValidPrices vp 
        ON cp.car = vp.car 
        AND vp.PricingDate <= cp.PricingDate
    WHERE vp.rn_valid = 1 -- 只取每辆车最新的有效价格
)
SELECT 
    car,
    PricingDate AS LatestDate,
    EffectivePrice
FROM AllRecords
WHERE rn_latest = 1;

解释 #

• ValidPrices把每辆车的有效价格按日期倒序排列，rn_valid=1就是该车辆最新的有效价格；
• AllRecords将所有记录和这个最新有效价格关联，最后筛选出每辆车的最新日期记录。

以上三种方案执行后，都会得到你想要的结果：

内容的提问来源于stack exchange，提问作者S.Crocker