SQL Server中填充Price空值并添加PriceDate列的技术问询

我需要处理数据表中的Price空值，同时为填充的空值添加对应的来源日期（PriceDate），规则如下：

• 5月的Price空值：用前序最近的非空Price对应的日期作为PriceDate
• 6月的Price空值：用6月5日的非空Price对应的日期作为PriceDate
• 原始非空Price的记录：PriceDate保持为NULL

目前已经用SQL Server的临时表#FilledPrices实现了Price的空值填充逻辑（先填充当月首个非空值之前的空值，再用前序非空值填充剩余空值），但还没完成PriceDate列的添加与赋值，求修改方案。

要实现这个需求，核心是在填充Price的过程中同步跟踪每个填充值的来源日期，而非仅处理Price字段。以下是基于你现有逻辑的修改步骤：

1. 调整临时表结构，保留原始Price用于判断 #

首先确保临时表包含原始Price字段（用来区分哪些是需要填充的空值）、记录日期，同时初始化PriceDate列：

-- 假设你的原始表名为YourTable，包含RecordDate（记录日期）和Price字段
SELECT 
    RecordDate,
    Price AS OriginalPrice, -- 保留原始值，用于判断是否为填充记录
    Price AS FilledPrice, -- 你的现有填充逻辑会更新这个字段
    CAST(NULL AS DATE) AS PriceDate -- 初始化来源日期为NULL
INTO #FilledPrices
FROM YourTable;

2. 处理5月空值的PriceDate赋值 #

用窗口函数LAST_VALUE按日期顺序获取前序最近的非空Price对应的日期，只更新原始Price为空的5月记录：

WITH MayFilling AS (
    SELECT 
        RecordDate,
        -- 取前序最近的非空Price对应的日期
        LAST_VALUE(CASE WHEN OriginalPrice IS NOT NULL THEN RecordDate END) OVER (
            ORDER BY RecordDate 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS SourceDate
    FROM #FilledPrices
    WHERE MONTH(RecordDate) = 5
)
UPDATE fp
SET fp.PriceDate = mf.SourceDate
FROM #FilledPrices fp
JOIN MayFilling mf ON fp.RecordDate = mf.RecordDate
WHERE fp.OriginalPrice IS NULL;

3. 处理6月空值的PriceDate赋值 #

先获取6月5日的非空Price对应的日期，再批量更新6月的空值记录：

-- 先获取6月5日的非空Price对应的日期
DECLARE @June5SourceDate DATE;
SELECT @June5SourceDate = RecordDate
FROM YourTable
WHERE MONTH(RecordDate) = 6 
  AND DAY(RecordDate) = 5 
  AND Price IS NOT NULL;

-- 更新6月所有原始Price为空的记录的PriceDate
UPDATE #FilledPrices
SET PriceDate = @June5SourceDate
WHERE MONTH(RecordDate) = 6 
  AND OriginalPrice IS NULL;

4. 最终结果验证 #

此时查询临时表，就能看到填充后的Price和对应的来源日期，原始非空记录的PriceDate保持NULL：

SELECT 
    RecordDate,
    FilledPrice AS Price,
    PriceDate
FROM #FilledPrices
ORDER BY RecordDate;

• 如果6月5日存在多条非空Price记录，可根据业务需求调整@June5SourceDate的取值逻辑（比如取第一条、最后一条或平均值对应的日期）
• 窗口函数中的ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW确保只取当前记录之前的非空值，符合“前序”的要求
• 保留OriginalPrice字段是关键，它能精准区分哪些记录是需要赋值PriceDate的填充记录

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