SQL实现分组加权均值计算 查找与指定标题最接近的2条记录

原有SQL问题点 #

1. 计算输入title（Australia）的加权总和时，子查询返回了3行拆分的加权值，没有做聚合，无法直接参与数值运算
2. 差值计算逻辑错误：正确逻辑应该是ABS(当前title的average - 目标title的average)，原写法错误写成了ABS(当前title加权和) - 目标title加权值集合
3. 重复UNION ALL的写法冗余，可通过CASE WHEN简化加权计算逻辑
4. 预期返回3条结果，原SQL写的LIMIT 2不符合返回要求

修正后SQL #

WITH title_agg AS (
    SELECT
        title,
        SUM(
            CASE contesting
                WHEN 'yes' THEN value * 1
                WHEN 'no' THEN value * 2
                WHEN 'undecided' THEN value * 3
            END
        ) / 3 AS average
    FROM Table1
    WHERE type = 'A'
    GROUP BY title
)
SELECT
    title,
    average
FROM title_agg
ORDER BY
    -- 保证输入的目标title排在首位
    title = 'Australia' DESC,
    -- 按与目标title的average差值绝对值升序排列，取最接近的记录
    ABS(average - (SELECT average FROM title_agg WHERE title = 'Australia')) ASC
LIMIT 3;

如果你的表中字段名实际为测试数据里的Competing而非contesting，把CASE后的字段名替换成对应字段即可。

结果验证 #

基于提供的测试数据计算：

• Australia加权和：26*1 + 162*2 + 37*3 = 462，average = 462/3 = 154
• Canada加权和：29*1 + 164*2 + 40*3 = 477，average = 477/3 = 159
• Turkey加权和：29*1 + 145*2 + 24*3 = 391，average ≈ 130
  执行后返回结果和预期完全一致。

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