SQL Server 2012按产品分组用同列下方最近值填充NULL值

需求规则说明 #

• 待处理表包含字段：date、Product、Order TYPE、Unit price
• 基础逻辑：按Product字段分组，组内按date升序排序，将Unit price为NULL的记录，替换为同组内该记录下方最近的非空Unit price值
• 约束规则：
  • 同一Product分组内Unit price可重复出现，无需做去重处理
  • 若NULL值位于同组最后一条非空Unit price记录之后（即下方无有效非空单价），保留NULL不替换

实现思路 #

SQL Server 2012 未支持窗口函数的IGNORE NULLS特性，因此采用行号锚定+关联匹配的方案实现，无递归、无逐行遍历，大数据量下执行效率稳定：

1. 按Product分区、date升序为所有记录生成连续行号，锚定每条记录的组内顺序
2. 对每条记录，匹配同组内行号大于自身、且Unit price非空的记录，取最小的匹配行号，即为当前记录下方最近的非空值位置
3. 关联对应位置的非空单价：原记录自带单价的直接保留，无单价的用匹配到的下方值填充，匹配不到有效非空值的保留NULL

可直接运行的实现代码 #

以下代码包含样例数据构造，实际使用时将SourceData部分替换为自身业务表即可：

WITH SourceData AS (
    -- 测试样例数据，实际使用时替换为你的业务表查询逻辑
    SELECT CAST(date AS DATE) date, Product, [Order TYPE], [Unit price]
    FROM (VALUES
        ('2020-01-01','产品1','生产订单',NULL),
        ('2020-01-03','产品1','生产订单',NULL),
        ('2020-01-04','产品1','销售订单',10),
        ('2020-01-05','产品1','生产订单',NULL),
        ('2020-01-06','产品1','生产订单',NULL),
        ('2020-01-07','产品1','销售订单',20),
        ('2020-01-08','产品1','生产订单',NULL),
        ('2020-01-01','产品2','生产订单',NULL),
        ('2020-01-02','产品2','生产订单',NULL),
        ('2020-01-03','产品2','销售订单',15),
        ('2020-01-05','产品2','生产订单',NULL),
        ('2020-01-06','产品2','销售订单',25),
        ('2020-01-07','产品2','生产订单',NULL),
        ('2020-01-07','产品2','生产订单',NULL),
        ('2020-01-07','产品2','生产订单',NULL)
    ) t(date, Product, [Order TYPE], [Unit price])
),
RowMark AS (
    -- 生成组内排序行号
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY Product ORDER BY date) AS rn
    FROM SourceData
),
MatchNearest AS (
    -- 匹配每条记录下方最近的非空值行号
    SELECT 
        a.*,
        MIN(b.rn) AS target_rn
    FROM RowMark a
    LEFT JOIN RowMark b 
        ON a.Product = b.Product 
        AND b.rn > a.rn 
        AND b.[Unit price] IS NOT NULL
    GROUP BY a.date, a.Product, a.[Order TYPE], a.[Unit price], a.rn
)
-- 关联取值输出最终结果
SELECT 
    m.date,
    m.Product,
    m.[Order TYPE],
    COALESCE(m.[Unit price], v.[Unit price]) AS [Unit price]
FROM MatchNearest m
LEFT JOIN RowMark v 
    ON m.Product = v.Product 
    AND m.target_rn = v.rn
ORDER BY m.Product, m.date;

结果说明 #

运行上述代码将完全匹配预期填充效果：

• 产品1分组：2020-01-01、2020-01-03的NULL值填充为10；2020-01-05、2020-01-06的NULL值填充为20；2020-01-08记录下方无有效非空值，保留NULL
• 产品2分组：2020-01-01、2020-01-02的NULL值填充为15；2020-01-05的NULL值填充为25；2020-01-07的三笔记录下方无有效非空值，保留NULL

适配调整提示 #

如果同一Product+date下存在多条记录，只需在ROW_NUMBER的ORDER BY子句中补充次排序字段（如订单创建时间、订单ID），保证组内排序和业务逻辑一致即可。

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