PostgreSQL行级Lift对比同年份区域均值打标SQL报错解决

现有业务数据表核心字段与计算逻辑如下：

• 原始字段：Shop、Year、Region、Waste
• 预计算字段1：Avg_Waste_YearRegion，按Year、Region分组计算的组内平均Waste
• 预计算字段2：Lift，行级指标，计算逻辑为当前行Waste / 同Year+Region组的平均Waste
• 目标列Column_I_want_To_Calculate计算规则：当前行Lift大于同Year、Region组内的平均Lift时赋值1，否则赋值0
• 执行异常：运行PostgreSQL查询时报错more than one row returned by a subquery used as an expression

这个错误的触发原因非常明确：SQL中被当做单值表达式使用的子查询返回了多行结果。绝大多数情况是计算组内平均Lift时，没有使用窗口函数，而是写了普通分组聚合的子查询，且没有做好单行匹配，导致数据库给单行数据赋值时匹配到多个同组结果，无法完成计算。

直接使用窗口函数完成计算即可，不需要嵌套多余子查询，性能和可读性都更好。
如果是从原始表一步计算所有字段，参考代码如下：

SELECT
    Shop,
    Year,
    Region,
    Waste,
    AVG(Waste) OVER (PARTITION BY Year, Region) AS Avg_Waste_YearRegion,
    Waste / AVG(Waste) OVER (PARTITION BY Year, Region) AS Lift,
    CASE 
        WHEN (Waste / AVG(Waste) OVER (PARTITION BY Year, Region)) 
            > AVG(Waste / AVG(Waste) OVER (PARTITION BY Year, Region)) OVER (PARTITION BY Year, Region) 
        THEN 1 
        ELSE 0 
    END AS Column_I_want_To_Calculate
FROM your_business_table;

如果你已经通过CTE或子查询提前算好了Lift字段，写法可以更简洁：

WITH pre_calc_result AS (
    SELECT
        Shop,
        Year,
        Region,
        Waste,
        AVG(Waste) OVER (PARTITION BY Year, Region) AS Avg_Waste_YearRegion,
        Waste / AVG(Waste) OVER (PARTITION BY Year, Region) AS Lift
    FROM your_business_table
)
SELECT
    *,
    CASE WHEN Lift > AVG(Lift) OVER (PARTITION BY Year, Region) THEN 1 ELSE 0 END AS Column_I_want_To_Calculate
FROM pre_calc_result;

以下是触发该报错的典型错误写法，不要直接把返回多行结果的分组子查询当做单值和行级字段做比较：

-- 错误示例：子查询返回所有分组的平均Lift，是多行结果，无法直接用于单行判断
SELECT
    *,
    Waste / (SELECT AVG(Waste) FROM your_table t2 WHERE t2.Year = t1.Year AND t2.Region = t1.Region) AS Lift,
    -- 下面这行就会触发报错
    CASE WHEN Lift > (SELECT AVG(lift_val) FROM (SELECT Waste/(SELECT AVG(Waste) FROM your_table t3 WHERE t3.Year = t4.Year AND t3.Region = t4.Region) AS lift_val FROM your_table t4) a WHERE a.Year = t1.Year AND a.Region = t1.Region) THEN 1 ELSE 0 END
FROM your_table t1;

如果非要用子查询关联的方式实现，必须保证分组聚合子查询每个分组只返回一行结果，再通过JOIN关联回主表，但是这种写法性能远低于窗口函数方案，不推荐使用。

额外优化提示：按照你给出的Lift计算逻辑，同Year、Region分组下的Lift平均值恒等于1。推导逻辑很简单：组内所有行Waste之和除以组平均Waste，结果等于组内数据行数，也就是组内Lift的求和值等于组内行数，除以行数得到的平均Lift必然是1。所以你的目标列本质是标记该行Waste是否高于同组平均Waste，可以直接简化判断逻辑为CASE WHEN Waste > AVG(Waste) OVER (PARTITION BY Year, Region) THEN 1 ELSE 0 END，减少重复计算，执行效率更高。

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