MySQL聚合关联查询实现：按每个rank计算大于当前rank的score平均值

原SQL错误原因 #

• 派生表（join后面的子查询）无法直接访问外层查询的t1.rank字段，属于语法错误
• 求平均的字段名错误，表中存分数字段是score，你错误使用了Revenue_Amount
• 筛选条件逻辑写反：你要求的是统计rank大于当前rank的记录，应该是t2.rank > t1.rank，原语句写反了
• join语句没有写关联条件，即使前面问题修复也会生成笛卡尔集，结果完全错误

方案1：关联子查询写法（兼容所有SQL版本，逻辑直观） #

直接在查询字段中使用关联子查询逐行计算对应平均值：

SELECT
  `rank`,
  (SELECT AVG(score) 
   FROM `Table` t2 
   WHERE t2.`rank` > t1.`rank`) AS average_score_of_greater_ranks
FROM `Table` t1
ORDER BY `rank`;

如果需要把最大rank对应的NULL（没有比它更大的rank）替换为0，可以使用COALESCE函数：

SELECT
  `rank`,
  COALESCE((SELECT AVG(score) FROM `Table` t2 WHERE t2.`rank` > t1.`rank`), 0) AS average_score_of_greater_ranks
FROM `Table` t1
ORDER BY `rank`;

方案2：窗口函数写法（支持MySQL 8.0+/PostgreSQL/SQL Server等现代数据库，性能更优） #

利用窗口函数的滑动框架特性，单次扫表即可完成计算，适合大数据量场景：

SELECT
  `rank`,
  AVG(score) OVER (ORDER BY `rank` DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS average_score_of_greater_ranks
FROM `Table`
ORDER BY `rank`;

逻辑说明：按rank倒序排序后，窗口范围取从数据集开头到当前行的前一行，正好覆盖所有rank大于当前值的记录，直接求平均即可。

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