最近要出一张月度销售报表需求方一口气提了三个要求统计每个销售员的订单总额、给每个订单算一次金额排名、再把金额换算成万元并保留两位小数。要是放在我刚写SQL那会儿这三件事我大概率会拆成好几条SQL导出来用Excel二次加工最后再拼一起。现在其实一条SELECT就能搞定因为MySQL正好提供了三套工具聚合函数负责把多行压成一行算汇总窗口函数负责在保留明细行的前提下做排名和累计数学函数负责对数值做舍入、取整、单位换算这些精细加工。这篇就围绕这三个函数家族展开把它们的核心用法、容易踩的坑、以及真实项目里怎么组合使用讲清楚。内容延续系列第七篇适合已经能把增删改查写利索、想进阶到数据处理和报表分析的同学也适合面试前想快速把函数体系梳理一遍的人。1. 内容整体设计与思路拆解1.1 三类函数在日常SQL里的分工先说聚合函数。它干的事情很纯粹把一组行压缩成一个结果。比如订单表有十万行你想知道总金额、平均金额、最大订单、最小订单一个SUM加一个AVG加一个MAX和MIN就搞定了。它的特点是你只能看到汇总值看不到任何一条明细数据。窗口函数的出现解决了一个很实际的问题很多时候你既想看明细又想看汇总。比如同一个班的考试成绩班主任想知道每个学生的分数还希望每行边上带着全班排名班级平均分。传统做法是查两条SQL再手动关联而窗口函数能直接在原来的明细行旁边追加一列汇总或排名信息行数不变只是多看了一眼。数学函数则是最后一个环节的加工。前面算出来的金额可能是小数报表里要四舍五入某个指标要按千元取整随机抽样要用到随机数分库分表要按用户ID取模。这些都是数学函数的主场。1.2 为什么要把这三类函数放在一起学因为它们在实际业务里几乎总是配套出现而不是孤立使用。举个例子统计各区域销售额排名前3的销售员并展示其销售额占区域总销售额的比例。这个需求里区域总销售额是聚合函数的工作排名是窗口函数的工作占比计算则离不开数学函数里的ROUND。你单独会任何一个函数都能写出SQL但组合起来才能写出一条不绕弯的效率SQL。另外这三个家族也正好对应了数据处理的三层逻辑聚合看整体、窗口看细节与排名、数学做精加工。想清楚这个思路遇到任何统计类需求你脑子里第一时间就能排出执行顺序先WHERE过滤范围再GROUP BY聚合再OVER排名最后ROUND做显示层加工。这也是这篇内容设计的整体线索。2. 聚合函数多行压缩成一行时的门道2.1 COUNT系列到底在数什么COUNT是SQL入门最早接触的函数但它身上藏着一个最常见的理解误区。COUNT(*)数的是表里的行数不管某列是不是NULL也不管是不是有重复值。COUNT(1)在MySQL里和COUNT(*)语义完全等价也是数总行数性能上在现代优化器里基本没有差别。真正需要注意的是COUNT(列名)它只数该列非NULL的行数。比如一张用户表有100个人其中50个人填写了手机号50个人没填。COUNT(*)返回100COUNT(phone)返回50。如果你以为COUNT(phone)能统计有多少用户那结果就会出大问题。还有个小细节COUNT(二级索引列)在InnoDB里很多时候能用覆盖索引扫描速度不一定比COUNT(*)慢但前提是这一列尽量不要是大字段类型否则扫描代价反而更高。我见过有人在COUNT(text类型的大字段列)查一次能慢好几秒这就是典型的用错了工具。2.2 SUM、AVG、MAX、MIN与NULL值的纠缠这个坑我在刚工作时踩过一次统计某天订单总额结果发现报表里有个单元格是空的而不是0。问题就出在SUM和AVG遇到NULL的规则上。SUM(amount)会把整组所有非NULL的amount相加如果整组都是NULL——也就是当天没有订单——返回的不是0而是NULL。AVG也一样它只拿非NULL行参与计算分母是非NULL行数而不是分组行数。MAX和MIN同样会直接忽略NULL整列都为NULL时返回NULL。所以写报表SQL时聚合外面套IFNULL几乎属于标配SELECT IFNULL(SUM(amount), 0) AS total_amount, IFNULL(AVG(amount), 0) AS avg_amount FROM orders WHERE order_date 2024-01-15;还有一个容易忽略的点MAX和MIN不是只能用在数值列上。日期类型的最大值最小值可以直接取到最近一次下单和最早一次下单字符串列的MAX会按字符序取最大值比如从一堆订单号里取到最大的那个字符串。聚合函数的适用范围比想象中宽得多。2.3 GROUP BY分组与HAVING过滤的坑GROUP BY的作用是把相同值的行归到同一组然后每一组输出一行聚合结果。基本格式是SELECT dept_id, COUNT(*) AS emp_cnt, ROUND(AVG(salary), 2) AS avg_salary FROM employee WHERE status active GROUP BY dept_id HAVING AVG(salary) 8000 ORDER BY avg_salary DESC;这条SQL代表了一个完整的执行顺序搞懂它对排查问题非常有帮助。SQL标准的执行顺序是先FROM取表再WHERE过滤然后GROUP BY分组接着HAVING对分组后的结果过滤再SELECT选取列最后ORDER BY排序。这意味着两件事第一WHERE里不能写聚合函数因为分组还没发生第二HAVING是在分组之后做过滤所以它可以使用聚合函数代价是它基于已经算好的分组结果做判断。实际使用中最常见的错误是能用WHERE过滤的偏要放到HAVING里。比如要统计在职员工各部门的平均工资把statusactive写在WHERE里员工表在分组之前就缩小了范围聚合的数据量小查询自然快如果写成HAVING statusactive等于先把所有在职和离职的人全分了组最后再过滤组性能和语义都不对。GROUP BY这层的另一个小技巧是WITH ROLLUP可以在分组结果末尾追加一行总计SELECT dept_id, COUNT(*), SUM(salary) FROM employee GROUP BY dept_id WITH ROLLUP;多出来的那行dept_id是NULL但SUM和COUNT是所有部门的总和。做报表小计非常好用只是后端代码解析时要注意识别这一行。2.4 GROUP_CONCAT把多行拼成一行GROUP_CONCAT不是标准SQL里的聚合函数但MySQL项目里几乎离不开它。它能把同一组的多行值拼成一个字符串。比如想知道每个部门有哪些员工一条SQL就能拼出来SELECT dept_id, GROUP_CONCAT(name ORDER BY salary DESC SEPARATOR 、) AS emp_list FROM employee GROUP BY dept_id;这个函数支持ORDER BY控制拼接顺序SEPARATOR指定分隔符默认是逗号也可以在前面加DISTINCT去重。它有个隐藏的坑系统变量group_concat_max_len默认只有1024字节拼接结果超过这个长度会被悄悄截断。以前做标签系统时把用户的所有标签拼出来拼到几百个就断了一半排查了半天才发现是这个参数在作怪。临时调大可以用SET SESSION group_concat_max_len 100000;要长期生效就得改配置文件。3. 窗口函数保留明细的前提下做分析3.1 窗口函数的语法结构MySQL 8.0开始原生支持窗口函数这是分析型SQL体验的一次大升级。它的语法也不复杂核心就三部分窗口函数 OVER ( [PARTITION BY 分组列] [ORDER BY 排序列] [窗口边界] )PARTITION BY负责把数据分成若干小组相当于只分组但不压缩行ORDER BY负责在每一个小组内部排序窗口边界则决定了每行计算时能看到周围哪些行。这里要特别强调一下默认行为因为很多人写错还不知道原因。如果只写PARTITION BY没写ORDER BY那么整个分组就是当前行的窗口也就是说每行都能看到整个分组的数据。如果写了PARTITION BY也写了ORDER BY默认窗口是从分组第一行到当前行这就是累计类计算的基础。很多人在FIRST_VALUE、LAST_VALUE上拿错结果基本都是被这个默认边界坑的后面细说。窗口函数带来的直接好处是你想让结果里每一行都带组内排名或组内累计值直接新增一列就行行数一条不减原始明细完全保留。这和GROUP BY的本质区别就在这——聚合是把多行变一行窗口是每行旁边多挂一组分析结果。3.2 排名三兄弟ROW_NUMBER、RANK、DENSE_RANK这三个排名函数面试必问业务里也天天用但很多人分不清。我造一张学生成绩表来说明CREATE TABLE student_score ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), subject VARCHAR(20), score INT ); INSERT INTO student_score (name, subject, score) VALUES (张三, 数学, 90), (李四, 数学, 85), (王五, 数学, 90), (赵六, 数学, 78), (孙七, 数学, 85);然后按分数降序排名SELECT name, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS rn, RANK() OVER (ORDER BY score DESC) AS rk, DENSE_RANK() OVER (ORDER BY score DESC) AS dr FROM student_score WHERE subject 数学;结果如下namescorernrkdr张三90111王五90211李四85332孙七85432赵六78553区别一句话就能讲完ROW_NUMBER是同分也强行给不同序号RANK是同分同名次但会跳号DENSE_RANK是同分同名次且不跳号。实际业务里如果排名后面还要按名次分页比如取第3页的第1名通常用DENSE_RANK更符合直觉如果只是需要唯一递增序号直接用ROW_NUMBER。还有一个NTILE(n)也常被忽略它能把数据平均分成n桶比如按成绩把人分成四等份用于分层抽样或分桶对比。3.3 聚合窗口函数累计求和与移动平均排名函数之外聚合函数本身也能当窗口函数用这是分析报表里最香的功能。比如要看每个人的分数和按分数降序排列后的累计分值SELECT name, score, SUM(score) OVER (ORDER BY score DESC) AS running_sum FROM student_score WHERE subject 数学;因为有了ORDER BY默认窗口就是分组第一行到当前行所以SUM会一行行累加90、180、265、350、428。这种写法比在程序里循环累加要优雅得多一条SQL直接给到前端。移动平均也是同样思路只是窗口边界变了SELECT name, score, AVG(score) OVER ( ORDER BY score ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS moving_avg FROM student_score WHERE subject 数学;意思很直白求前一行、当前行、后一行三者的平均值。窗口边界可以自由控制常用的有ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW分组第一行到当前行做累计ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING前后各一行做移动平均ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING前后各两行做更宽的滑动窗口ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING整个分组做组内总量另外还可以配合PARTITION BY做分组内的累计。比如统计每个用户每个月的消费额要同时看每一笔消费额和到当月的累计消费额用SUM(amount) OVER (PARTITION BY user_id ORDER BY month)就能实现。这类组内累计需求如果不用窗口函数通常要自连接或者写存储过程效率差一大截。3.4 偏移函数LAG、LEAD、FIRST_VALUE、LAST_VALUE偏移函数解决的是上下行对比问题。LAG取当前行前第n行的值LEAD取后第n行的值最典型的场景是算环比、同比。假设有一张月度销售额表monthly_sale(id, month, amount)要算每个月的环比增长率SELECT month, amount, LAG(amount, 1) OVER (ORDER BY month) AS prev_amount, ROUND( (amount - LAG(amount, 1) OVER (ORDER BY month)) / LAG(amount, 1) OVER (ORDER BY month) * 100, 2 ) AS growth_rate FROM monthly_sale ORDER BY month;LAG的第三个参数是取不到值时的默认值比如第一行没有前一行默认返回NULL也可以写成LAG(amount, 1, 0)让第一行的前置金额是0。需要注意的是窗口函数内部引用的表达式不能直接用别名简写所以上面把LAG(amount)重复写了三遍。想写得简洁可以再套一层子查询先算好prev_amount再在外层做比例计算。FIRST_VALUE和LAST_VALUE是取窗口内第一个值和最后一个值。它们被问得最多的一个问题就是为什么LAST_VALUE取到的不是组内最后一行原因就在开头说的默认窗口边界。当写了ORDER BY之后窗口默认只到当前行LAST_VALUE看到的最后一个值就是当前行自己。想拿到整个分组最后一行必须显式声明窗口边界SELECT name, score, FIRST_VALUE(score) OVER (ORDER BY score) AS lowest_score, LAST_VALUE(score) OVER ( ORDER BY score ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS highest_score FROM student_score;3.5 MySQL 5.7用户变量模拟排名如果项目还跑在MySQL 5.7上原生窗口函数用不了只能靠用户变量模拟。经典的排名写法是这样的SET prev_score : NULL; SET curr_rank : 0; SELECT name, score, curr_rank : IF(prev_score score, curr_rank, curr_rank 1) AS rk, prev_score : score AS prev_score FROM ( SELECT name, score FROM student_score WHERE subject 数学 ORDER BY score DESC ) t;这套方案能跑但很脆弱。用户变量的赋值顺序在MySQL里没有严格保证而且子查询排序依赖外部变量的处理逻辑稍不注意结果就是乱的。最稳妥的建议还是升级到8.0。MySQL 8.0之后窗口函数全面可用无论是可读性、稳定性、性能都完全不是用户变量能比的。窗口函数的性能问题是很多团队担心的点。它本质上是先分组组内排序再随时访问窗口范围内的行所以数据量大时EXPLAIN里经常能看到Using filesort这很正常。优化方向一般是两个一是尽量让PARTITION BY列和ORDER BY列命中联合索引减少排序规模二是在窗口函数之前先用WHERE把数据范围砍小别让窗口函数在百万行的全集上做全局排序。4. 数学函数数字加工里的边角料也有大用4.1 舍入一族ROUND、TRUNCATE、CEIL、FLOOR数学函数里最常用的是舍入相关的一组它们看着像实际行为差异很大。函数作用例子结果ROUND(x, d)按四舍五入保留d位小数d省略则取整数ROUND(123.456, 2)123.46TRUNCATE(x, d)直接截断不四舍五入TRUNCATE(123.456, 2)123.45CEIL(x)向上取整返回不小于x的最小整数CEIL(123.45)124FLOOR(x)向下取整返回不大于x的最大整数FLOOR(123.45)123这里最容易踩的坑是负数和取整方向。比如TRUNCATE(-123.456, 0)返回-123它是往0方向截断而FLOOR(-123.456)返回-124它是往负无穷方向取整。两者对正数看着一样对负数行为完全不同。SELECT负数入库之前一定要先在测试环境确认取整方向是不是业务想要的那个方向。还有一个小众但实用的能力ROUND和TRUNCATE的第二个参数支持负数。ROUND(1234, -2)结果是1200也就是在十位做四舍五入TRUNCATE(1234, -2)结果是1200直接按百位截断。做数据分档的时候比如金额按百元档位输出这个负位数参数会显得非常顺手。4.2 MOD、RAND、SIGN的实战用途MOD取模在分库分表和分片里是常客。比如要把用户均匀分到4个库最简单的分片键计算就是MOD(user_id, 4)。还可以做奇偶判断MOD(id, 2) 1表示奇数。笔试面试里也常考判断一个数是不是偶数不让你用%有没有别的方式其实就是MOD(id, 2)反过来用。RAND返回0到1之间的随机小数可以指定种子RAND(seed)同一种子生成的随机序列完全一样这在做测试数据复现时非常有用。RAND最常见的误用场景是ORDER BY RAND()SELECT * FROM big_table ORDER BY RAND() LIMIT 10;这条SQL会把整个表每行都生成一个随机数再排序表一大直接卡死。随机抽样的替代方案是利用主键索引跳跃SELECT * FROM big_table WHERE id (SELECT FLOOR(MAX(id) * RAND()) FROM big_table) ORDER BY id LIMIT 10;SIGN是符号函数负数返回-10返回0正数返回1。做数据趋势判断时很实用比如环比变动方向、涨跌标记一条CASE加SIGN就能把正负趋势转成前端好渲染的方向标识。4.3 幂、对数、平方根等常用数学函数除了舍入和随机平时会碰到的数学函数还有一批我整理成一张表方便查阅函数说明例子结果ABS(x)绝对值ABS(-12.5)12.5POWER(x, y)x的y次幂POWER(2, 10)1024SQRT(x)平方根负数返回NULLSQRT(16)4EXP(x)e的x次幂EXP(1)2.71828LN(x)自然对数LN(2.71828)1LOG(x)以e为底的自然对数同上同上LOG(base, x)以base为底的对数LOG(2, 8)3RADIANS(x) / DEGREES(x)角度与弧度互转RADIANS(180)3.14159SIN / COS / TAN(x)三角函数参数是弧度SIN(PI()/2)1PI()圆周率常量PI()3.14159这些函数在业务代码里出现的频率不高但一旦需要处理坐标换算、功率计算、概率分布这类场景它们就是救命工具。比如地图类应用算两个经纬度点的距离最后一步通常就是DEGREES加SIN加COS一起上。4.4 一个能直接落到项目里的数学函数示例综合前面的知识点我写一段实际报表项目里很常见的SQL订单金额按万元显示、保留两位小数同时按千元向上取整做一个分档字段再用MOD生成一个分片键SELECT order_no, amount, ROUND(amount / 10000, 2) AS amount_wan, CEIL(amount / 1000) * 1000 AS amount_ceil_to_thousand, MOD(order_no, 10) AS shard_key FROM orders WHERE order_date 2024-01-01;这段SQL里的逻辑很直白金额除以一万就是万元ROUND保留两位CEIL(amount / 1000)把金额抬到千元档位再乘回1000得到向上取整到千元的数值MOD用订单号取10的余数作为分片键。一个字段做精度一个字段做分档一个字段做分布均匀性三类函数在一段SQL里全用上了。5. 常见问题与排查技巧实录5.1 排名函数傻傻分不清面试和业务里最常问的就是ROW_NUMBER、RANK、DENSE_RANK的区别。记住一句话ROW_NUMBER是强制连续编号RANK是并列但跳号DENSE_RANK是并列且不跳号。如果还容易记混就直接跑这条SQL看结果永远比死记定义快SELECT score, ROW_NUMBER() OVER (ORDER BY score DESC) AS rn, RANK() OVER (ORDER BY score DESC) AS rk, DENSE_RANK() OVER (ORDER BY score DESC) AS dr FROM student_score;另外一个容易被忽略的是并排行之间的顺序。ROW_NUMBER在同分情况下分配的顺序不是固定的取决于MySQL内部的排序实现不要在业务里假设同分时谁一定排前面。真想规定同分时的次序就再加一个排序列。5.2 聚合函数统计不准的常见原因为什么SUM出来的数不对是排查频率很高的问题我见过几个典型原因。第一NULL被忽略。统计某字段平均值时如果缺失值很多AVG算出来的是非NULL行的均值而不是全量行的均值口径很容易对不上。第二COUNT(列名)和COUNT(*)混用。想统计用户总量就用COUNT(*)想统计有手机号的用户数才用COUNT(phone)两者结果不一样是正常的不是数据库故障。第三GROUP BY的列没加索引数据量一大查询就会冒出Using temporary的临时表性能断崖式下跌。第四WHERE和HAVING用反把本应提前过滤的数据留到分组之后导致聚合结果失真。排查这类问题最有效的办法是先拿一条最小的数据集手工算一遍结果再和SQL输出对比定位到底哪一层逻辑和预期不一致。5.3 窗口函数跑得慢怎么办窗口函数慢90%的情况都是排序和数据范围的问题。先用EXPLAIN看执行计划如果Extra里出现Using filesort且数据量巨大优先做两件事一是检查PARTITION BY和ORDER BY所涉及的列能不能组成索引比如经常写PARTITION BY user_id ORDER BY create_time那(user_id, create_time)的联合索引就可能直接帮到它二是在外面套一层查询先用WHERE把时间段或状态码过滤掉再跑窗口函数让参与排序的行数尽量少。还有一点窗口函数尽量不要嵌套多层视图或子查询每一层都可能引入额外的排序。曾经接手过一条统计SQL三层子查询全上窗口函数排查下来有一层其实可以用普通聚合替代去掉之后查询时间从8秒降到了0.5秒。5.4 舍入和随机数的坑ROUND的精度问题和RAND的性能问题是最常见的两个坑。金额精度上如果字段是FLOAT或DOUBLEROUND(1.005, 2)的结果可能不是预期的1.01因为浮点数本身存储就有误差。业务上涉及金额、税率等精确数值字段类型一定要用DECIMAL而不是FLOAT或DOUBLE这个习惯能省下大量对账时抓头发的时间。随机数方面ORDER BY RAND()只适合非常小的表一旦表上了几十万行用它做随机抽样基本就是灾难。前面提到的主键范围跳变方案速度会快一个数量级代价只是抽样的随机性不如RAND完全均匀但日常报表足够用了。最后分享一个我自己的使用习惯。做数据报表时我基本遵循三步走先用WHERE把数据范围卡死再用聚合函数把核心指标算出来最后用窗口函数解决排名、累计和对比需求数学函数只在前两步之间做数值精度和单位加工。顺序理清了SQL基本不会写得又臭又长。窗口函数出现之后很多以前要两条SQL配合的业务场景现在都收敛成了一条这是MySQL 8.0带来的最大红利如果你还在老版本上辛苦地写用户变量模拟排名真该认真考虑升级了。