企业官网建设流程全解析

做数据分析的时候，常有人问我一个问题：用GROUP BY做分组统计，结果只留下分组后的汇总行，可我还想同时看到明细数据，怎么办？比如按品类统计销量最高的三款商品，既要有品类名、商品名，又要按销量排出名次。常规思路是写子查询、做自连接，SQL又臭又长，性能还不一定好。MySQL 8.0推出的窗口函数就是专门解决这类问题的，它能在一行数据上“开一个窗口”，算出一组相关的值，而不会像GROUP BY那样把行数压下去。

这篇文章我围绕“窗口函数做分组”这个主题，把语法拆开讲透，配几个可以直接套用的实操案例，再聊聊执行计划和常见坑。适合正在用MySQL 5.7但准备升8.0的小伙伴，也适合那些会用GROUP BY但还没搞懂PARTITION BY和GROUP BY到底差在哪的开发者。

1. 分组这件事，GROUP BY和窗口函数差在哪

先说一个最容易被混淆的点。很多人第一次看到窗口函数里的PARTITION BY，会觉得这不就是另一个GROUP BY吗？两者确实都干“分组”这件事，但返回结果的行数逻辑完全不同。

GROUP BY的语义是“把多行合并成一行”。执行完后，每一组只留下一个聚合结果，非聚合列要么出现在GROUP BY子句里，要么被聚合函数包裹，否则MySQL会直接报错。这就意味着，如果你想在结果里同时看到订单明细和该订单所属用户的消费总额，用GROUP BY做不到——因为明细行已经被压掉了。

窗口函数不一样。它是在不留变化的情况下，“贴着每一行”额外算出一列分组结果。SUM(amount) OVER (PARTITION BY user_id)这一列，每一行都对应一个用户，但这一列的值是该用户所有订单的金额总和。每一行还是原样，没有丢失任何明细信息，只是旁边多出来一个“属于这个分组”的计算结果。

做个表对比一下：

实际业务中，这两种方式没有谁替代谁，是互补关系。GROUP BY适合出汇总报表，窗口函数适合在明细基础上做分析。你完全可以在一条SQL里同时用GROUP BY和窗口函数，比如先GROUP BY算出每个品类的总销量，再用窗口函数对这个结果集排序，两者不冲突。

1.1 窗口函数解决的四类典型分组需求

结合我做过的项目，窗口函数在分组场景下最常见的四类需求：

第一类：组内排名。每个班级按成绩排名、每个片区按销售额排名、每个分类按库存排名。这类需求用ROW_NUMBER、RANK、DENSE_RANK等着数函数就能搞定，而且一次扫描就能完成，不需要自连接。

第二类：组内累计值。分析用户消费行为时，经常要算“截止到当前订单，该用户的累计消费金额”。这就是SUM() OVER (PARTITION BY user_id ORDER BY order_time)，窗口内按时间排序，累加每一行，形成一条单调递增的曲线。用GROUP BY做这种计算只能算出总额，算不出中间过程的累计曲线。

第三类：组内对比。每个员工的薪资和所在部门的平均薪资差多少？每个商品的售价和所属品类的平均售价差多少？如果先GROUP BY再回表JOIN，SQL很绕；用AVG() OVER (PARTITION BY dept_id)直接在每行旁边算出部门均值，相减即可，清晰明了。

第四类：组间占比。每个品类的销售额占整体销售额的比例。传统写法要把总销售额单独查一次然后JOIN，窗口函数直接用SUM(sales) OVER ()拿到全局总和，一列搞定。

这四个场景覆盖了我在实际工作中遇到的大多数窗口函数应用，本质上都是“先按某个维度切分组，再在组内做计算”。

2. 窗口函数语法拆解：OVER(PARTITION BY ...)到底怎么读

窗口函数的语法表面上看有点吓人，核心其实就是两个部分：函数本身和OVER子句。OVER子句又由三块组成：PARTITION BY（分组）、ORDER BY（排序）、窗口子句（划范围）。

完整的语法长这样：

函数名(字段) OVER ( PARTITION BY 分组字段1, 分组字段2 ORDER BY 排序字段 ROWS BETWEEN 起始行 AND 结束行 )

PARTITION BY和ORDER BY是可选的，但实际使用中几乎不会都不写。什么都不写的时候，相当于把整张表当成一个窗口，所有行参与计算，适合算全局占比、全局排名。只写PARTITION BY不写ORDER BY，比如SUM(amount) OVER (PARTITION BY user_id)，算的是每个用户的总金额，每一行都一样。只写ORDER BY不写PARTITION BY，比如SUM(amount) OVER (ORDER BY order_time)，就是针对全表按时间累加。

理解这些的关键是：PARTITION BY决定了窗口的“边界”，ORDER BY决定了窗口内数据的“顺序”，窗口子句决定了参与计算的具体行范围。

2.1 三种排名函数的区别

排名是窗口函数最常用的场景，但很多人分不清ROW_NUMBER、RANK、DENSE_RANK。我用一组成绩数据放在一张表里说明：

三种函数的结果：

注意看几个关键点：ROW_NUMBER是纯粹的物理行号，即使分数一样也强制分前后，不会出现并列名次。RANK遇到并列时，相同值的行占用同一个名次，但后续名次会跳过。刚才的例子中，三个90分并列第一，下一个85分直接跳到第3名，中间空了一个2。DENSE_RANK也是并列相同名次，但后续名次不跳号，85分排在第二。

实操的时候，具体选哪个要看业务需求的表述。需要唯一编号的，比如“每个用户每月第几笔订单”，用ROW_NUMBER，因为订单有先后之分，不可能两笔订单同时是第1笔。展示排行榜名次的，比如“月销前十榜单”，通常用RANK或DENSE_RANK，避免并列时有人没名次。我个人的习惯是，如果业务方没有明确要求，优先用DENSE_RANK，因为名次连续，报表更好看。

2.2 聚合函数改造成窗口函数

除了排名函数，任何聚合函数——SUM、AVG、COUNT、MAX、MIN——都可以加OVER变成窗口函数。窗口版聚合函数和普通聚合函数最大的区别是计算粒度不同，普通聚合把整个分组汇总成一个数，窗口版聚合却能在每一行旁边计算分组的聚合值，让这个值参与行级运算。

这里要留意ORDER BY对聚合的影响。SUM(amount) OVER (PARTITION BY user_id)不带ORDER BY，算的是组内全部行的总和。加上ORDER BY order_time之后，它算的是从组内第一行到当前行的累计值，这就是累计求和。

还有一个容易忽略的细节：COUNT(*) OVER (PARTITION BY user_id)不带ORDER BY时，返回的是每个分组的总行数。同一分组里每一行返回的值都相同，等于给每行贴了个组大小标签。这在计算占比时特别有用，不用先GROUP BY再回表JOIN。

3. 实操案例：MySQL窗口函数分组应用全流程

光讲语法不落地没意义。我拿一个典型的电商销售明细场景来演示。假设有一张销售记录表，记录了每笔订单属于哪个品类、哪个商品、下单用户、下单时间和金额。建表语句里，引擎、字符集这些我就不展开了，直接看核心内容：

CREATE TABLE sales_detail ( id INT PRIMARY KEY AUTO_INCREMENT, category VARCHAR(50), product_name VARCHAR(100), user_id INT, order_time DATETIME, amount DECIMAL(10, 2) );

往里面插入一些测试数据，覆盖多品类、多商品、多用户的情况。这里不贴全部INSERT了，总之要让每个品类至少有三款商品，方便我们验证排名结果。

3.1 案例一：找出每个品类销量前三的商品

这是最常见也最容易被GROUP BY困住的需求。先按品类和商品分组，统计每个商品的销量总和，然后用窗口函数在分组后的结果集上排名，最后取前三。SQL是这样：

SELECT category, product_name, total_sales, sales_rank FROM ( SELECT category, product_name, SUM(amount) AS total_sales, ROW_NUMBER() OVER ( PARTITION BY category ORDER BY SUM(amount) DESC ) AS sales_rank FROM sales_detail GROUP BY category, product_name ) t WHERE sales_rank <= 3;

关键点是：GROUP BY先把细粒度数据汇总成商品维度的结果，然后窗口函数在“已汇总的结果集”上做分组排名。这里窗口函数的PARTITION BY针对的是GROUP BY之后的输出，不是原始表。很多人第一次写会搞混，以为窗口函数能在GROUP BY之前直接对原始表排名，其实顺序是固定的——先过滤、再分组、再开窗。我把GROUP BY和窗口函数写在同一条SQL里，它们各自负责不同的环节。

注意：如果你是MySQL 5.7及更早版本，这条SQL会直接报错，因为窗口函数是8.0才支持的特性。5.7环境下只能用用户变量模拟排名，或者用等值连接加HAVING COUNT的方式，SQL会变得非常绕，这也是我建议有条件就升8.0的原因。

同样的思路，把ROW_NUMBER换成DENSE_RANK，就能得到允许并列名次的前三名，结果集会多于三行，业务上通常也需要这样的榜单。

3.2 案例二：计算每个用户的累计消费额与占比

统计用户累计消费是一个典型的用窗口函数大幅简化SQL的场景。假设我们要看每个用户的每一笔订单、该订单的金额，以及截止到这笔订单为止该用户的累计消费金额，以及该笔订单金额占该用户总消费金额的比例。

SELECT user_id, order_time, amount, SUM(amount) OVER ( PARTITION BY user_id ORDER BY order_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_amount, amount / SUM(amount) OVER (PARTITION BY user_id) AS order_ratio FROM sales_detail ORDER BY user_id, order_time;

这里我故意做了两个不同的窗口：第一个窗口带ORDER BY order_time，并且显式声明了ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，表示窗口范围从该用户第一笔订单到当前这笔订单，算出来的是截止当前订单的累计金额。第二个窗口不带ORDER BY，范围是整个用户的所有订单，SUM算出来的就是用户总消费，拿当前订单金额除以总金额，就是这笔订单占用户总消费的比例。

实际写代码时，SUM(amount) OVER (PARTITION BY user_id ORDER BY order_time)这种写法不写窗口子句也能得到同样的累计结果，因为MySQL默认会给带ORDER BY的窗口设定成“从起点到当前行”。但我还是建议你显式写出窗口子句，防止其他人看代码时产生歧义，也防止某些数据库方言的默认行为不一致。

3.3 案例三：组内环比与移动平均

环比计算在运营报表里很常见：本月的销售额和上个月相比是涨了还是跌了。窗口函数提供了LAG和LEAD这两个偏移函数，LAG能取到当前行前面第N行的值，LEAD能取到后面第N行的值，非常适合做环比、同比和同期对比。

SELECT category, order_month, monthly_sales, LAG(monthly_sales, 1) OVER ( PARTITION BY category ORDER BY order_month ) AS prev_month_sales, ROUND( (monthly_sales - LAG(monthly_sales, 1) OVER ( PARTITION BY category ORDER BY order_month )) / LAG(monthly_sales, 1) OVER ( PARTITION BY category ORDER BY order_month ) * 100, 2 ) AS growth_rate FROM ( SELECT category, DATE_FORMAT(order_time, '%Y-%m') AS order_month, SUM(amount) AS monthly_sales FROM sales_detail GROUP BY category, DATE_FORMAT(order_time, '%Y-%m') ) t ORDER BY category, order_month;

这段SQL的逻辑是：先用GROUP BY按品类和月份汇总出月销售额，再在外层用LAG取出上个月的销售额，最后计算环比增长率。第一个月因为没有上个月的数据，LAG返回NULL，增长率自然也是NULL，这是正常现象，不用特殊处理，报表上展示为“-”就行。

还有一点值得说：当你要用某个窗口函数的结果再参与运算时，一定要嵌套一层子查询再算。不要想着在同一条SELECT里引用同一个窗口函数的结果多次做复杂运算，虽然MySQL允许你重复写这个窗口函数，但SQL会变得冗长且执行计划会重复计算，不如子查询干净。

4. 分组场景下窗口函数的执行计划与性能调优

窗口函数用起来很爽，但很多同学在生产环境跑大表时会遇到性能问题。这时候你需要知道MySQL到底怎么执行窗口函数。

先说执行顺序。一条SQL从书写到执行的逻辑顺序是FROM、WHERE、GROUP BY、HAVING、窗口函数、SELECT、DISTINCT、ORDER BY、LIMIT。窗口函数在GROUP BY和HAVING之后执行，这意味着窗口函数的输入是已经过滤和聚合完的结果集。这个顺序决定了两件事：窗口函数只能处理已经留下来的行，过滤条件写得越早，窗口函数处理的数据量就越小；同时，窗口函数不能出现在WHERE里，因为WHERE执行的时候窗口函数还没算。

执行窗口函数时，MySQL内部的大致流程是：先根据PARTITION BY字段把结果集分成多个分区，每个分区再根据ORDER BY字段做一次排序，然后逐个分区计算窗口值。所以窗口函数的性能瓶颈主要集中在两个地方：分区带来的数据重排和排序带来的内存/临时文件消耗。

4.1 用EXPLAIN观察窗口函数执行开销

MySQL的EXPLAIN在窗口函数场景下，Extra列通常会出现Using filesort，这代表执行计划里有排序操作，不一定是要避免的，但要注意排序规模。如果数据量很大，排序就会从内存落到磁盘上的临时表，IO开销会变得非常明显。

怎么判断排序规模？一种办法是用EXPLAIN ANALYZE，这是MySQL 8.0.18以后支持的功能，它能实际执行SQL并返回每一步的耗时和行数。拿刚才的排名案例来分析，会看到在窗口函数步骤有一行类似“Table scan”和“filesort”的信息，这就是窗口排序的现场。

如果你的查询性能不理想，优先看有没有办法让PARTITION BY和ORDER BY字段顺序匹配上某一个联合索引。最理想的情况是联合索引的第一个字段等于PARTITION BY字段，第二个字段等于ORDER BY字段，这样MySQL在读取索引时数据就已经按分区和排序排好了，可能直接省掉filesort。比如PARTITION BY category ORDER BY order_time，那么联合索引(category, order_time)就非常契合这个窗口定义。

4.2 减少排序与内存占用的一些策略

第一，尽可能在窗口函数执行前缩小数据量。前面说过窗口函数是在GROUP BY和HAVING之后执行的，所以能用GROUP BY先聚合的，就先聚合，不要对全表明细开窗。对明细行数巨大的订单表做排名，和对商品维度的汇总表做排名，量级差距可能不止一个数量级。

第二，避免在窗口函数里使用SELECT DISTINCT。窗口函数产生的列标记着每一行，加上DISTINCT后，MySQL会对结果集再做一次去重，代价是全表级的临时表，通常等于把同样的数据多算了一遍。如果业务上真的需要去重，想想能不能在子查询里提前去重，而不是在外层DISTINCT。

第三，限制PARTITION的数量。PARTITION BY user_id如果用户的量级非常大，比如千万级，那窗口分区的数量就是千万个，每个分区可能只有一行数据。这种情况下排序成本反而低，但每个分区都要做一遍初始化操作。如果业务可以接受，可以考虑改用普通聚合加JOIN，在某些特定场景下性能反而更好。

第四，排序字段不要写复杂的表达式。比如ORDER BY DATE_FORMAT(order_time, '%Y-%m')，这种写法会导致MySQL无法使用索引排序，只能全量排序再计算。如果非得按月份排序，提前在表中冗余一列month，或者用GROUP BY DATE_FORMAT(...)先把结果集压缩。

5. 常见问题与排查实录

实际使用窗口函数做分组，有几个坑我踩过不止一次，列出来给各位提个醒。

第一个坑：PARTITION BY字段里有NULL。窗口函数对NULL的处理和GROUP BY不一样。GROUP BY会把所有NULL放到同一个分组里，窗口函数虽然官方说PARTITION BY会把NULL也视为一个独立的组，但在MySQL某些版本中，NULL的处理行为可能和预期不完全一致。我遇到过一次：用户表中未填写省份的几条记录被单独分成了一个“NULL省份”的组，单独排名没问题，但业务方要求NULL统一归到“未知省份”，如果你的SQL没有处理这种情况，结果就会和预期不符。解决办法是提前用COALESCE把NULL值替换成指定值，比如COALESCE(province, '未知')。

第二个坑：ORDER BY的字段放错地方导致“同值不同排名”。写ROW_NUMBER的时候，如果PARTITION BY正确但ORDER BY没有包含足以区分顺序的字段，并列值之间的顺序就变得不确定。比如订单表按用户分区、按下单时间排序，但同一秒有两笔订单，ROW_NUMBER分配的前后顺序无法保证每次执行结果一致。严格来说，需要排名的业务场景，把唯一标识字段（如订单号）追加到ORDER BY的最后作为决胜条件。

第三个坑：窗口函数里嵌套聚合函数。很多人会尝试写SUM(AVG(amount)) OVER (...)，MySQL会直接报错，因为聚合函数不能再套窗口函数。正确做法是先写一层子查询算出AVG(amount)，外层再用SUM(...) OVER (...)。这个嵌套顺序问题，本质上还是执行顺序决定的。

第四个坑：MySQL 5.7及之前版本不支持窗口函数。如果你负责的库还是5.7，又无法立刻升级，只能用用户变量来模拟排名。典型写法是：

SELECT category, product_name, total_sales, @rank := IF(@prev_category = category, @rank + 1, 1) AS sales_rank, @prev_category := category FROM ( SELECT category, product_name, SUM(amount) AS total_sales FROM sales_detail GROUP BY category, product_name ORDER BY category, SUM(amount) DESC ) t, (SELECT @rank := 0, @prev_category := NULL) r;

这段代码能跑，但依赖用户变量赋值的执行顺序。MySQL官方文档虽然没有明令禁止用户变量依赖SELECT中其他表达式的求值顺序，但实际执行时，上例的@prev_category := category必须放在@rank计算之后，否则前一行还没有更新分类，排名就会错。这种写法很脆弱，做复杂分组时极易出错，只适合过渡阶段。

第五个坑：窗口子句边界写错导致累计值异常。ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW是累计到当前行，ROWS BETWEEN 1 PRECEDING AND CURRENT ROW是当前行与前一行共两行，ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING是分组的全部行。如果默认边界和你的预期不一致，数值就会莫名其妙。我建议新手把窗口子句当作文档一样完整写出来，不要省。

还有一个容易被忽略的点：窗口函数的结果不能直接在WHERE中使用，必须套一层子查询。这一点和第3节里提到的嵌套原因一致，因为执行顺序不允许。如果你写了WHERE sales_rank = 1，MySQL会报错“Unknown column 'sales_rank' in 'where clause'”。必须先用子查询把窗口列算出来，再在外面过滤。

最后分享两个经验。第一，窗口函数对习惯写SQL但没系统学过8.0新特性的人来说，最大的挑战不是语法本身，而是思维模式的转变：GROUP BY是“压缩视角”，窗口函数是“逐行视角”。实际分析任务中，能改写成窗口函数的自连接SQL，尽量改写，代码可读性和执行效率都会有明显改善，我这边对比过好几条SQL，改完后执行时间普遍下降三成以上，这在千万级数据量上很可观。第二，PARTITION BY字段的基数不要太小也不要太大，太小了分区没有区分度，太大了每个分区只有几行数据，窗口函数的收益就体现不出来。结合业务合理选择分组维度，比后期调索引更关键。

MySQL的窗口函数这个特性，我是从8.0正式发布稳定后才敢在线上用的，之前5.6时代用变量模拟，5.7时代写法有了改善但依然别扭。现在再让我去写那些依赖自连接和临时表的SQL，真的回不去了。建议各位把手头还在跑5.7的环境尽早规划升级路径，窗口函数只是一个例子，8.0里很多新特性都能让日常开发省下大把时间。对于只是偶尔写写分析SQL的人来说，掌握本文这几种常用窗口函数，足以覆盖90%以上的分组分析需求。