开头直接切入SQL聚合是数据分析最常用的能力也是高频面试题。分享自己刷“高频SQL 50题”中聚合部分的经验拆解考点、常见场景、性能陷阱并附实战SQL。注意每段150字以上总5000字以上。需要认真扩展。 换一个技术圈里比较有意思的现象刷过LeetCode SQL题库或者各种“高频SQL 50题”的人十有八九都会卡在“聚合”这一块。不是因为它难而是因为平时写业务SQL时大多数情况只是拿COUNT、SUM、AVG来糊弄个报表数字真到了面试那种“给你一张表、一个业务场景、让你写出统计结果”的题目才会发现聚合这玩意儿藏着大量细节。聚合查询表面上是SQL里最“基础”的功能但恰恰是它决定了你能不能高效处理数据。我见过不少工作三五年的开发写复杂报表时还在用“一张临时表多个子查询各种JOIN”硬编效率低且容易出错。这篇文章就把我刷“高频SQL 50题”里聚合部分的一些心得整理出来同时结合日常工作踩过的坑把聚合函数、分组逻辑、窗口函数、性能优化一次性讲透。不管你是准备面试、在做数据分析还是每天跟报表打交道的后端开发这篇内容应该都能帮你在聚合这条路上少走弯路。1. 聚合SQL到底考什么从高频50题看考点分布先把“高频SQL 50题”里聚合相关的题拉出来看一眼你会发现考点非常集中。拿我自己刷过的版本来说聚合相关的题目大概占了三成主要分布在几个方向1.1 最基础的三件套COUNT、SUM、AVG这三个函数看起来简单实际上每个都有坑。COUNT()和COUNT(列)的区别多少人面试第一问就被问倒了COUNT()是统计所有行数包括NULL值的行COUNT(列)是统计该列非NULL值的行数。SUM(列)会忽略NULL但如果整列都是NULLSUM返回NULL而不是0。AVG同样忽略NULL计算时不会把NULL当作0去参与总和这跟很多人的直觉不一样。高频题里常见套路是给你一张订单表、一张用户表让你统计每个用户的订单数、订单总额、平均订单金额。这题看似简单但能考察的点很多需不需要保留没有订单的用户如果用LEFT JOIN那COUNT字段时要不要做空值处理深挖下去就能看出你到底是在背SQL还是在理解SQL。我建议入门者先把这三件套吃透尤其搞清楚它们的NULL语义。别小看这个很多线上统计Bug就是从这里来的。1.2 分组聚合与HAVING的隐藏细节有了聚合函数自然会引出一个经典问题如果要在聚合之后再过滤用WHERE还是HAVING这个考点几乎每套题都有。标准答案是WHERE在分组前过滤HAVING在分组后过滤。很多人背下来了但真写的时候还是会搞混。比如“查出订单数大于5的用户”正确的写法一定是GROUP BY user_id之后用HAVING COUNT(*) 5。如果你把条件写在WHERE里SQL会直接报错“聚合函数不能出现在WHERE子句中”吗有些数据库会报错有些数据库语法上允许但结果完全不对。除了这个基础点高频题里还会考“GROUP BY多个字段”、“GROUP BY与DISTINCT的区别”、“分组后再排序取Top N”等变形。说实话如果能把HAVING的过滤逻辑、GROUP BY的字段顺序、以及聚合函数作用于分组后的结果集这三件事理清楚你基本就能PK掉80%的面试者。2. 面试最爱出的聚合场景从“按部门统计”到“连续问题”刷题刷多了你会发现聚合题从来不直接说“请用分组聚合”它总是包装成一个业务需求。这时候能不能把需求翻译成SQL逻辑就是核心能力。2.1 经典“分组统计”题的完整拆解看一个最常见也最容易被问的题目有一张员工表Employee字段包括emp_id, emp_name, dept_id, hire_date, salary。现在要统计每个部门的员工人数、平均工资、最高工资和最低工资并且只显示平均工资大于5000的部门按平均工资降序排列。第一眼看过去这不就是Hello World等级吗很多人的写法是这样SELECT dept_id, COUNT(*) AS emp_cnt, AVG(salary) AS avg_sal, MAX(salary) AS max_sal, MIN(salary) AS min_sal FROM employee GROUP BY dept_id HAVING AVG(salary) 5000 ORDER BY avg_sal DESC;这确实能跑但有一个隐藏问题如果只想统计在职员工或者只统计工资非空的员工你要不要加WHERE更关键的是如果dept_id在另一张部门表里你想显示部门名称而不是编号就要JOIN部门表。而JOIN之后会不会因为部门表里没有匹配记录导致数据变少这些都是面试官希望你能主动提到的。我的习惯是先把业务拆成三层数据范围WHERE、分组维度GROUP BY、聚合后过滤HAVING。每一步都问自己一个问题这一层过滤会不会改变聚合的基数这样写出来的SQL才不容易翻车。2.2 结合窗口函数累计求和与移动平均聚合题做到后面一定会遇到窗口函数。因为普通GROUP BY会把多行压缩成一行而很多业务要的是“既能保留明细行又能看到分组统计值”这时候就需要SUM() OVER()这类窗口语法。窗口函数的核心逻辑是它不减少行数只是在每一行后面带上一个“窗口范围”的计算结果。比如计算每个部门内每个员工的工资占部门总工资的比例SELECT emp_id, emp_name, dept_id, salary, SUM(salary) OVER(PARTITION BY dept_id) AS dept_total_salary, ROUND(salary * 100.0 / SUM(salary) OVER(PARTITION BY dept_id), 2) AS pct FROM employee;写到这里你就能感受到同一个SUM函数在GROUP BY里是“聚合行”在窗口里是“计算列”。很多人分不清这两者写报表时就容易出现“行数变少”或者“重复统计”的尴尬。再升级一点就是“求每个用户连续登录天数”。这题几乎是所有聚合高频题里最经典的题解法也很有意思先用ROW_NUMBER()按用户分组、按日期排序得到一个序号然后用登录日期减去序号得到一组“日期差值”。只要用户连续登录日期减去序号后的值就是一样的一旦断签差值就会变。最后再按用户和差值分组聚合就能统计出连续天数。这个套路我在后面实战部分会再展开这里先记住一句话窗口函数是聚合思维从“整体”走向“局部”的关键工具。3. 聚合查询的性能陷阱与优化思路学习聚合不能只盯着语法性能同样重要。工作里我经常在慢SQL排查现场看到类似“SELECT COUNT(*) FROM 大表”这种直接把数据库CPU打满的查询。聚合操作天然要扫描大量数据如果底子没打好一张千万级表就能让你体验什么叫“卡死”。3.1 为什么COUNT(*)比COUNT(列)快很多老开发会推荐“能写COUNT()就别写COUNT(列)”这背后不是迷信而是有索引层面的道理。在多数数据库里如果一张表没有定义主键或合适的二级索引COUNT(列)需要判断每个值是否为NULL而COUNT()是直接数行数并不关心具体列的值。在InnoDB引擎MySQL为例里COUNT(*)的优化空间更大尤其当表上有覆盖索引时它可以直接走索引扫描而不需要回到聚簇索引去读整行数据。反过来如果你COUNT一个很小的非索引列确实可能要全表一遍但即便全表也还是比COUNT(列)多一层“判断NULL”的开销。所以我的习惯是统计总行数用COUNT()统计某个字段有值的数量才用COUNT(字段)。很多人用COUNT(id)来数行数如果id列非空结果一样但逻辑上不如COUNT()清晰。3.2 聚合查询慢的常见原因与优化手段慢SQL优化是一个很大的话题但聚合查询的优化方向其实很固定。第一先看能不能下推过滤条件。WHERE过滤要尽早执行让进入聚合的数据量最小化。千万不要在聚合前的子查询里把所有明细查出来再在外面套一层聚合。一些新手写“SELECT COUNT(*) FROM (SELECT * FROM big_table) t”这种完全可以避免。第二合理利用索引。GROUP BY字段上如果建有索引数据库就不需要额外排序。MySQL里GROUP BY默认会做排序操作如果分组字段能用索引覆盖性能会有质的提升。不过要注意联合索引的字段顺序要匹配GROUP BY的顺序否则依然要文件排序。第三监视执行计划。无论是MySQL还是PostgreSQL都能用EXPLAIN看到聚合阶段是不是出现了Using temporary或者Using filesort。出现临时表不一定是坏事但数据量一大就容易写磁盘这时候可以考虑通过改写SQL结构或者调整数据库参数比如tmp_table_size、max_heap_table_size来缓解。第四如果实时聚合实在跑不动可以考虑用物化视图或者定时汇总表。这不是SQL语法层面的事但却是实际工作中最常用的手段。常见做法是每天凌晨跑一个离线任务把前一天的聚合结果存到中间表前端查询直接查汇总表。这样虽然会有一定程度的数据延迟但换来了查询速度的稳定。4. 那些年我踩过的聚合坑6个必看注意事项我最近一次“翻车”是在做销售报表时某个产品线的销售额怎么算都对不上。后来排查了半天发现是SUM函数把NULL值直接忽略了而那个字段在月初的很多订单里确实录的是NULL导致计算结果差了一截。从此以后我对聚合函数的“隐性行为”特别敏感。4.1 NULL值对聚合结果的影响先列一个我自己整理的规则表建议刻在脑子里场景结果COUNT(*)统计所有行不考虑NULLCOUNT(字段)只统计字段非NULL的行SUM(字段)忽略NULL全NULL则返回NULLAVG(字段)忽略NULL全NULL则返回NULLMAX/MIN忽略NULL全NULL则返回NULLGROUP BY 某列NULL会单独成为一组实际开发中前三种坑最致命。比如SUM返回NULL这件事如果你是在Java后台直接用sumResult字段很可能会因为null导致NPE正确做法是用COALESCE(SUM(列), 0)包一层。AVG也一样如果表里恰好没有符合条件的数据返回NULL而不是0前端一展示就变成“空白”用户还以为系统坏了。4.2 浮点数求和的精度问题SQL聚合不只处理整数更多时候处理的是金额、百分比、汇率这类浮点数。很多数据库的FLOAT/DOUBLE类型在计算二进制小数时会有误差比如0.1加0.2得到0.30000000000000004。如果你直接拿这个结果跟0.3比较结果是不相等的报表上则可能看到一连串奇怪的小数。金融计算一律用DECIMAL/NUMERIC类型比如DECIMAL(10,2)或者DECIMAL(20,4)。MySQL里SUM(DECIMAL)的精度也是可控的但仍然建议在最终输出前用ROUND处理一下。另外等值时不要用“0.3”而是用ABS(SUM(x) - 0.3) 0.000001这种比较方式。4.3 去重聚合与GROUP BY的配合COUNT(DISTINCT 字段)是另一个高频陷阱。它确实能统计唯一值个数但性能很差因为数据库需要去重后才能计数。当数据量大时这几乎是所有聚合操作里最慢的。优化手段主要有两种一是先GROUP BY去重再在外面COUNTSELECT COUNT(*) FROM ( SELECT DISTINCT user_id FROM event_log WHERE create_date 2024-01-01 AND user_id IS NOT NULL ) t;这种方式在数据量较大时往往比直接COUNT(DISTINCT user_id)要快。原因在于子查询里可以先走索引/分组把结果集缩小后再在外层计数。二是如果业务中经常需要统计唯一用户数干脆在ETL阶段就把唯一用户ID对应的明细表单独拉出来维护查询时直接查一张已经去重的表。这也是“用空间换时间”的典型场景。4.4 小心GROUP BY的隐式排序MySQL 5.7和8.0的行为差异特别值得留意。5.7及之前GROUP BY默认会按照分组字段排序很多人写“GROUP BY dept_id LIMIT 1”就顺手取到了第一条。但8.0里默认排序行为变了如果依赖这种隐式顺序很可能得到完全不同的结果。正确做法是需要排序就明确写ORDER BY不要依赖任何隐式行为。同理HAVING也不要跟WHERE混淆。我曾经见过一个同事把租期在WHERE里写了“租期5年”又在HAVING里写了“租期5年”结果当然是没报错但多了一次过滤数据没问题但SQL读起来很别扭维护成本很高。4.5 聚合大小与行转列/列转行的坑用聚合做行转列Pivot时很多人会写成多个SUM加CASE WHEN。比如统计每个月的销售额把12个月变成12列。写法上没错但列一多SQL就会很臃肿动态月份更是难以维护。更通用的做法是用CASE WHEN配合GROUP BY但如果你用的数据库支持FILTER子句PostgreSQL那语法会简洁很多SELECT user_id, COUNT(*) FILTER (WHERE status success) AS success_cnt, COUNT(*) FILTER (WHERE status fail) AS fail_cnt FROM orders GROUP BY user_id;MySQL没有FILTER只能写CASE WHEN加NULL或0。注意SUM(CASE WHEN ... THEN 1 ELSE 0 END)和COUNT(CASE WHEN ... THEN 1 END)效果一样但后者如果CASE条件不满足就会返回NULLCOUNT会忽略NULL所以等价有人会写成COUNT(CASE WHEN ... THEN 1 ELSE NULL END)也没问题但不推荐直接用COUNT(CASE WHEN ... THEN 1 END)更简洁。4.6 聚合的无限延伸ROLLUP与GROUPING SETS做报表时经常遇到“既要每个部门总人数又要所有部门合计”的情况。传统写法是两个查询用UNION ALL拼在一起后来我发现如果数据库支持GROUP BY WITH ROLLUP一行搞定SELECT dept_id, COUNT(*) AS emp_cnt FROM employee GROUP BY dept_id WITH ROLLUP;结果里会多出一行dept_id为NULL的记录那就是全部门合计。PostgreSQL和SQL Server支持GROUPING SETS能更灵活地控制组合维度。这些都是聚合题里比较少直接考但实际工作却很加分的点。5. 高频聚合SQL实战从需求到SQL的完整推演纸上谈兵再多不如直接上手跑几个完整需求。这里我用自己整理过的三个高频案例带大家完整走一遍从需求到SQL的推演过程你会发现聚合的核心不外乎“逻辑拆分”和“语法细节”。5.1 需求1统计各部门各职位平均薪资表结构很简单employee(emp_id, emp_name, dept_id, job_title, salary)。需求是统计每个部门、每个职位的平均薪资并显示平均薪资排名前3的记录按平均薪资降序。第一步先写基础分组SELECT dept_id, job_title, AVG(salary) AS avg_salary FROM employee GROUP BY dept_id, job_title;第二步加排名。SQL Server支持DENSE_RANK()MySQL 8.0、PostgreSQL也支持。我们直接把上面的结果作为子查询外面套一个窗口排名SELECT dept_id, job_title, avg_salary FROM ( SELECT dept_id, job_title, AVG(salary) AS avg_salary, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY AVG(salary) DESC) AS rk FROM employee GROUP BY dept_id, job_title ) t WHERE rk 3;看到这里你会发现其实“每个部门内排名前3”这个需求核心是先按部门分组算出平均薪资再在部门内部做窗口排序两个维度分工明确。有人会问能不能不用子查询直接在原表上用窗口函数可以但写法会重复聚合逻辑。比如SELECT dept_id, job_title, avg_salary, rk FROM ( SELECT dept_id, job_title, AVG(salary) OVER (PARTITION BY dept_id, job_title) AS avg_salary, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY AVG(salary) OVER (PARTITION BY dept_id, job_title) DESC) AS rk FROM employee ) t WHERE rk 3 GROUP BY dept_id, job_title, avg_salary, rk;这种写法虽然也能跑但窗口函数嵌套窗口函数可读性极差而且有些数据库对“ORDER BY后面直接跟窗口聚合函数”的写法支持并不好。我个人的习惯是优先用GROUP BY做好聚合再用子查询包一层处理排名逻辑清晰也方便加WHERE条件。5.2 需求2求每个用户连续登录天数这题在面试题里出镜率极高。给一张login_log(user_id, login_date)一天可能有多条登录记录求每个用户的最大连续登录天数。解题套路分三步第一步按用户和日期去重。同一天登录多次只算一天有效所以要先DISTINCTSELECT DISTINCT user_id, login_date FROM login_log;第二步用ROW_NUMBER()给每个用户按日期编号SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM ( SELECT DISTINCT user_id, login_date FROM login_log ) t;第三步核心公式login_date - rn。因为rn是连续递增的如果登录日期也是连续的那么日期减序号得到的值一定相同。所以WITH daily AS ( SELECT DISTINCT user_id, login_date FROM login_log ), numbered AS ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM daily ), groups AS ( SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL rn DAY) AS grp FROM numbered ) SELECT user_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS consecutive_days FROM groups GROUP BY user_id, grp ORDER BY user_id, start_date;想要最大连续天数就再用MAX(consecutive_days)按用户分组取一次。我在讲这个题时总会强调一个点SQL解连续问题本质上就是“把连续的日期归到同一个组”。日期减去行号这个技巧是理解“组的概念”最直观的案例。一旦理解了碰到“连续3天活跃”“连续签到7天”这类问题都能举一反三。5.3 需求3同比环比计算用LAG/LEAD业务方常要求“本月销售额相比上月增长多少、相比去年同期增长多少”。如果只有一张按月汇总的销售表sales_monthly(month_date, sales_amount)用LAG函数非常方便。SELECT month_date, sales_amount, LAG(sales_amount) OVER (ORDER BY month_date) AS prev_month_sales, sales_amount - LAG(sales_amount) OVER (ORDER BY month_date) AS mom_growth, ROUND( (sales_amount - LAG(sales_amount) OVER (ORDER BY month_date)) / LAG(sales_amount) OVER (ORDER BY month_date) * 100, 2 ) AS mom_growth_rate FROM sales_monthly ORDER BY month_date;同比需要往前推12个月就用LAG(sales_amount, 12)。但这里有个常见大坑如果某个月份没有数据LAG会直接跳到再往前一行而不是跳过空洞找12个月前的值。要更严谨你得先用ORDER BY month_date保证每月一行如果原表缺月份要先通过LEFT JOIN日期维度表补齐。另外LAG取不到值时返回NULL所以计算增长率时一定要用COALESCE或NULLIF避免除零。NULLIF(prev_month_sales, 0)这个函数很实用我一般会配合COALESCE输出NULL再在展示层处理。用窗口函数做同比环比本质上是聚合查询的另一种延续聚合是“把多行压成一行”窗口是“让每一行走读相邻行的值”。理解了这层关系你就不容易再把窗口函数和GROUP BY混为一谈。6. 刷题与实战之间的最后一公里最后聊一点很多人不太重视、但我觉得很关键的内容。刷“高频SQL 50题”时我们通常关注的是“答案对不对”。但在实际工作里SQL的好坏除了正确性还要看可读性、执行效率、可维护性。同一个需求有人写出的SQL一眼看懂有人写出的SQL嵌套八层子查询跑起来倒是不慢但三个月后没人敢动。我个人的实践是在本地建一套和线上结构相同的测试库专门用来跑各种SQL实验。刷题的时候也不要只满足于通过测试用例多问问自己如果这个表有1000万行这条SQL会不会挂如果业务方突然要加一个过滤条件我改起来方不方便如果把这段逻辑做成一个视图别人看代码能不能看懂聚合是所有SQL能力中最不能“只背不练”的一部分。你可以在网上找到无数条“语法正确”的SQL但只有真正处理过脏数据、NULL和浮点精度之后才会明白为什么很多老手会坚持用COALESCE包裹可能为NULL的聚合结果为什么统计用户数要用COUNT(DISTINCT user_id)而不是COUNT(user_id)。如果你正准备面试建议把聚合相关的50题按上面说的几个分类去刷函数语义、分组过滤、窗口排序、连续问题、行转列。每类找到2到3个代表性题目彻底吃透比刷满100题更有用。最后再分享一个小习惯每次写完一条聚合SQL我都会强制自己多看一眼那两样东西——执行计划里有没有出现Using temporary/Using filesort以及统计结果里有没有意外的NULL值。这两个检查点救了我非常多线上事故。 
长河君 · 2026/9/8 21:34:06 · 阅读 1.2k SQL聚合全解析：从高频面试题到实战性能优化
开头直接切入SQL聚合是数据分析最常用的能力也是高频面试题。分享自己刷“高频SQL 50题”中聚合部分的经验拆解考点、常见场景、性能陷阱并附实战SQL。注意每段150字以上总5000字以上。需要认真扩展。 换一个技术圈里比较有意思的现象刷过LeetCode SQL题库或者各种“高频SQL 50题”的人十有八九都会卡在“聚合”这一块。不是因为它难而是因为平时写业务SQL时大多数情况只是拿COUNT、SUM、AVG来糊弄个报表数字真到了面试那种“给你一张表、一个业务场景、让你写出统计结果”的题目才会发现聚合这玩意儿藏着大量细节。聚合查询表面上是SQL里最“基础”的功能但恰恰是它决定了你能不能高效处理数据。我见过不少工作三五年的开发写复杂报表时还在用“一张临时表多个子查询各种JOIN”硬编效率低且容易出错。这篇文章就把我刷“高频SQL 50题”里聚合部分的一些心得整理出来同时结合日常工作踩过的坑把聚合函数、分组逻辑、窗口函数、性能优化一次性讲透。不管你是准备面试、在做数据分析还是每天跟报表打交道的后端开发这篇内容应该都能帮你在聚合这条路上少走弯路。1. 聚合SQL到底考什么从高频50题看考点分布先把“高频SQL 50题”里聚合相关的题拉出来看一眼你会发现考点非常集中。拿我自己刷过的版本来说聚合相关的题目大概占了三成主要分布在几个方向1.1 最基础的三件套COUNT、SUM、AVG这三个函数看起来简单实际上每个都有坑。COUNT()和COUNT(列)的区别多少人面试第一问就被问倒了COUNT()是统计所有行数包括NULL值的行COUNT(列)是统计该列非NULL值的行数。SUM(列)会忽略NULL但如果整列都是NULLSUM返回NULL而不是0。AVG同样忽略NULL计算时不会把NULL当作0去参与总和这跟很多人的直觉不一样。高频题里常见套路是给你一张订单表、一张用户表让你统计每个用户的订单数、订单总额、平均订单金额。这题看似简单但能考察的点很多需不需要保留没有订单的用户如果用LEFT JOIN那COUNT字段时要不要做空值处理深挖下去就能看出你到底是在背SQL还是在理解SQL。我建议入门者先把这三件套吃透尤其搞清楚它们的NULL语义。别小看这个很多线上统计Bug就是从这里来的。1.2 分组聚合与HAVING的隐藏细节有了聚合函数自然会引出一个经典问题如果要在聚合之后再过滤用WHERE还是HAVING这个考点几乎每套题都有。标准答案是WHERE在分组前过滤HAVING在分组后过滤。很多人背下来了但真写的时候还是会搞混。比如“查出订单数大于5的用户”正确的写法一定是GROUP BY user_id之后用HAVING COUNT(*) 5。如果你把条件写在WHERE里SQL会直接报错“聚合函数不能出现在WHERE子句中”吗有些数据库会报错有些数据库语法上允许但结果完全不对。除了这个基础点高频题里还会考“GROUP BY多个字段”、“GROUP BY与DISTINCT的区别”、“分组后再排序取Top N”等变形。说实话如果能把HAVING的过滤逻辑、GROUP BY的字段顺序、以及聚合函数作用于分组后的结果集这三件事理清楚你基本就能PK掉80%的面试者。2. 面试最爱出的聚合场景从“按部门统计”到“连续问题”刷题刷多了你会发现聚合题从来不直接说“请用分组聚合”它总是包装成一个业务需求。这时候能不能把需求翻译成SQL逻辑就是核心能力。2.1 经典“分组统计”题的完整拆解看一个最常见也最容易被问的题目有一张员工表Employee字段包括emp_id, emp_name, dept_id, hire_date, salary。现在要统计每个部门的员工人数、平均工资、最高工资和最低工资并且只显示平均工资大于5000的部门按平均工资降序排列。第一眼看过去这不就是Hello World等级吗很多人的写法是这样SELECT dept_id, COUNT(*) AS emp_cnt, AVG(salary) AS avg_sal, MAX(salary) AS max_sal, MIN(salary) AS min_sal FROM employee GROUP BY dept_id HAVING AVG(salary) 5000 ORDER BY avg_sal DESC;这确实能跑但有一个隐藏问题如果只想统计在职员工或者只统计工资非空的员工你要不要加WHERE更关键的是如果dept_id在另一张部门表里你想显示部门名称而不是编号就要JOIN部门表。而JOIN之后会不会因为部门表里没有匹配记录导致数据变少这些都是面试官希望你能主动提到的。我的习惯是先把业务拆成三层数据范围WHERE、分组维度GROUP BY、聚合后过滤HAVING。每一步都问自己一个问题这一层过滤会不会改变聚合的基数这样写出来的SQL才不容易翻车。2.2 结合窗口函数累计求和与移动平均聚合题做到后面一定会遇到窗口函数。因为普通GROUP BY会把多行压缩成一行而很多业务要的是“既能保留明细行又能看到分组统计值”这时候就需要SUM() OVER()这类窗口语法。窗口函数的核心逻辑是它不减少行数只是在每一行后面带上一个“窗口范围”的计算结果。比如计算每个部门内每个员工的工资占部门总工资的比例SELECT emp_id, emp_name, dept_id, salary, SUM(salary) OVER(PARTITION BY dept_id) AS dept_total_salary, ROUND(salary * 100.0 / SUM(salary) OVER(PARTITION BY dept_id), 2) AS pct FROM employee;写到这里你就能感受到同一个SUM函数在GROUP BY里是“聚合行”在窗口里是“计算列”。很多人分不清这两者写报表时就容易出现“行数变少”或者“重复统计”的尴尬。再升级一点就是“求每个用户连续登录天数”。这题几乎是所有聚合高频题里最经典的题解法也很有意思先用ROW_NUMBER()按用户分组、按日期排序得到一个序号然后用登录日期减去序号得到一组“日期差值”。只要用户连续登录日期减去序号后的值就是一样的一旦断签差值就会变。最后再按用户和差值分组聚合就能统计出连续天数。这个套路我在后面实战部分会再展开这里先记住一句话窗口函数是聚合思维从“整体”走向“局部”的关键工具。3. 聚合查询的性能陷阱与优化思路学习聚合不能只盯着语法性能同样重要。工作里我经常在慢SQL排查现场看到类似“SELECT COUNT(*) FROM 大表”这种直接把数据库CPU打满的查询。聚合操作天然要扫描大量数据如果底子没打好一张千万级表就能让你体验什么叫“卡死”。3.1 为什么COUNT(*)比COUNT(列)快很多老开发会推荐“能写COUNT()就别写COUNT(列)”这背后不是迷信而是有索引层面的道理。在多数数据库里如果一张表没有定义主键或合适的二级索引COUNT(列)需要判断每个值是否为NULL而COUNT()是直接数行数并不关心具体列的值。在InnoDB引擎MySQL为例里COUNT(*)的优化空间更大尤其当表上有覆盖索引时它可以直接走索引扫描而不需要回到聚簇索引去读整行数据。反过来如果你COUNT一个很小的非索引列确实可能要全表一遍但即便全表也还是比COUNT(列)多一层“判断NULL”的开销。所以我的习惯是统计总行数用COUNT()统计某个字段有值的数量才用COUNT(字段)。很多人用COUNT(id)来数行数如果id列非空结果一样但逻辑上不如COUNT()清晰。3.2 聚合查询慢的常见原因与优化手段慢SQL优化是一个很大的话题但聚合查询的优化方向其实很固定。第一先看能不能下推过滤条件。WHERE过滤要尽早执行让进入聚合的数据量最小化。千万不要在聚合前的子查询里把所有明细查出来再在外面套一层聚合。一些新手写“SELECT COUNT(*) FROM (SELECT * FROM big_table) t”这种完全可以避免。第二合理利用索引。GROUP BY字段上如果建有索引数据库就不需要额外排序。MySQL里GROUP BY默认会做排序操作如果分组字段能用索引覆盖性能会有质的提升。不过要注意联合索引的字段顺序要匹配GROUP BY的顺序否则依然要文件排序。第三监视执行计划。无论是MySQL还是PostgreSQL都能用EXPLAIN看到聚合阶段是不是出现了Using temporary或者Using filesort。出现临时表不一定是坏事但数据量一大就容易写磁盘这时候可以考虑通过改写SQL结构或者调整数据库参数比如tmp_table_size、max_heap_table_size来缓解。第四如果实时聚合实在跑不动可以考虑用物化视图或者定时汇总表。这不是SQL语法层面的事但却是实际工作中最常用的手段。常见做法是每天凌晨跑一个离线任务把前一天的聚合结果存到中间表前端查询直接查汇总表。这样虽然会有一定程度的数据延迟但换来了查询速度的稳定。4. 那些年我踩过的聚合坑6个必看注意事项我最近一次“翻车”是在做销售报表时某个产品线的销售额怎么算都对不上。后来排查了半天发现是SUM函数把NULL值直接忽略了而那个字段在月初的很多订单里确实录的是NULL导致计算结果差了一截。从此以后我对聚合函数的“隐性行为”特别敏感。4.1 NULL值对聚合结果的影响先列一个我自己整理的规则表建议刻在脑子里场景结果COUNT(*)统计所有行不考虑NULLCOUNT(字段)只统计字段非NULL的行SUM(字段)忽略NULL全NULL则返回NULLAVG(字段)忽略NULL全NULL则返回NULLMAX/MIN忽略NULL全NULL则返回NULLGROUP BY 某列NULL会单独成为一组实际开发中前三种坑最致命。比如SUM返回NULL这件事如果你是在Java后台直接用sumResult字段很可能会因为null导致NPE正确做法是用COALESCE(SUM(列), 0)包一层。AVG也一样如果表里恰好没有符合条件的数据返回NULL而不是0前端一展示就变成“空白”用户还以为系统坏了。4.2 浮点数求和的精度问题SQL聚合不只处理整数更多时候处理的是金额、百分比、汇率这类浮点数。很多数据库的FLOAT/DOUBLE类型在计算二进制小数时会有误差比如0.1加0.2得到0.30000000000000004。如果你直接拿这个结果跟0.3比较结果是不相等的报表上则可能看到一连串奇怪的小数。金融计算一律用DECIMAL/NUMERIC类型比如DECIMAL(10,2)或者DECIMAL(20,4)。MySQL里SUM(DECIMAL)的精度也是可控的但仍然建议在最终输出前用ROUND处理一下。另外等值时不要用“0.3”而是用ABS(SUM(x) - 0.3) 0.000001这种比较方式。4.3 去重聚合与GROUP BY的配合COUNT(DISTINCT 字段)是另一个高频陷阱。它确实能统计唯一值个数但性能很差因为数据库需要去重后才能计数。当数据量大时这几乎是所有聚合操作里最慢的。优化手段主要有两种一是先GROUP BY去重再在外面COUNTSELECT COUNT(*) FROM ( SELECT DISTINCT user_id FROM event_log WHERE create_date 2024-01-01 AND user_id IS NOT NULL ) t;这种方式在数据量较大时往往比直接COUNT(DISTINCT user_id)要快。原因在于子查询里可以先走索引/分组把结果集缩小后再在外层计数。二是如果业务中经常需要统计唯一用户数干脆在ETL阶段就把唯一用户ID对应的明细表单独拉出来维护查询时直接查一张已经去重的表。这也是“用空间换时间”的典型场景。4.4 小心GROUP BY的隐式排序MySQL 5.7和8.0的行为差异特别值得留意。5.7及之前GROUP BY默认会按照分组字段排序很多人写“GROUP BY dept_id LIMIT 1”就顺手取到了第一条。但8.0里默认排序行为变了如果依赖这种隐式顺序很可能得到完全不同的结果。正确做法是需要排序就明确写ORDER BY不要依赖任何隐式行为。同理HAVING也不要跟WHERE混淆。我曾经见过一个同事把租期在WHERE里写了“租期5年”又在HAVING里写了“租期5年”结果当然是没报错但多了一次过滤数据没问题但SQL读起来很别扭维护成本很高。4.5 聚合大小与行转列/列转行的坑用聚合做行转列Pivot时很多人会写成多个SUM加CASE WHEN。比如统计每个月的销售额把12个月变成12列。写法上没错但列一多SQL就会很臃肿动态月份更是难以维护。更通用的做法是用CASE WHEN配合GROUP BY但如果你用的数据库支持FILTER子句PostgreSQL那语法会简洁很多SELECT user_id, COUNT(*) FILTER (WHERE status success) AS success_cnt, COUNT(*) FILTER (WHERE status fail) AS fail_cnt FROM orders GROUP BY user_id;MySQL没有FILTER只能写CASE WHEN加NULL或0。注意SUM(CASE WHEN ... THEN 1 ELSE 0 END)和COUNT(CASE WHEN ... THEN 1 END)效果一样但后者如果CASE条件不满足就会返回NULLCOUNT会忽略NULL所以等价有人会写成COUNT(CASE WHEN ... THEN 1 ELSE NULL END)也没问题但不推荐直接用COUNT(CASE WHEN ... THEN 1 END)更简洁。4.6 聚合的无限延伸ROLLUP与GROUPING SETS做报表时经常遇到“既要每个部门总人数又要所有部门合计”的情况。传统写法是两个查询用UNION ALL拼在一起后来我发现如果数据库支持GROUP BY WITH ROLLUP一行搞定SELECT dept_id, COUNT(*) AS emp_cnt FROM employee GROUP BY dept_id WITH ROLLUP;结果里会多出一行dept_id为NULL的记录那就是全部门合计。PostgreSQL和SQL Server支持GROUPING SETS能更灵活地控制组合维度。这些都是聚合题里比较少直接考但实际工作却很加分的点。5. 高频聚合SQL实战从需求到SQL的完整推演纸上谈兵再多不如直接上手跑几个完整需求。这里我用自己整理过的三个高频案例带大家完整走一遍从需求到SQL的推演过程你会发现聚合的核心不外乎“逻辑拆分”和“语法细节”。5.1 需求1统计各部门各职位平均薪资表结构很简单employee(emp_id, emp_name, dept_id, job_title, salary)。需求是统计每个部门、每个职位的平均薪资并显示平均薪资排名前3的记录按平均薪资降序。第一步先写基础分组SELECT dept_id, job_title, AVG(salary) AS avg_salary FROM employee GROUP BY dept_id, job_title;第二步加排名。SQL Server支持DENSE_RANK()MySQL 8.0、PostgreSQL也支持。我们直接把上面的结果作为子查询外面套一个窗口排名SELECT dept_id, job_title, avg_salary FROM ( SELECT dept_id, job_title, AVG(salary) AS avg_salary, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY AVG(salary) DESC) AS rk FROM employee GROUP BY dept_id, job_title ) t WHERE rk 3;看到这里你会发现其实“每个部门内排名前3”这个需求核心是先按部门分组算出平均薪资再在部门内部做窗口排序两个维度分工明确。有人会问能不能不用子查询直接在原表上用窗口函数可以但写法会重复聚合逻辑。比如SELECT dept_id, job_title, avg_salary, rk FROM ( SELECT dept_id, job_title, AVG(salary) OVER (PARTITION BY dept_id, job_title) AS avg_salary, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY AVG(salary) OVER (PARTITION BY dept_id, job_title) DESC) AS rk FROM employee ) t WHERE rk 3 GROUP BY dept_id, job_title, avg_salary, rk;这种写法虽然也能跑但窗口函数嵌套窗口函数可读性极差而且有些数据库对“ORDER BY后面直接跟窗口聚合函数”的写法支持并不好。我个人的习惯是优先用GROUP BY做好聚合再用子查询包一层处理排名逻辑清晰也方便加WHERE条件。5.2 需求2求每个用户连续登录天数这题在面试题里出镜率极高。给一张login_log(user_id, login_date)一天可能有多条登录记录求每个用户的最大连续登录天数。解题套路分三步第一步按用户和日期去重。同一天登录多次只算一天有效所以要先DISTINCTSELECT DISTINCT user_id, login_date FROM login_log;第二步用ROW_NUMBER()给每个用户按日期编号SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM ( SELECT DISTINCT user_id, login_date FROM login_log ) t;第三步核心公式login_date - rn。因为rn是连续递增的如果登录日期也是连续的那么日期减序号得到的值一定相同。所以WITH daily AS ( SELECT DISTINCT user_id, login_date FROM login_log ), numbered AS ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM daily ), groups AS ( SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL rn DAY) AS grp FROM numbered ) SELECT user_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS consecutive_days FROM groups GROUP BY user_id, grp ORDER BY user_id, start_date;想要最大连续天数就再用MAX(consecutive_days)按用户分组取一次。我在讲这个题时总会强调一个点SQL解连续问题本质上就是“把连续的日期归到同一个组”。日期减去行号这个技巧是理解“组的概念”最直观的案例。一旦理解了碰到“连续3天活跃”“连续签到7天”这类问题都能举一反三。5.3 需求3同比环比计算用LAG/LEAD业务方常要求“本月销售额相比上月增长多少、相比去年同期增长多少”。如果只有一张按月汇总的销售表sales_monthly(month_date, sales_amount)用LAG函数非常方便。SELECT month_date, sales_amount, LAG(sales_amount) OVER (ORDER BY month_date) AS prev_month_sales, sales_amount - LAG(sales_amount) OVER (ORDER BY month_date) AS mom_growth, ROUND( (sales_amount - LAG(sales_amount) OVER (ORDER BY month_date)) / LAG(sales_amount) OVER (ORDER BY month_date) * 100, 2 ) AS mom_growth_rate FROM sales_monthly ORDER BY month_date;同比需要往前推12个月就用LAG(sales_amount, 12)。但这里有个常见大坑如果某个月份没有数据LAG会直接跳到再往前一行而不是跳过空洞找12个月前的值。要更严谨你得先用ORDER BY month_date保证每月一行如果原表缺月份要先通过LEFT JOIN日期维度表补齐。另外LAG取不到值时返回NULL所以计算增长率时一定要用COALESCE或NULLIF避免除零。NULLIF(prev_month_sales, 0)这个函数很实用我一般会配合COALESCE输出NULL再在展示层处理。用窗口函数做同比环比本质上是聚合查询的另一种延续聚合是“把多行压成一行”窗口是“让每一行走读相邻行的值”。理解了这层关系你就不容易再把窗口函数和GROUP BY混为一谈。6. 刷题与实战之间的最后一公里最后聊一点很多人不太重视、但我觉得很关键的内容。刷“高频SQL 50题”时我们通常关注的是“答案对不对”。但在实际工作里SQL的好坏除了正确性还要看可读性、执行效率、可维护性。同一个需求有人写出的SQL一眼看懂有人写出的SQL嵌套八层子查询跑起来倒是不慢但三个月后没人敢动。我个人的实践是在本地建一套和线上结构相同的测试库专门用来跑各种SQL实验。刷题的时候也不要只满足于通过测试用例多问问自己如果这个表有1000万行这条SQL会不会挂如果业务方突然要加一个过滤条件我改起来方不方便如果把这段逻辑做成一个视图别人看代码能不能看懂聚合是所有SQL能力中最不能“只背不练”的一部分。你可以在网上找到无数条“语法正确”的SQL但只有真正处理过脏数据、NULL和浮点精度之后才会明白为什么很多老手会坚持用COALESCE包裹可能为NULL的聚合结果为什么统计用户数要用COUNT(DISTINCT user_id)而不是COUNT(user_id)。如果你正准备面试建议把聚合相关的50题按上面说的几个分类去刷函数语义、分组过滤、窗口排序、连续问题、行转列。每类找到2到3个代表性题目彻底吃透比刷满100题更有用。最后再分享一个小习惯每次写完一条聚合SQL我都会强制自己多看一眼那两样东西——执行计划里有没有出现Using temporary/Using filesort以及统计结果里有没有意外的NULL值。这两个检查点救了我非常多线上事故。