SQL不使用子查询基于去重id对应行计算平均值的方法

解决方案 #

你需要的无嵌套子查询实现可以通过QUALIFY子句过滤每个id的唯一行后直接计算平均值，通用写法如下：

SELECT AVG(value) AS avg
FROM your_table
QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) = 1;

原理解释 #

• PARTITION BY id 按id对数据分组，相同id的行归属同一个分组
• ROW_NUMBER() OVER (...) 给每个分组内的行按顺序生成从1开始的序号
• QUALIFY 子句直接过滤出每个分组内序号为1的行，即每个id仅保留1行数据，无需额外嵌套子查询
• 最后用AVG(value)对过滤后的行计算平均值，你的示例数据执行后结果为20，完全符合需求。

数据库不支持QUALIFY的兼容写法 #

如果你使用的数据库不支持QUALIFY语法（比如MySQL 8.0及更低版本），且你的数据满足同一个id对应的value值完全相同的前提（你的示例数据符合该特征），可以用下面的写法实现：

SELECT SUM(DISTINCT id * value) / SUM(DISTINCT id) AS avg FROM your_table;

该写法逻辑：相同id的id*value结果完全一致，用SUM(DISTINCT)可以实现每个id的value仅累加一次，除以SUM(DISTINCT id)得到的去重后id总和，最终输出正确的平均值。

原有写法的问题 #

你的思路本身是正确的，但SQL的执行顺序决定了窗口函数的计算晚于聚合函数，因此语法上不允许将窗口函数嵌套在聚合函数的参数内部，所以你的写法无法执行。

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