求助：基于ItemNo与Ref的特定ItemValue平均SQL实现

需求说明：
从ItemDetails表查询数据时，需按以下逻辑处理ItemValue字段：

• 当同一Ref下的同一ItemNo对应的所有ItemValue均相同时，将这些行的ItemValue替换为总和除以行数的平均值
• 若同一Ref下同一ItemNo的ItemValue存在不同值，或同一ItemNo对应不同Ref，则保持ItemValue原值不变

当前使用的SQL语句无法满足需求：

SELECT ItemNo, AVG(ItemValue) OVER (PARTITION BY ItemNo) / COUNT(*) OVER(PARTITION BY ItemNo) AS ItemValue 
FROM ItemDetails ;

示例1：同一Ref下同一ItemNo的ItemValue全部相同 #

输入数据 #

预期输出 #

示例2：同一Ref下同一ItemNo的ItemValue存在不同值 #

输入数据 #

预期输出 #

示例3：同一ItemNo对应不同Ref（ItemValue相同） #

输入数据 #

预期输出 #

注：行重复是由其他列导致的。

解决方案SQL #

SELECT 
    ItemNo,
    CASE
        -- 同一Ref+ItemNo分组内所有ItemValue相同，计算平均值
        WHEN MIN(ItemValue) OVER (PARTITION BY Ref, ItemNo) = MAX(ItemValue) OVER (PARTITION BY Ref, ItemNo)
        THEN SUM(ItemValue) OVER (PARTITION BY Ref, ItemNo) / COUNT(*) OVER (PARTITION BY Ref, ItemNo)
        -- 其他情况保留原值
        ELSE ItemValue
    END AS ItemValue,
    Ref
FROM ItemDetails;

逻辑说明 #

1. 以Ref和ItemNo作为分组依据（PARTITION BY Ref, ItemNo），确保只处理同一Ref下的同一ItemNo组合
2. 通过MIN(ItemValue)和MAX(ItemValue)判断分组内所有值是否一致：若两者相等，说明所有ItemValue相同
3. 符合条件时，用分组内的总和除以行数得到平均值；不符合则直接返回原ItemValue

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