PostgreSQL计算加权平均时提示字段不存在错误如何解决

• 别名引用规则限制：SQL 执行时SELECT子句的解析晚于聚合、分组逻辑，你在当前SELECT中定义的别名numberXWeightingFactor无法直接在同层级的聚合函数中调用，因此会提示列不存在。
• 原SQL存在多处语法错误：
  • 第二个WHEN的判断条件缺少AND关键字，正确写法为WHEN value <= 0.15 AND value > 0.1 THEN 'good'
  • 第三个WHEN中将字段名value错写为valuey
  • GROUP BY指定的分组字段是stars，但查询中分组维度是自定义的ratings，逻辑不匹配
  • 重复编写计算总抵押品数量的子查询，代码冗余且执行效率低

你可以通过CTE（公用表表达式）先计算中间结果，外层再直接调用中间字段做聚合即可解决别名引用问题，以下是修正后的完整SQL：

-- 先提前计算总抵押品数量，避免重复查询
WITH total_collateral AS (
    SELECT COUNT(DISTINCT collateral_id) AS total_num
    FROM triggered.table
    WHERE id = 386
),
-- 计算每个评分对应的数量、权重
rating_metrics AS (
    SELECT 
        CASE
            WHEN value <= 0.1 THEN 'very good'
            WHEN value <= 0.15 AND value > 0.1 THEN 'good'
            WHEN value <= 0.20 AND value > 0.15 THEN 'not that good'
            WHEN value <= 0.25 AND value > 0.20 THEN 'bad'
            ELSE 'very bad'
        END AS ratings,
        COUNT(*) AS item_count,
        COUNT(*) * 1.0 / tc.total_num AS weight
    FROM triggered.table t
    CROSS JOIN total_collateral tc
    WHERE t.id = 386
    GROUP BY ratings, tc.total_num
)
-- 最终计算加权平均值
SELECT SUM(item_count * weight) AS weighted_average
FROM rating_metrics;

注：代码中的*1.0是为了避免部分数据库的整数除法规则导致精度丢失，若你使用的数据库默认支持浮点数除法可删除该写法。

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