SQL示例：为什么普通聚合比窗口函数更高效？

SQL聚合查询优化分析：比较普通聚合与窗口函数在统计岗位简历数量时的性能差异。

---

普通聚合（GROUP BY）只需一次数据扫描和计算，内存占用低（仅维护不同job的哈希表），直接输出分组结果（如10行）。

---

窗口函数（OVER）虽然计算结果相同，但必须为每行数据（如100万行）计算并维护窗口聚合，需要额外排序和内存开销，最后还需DISTINCT去重。

---

测试表明，对于简单分组统计，普通聚合效率更高，因其避免冗余计算和大量中间结果处理。

---

窗口函数适用于需要保留明细数据的场景，而普通聚合更适合纯汇总需求。

SQL示例

题目

SQL283 实习广场投递简历分析(一)

---

描述

在牛客实习广场有很多公司开放职位给同学们投递，同学投递完就会把简历信息存到数据库里。

现在有简历信息表(resume_info)，部分信息简况如下:

id	job	date	num
1	C++	2025-01-02	53
2	Python	2025-01-02	23
3	Java	2025-01-02	12
4	Java	2025-02-03	24
5	C++	2025-02-03	23
6	Python	2025-02-03	34
7	Python	2025-03-04	54
8	C++	2025-03-04	65
9	Java	2025-03-04	92
10	Java	2026-01-04	230

第1行表示，在2025年1月2号，C++岗位收到了53封简历

。。。

最后1行表示，在2026年1月4号，Java岗位收到了230封简历

请你写出SQL语句查询在2025年内投递简历的岗位和数量，并且按数量降序排序，以上例子查询结果如下:

job	cnt
C++	141
Java	128
Python	111

注意：MySQL用YEAR(date)，Oracle用TO_CHAR(date,'YYYY') = '2025'

解答

select distinct job, sum(num) over(partition by job) as cnt from resume_info where year(date)=2025 order by cnt desc; --使用普通聚合（更高效） SELECT job, SUM(num) AS cnt FROM resume_info WHERE YEAR(date) = 2025 GROUP BY job ORDER BY cnt DESC;

为什么普通聚合比窗口函数更高效？

普通聚合比窗口函数更高效，主要有以下几个原因：

1.只需一次数据扫描和一次计算

普通聚合：

sql

SELECT job, SUM(num) AS cnt FROM resume_info WHERE YEAR(date) = 2025 GROUP BY job

• 数据扫描1次

• 每个job只计算1次SUM

• 输出结果行数 = 不重复的job数量（如10行）

窗口函数：

sql

SELECT DISTINCT job, SUM(num) OVER(PARTITION BY job) AS cnt FROM resume_info WHERE YEAR(date) = 2025

• 数据扫描1次

• 但需要为每一行都计算和维护窗口聚合

• 中间结果行数 = 原始数据行数（如100万行）

• 最后再用DISTINCT去重

2.内存和排序开销不同

普通聚合

• 使用Hash Aggregate（哈希聚合）

• 内存中维护一个哈希表，key是job

• 直接累加，无需排序

• 内存占用：O(不同job数量)

窗口函数

• 需要先按PARTITION BY字段排序（隐式或显式）

• 维护窗口缓冲区

• 内存占用：O(数据总量) 或需要磁盘溢出

• 额外排序开销：O(n log n)

3.实际执行计划对比

普通聚合执行计划

text

-> HashAggregate (job, SUM(num)) -> Table Scan (filter: YEAR=2025)

• 简单直接，无额外操作

窗口函数执行计划

text

-> Unique (DISTINCT去重) ← 额外操作 -> WindowAgg (SUM OVER) ← 为每行计算 -> Sort (按job排序) ← 额外排序 -> Table Scan

4.数据量对比示例

假设有100万条简历数据，10种不同的job：

5.使用窗口函数的场景

窗口函数虽然低效，但在需要保留明细行时不可替代：

sql

-- 需要每个岗位的平均薪资，同时保留每个人原数据 SELECT name, job, salary, AVG(salary) OVER(PARTITION BY job) AS avg_job_salary FROM employees

总结

• 普通聚合：只关心每个组的汇总结果 → 用GROUP BY

• 窗口函数：需要同时看到明细和汇总 → 用OVER()

对于你的需求（统计每个job的总num），普通聚合是正确且高效的选择。

窗口函数需要为每一行都计算和维护窗口聚合，计算结果不是相同的吗，为什么需要多次计算

这确实是个容易混淆的地方。让我详细解释：

窗口函数的核心特性

计算结果确实相同，但数据库无法提前知道每行的计算结果是否相同，所以必须为每一行都计算一次。

为什么不能只计算一次？

1.窗口函数设计为返回行级结果

sql

SELECT name, -- 张三 job, -- 工程师 salary, -- 30000 SUM(salary) OVER(PARTITION BY job) AS total_salary -- 工程师组总计: 90000 FROM employees;

预期输出：

text

name job salary total_salary 张三 工程师 30000 90000 ← 第1行 李四 工程师 35000 90000 ← 第2行（相同值，但必须出现） 王五 工程师 25000 90000 ← 第3行（相同值，但必须出现） 赵六 销售 40000 40000 ← 第4行（不同组）

数据库的问题是：它不知道最终需要输出多少行，只能老老实实地为每一行计算。

2.PARTITION BY可以非常复杂

sql

-- 滑动窗口：每组内计算累加和 SELECT date, sales, SUM(sales) OVER(PARTITION BY product ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_sum FROM sales;

这种场景下，每行的计算结果都不同，所以必须逐行计算。

3.数据库的通用实现策略

数据库的优化器为了处理各种复杂场景，采用了统一的执行模型：

text

原始数据 (100万行) ↓ 【排序】按PARTITION BY字段排序 ↓ 【窗口聚合】为每一行计算 - 维护当前窗口的状态 - 每读一行，更新聚合值 - 为这一行输出结果 ↓ 输出 100万行 (每行都带有窗口聚合结果)

理论上可以优化吗？

可以，但数据库一般不做这个优化，因为：

优化方案的复杂性

sql

-- 如果数据库想做优化 -- 1. 先分析窗口函数：SUM(num) OVER(PARTITION BY job) -- 2. 发现没有ORDER BY，窗口固定 -- 3. 改为：先聚合出每个job的SUM，再回连原表 -- 等价于： WITH job_total AS ( SELECT job, SUM(num) AS cnt FROM resume_info GROUP BY job ) SELECT r.job, j.cnt FROM resume_info r JOIN job_total j ON r.job = j.job;

为什么不自动做这个优化？

• 可能改变语义（如果有DISTINCT或复杂WHERE条件）

• 优化器需要额外开销来判断是否值得

• 窗口函数语法保证行为一致，优化可能引入bug

实际测试对比

假设一个简单的场景：

sql

-- 100万行数据，10个分组 SELECT job, SUM(num) OVER(PARTITION BY job) FROM resume_info;

理论上：

• 理想优化：计算10次SUM，输出100万行相同值

• 实际执行：扫描100万行，在内存中维护10个累加器，为100万行都输出一次

虽然结果是100万行相同的数字，但数据库必须产生100万行输出，这个过程本身就比只输出10行要慢得多。

总结

你的理解是对的：值相同，但行数不同。

• 普通聚合：输出10行，每行一个结果

• 窗口函数：输出100万行，每行都附加一个结果（虽然很多行结果相同）

这就是为什么窗口函数更慢的原因：不是计算本身慢，而是要为每一行都产生输出，这些输出在CPU、内存、网络传输上都是成本。