在数据分析和报表开发场景中，计算某一列的平均值并与每行数据进行对比是一个极其常见的需求。传统实现方式通常依赖标量子查询或相关子查询来完成这种「全局平均值 + 明细行」的模式，然而这类写法在数据量增大时会暴露出严重的性能问题。本文将从工程实践角度出发，详细阐述如何通过 SQL 原生聚合函数与窗口函数的组合，彻底消除重复扫描带来的性能损耗，并给出可落地的关键参数配置。
子查询方式的性能瓶颈
让我们先看一个典型的业务场景：某电商平台需要统计每个用户的订单金额，同时展示所有订单的平均金额用于对比。传统写法可能是这样的：
SELECT
user_id,
order_amount,
(SELECT AVG(order_amount) FROM orders) AS avg_amount
FROM orders
WHERE status = 'completed';
这种标量子查询的写法看似简洁，但在实际执行时，数据库需要对每一条结果行单独执行一次子查询。如果主查询返回十万条记录，那么 AVG () 聚合计算会被执行十万次，这是典型的相关子查询性能陷阱。即使数据库优化器能够将子查询提升为物化视图或公共表达式，其计算开销仍然不可忽视。在 PostgreSQL 和 MySQL 的执行计划中，这种查询通常表现为 Nested Loop Join 配合重复的 Seq Scan，在大表上可能产生数秒甚至数十秒的延迟。
窗口函数的优雅解决方案
现代关系型数据库均已支持窗口函数，这一特性为上述问题提供了优雅且高效的解决思路。窗口函数的核心优势在于：它能够在保持结果集维度不变的前提下，对一组行进行聚合计算，并且只扫描一次数据即可完成全部运算。具体到平均值计算的场景，使用 AVG () OVER () 语法即可实现「一次计算，处处复用」的效果：
SELECT
user_id,
order_amount,
AVG(order_amount) OVER () AS avg_amount
FROM orders
WHERE status = 'completed';
上述查询的执行计划只会产生一次对 orders 表的顺序扫描，AVG () 聚合操作作为窗口函数在扫描过程中即时完成，结果被附加到每一行返回。这意味着无论主查询返回多少行，聚合计算只执行一次，时间复杂度从 O (N×M) 降低至 O (N)，其中 N 为主查询返回行数，M 为子查询的聚合计算次数。在实际的 PostgreSQL 环境中测试对比，十万级数据量下窗口函数方案的响应时间通常比子查询方案快 5 到 10 倍。
分组平均值的进阶用法
如果业务需求进阶为「按月份统计每个用户的订单金额，同时展示该月的全局平均值」，窗口函数同样能够胜任。此时需要引入 PARTITION BY 子句来限定聚合范围：
SELECT
DATE_TRUNC('month', created_at) AS month,
user_id,
order_amount,
AVG(order_amount) OVER (PARTITION BY DATE_TRUNC('month', created_at)) AS monthly_avg
FROM orders
WHERE status = 'completed';
这个查询在保持单次扫描的同时，精准地计算了每个月份的平均值。对于需要同时展示年度累计平均值和月度平均值的复杂报表，也可以在同一个查询中定义多个窗口函数，彼此之间互不干扰：
SELECT
DATE_TRUNC('month', created_at) AS month,
user_id,
order_amount,
AVG(order_amount) OVER (PARTITION BY DATE_TRUNC('month', created_at)) AS monthly_avg,
AVG(order_amount) OVER () AS yearly_avg
FROM orders
WHERE status = 'completed';
执行计划显示，数据库在单次扫描中完成了两个窗口函数的计算，额外开销几乎可以忽略不计。
CTE 方案与适用场景
对于某些遗留系统或不支持窗口函数的数据库版本，可以使用公共表表达式（CTE）配合 CROSS JOIN 的方式达到类似效果：
WITH global_avg AS (
SELECT AVG(order_amount) AS avg_amount
FROM orders
WHERE status = 'completed'
)
SELECT
o.user_id,
o.order_amount,
g.avg_amount
FROM orders o
CROSS JOIN global_avg g
WHERE o.status = 'completed';
这种写法将聚合计算提取到 CTE 中，只执行一次然后与主表进行连接。虽然执行计划与窗口函数略有不同（通常是 Hash Join 或 Merge Join），但在实际性能表现上两者处于同一量级。在 DuckDB 和 ClickHouse 等面向分析型场景的数据库中，CTE 方案甚至可能因为向量化执行的优化而略占优势。
工程实践关键参数
在生产环境中部署上述优化时，以下参数和监控点值得关注。首先是执行计划分析：在 PostgreSQL 中使用 EXPLAIN ANALYZE 观察 total planning time 和 actual total time，确保查询没有产生重复的 Seq Scan 或 Index Scan。在 MySQL 8.0+ 中，同样建议通过 EXPLAIN FORMAT=JSON 获取详细的执行成本。其次是索引策略：虽然窗口函数的聚合不依赖索引，但若查询中包含 WHERE 条件过滤，创建合适的索引能够显著减少扫描数据量。对于「按月份分组计算平均值」的场景，建议在 created_at 和 status 列上建立复合索引。
对于数据量超过亿级的超大型表，可以考虑使用物化视图预先计算常用维度的平均值。物化视图的刷新策略需要根据业务对数据新鲜度的要求进行配置：对于实时性要求较高的场景，建议使用 REFRESH ON COMMIT；对于可接受小时级延迟的场景，可以使用定时任务按需刷新。在 ClickHouse 等列式存储数据库中，物化视图的物化聚合效果尤为显著，能够将复杂的多次聚合查询转化为简单的点查。
迁移检查清单
将现有子查询方案迁移至窗口函数或 CTE 方案时，建议按以下清单进行验证。确保业务逻辑完全一致：窗口函数的 PARTITION BY 分区键必须与原子查询的过滤条件等价。验证空值处理：AVG () 默认忽略 NULL 值，这与某些数据库的子查询行为可能存在差异，必要时可使用 COALESCE 或 NVL 进行兜底。执行回归测试：在相同的测试数据集上对比优化前后的查询结果，确保数值完全一致后再部署上线。
综上所述，使用 SQL 原生的 AVG () 聚合函数配合窗口函数或 CTE 方案，能够将原本 O (N×M) 复杂度的重复计算优化为 O (N) 的单次扫描。在百万级数据量场景下，这种优化的性能提升通常是数量级的。对于工程团队而言，关键在于识别这类「全局聚合 + 明细行」的业务模式，并在代码审查阶段强制执行窗口函数的使用规范。
资料来源：SQL Team 论坛关于 average 聚合函数的讨论、Stack Overflow 上关于 SQL 查询优化的技术问答、AWS 数据库博客对 Amazon Aurora PostgreSQL 相关子查询优化的分析。