MySQL中基于item列去重后计算平均值的最高效方法

假设我们有表A，结构和数据如下：

需求是按item去重后计算平均价格，示例结果应为1.5（即(1+2)/2）。原来的实现用了嵌套子查询：

SELECT AVG(price) FROM (SELECT MIN(price) FROM A GROUP BY item)

但面对超大规模数据时，这个子查询方案效率太低，下面是几个更高效的替代方案：

方案1：窗口函数过滤重复项 #

用ROW_NUMBER()给每个item分组里的记录标序号，只保留每组第一条来计算平均，避免子查询带来的额外开销：

SELECT AVG(price)
FROM (
    SELECT price,
           ROW_NUMBER() OVER (PARTITION BY item ORDER BY id) AS rn
    FROM A
) t
WHERE rn = 1;

如果同一个item对应的price完全一致（比如示例里的banana价格都是1），还能更简单：

方案2：DISTINCT直接去重计算 #

SELECT AVG(DISTINCT price)
FROM A;

⚠️ 注意：这个方案只适合同一item的price完全相同的场景。如果同一item有不同price，得先确定取哪个价格（比如MIN/MAX）再去重，这时候方案1更通用。

性能优化要点 #

• 给item列建索引：不管是分组还是窗口函数分区，索引能极大提升大数据量下的查询速度。
• 优先用窗口函数（如果数据库支持，比如MySQL 8.0+、PostgreSQL）：窗口函数的执行计划通常比嵌套子查询更高效，减少临时表的创建。
• 无价格差异时选DISTINCT：逻辑简单，性能最优。

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