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

SQL聚合查询优化分析比较普通聚合与窗口函数在统计岗位简历数量时的性能差异。普通聚合GROUP BY只需一次数据扫描和计算内存占用低仅维护不同job的哈希表直接输出分组结果如10行。窗口函数OVER虽然计算结果相同但必须为每行数据如100万行计算并维护窗口聚合需要额外排序和内存开销最后还需DISTINCT去重。测试表明对于简单分组统计普通聚合效率更高因其避免冗余计算和大量中间结果处理。窗口函数适用于需要保留明细数据的场景而普通聚合更适合纯汇总需求。SQL示例题目SQL283 实习广场投递简历分析(一)描述在牛客实习广场有很多公司开放职位给同学们投递同学投递完就会把简历信息存到数据库里。现在有简历信息表(resume_info)部分信息简况如下:idjobdatenum1C2025-01-02532Python2025-01-02233Java2025-01-02124Java2025-02-03245C2025-02-03236Python2025-02-03347Python2025-03-04548C2025-03-04659Java2025-03-049210Java2026-01-04230第1行表示在2025年1月2号C岗位收到了53封简历。。。最后1行表示在2026年1月4号Java岗位收到了230封简历请你写出SQL语句查询在2025年内投递简历的岗位和数量并且按数量降序排序以上例子查询结果如下:jobcntC141Java128Python111注意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.只需一次数据扫描和一次计算普通聚合sqlSELECT job, SUM(num) AS cnt FROM resume_info WHERE YEAR(date) 2025 GROUP BY job数据扫描1次每个job只计算1次SUM输出结果行数 不重复的job数量如10行窗口函数sqlSELECT 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: YEAR2025)简单直接无额外操作窗口函数执行计划text- Unique (DISTINCT去重) ← 额外操作 - WindowAgg (SUM OVER) ← 为每行计算 - Sort (按job排序) ← 额外排序 - Table Scan4.数据量对比示例假设有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.窗口函数设计为返回行级结果sqlSELECT name, -- 张三 job, -- 工程师 salary, -- 30000 SUM(salary) OVER(PARTITION BY job) AS total_salary -- 工程师组总计: 90000 FROM employees;预期输出textname 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、内存、网络传输上都是成本。