如何遍历各Product组，用相邻值均值填充缺失日期与Price？

需求说明 #

为数据表填充缺失的日期与Price字段，缺失的Price需使用仅该缺失值的上一个和下一个有效数值的均值填充，而非所有行的平均值。

样本数据 #

IF OBJECT_ID('tempdb..#mytable') IS NOT NULL DROP TABLE #mytable
CREATE TABLE #mytable (CreateDate datetime, Product Varchar(4), Price money)
INSERT INTO #mytable VALUES
('2023-06-09 17:01:00.000','Tree',1),
('2023-06-09 17:02:00.000','Tree',2),
('2023-06-09 17:03:00.000','Tree',3),
('2023-06-09 17:04:00.000','Tree',4),
('2023-06-09 17:01:00.000','BB',20),
('2023-06-09 17:02:00.000','BB',40),
('2023-06-09 17:04:00.000','BB',60),
('2023-06-09 17:01:00.000','Car',20),
('2023-06-09 17:03:00.000','Car',30),
('2023-06-09 17:04:00.000','Car',50)

现有步骤 #

步骤1：创建完整时间范围集合 #

IF OBJECT_ID('tempdb..#DateRange') IS NOT NULL DROP TABLE #DateRange
CREATE TABLE #DateRange(CreateDate datetime Primary key Clustered)
GO

DECLARE @startdate datetime = '2023-06-09 17:01:00', @endtime datetime = '2023-06-09 17:04:00'
WHILE (@startdate <= @endtime)
BEGIN
    INSERT INTO #DateRange VALUES (@startdate)
    SET @startdate = DATEADD(MINUTE, 1, @startdate)
END

步骤2：现有填充逻辑（仅针对单个产品） #

现有代码仅能处理指定产品（如Product='BB'），无法批量处理所有产品组：

SELECT d.CreateDate,
COALESCE(Price, AVG(Price) OVER ()) as New_Price,
m.*
FROM #DateRange d
LEFT OUTER JOIN #mytable m on d.CreateDate = m.CreateDate
AND Product = 'BB'

问题 #

如何遍历每个Product组，无需显式指定单个产品（如Product='BB'），即可实现用缺失值前后有效数值的均值填充的逻辑？

解决方案 #

通过生成「所有产品×完整日期」的笛卡尔积，再结合LAG()和LEAD()窗口函数获取每个缺失值的前后有效价格，最后计算均值填充：

-- 生成所有产品与完整日期的组合
WITH ProductDateCombination AS (
    SELECT 
        dr.CreateDate,
        p.Product
    FROM #DateRange dr
    CROSS JOIN (SELECT DISTINCT Product FROM #mytable) p
),
-- 关联原表并获取前后有效价格
ProductPriceWithLagLead AS (
    SELECT 
        pdc.CreateDate,
        pdc.Product,
        mt.Price,
        -- 获取当前产品组中，上一个时间点的有效Price
        LAG(mt.Price) OVER (PARTITION BY pdc.Product ORDER BY pdc.CreateDate) AS PrevPrice,
        -- 获取当前产品组中，下一个时间点的有效Price
        LEAD(mt.Price) OVER (PARTITION BY pdc.Product ORDER BY pdc.CreateDate) AS NextPrice
    FROM ProductDateCombination pdc
    LEFT JOIN #mytable mt 
        ON pdc.CreateDate = mt.CreateDate 
        AND pdc.Product = mt.Product
)
-- 计算填充后的价格
SELECT 
    CreateDate,
    Product,
    COALESCE(
        Price,
        -- 用前后值的均值填充，若只有前/后值则取存在的那个
        CASE 
            WHEN PrevPrice IS NOT NULL AND NextPrice IS NOT NULL THEN (PrevPrice + NextPrice)/2
            WHEN PrevPrice IS NOT NULL THEN PrevPrice
            WHEN NextPrice IS NOT NULL THEN NextPrice
            ELSE NULL -- 极端情况：该产品无任何有效价格
        END
    ) AS FilledPrice
FROM ProductPriceWithLagLead
ORDER BY Product, CreateDate

核心逻辑说明 #

• ProductDateCombination CTE：生成每个产品对应所有时间点的完整组合，确保没有缺失的日期行。
• ProductPriceWithLagLead CTE：通过PARTITION BY Product按产品分组，用LAG()和LEAD()分别获取当前时间点的上一个、下一个有效价格。
• 最终查询中，用COALESCE优先保留原Price，缺失时根据前后值的存在情况计算均值（或仅取存在的单个值）。

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