企业官网建设流程全解析

取"每个部门工资前三名"这个需求，我面试过不少写SQL的候选人，也带过不少新人，发现大家撞上这类问题时的第一反应特别一致：先ORDER BY salary DESC再LIMIT 3，拿到的却是全公司前三；换GROUP BY，每个部门只剩一行汇总，人又没了。于是有人开始写三层子查询、写自连接，SQL跟俄罗斯套娃一样越套越深，跑起来还慢。其实窗口函数（Window Function）就是专门干这个的：它能在不折叠明细的前提下，在分组内做排序、排名、累计、移动计算，顺手把占比、同比、最新状态这些高频需求一并解决。现在MySQL 8.0、PostgreSQL、SQL Server、Oracle等主流数据库都已经原生支持，做数据分析或者后端开发基本绕不开。这篇文章我准备把窗口函数从语法骨架到生产实战完整串一遍，重点讲清楚它到底解决了什么、帧是怎么回事、哪几个坑我实际踩过，希望给你省点时间。

1. 先搞清楚GROUP BY的“折叠”与窗口函数的“不折叠”

1.1 GROUP BY做不到的四件事

很多人学窗口函数之前，其实并没有把“GROUP BY的边界”想透。GROUP BY能做的事是“把一个组压成一行”，但现实中大量的需求是“保留每一行明细，同时在组内做计算”，这就出现了真空地带。举几个我工作中真实遇到过的场景：

• 组内TopN：每个部门工资前三名。GROUP BY之后部门内的人都折叠成了聚合值，根本拿不到“第三名是谁”。
• 占比：每条销售记录既要保留自己的金额，又要带上所在地区的合计，用来算占比。GROUP BY能做到地区合计，但做不到“每条明细旁边都带着这个合计”。
• 移动平均：7日销售额移动平均，每一天需要一个横跨过去7天的滑动窗口，这个窗口和“按月分组”“按年分组”完全不是一个维度。
• 同比环比：今天和昨天比、这个月和上个月比，需要访问相邻行的数据，GROUP BY天生没有“相邻行”的概念。

这四个问题用自连接或者子查询也能写，但SQL会变得非常绕，而且一旦组内行数多、关联字段多，性能经常是灾难级别的。窗口函数就是为这些“分组之内、明细之上”的计算场景设计的。

1.2 折叠 vs 贴标签：一个容易记住的类比

我一般喜欢这样跟团队里的小朋友解释：GROUP BY像装箱，一箱苹果送出去之后，你只记得“这箱平均重量是185克”，每颗苹果长什么样全丢了。窗口函数则像在每个苹果上贴一个分组标签，苹果仍然一个个摆在桌上，但你能一眼看出哪些属于同一箱，还能按标签给它们排队、算累计、做标记，原始信息一点不少。

这就是“折叠”和“不折叠”的本质区别。GROUP BY的输出行数等于分组数，窗口函数的输出行数等于原表行数，每一行旁边多出一列计算结果。这个区别决定了你能拿它干什么：右键要保留明细、要分别操作每一行的时候，窗口函数才是对路的工具。

1.3 窗口函数到底能算出哪些东西

从能力上划分，窗口函数大致分四类：

后面我会按这三类（统计类用得少，先不展开）逐个拆解，但先得把窗口函数的“骨架”搭起来，因为很多坑都是从骨架理解偏差开始的。

2. OVER语法骨架：PARTITION BY、ORDER BY、帧三件套

2.1 一个窗口函数由三部分构成

窗口函数的通用语法长这样：

窗口函数名(...) OVER ( [PARTITION BY 分组列] [ORDER BY 排序列] [ROWS|RANGE BETWEEN 帧起点 AND 帧终点] )

三部分各管一摊：

• PARTITION BY负责把数据切分成几个互不相交的分组，和GROUP BY的分组逻辑相似，但完全不折叠行。
• ORDER BY负责在组内排序，它决定“当前行”的位置，也直接影响帧的默认起点。
• 最后一行的帧（Frame）是最少被人提、也最容易出事的部分，它定义“计算时到底看当前行附近的哪些行”。

我见过不少人把这三件事混在一起记，导致一个非常经典的误区：以为SUM(x) OVER(ORDER BY date)是整个表的合计。其实不是，后面我会在第5节详细讲。

2.2 PARTITION BY 和 GROUP BY 到底有什么区别

用一个表格把两者的差异摊开，应该一次就能记住：

举个特别典型的例子，同一张员工表：

SELECT dept_id, SUM(salary) AS dept_total FROM emp GROUP BY dept_id;

这个查询输出的是每个部门一行合计。而下面这个：

SELECT emp_id, emp_name, dept_id, salary, SUM(salary) OVER(PARTITION BY dept_id) AS dept_total FROM emp;

输出的是每个员工一行，每一行旁边都带着自己部门的工资合计。换句话说，PARTITION BY更像“在每条明细上挂一个组内汇总值”，而不是把明细吞掉。这个区别是所有窗口函数应用的基础。

2.3 ORDER BY在窗口里的三重作用

ORDER BY放在OVER()里面和放在SQL最外层的语义完全不同。它在窗口里有三重作用：

第一，决定排名类函数的先后顺序，这个最好理解。第二，让聚合函数变成“累计到当前行”的语义，比如：

SELECT sale_date, amount, SUM(amount) OVER(ORDER BY sale_date) AS cum_amount FROM daily_sales;

这里的cum_amount不是全表合计，而是“从最早日期累计到当天的合计”。每一天看到的数字都在变大，这就是累计求和。第三，ORDER BY参与定义帧边界，有它和没它，默认的“视野范围”完全不同。

2.4 帧（Frame）到底是什么

帧可以理解成“当前行在计算时能看到的行集合”。它有两种写法，ROWS和RANGE，语义有差异：

• ROWS BETWEEN 6 PRECEDING AND CURRENT ROW：物理上取当前行往前数6行，固定7行参与计算，做移动平均基本用它。
• RANGE BETWEEN ...：按键值范围框行，相同排序值的行会被一起框进来。比如按日期排序时，同一天有10条记录，RANGE会把10条全框进来，ROWS可能只框其中几条。

对新手而言，先记住一个结论：有ORDER BY时，帧默认是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，也就是“从分组起点到当前行（含相同排序值的所有行）”。没有ORDER BY时，默认是整个分区。这个默认行为既是累计计算的基础，也是无数诡异结果的根源。

3. 三类核心窗口函数逐个拆解：聚合、排名、偏移

3.1 聚合窗口函数：SUM/AVG/COUNT/MAX/MIN

聚合窗口函数就是把SUM、AVG这些通常跟GROUP BY搭配的函数放进OVER()里，行为从“折叠”变为“附加”。它们最常见的用途是算占比和组内对比，比如：

SELECT emp_id, emp_name, dept_id, salary, SUM(salary) OVER(PARTITION BY dept_id) AS dept_total, ROUND(salary / SUM(salary) OVER(PARTITION BY dept_id), 4) AS dept_ratio FROM emp;

一次查询同时拿到每个员工的工资、所在部门总工资、个人占部门比例。这个需求在窗口函数出现之前，得用子查询先算部门合计，再关联回员工表，写法繁琐还容易漏数据。

这里有一个细节很容易忽略：COUNT(*)和COUNT(列名)在窗口里同样有区别。COUNT(*)统计分区内所有行，包括某列为NULL的行；COUNT(salary)只统计salary不为NULL的行。如果分区内某列的NULL很多，两个结果会不一样，取数前要想清楚口径。

3.2 排名窗口函数：ROW_NUMBER/RANK/DENSE_RANK/NTILE

排名函数是窗口函数里最“出圈”的一类，也是TopN问题的标准答案。但三个函数对并列值的处理完全不同，选错就是事故。假设一组工资是100、100、90、80：

实际选型我一般是这么判断的：如果需求明确是“取前3条记录”，不需要关心并列，用ROW_NUMBER；如果是竞赛排名或者希望并列时后续名次空出来，用RANK；如果希望并列名次相同且不跳号，比如取“排名前3的人”且允许并列多取，用DENSE_RANK。

NTILE(n)稍微特殊一点，它把分区内有序数据尽量均匀地切成n桶，返回桶号。比如NTILE(4) OVER(ORDER BY score DESC)可以把学生按成绩分成四档。我用它做过一次抽样：给用户打标后按用户ID分区、按活跃度排序再分成10桶，取其中一桶做人工核查，比随机抽样更可控。

3.3 偏移窗口函数：LAG/LEAD/FIRST_VALUE/LAST_VALUE

偏移类的核心逻辑是“访问当前行之前或之后的某一行”。LAG(列, 偏移量, 默认值)取当前行往上数第n行的值，LEAD(列, 偏移量, 默认值)取往下数第n行的值。注意偏移量为1时可以省略，默认值不写时返回NULL。

典型用法是算环比：

SELECT sale_date, amount, amount - LAG(amount, 1, 0) OVER(ORDER BY sale_date) AS diff_from_prev, amount - LAG(amount, 7, 0) OVER(ORDER BY sale_date) AS diff_from_7d_ago FROM daily_sales;

LAG会把上一行的销售额带回到当前行，然后直接做减法。这里有个容易忽略的点：第一行没有“上一行”，如果不给默认值，LAG返回NULL，amount - NULL结果也是NULL，展示出来就是一个空值，报表上特别显眼。所以生产环境我一般都会用COALESCE或者直接在LAG第三参数填0。

FIRST_VALUE和LAST_VALUE取分区内第一个和最后一个值。但有一个特别坑的默认行为：当OVER()里写了ORDER BY时，LAST_VALUE的默认帧终点是“当前行”，也就是说，按日期升序排列时，到每一行计算“最后一条”，看到的其实还是当前行自己。我之前一度以为函数坏了，后来才反应过来是帧在捣鬼。要拿到真正的分区最后一行，必须显式指定帧：

SELECT sale_date, amount, FIRST_VALUE(amount) OVER(ORDER BY sale_date) AS first_amount, LAST_VALUE(amount) OVER(ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_amount FROM daily_sales;

这个坑值得记一下：凡是涉及“取首尾值”的窗口查询，先检查帧有没有写对。后面第5节我会再复盘一次完整的排查思路。

4. 五个生产高频场景的完整SQL

4.1 场景一：每个部门工资TopN

这是最经典的窗口函数入门题，也是一个典型的“看着简单、写起来容易错”的需求。正确写法是先用ROW_NUMBER在部门内按工资降序编号，再在外面过滤：

WITH ranked AS ( SELECT emp_id, emp_name, dept_id, salary, ROW_NUMBER() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM emp ) SELECT emp_id, emp_name, dept_id, salary FROM ranked WHERE rn <= 3;

注意这里必须用CTE或子查询包一层，不能在WHERE里直接写rn <= 3，原因很简单：SQL的执行顺序里，窗口函数在WHERE之后才执行，WHERE阶段根本看不到rn这个列，直接写会报错。这个细节新手必踩。

4.2 场景二：销售额累计到当天 + 日环比

一个查询同时看两个趋势指标：累计值和环比差额。

SELECT sale_date, amount, SUM(amount) OVER(ORDER BY sale_date) AS run_total, amount - LAG(amount, 1, 0) OVER(ORDER BY sale_date) AS day_over_day FROM daily_sales;

run_total用的是聚合窗口函数加ORDER BY，不需要显式写帧，因为默认就是“从起点累计到当前行”，正好符合需求。日环比则用LAG把前一天金额带回来做差。这个组合在生产报表里出现频率极高，一条SQL解决两个问题。

4.3 场景三：7日移动平均平滑趋势

日销售额波动通常很大，直接看折线图全是毛刺，业务方更爱看平滑后的趋势。7日移动平均的标准写法：

SELECT sale_date, amount, AVG(amount) OVER(ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d FROM daily_sales;

这里用ROWS而不是RANGE，是因为我们想要物理上“最近7条记录”，而不是“最近7个日期对应的所有记录”。如果一天有多条记录，RANGE BETWEEN 7 PRECEDING会把不止7行的数据都框进来，平均值就会偏。这个区别在生产里很容易被忽略，但直接影响数字。

4.4 场景四：每个用户的最新一条状态

用户状态表每天会写入多条记录，要取每个人的最新状态，这也是窗口函数擅长的“每组取最新”问题：

WITH latest AS ( SELECT user_id, status, update_time, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY update_time DESC) AS rn FROM user_status_log ) SELECT user_id, status, update_time FROM latest WHERE rn = 1;

思路和TopN完全一致：分区键换成user_id，排序键换成update_time DESC，然后过滤rn = 1。这个模式可以套用到“每个商品最新价格”“每个订单最新物流状态”等各种场景，属于窗口函数的万能解法之一。

4.5 场景五：地区销售额占比与排名同屏输出

窗口函数最舒服的地方是一个查询里同时输出多个粒度的结果。比如既要每个地区每条记录的金额占比，又要看它在地区内的排名：

SELECT region, sale_date, amount, ROUND(amount / SUM(amount) OVER(PARTITION BY region), 4) AS region_ratio, RANK() OVER(PARTITION BY region ORDER BY amount DESC) AS region_rank FROM daily_sales;

一条SQL同时完成：明细保留、地区合计、每条记录的占比、地区内排名。如果是用传统写法，得先GROUP BY地区得到合计，再关联回明细，再在结果集上做排序，至少三步，而且中间很容易产生重复数据。这就是窗口函数“一次扫描、多次计算”的价值。

5. 窗口函数最容易踩的五个坑及完整排查链路

5.1 坑一：在WHERE里过滤排名结果

这个前面已经提过，但值得展开说一下完整链路。第一次写TopN的人，十有八九会先试这个：

SELECT emp_id, emp_name, dept_id, salary, ROW_NUMBER() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM emp WHERE rn <= 3;

跑出来直接报错“Unknown column rn”。这时候不要慌，先在脑子里过一遍SQL的执行顺序：FROM → WHERE → GROUP BY → HAVING → 窗口函数 → SELECT → DISTINCT → ORDER BY → LIMIT。窗口函数在WHERE之后执行，所以WHERE阶段根本不存在rn这个列。解决办法不是去改数据库配置，而是用CTE或子查询把窗口结果先算出来，再在外层过滤。定位到“执行顺序”这个根因之后，同类问题基本不会再犯。

5.2 坑二：以为SUM OVER(ORDER BY)是全表合计

这是我自己刚接触窗口函数时踩过的坑，也见过不少同事踩。看下面这个查询：

SELECT sale_date, amount, SUM(amount) OVER(ORDER BY sale_date) AS col FROM daily_sales;

预期是全表合计一个常数，实际跑出来却逐行递增，于是开始怀疑数据库是不是算错了。排查链路很简单：有ORDER BY时，默认帧是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，也就是累计到当前行。如果真的要全表合计，两个选择：要么去掉ORDER BY，写SUM(amount) OVER()；要么显式写帧ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。判断标准就一句话：你到底想要“每一行都看到同一个总数”，还是“每一行看到从前到现在的累计值”。

5.3 坑三：RANK与DENSE_RANK选错，TopN人数不对

业务方说“我要每个部门工资前3”，如果直接RANK()然后WHERE rn <= 3，很可能取回来五六个人。原因在于并列值：如果有5个人工资都是99万，RANK会让这5个人并列第一，前3名必然超过3人。DENSE_RANK更“慷慨”，并列的人名次相同且不跳号，但同样可能因为并列多取人。

排查这类问题，不要急着改函数，先问业务方一个关键问题：“前3名是严格只要3个人，还是名次在前三的人都要？”如果严格3人，用ROW_NUMBER，并列时按某种规则分出先后；如果要包含所有满足名次条件的人，再用RANK或DENSE_RANK。我在实际工作中就因为这个和业务方来回确认过，数据口径这种东西，宁可多问一句，不要事后返工。

5.4 坑四：LAST_VALUE取出来的不是最后一行

现象是这样的：按日期升序排序，用LAST_VALUE(amount) OVER(ORDER BY sale_date)想取每天之后的最新值，结果每一行返回的都是当前行自己的值，等于没取。排查时先怀疑函数写错，但检查语法无误后，就需要回到帧的默认边界：有ORDER BY时，帧的终点默认是CURRENT ROW。什么叫“最后一行”？在每一行的视角里，到当前行就结束了，所以LAST_VALUE自然返回当前行。真正的修复方式是显式把帧终点扩展到分区末尾：

LAST_VALUE(amount) OVER(ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

记住一个经验：凡是想取“分区内最后一条”的值，必须先检查帧，显式写UNBOUNDED FOLLOWING，不要依赖默认行为。

5.5 坑五：窗口函数嵌套聚合直接报错

有时候我希望“先按部门聚合，再在聚合结果上做排名”，于是顺手写出这样的SQL：

SELECT dept_id, MAX(SUM(salary)) OVER() FROM emp;

这在大部分数据库里直接报错，原因是窗口函数的参数里不能直接嵌套聚合函数。排查思路是意识到这两层计算的先后关系：SUM(salary)需要先按部门算出来，之后才轮到窗口函数加工。解决办法是先用GROUP BY把聚合结果做成一张中间结果，再对它开窗口：

WITH dept_salary AS ( SELECT dept_id, SUM(salary) AS total_salary FROM emp GROUP BY dept_id ) SELECT dept_id, total_salary, RANK() OVER(ORDER BY total_salary DESC) AS dept_rank FROM dept_salary;

这个套路也提醒我：窗口函数说的是“在已有列上做行间计算”，它不会替你完成“先分组聚合再开窗”这个两阶段动作，得自己拆开写。

6. 窗口函数的性能优化与我的使用习惯

6.1 窗口函数为什么慢：全分区排序的代价

窗口函数不是免费午餐。它慢在几个地方：PARTITION BY需要把数据按分区键重新组织，ORDER BY需要在每个分区内排序，复杂的帧计算需要反复访问邻近行。数据量百万级以下感觉不明显，一旦到千万级，一个不讲究的窗口查询能把数据库拖到告警。

我之前处理过一张千万级的订单明细表，要在每个客户分区内算累计消费，最初版本直接在全部历史明细上开窗，跑了快两分钟。优化之后十几秒内出结果，关键在于三件事。

6.2 三条优化原则

第一，先过滤，后开窗。窗口函数是在“FROM已经确定的行集”上计算的，所以能用WHERE先缩小的范围一定要先缩小。比如只算最近三个月，就先在子查询里过滤日期，再对过滤后的结果开窗，不要在全表上开完窗再过滤。

第二，建匹配PARTITION BY + ORDER BY的组合索引。窗口函数的排序可以被索引直接满足，省掉内存排序。比如经常写PARTITION BY user_id ORDER BY create_time，就建(user_id, create_time)联合索引。这个优化对排名类、累计类窗口特别明显。

第三，不要把窗口函数套好几层。有人为了在一个结果里同时算排名和累计，写三层嵌套子查询，每层都带窗口函数。实际上大部分需求一层窗口就能完成，因为OVER()里可以同时写多个不同的窗口定义。复用同一个窗口用WINDOW子句或者CTE，别硬套。

6.3 避免在窗口结果上做DISTINCT

还有一个我自己踩过的性能坑：在带窗口函数的查询外层加DISTINCT。窗口函数是“逐行计算”的，算完以后每一行都会带着一份窗口结果，DISTINCT需要把所有这些列全部比对一遍，代价极高。而且如果窗口结果本身受排序影响，DISTINCT之后留下来的行可能和你预期的并不一致。正确的做法是在开窗之前先把重复数据清掉，明细干净了再上窗口。

6.4 我的使用习惯

写窗口函数这几年，我养成了一个习惯：动手写SQL之前，先在纸上画一个“当前行 + 帧范围”的小草图。窗口很小，想象一下计算到某一行时，到底哪些行参与计算，结果就明朗了。三个默认值我到现在还会反复默念：没有ORDER BY就是整个分区，有ORDER BY默认累计到当前行，LAST_VALUE想取真末尾必须显式扩帧。TopN的需求我从来不会上来就写函数，先确认并列怎么处理，再决定用ROW_NUMBER、RANK还是DENSE_RANK。

窗口函数真正的难点不在语法，而在“计算视野”的转换：从“我要对整组算一个数”切换到“我要对每一行，在它所属的组里，取它附近的一小片行来算”。一旦完成了这个转换，后面再用LAG算环比、用ROWS做移动平均、用ROW_NUMBER取最新记录，都只是顺手的事。