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：
| 指标 | 普通聚合 | 窗口函数 |
|---|---|---|
| 中间结果行数 | 10行 | 100万行 |
| 排序开销 | 无 | O(100万 log 100万) |
| 内存占用 | ~KB级别 | ~MB到GB级别 |
| 最终输出 | 10行 | 10行（去重后） |
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、内存、网络传输上都是成本。
