使用COALESCE和PARTITION BY更新NULL值时遇窗口函数错误求助

需求说明 #

需将表中同一Item（物品）和DateAdded（添加日期）分组下的SalePrice（售价）、SaleDate（销售日期）列的NULL值，用同组内对应列的非NULL值填充，更新前后的数据示例如下：

更新前 #

更新后 #

原SQL错误分析 #

原SQL尝试直接在UPDATE的SET子句中使用窗口函数，触发报错：

Windowed functions can only appear in the SELECT or ORDER BY clauses.

错误原因：SQL语法规定，窗口函数（如MAX() OVER()）只能出现在SELECT或ORDER BY子句中，不能直接用于UPDATE的SET部分。此外原SQL中COALESCE(SaleDate, MAX(...))属于冗余写法，ISNULL已经完成了NULL判断。

正确实现方案 #

方案1：使用CTE（公共表表达式） #

先通过CTE获取每组的有效非NULL值，再关联原表进行更新：

WITH GroupedValues AS (
    SELECT 
        Item,
        CAST(DateAdded AS DATE) AS DateAddedDate,
        MAX(SalePrice) AS GroupSalePrice,
        MAX(SaleDate) AS GroupSaleDate
    FROM table1
    WHERE CAST(DateAdded AS DATE) > '2024-01-01'
    GROUP BY Item, CAST(DateAdded AS DATE)
    -- 过滤掉无有效数据的分组，避免无效更新
    HAVING MAX(SalePrice) IS NOT NULL AND MAX(SaleDate) IS NOT NULL
)
UPDATE t1
SET 
    SalePrice = ISNULL(t1.SalePrice, gv.GroupSalePrice),
    SaleDate = ISNULL(t1.SaleDate, gv.GroupSaleDate)
FROM table1 t1
JOIN GroupedValues gv 
    ON t1.Item = gv.Item 
    AND CAST(t1.DateAdded AS DATE) = gv.DateAddedDate
WHERE CAST(t1.DateAdded AS DATE) > '2024-01-01'

方案2：使用子查询 #

逻辑与CTE一致，改用子查询实现：

UPDATE t1
SET 
    SalePrice = ISNULL(t1.SalePrice, gv.GroupSalePrice),
    SaleDate = ISNULL(t1.SaleDate, gv.GroupSaleDate)
FROM table1 t1
JOIN (
    SELECT 
        Item,
        CAST(DateAdded AS DATE) AS DateAddedDate,
        MAX(SalePrice) AS GroupSalePrice,
        MAX(SaleDate) AS GroupSaleDate
    FROM table1
    WHERE CAST(DateAdded AS DATE) > '2024-01-01'
    GROUP BY Item, CAST(DateAdded AS DATE)
    HAVING MAX(SalePrice) IS NOT NULL AND MAX(SaleDate) IS NOT NULL
) gv ON t1.Item = gv.Item AND CAST(t1.DateAdded AS DATE) = gv.DateAddedDate
WHERE CAST(t1.DateAdded AS DATE) > '2024-01-01'

方案说明 #

• 先按Item和DateAdded（转换为DATE类型，确保日期分组准确）分组，通过MAX()获取每组的有效非NULL值（因为每组仅存在一个非NULL值，MAX()可直接提取该值）
• 用HAVING子句过滤掉无有效数据的分组（如示例中2024-02-06的Banana），避免将NULL值更新到其他行
• 关联原表与分组结果，使用ISNULL()判断原字段是否为NULL，是则替换为组内的有效值

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