MySQL函数深度解析：聚合、窗口、数学函数的本质区别与SQL实战

1. 三类函数的定位差异很多人第一次搞清楚“它们根本不是一个维度”接触MySQL有一段时间的同学大概率都有过这样的困惑明明都是“函数”为什么聚合函数、窗口函数、数学函数用起来的感觉完全不一样甚至很多人在写SQL的时候会把SUM()既当聚合用又当窗口用还搞不清楚什么时候该用ROUND()什么时候该用FORMAT()。这其实是因为——这三类函数压根不在同一个维度上。聚合函数多行输入一行输出。典型代表COUNT()、SUM()、AVG()、MAX()、MIN()。它的核心逻辑是“把一组行折叠成一个值”。注意这个“一组行”的边界是由GROUP BY决定的如果没有GROUP BY那就是把全表当成一组。窗口函数多行输入多行输出。典型代表ROW_NUMBER()、RANK()、SUM() OVER(PARTITION BY ...)。它是在“不折叠行数”的前提下对每一行计算出一个基于其所属窗口的值。换句话说聚合函数是“压缩”窗口函数是“透视”。数学函数一行输入一行输出严格说是单值输入单值输出。典型代表ABS()、ROUND()、CEILING()、FLOOR()、RAND()、POW()。它做的是纯粹的数值变换跟行与行的关系没有任何瓜葛。这个区分听起来简单但实际开发中有大量写错SQL的情况根子就在于没想明白“你到底是想让结果变少还是保持原行数只是多一列”。举个最常见的场景你想统计“每个部门的平均薪资”那你用AVG(salary) GROUP BY dept_id结果是每个部门一行。但如果你想在“保留每一名员工明细”的基础上额外看到“他所在部门的平均薪资”用来做对比那就必须用窗口函数AVG(salary) OVER(PARTITION BY dept_id)。这两种写法表面看到的数据可能长得差不多但行数完全不同语义完全不同性能特征也完全不同。我自己的习惯是拿到需求第一步先问自己三句话结果集的行数是变少、不变、还是变多计算的范围是全表、分组、还是每行自己的“滑动区间”结果的精度要求和舍入规则是什么这三点想清楚了下面所有函数的选择就是顺水推舟的事。这篇就把三类函数逐个拆开讲清楚原理、用法、典型坑再加一个三类函数协同使用的综合例子。2. 聚合函数GROUP BY只是起点真正的坑在分组逻辑和NULL上2.1 聚合的本质是“折叠”折叠的边界由GROUP BY决定聚合函数与GROUP BY几乎是绑定出现的但很多人对GROUP BY的理解停留在“用来分组”这句话上而没有真正理解它的执行逻辑。当MySQL执行一条带GROUP BY的查询时实际发生的事情是先把表中所有行按照分组字段的值重新排列可以理解为按桶归类同一个桶里的所有行被合并成一组然后聚合函数在这个桶内执行折叠计算。这里有一个极其容易踩的坑SELECT中出现的非聚合列必须出现在GROUP BY中。MySQL有一个著名的宽松模式它允许SELECT中出现既不在GROUP BY里、也不是聚合函数的列并且不会报错只是返回的值是“该组内的任意一行”的该列值。这在ONLY_FULL_GROUP_BY未开启的MySQL 5.7及更早版本里非常常见。我见过一个真实的生产事故某运营报表统计每天的订单量和订单金额SQL里GROUP BY order_date但SELECT里额外带上了user_id。因为MySQL的宽松模式没报错上线跑了两个月直到某天某组内有多个用户报表中随机出现了一个用户ID数据对不上账排查了半天才定位到这个SQL。所以现在我的铁律是凡是出现在SELECT列表中的非聚合列一律显式写入GROUP BY。如果某个列的值在组内不确定、且你只需要任取一个那就别直接裸写要么用MAX()/MIN()包一层要么明确用ANY_VALUE()表达意图。2.2 COUNT(*)与COUNT(字段)的区别是面试也是实战的分水岭COUNT(*)和COUNT(字段)的区别很多人都知道“一个是统计行数一个是统计非NULL个数”但实战中还是会出错。COUNT(*)统计的是“行的个数”不管这一行里的字段是不是NULL都会被算进去。它本质上是统计“从表中取出的行数”。COUNT(column)统计的是“该列非NULL值的个数”凡是为NULL的行直接跳过。这个区别最典型的战场是LEFT JOIN场景。比如你有两张表一张是订单主表orders一张是订单明细表order_items一个订单对应多条明细。你想知道“每个订单关联了几条有效明细”你会写SELECT o.order_id, COUNT(oi.item_id) AS item_count FROM orders o LEFT JOIN order_items oi ON o.order_id oi.order_id GROUP BY o.order_id;如果订单10001在明细表中一条记录都没有LEFT JOIN后它的oi.item_id全是NULLCOUNT(oi.item_id)返回0这是正确的逻辑——“该订单没有任何明细”。但如果我改用COUNT(*)它会数出1行因为LEFT JOIN保底保留了orders表这一行结果就是订单10001显示有1条明细完全错了。这个坑的来源就是COUNT(*)在JOIN场景下数的是“JOIN之后的结果行数”而不是你心里的“主表行数”。记住需要统计“关联上多少条”的时候一律用COUNT(右表主键)不要用COUNT(*)。2.3 HAVING与WHERE的执行顺序差异聚合之后才能过滤聚合函数的第二个使用边界是WHERE和HAVING的分工。简单的说法是WHERE在分组聚合之前过滤行HAVING在分组聚合之后过滤组。这个说法对但不直观。我习惯用一个类比WHERE是“进考场之前的安检”不合格的人直接不让进考场HAVING是“考完试出成绩之后只挑及格线以上的人发奖学金”。举例查询“平均薪资大于8000的部门且该部门员工入职年限必须大于一年”SELECT dept_id, AVG(salary) AS avg_salary FROM employees WHERE hire_date DATE_SUB(CURDATE(), INTERVAL 1 YEAR) GROUP BY dept_id HAVING AVG(salary) 8000;这里WHERE先把入职不满一年的员工从计算范围中排除然后按部门分组求平均薪资再用HAVING筛选平均薪资达标的部门。如果把hire_date的过滤条件错放到HAVING里MySQL会直接报错因为hire_date不是分组键、也不是聚合结果聚合后压根没有这个原始列了。实战中常见的错误是把WHERE能干的活硬塞给HAVING造成全表分组后再逐组过滤性能下降一截。记住能用WHERE过滤的绝不放HAVING。HAVING只用来过滤“聚合值”和“分组键”。2.4 聚合函数与DISTINCT组合去重计数的正确姿势COUNT(DISTINCT column)是另一个经常被低估的组合。比如你要统计“每天有多少个不同用户下单”肯定不能用COUNT(*)那是订单数也不能用COUNT(user_id)那是订单次数同一个用户下多单会被反复计算必须用COUNT(DISTINCT user_id)SELECT order_date, COUNT(DISTINCT user_id) AS uv FROM orders WHERE order_date 2024-01-01 GROUP BY order_date;性能上要有一点心理准备COUNT(DISTINCT)的实现代价比COUNT(*)高很多它需要在内存或临时表里维护一个去重集合。数据量大到一定程度这个操作会显著拖慢查询。我踩过的坑是一张千万级表上做COUNT(DISTINCT user_id)分组统计一次查询跑了十几秒后来发现用户ID唯一性其实可以由另一张用户维度表保证范围于是改成先去用户表里圈定user_id范围再用COUNT(*)配合JOIN效率翻了几倍。另外SUM()、AVG()在遇到全NULL组时返回NULL而不是0。很多人希望“没数据就返回0”需要配合IFNULL()或COALESCE()处理。比如统计某产品线当月销售额没有订单时显示0而不是NULLSELECT product_line, COALESCE(SUM(amount), 0) AS total_amount FROM sales WHERE sale_month 2024-06 GROUP BY product_line;3. 窗口函数SQL从“折叠”走向“透视”的分水岭3.1 为什么需要窗口函数不压缩行数的同时看到分组聚合结果在窗口函数出现之前想在保留明细的同时看到分组汇总值通常靠的是“先GROUP BY得到汇总子查询再JOIN回明细表”。比如“每个员工看自己薪资的同时还想看部门平均薪资”SELECT e.emp_name, e.salary, d.avg_salary FROM employees e LEFT JOIN ( SELECT dept_id, AVG(salary) AS avg_salary FROM employees GROUP BY dept_id ) d ON e.dept_id d.dept_id;这个写法能跑但非常别扭多了一层子查询多一次JOIN代码可读性差而且一旦需要算“每个员工所在部门薪资排名”这种写法就力不从心了。窗口函数解决的就是这个场景它让你在每一行上都可以引用“以某种规则划定的一组行”的聚合结果、排名位置、偏移取值而完全不改变结果集的行数。MySQL从8.0版本开始完整支持窗口函数这是8.0最大的特性之一。如果你还在用5.7又频繁需要排名、累计、同比环比我的建议是尽快计划升级或者至少评估一下5.7里可用模拟方案的成本。3.2 OVER()的核心概念PARTITION BY不是GROUP BY的别名窗口函数的语法核心是OVER()子句其中三个关键组成部分各有职责PARTITION BY划定“窗口”的行范围。它看起来像GROUP BY但二者有本质区别PARTITION BY不会合并行它只是把结果集在逻辑上分成若干“分区”每一行仍然保留只是窗口计算只在本分区内进行。ORDER BY决定窗口内行的排序顺序。它直接影响排名类函数的结果也决定“累计型”窗口函数如SUM() OVER(...)的累计方向。ROWS/RANGE进一步限定窗口的“帧”范围支持滑动窗口场景如“近3天累计”。初学者最容易犯的错是把PARTITION BY dept_id理解成“按部门分组”然后以为SELECT里的dept_id会被折叠成每个部门一行——不窗口函数绝不减少行数每一行都会原样返回只是多了个计算列。举个例子查询每个员工薪资及所在部门的平均薪资SELECT emp_name, dept_id, salary, AVG(salary) OVER(PARTITION BY dept_id) AS dept_avg_salary FROM employees;这个结果中部门10有5个员工那么这5行每一行都会显示同样的部门平均薪资而5行明细全部保留。这和GROUP BY得到一行完全不同。3.3 排名函数三大件ROW_NUMBER、RANK、DENSE_RANK的差异排名类窗口函数是面试和实际业务的双重高频点但三者差异至今仍有人混淆。ROW_NUMBER()按排序顺序给每一行一个不重复的序号从1开始。即使排序值完全相同也会强制区分出先后先后顺序不确定。RANK()排序值相同的行会得到相同排名但下一个排名会跳跃。比如两个并列第1下一个直接是第3。DENSE_RANK()排序值相同得到相同排名但下一个排名不跳跃。两个并列第1下一个是第2。以成绩表为例姓名成绩ROW_NUMBERRANKDENSE_RANK张三95111李四95211王五90332赵六85443什么场景用哪个我一般这样定需求是“取Top N”且Top N不允许并列用ROW_NUMBER()比如抽奖、取前10名下单用户。需求是“竞赛排名”并列名次必须留空位用RANK()。需求是“并列名次后下一名紧跟着”比如评级分类95分以上是A90到95是B等用DENSE_RANK()。“取每个部门薪资最高的员工”是经典场景但要注意——如果部门内有两个同薪资的员工用ROW_NUMBER()会只保留一个用RANK()或DENSE_RANK()可能返回多个。这时候要先确认业务口径SELECT emp_name, dept_id, salary FROM ( SELECT emp_name, dept_id, salary, ROW_NUMBER() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employees ) t WHERE rn 1;3.4 聚合类窗口函数累计求和与移动平均的实战写法窗口函数里SUM()、AVG()、MAX()、MIN()都可以直接配合OVER()使用形成“聚合窗口函数”。它们和普通聚合的最大区别在于普通聚合是一次性算出整个分组的汇总值然后折叠成一行聚合窗口函数是每一行都保留并且可以配合ORDER BY做一个“逐渐扩展”的累计。累计求和是个经典场景。比如订单明细里要计算“每个用户从第一单到当前单的累计消费金额”SELECT user_id, order_date, amount, SUM(amount) OVER(PARTITION BY user_id ORDER BY order_date) AS cumulative_amount FROM orders;注意这里ORDER BY order_date的作用它告诉MySQL“累计要按照订单日期的先后顺序来到当前行为止”。如果不写ORDER BY窗口就是整个分区内所有行每一行显示的累计值都一样——都是该用户全部订单的总和那就不是“累计”而是“总计”了。这是非常容易踩的一个点同样的SQL只差一个ORDER BY语义天差地别。滑动窗口则是帧范围的应用比如加上ROWS BETWEEN 2 PRECEDING AND CURRENT ROWSELECT order_date, amount, AVG(amount) OVER(ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3d FROM daily_sales;这段SQL计算的是“当天及之前两天的平均销售额”也就是3日移动平均。在库存预测、销售波动分析里这种写法非常实用。MySQL 8.0中帧范围还支持RANGE模式比如RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW可以直接按时间区间滑动不需要自己数行数。3.5 取值类窗口函数LAG、LEAD、FIRST_VALUE、LAST_VALUE这几兄弟处理的是“跨行取值”常用于同比环比、前后对比。LAG(column, n, default)取当前行往前第n行的值。LEAD(column, n, default)取当前行往后第n行的值。FIRST_VALUE(column)窗口内第一行的值。LAST_VALUE(column)窗口内最后一行的值。但它有个容易被忽略的细节默认窗口帧是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW所以LAST_VALUE默认返回的是“当前行”而不是整个分区的最后一行。想拿分区最后一行必须显式指定帧ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。举个例子计算每天的销售额及“前一天销售额”SELECT order_date, amount, LAG(amount, 1) OVER(ORDER BY order_date) AS previous_day_amount, amount - LAG(amount, 1) OVER(ORDER BY order_date) AS day_over_day_change FROM daily_sales;这个写法在做日环比报表时特别常用比自连接优雅得多。而且自连接在数据量大时性能往往差于窗口函数因为自连接需要把同一张表加载多份、做匹配运算窗口函数则是在一次扫描基础上完成计算。3.6 窗口函数与GROUP BY能否混用什么时候能、什么时候不能一个容易踩坑的点窗口函数和GROUP BY同时出现时窗口计算是在分组之后进行的。也就是说GROUP BY先把行折叠成组然后窗口函数在折叠后的结果集上开窗。所以如果SQL里写了GROUP BY department然后又写SUM(salary) OVER(ORDER BY department)这里的SUM(salary)是对“每个department这一组”的salary也就是组内总薪资再做累计而不是对原始员工薪资做累计。我见过有人想实现“每个部门总薪资的累计占比”写成SELECT dept_id, SUM(salary) AS dept_total, SUM(SUM(salary)) OVER(ORDER BY dept_id) AS cumulative_dept_total FROM employees GROUP BY dept_id;这里内部的SUM(salary)是普通聚合得到部门总薪资外层的SUM(...) OVER(...)是在“部门分组结果”上开窗口做累计所以必须套一层SUM(SUM(salary))即“对聚合结果再聚合窗口求和”。这个双重嵌套经常让新手懵但理解了“窗口计算发生在聚合之后”就顺理成章了。如果业务确实是“先按部门聚合再对聚合结果做排名或累计”那这个写法完全没问题。但如果本意是“对每个员工计算他在部门内的排名”那就别写GROUP BY直接把窗口函数写在明细查询上。4. 数学函数被低估的数值处理工具箱4.1 取整家族ROUND、CEILING、FLOOR精度与舍入规则要看清数学函数在SQL里不像聚合、窗口那样“显眼”但实际写报表、做财务处理时几乎绕不开。最常用的是取整家族。ROUND(x, d)是四舍五入到d位小数。注意它的舍入规则是“四舍五入”不是“银行家舍入”。如果你处理金融金额需要确认业务方要的是哪种舍入方式因为ROUND在MySQL里的行为是对半分时“远离零舍入”而某些财务系统要求“逢五舍双”银行家舍入那就不能靠原生ROUND实现要么在应用层处理要么用自定义函数。CEILING(x)向上取整得到不小于x的最小整数。FLOOR(x)向下取整得到不大于x的最大整数。实战场景分页逻辑里“总页数 总条数 / 每页条数”这一般要向上取整。比如总数25每页10条25 / 10 2.5向上取整得到3页SELECT CEILING(COUNT(*) / 10) AS total_pages FROM orders;而金额计算里可能需要保留两位小数同时防止浮点误差。这里有一个经验财务金额不要用FLOAT/DOUBLE要用DECIMAL。DECIMAL(10,2)配合ROUND才是主流做法。ROUND(19.999, 2) 结果是 20.00 ROUND(19.995, 2) 结果是 19.99 -- 浮点存储误差导致非预期这个例子说明如果字段类型是FLOATROUND的结果可能和数学期望不一致。所以别再问我为什么19.995四舍五入不是20.00——先查字段类型大概率是FLOAT埋的雷。4.2 绝对值、符号、余数ABS、SIGN、MOD的工程用途ABS(x)取绝对值SIGN(x)返回符号正数返回1负数返回-1零返回0MOD(x, y)返回x除以y的余数。这三个函数看起来基础工程价值很大。ABS()计算误差时常用比如“找出与预测销售额偏差绝对值大于1000的月份”。SIGN()处理方向性数据比如库存异动方向用SIGN判断是补货还是消耗配合CASE还能做状态映射。MOD()经典用途是分表分库时的数据路由依据。比如一张用户表按ID取模分10张表那就可以用MOD(user_id, 10)决定数据落到哪张表。查询的时候也用同样的取模规则定位目标表这是中间件分表策略最基本的原理之一。另外MOD()还常用来做奇偶判断MOD(id, 2) 1是奇数 0是偶数。这在轮流分派、抽样分组里很实用。4.3 幂、根号与对数POW、SQRT、LOG的适用场景POW(x, y)计算x的y次幂。SQRT(x)计算平方根。LOG(x)计算自然对数LOG(base, x)指定底数。这些函数平时用得少但金融计算和数据分析里有用武之地复利计算本金P年利率r年数n本息和P * POW(1 r, n)。衰减模型放射性衰减、用户流失预测用EXP()和LOG()组合拟合曲线。标准差指标STDDEV()本身是聚合函数但如果你想在SQL层手算方差就离不开POW()。比如预测未来三年用户规模已知年增长率为5%当前用户10万第三年用户规模SELECT 100000 * POW(1.05, 3) AS predicted_users;4.4 RAND()的各种姿势随机抽样、随机排序、随机分桶RAND()返回0到1之间的随机浮点数使用广泛。最直观的用法是随机排序取N条SELECT * FROM products ORDER BY RAND() LIMIT 5;这个写法在小表上没问题但大表上性能极差——它会对全表每行生成随机数、排序、取前5。表有几百万行每次查询都要全表扫描加排序非常昂贵。生产环境要抽样更稳妥的是先算一个近似随机ID范围SELECT * FROM products WHERE id (SELECT FLOOR(MAX(id) * RAND()) FROM products) ORDER BY id LIMIT 5;这种方式仍有瑕疵ID不连续时抽样可能偏少但对于大多数“随机展示”场景效率和随机性都比ORDER BY RAND()好得多。RAND()的另一个用途是“按概率分桶”。比如A/B测试需要把用户随机分成两组各自50%SELECT user_id, CASE WHEN RAND() 0.5 THEN control_group ELSE test_group END AS bucket FROM users;注意只要SQL里用到RAND()这一行每次执行结果都可能不同。如果需要对一批用户做稳定的随机分组比如今天分完、明天查询分组不变就得把分桶结果落表不能每次查询现场算。4.5 角度与弧度RADIANS、DEGREES和SIN/COS的定位场景SIN()、COS()、TAN()默认参数是弧度不是角度。RADIANS(x)把角度转弧度DEGREES(x)把弧度转角度。地理坐标计算里这个组合很常用比如计算两个经纬度点之间的距离。假设两个点坐标分别为(lat1, lon1)和(lat2, lon2)简化的球面距离公式HaversineSELECT 6371 * 2 * ASIN(SQRT( POW(SIN(RADIANS((lat2 - lat1) / 2)), 2) COS(RADIANS(lat1)) * COS(RADIANS(lat2)) * POW(SIN(RADIANS((lon2 - lon1) / 2)), 2) )) AS distance_km FROM ...这里面的RADIANS()必不可少否则三角函数输入的度数偏差会引发灾难性误差。顺带说一句真正的生产环境我建议这类计算用空间函数ST_Distance_Sphere()或者直接在应用层算SQL写哈弗辛公式虽然可行但可读性和维护性都一般。数学函数的正确使用方式是“知道它能干什么而不是什么场景都硬上”。4.6 格式化与进制转换FORMAT、CONV、以及TRUNCATE的精度陷阱FORMAT(x, d)把数字格式化为“千分位分隔符指定小数位”的字符串。比如FORMAT(1234567.891, 2)返回1,234,567.89。注意返回类型是字符串不适合继续做数值运算。别把这个和ROUND搞混ROUND返回数值FORMAT返回字符串。TRUNCATE(x, d)直接截断到d位小数不做四舍五入。比如TRUNCATE(19.999, 2)返回19.99。如果你需要“不四舍五入只截断”它就是答案。但在使用时要清楚TRUNCATE本身是安全的数值截断不会像ROUND配FLOAT那样出现浮点误差导致的意外。CONV(num, from_base, to_base)做进制转换比如十六进制字符串转十进制CONV(FF, 16, 10)返回255。这主要用于底层开发日志解析、报文处理等日常业务SQL里用得相对少。5. 三类函数协同作战一个完整业务报表的演进5.1 从明细到汇总GROUP BY先行的基础报表假设有一个电商业务订单明细表order_items结构大致为字段说明order_id订单号user_id用户IDproduct_id商品IDcategory商品品类amount实付金额order_date订单日期第一个需求“统计每月的销售总额和订单数”。这用聚合函数就够了SELECT DATE_FORMAT(order_date, %Y-%m) AS month, COUNT(DISTINCT order_id) AS order_count, SUM(amount) AS total_amount FROM order_items GROUP BY DATE_FORMAT(order_date, %Y-%m) ORDER BY month;注意这里订单数用COUNT(DISTINCT order_id)因为一个订单可能包含多行明细。如果直接用COUNT(*)得到的是“明细行数”不是“订单数”。5.2 明细与汇总同时展示窗口函数补齐上下文的差距第二个需求来了老板想看“每个月的销售额、订单数同时要看到每月的销售额占全年总销售额的比例、以及环比增长率”。年度总销售额是一个“全表汇总值”在GROUP BY之后所有行都一样可以用窗口函数一次性算出来不需要额外子查询SELECT DATE_FORMAT(order_date, %Y-%m) AS month, COUNT(DISTINCT order_id) AS order_count, SUM(amount) AS total_amount, SUM(amount) / SUM(SUM(amount)) OVER() * 100 AS pct_of_total, ( SUM(amount) - LAG(SUM(amount)) OVER(ORDER BY DATE_FORMAT(order_date, %Y-%m)) ) / LAG(SUM(amount)) OVER(ORDER BY DATE_FORMAT(order_date, %Y-%m)) * 100 AS mom_growth FROM order_items WHERE order_date 2023-01-01 AND order_date 2024-01-01 GROUP BY DATE_FORMAT(order_date, %Y-%m) ORDER BY month;这里出现了前面提过的“窗口函数作用在聚合结果上”的典型写法内部的SUM(amount)先做普通聚合得到月度销售额外层的SUM(SUM(amount)) OVER()在聚合结果上计算全年累计LAG(SUM(amount))取上个月的聚合值做环比。这个SQL里有两层逻辑读起来有点绕但执行效率其实很高因为只需要一次分组聚合然后窗口函数在聚合后的少量结果行上运算完全不需要额外的自连接子查询。5.3 增加ABC分类和随机抽样检验数学函数收尾第三个需求对每个品类做ABC分类——累计销售额占比前70%是A类70%到90%是B类剩下是C类。这个需要用“累计占比”来判断靠的是窗口函数的累计求和SELECT category, SUM(amount) AS sales_amount, SUM(SUM(amount)) OVER(ORDER BY SUM(amount) DESC) AS cumulative_sales, SUM(SUM(amount)) OVER(ORDER BY SUM(amount) DESC) / SUM(SUM(amount)) OVER() AS cumulative_ratio FROM order_items WHERE order_date 2023-01-01 AND order_date 2024-01-01 GROUP BY category ORDER BY sales_amount DESC;然后在外面套一层用CASE按cumulative_ratio的阈值分类SELECT category, sales_amount, cumulative_ratio, CASE WHEN cumulative_ratio 0.7 THEN A WHEN cumulative_ratio 0.9 THEN B ELSE C END AS abc_class FROM ( SELECT category, SUM(amount) AS sales_amount, SUM(SUM(amount)) OVER(ORDER BY SUM(amount) DESC) / SUM(SUM(amount)) OVER() AS cumulative_ratio FROM order_items WHERE order_date 2023-01-01 AND order_date 2024-01-01 GROUP BY category ) t ORDER BY sales_amount DESC;这就是典型的“先GROUP BY聚合成汇总行再用窗口函数做累计占比最后用数学比较做分类”的三类函数协同。最后还要对A类品类下的商品做一次抽样检查验证数据质量。这个时候数学函数就派上用场了——随机抽取A类品类下3个商品明细SELECT product_id, category, amount FROM order_items WHERE category IN (SELECT category FROM {上面那个abc子查询} WHERE abc_class A) AND id ( SELECT FLOOR(MAX(id) * RAND()) FROM order_items WHERE category IN (SELECT category FROM {上面那个abc子查询} WHERE abc_class A) ) ORDER BY id LIMIT 3;这里用FLOOR(MAX(id) * RAND())做近似随机起点避免ORDER BY RAND()全表排序。虽然不是绝对均匀的随机抽样但对数据质量检查来说完全够用。6. 常用函数速查与避坑清单把这次提到的函数整理成一张速查表方便平时查分类函数功能典型坑聚合COUNT(*)统计行数含NULL行LEFT JOIN下会数出1行而非0聚合COUNT(col)统计非NULL值个数传NULL直接不计数聚合SUM / AVG求和 / 求平均全NULL组返回NULL需COALESCE聚合COUNT(DISTINCT col)去重计数数据量大时性能成本高窗口ROW_NUMBER唯一序号排名并列时随机分先后窗口RANK跳跃排名并列后下一个排名跳号窗口DENSE_RANK连续排名并列后下一个排名不跳窗口LAG / LEAD前后N行取值注意默认值和越界行为窗口SUM OVER(ORDER BY)累计求和忘写ORDER BY变成总计窗口AVG OVER(ROWS BETWEEN)滑动平均帧范围写错导致结果不预期数学ROUND四舍五入FLOAT字段有浮点误差陷阱数学CEILING / FLOOR向上 / 向下取整负数场景语义要确认数学RAND随机数大表ORDER BY RAND性能差数学TRUNCATE直接截断注意和ROUND语义区别数学FORMAT格式化字符串结果是字符串不能直接参与运算避坑清单我浓缩成五条都是能直接抄进代码注释里的那种SELECT中的非聚合列必须写进GROUP BY。MySQL宽松模式不报错但结果是随机的排查事故成本极高。LEFT JOIN后统计关联数量用COUNT(右表主键)而不是COUNT(*)。无匹配记录时前者返回0后者会返回1。窗口函数不减少行数。如果发现结果行数和预期不一致先检查是不是把窗口函数当聚合函数用了。聚合窗口和普通聚合混用时记住窗口计算发生在分组之后。需要“对聚合结果再开窗”就必须写成SUM(SUM(amount)) OVER(...)。财务字段用DECIMAL别用FLOAT。FLOAT配合ROUND出现的精度问题调试起来非常隐蔽。7. 我的个人体会用MySQL这些年我对这三类函数的态度一直在变化。早期做业务开发聚合函数用得最多窗口函数看不太懂数学函数觉得无关紧要。后来做数据分析类需求多了才发现窗口函数才是“让SQL从工具变成表达力”的关键——很多以前要用临时表、自连接、子查询绕半天才能实现的需求一个OVER()就清清爽爽地搞定了。数学函数则是“平时用不着用着要命”的类型。我印象最深的是一次促销活动复盘运营要按金额档位给用户分群我用FLOOR(amount / 100) * 100做了档位映射原本几百行的CASE WHEN瞬间被简化成一行。类似的例子还有库存周转天数计算、价格区间分布统计——都是数学函数在发挥杠杆作用。我也理解很多人对这三类函数的畏惧感尤其是窗口函数那一堆子句初看确实吓人。但我的建议是不要试图一次记住所有函数的参数先记住“聚合折叠行、窗口透视行、数学变换值”这个本质然后在实际需求里逐个击破。碰到“保持明细还能看到汇总”的需求就翻窗口函数碰到“四舍五入、取整、随机”的需求就翻数学函数碰到“统计、汇总、分组”的需求就翻聚合函数。SQL的学习没有捷径但方向对了函数再多也不怕。这篇的内容覆盖了最实用的一线场景剩下的就是在自己的表结构里多写、多踩坑、多总结。