企业官网建设流程全解析

今天是2026年1月26日，周一。这篇学习日记（260126 就是今天的日期）记录的是我一整天从零开始系统啃"SQL 窗口函数"的过程。起因并不复杂：上周帮业务侧搭报表，发现一个"既要看明细、又要看分组汇总、还要看排名"的需求，GROUP BY 怎么拼都别扭，最后用三四个子查询加两轮 JOIN 才勉强跑通，性能还惨不忍睹。我当时就决定，必须把窗口函数彻底搞明白。

窗口函数能解决什么问题，一句话讲清：在不合并明细行的前提下，给每一行补上它所在分组内计算出来的统计值。比如同一行里既能看到这笔订单金额，又能看到这个部门的总销售额，还能看到它在部门里的排名和累计进度。适合谁看这篇文章：正在学 SQL 的初学者、天天写报表的数据分析师，以及想从"会 GROUP BY"跨到"能处理复杂分析需求"的后端开发同学。

1. 为什么我会专门安排一整天学窗口函数

1.1 一个真实需求把我逼到这一步

业务那边提的需求其实很常见：有一张订单表，结构大概是订单号、部门、员工、金额、日期。老板想看的报表长这样：每个员工一条明细，同时旁边要有"这个员工所在部门的总销售额""部门内按销售额从高到低的排名""部门内从月初到当天的累计销售额"。

这个需求放在"只会 GROUP BY"的人手里，真的很痛苦。GROUP BY 会把同一个部门的员工合并成一行，但老板又要看每个员工，两件事本身就是矛盾的。我早期为了在明细行旁边带出部门总量，写过这种查询：

SELECT o1.employee, o1.amount, o1.dept_id, (SELECT SUM(o2.amount) FROM sales_order o2 WHERE o2.dept_id = o1.dept_id) AS dept_total, (SELECT COUNT(*) + 1 FROM sales_order o3 WHERE o3.dept_id = o1.dept_id AND o3.amount > o1.amount) AS dept_rank FROM sales_order o1;

这种写法的问题太多了。第一，SQL 里密密麻麻塞满相关子查询，读起来像在看乱码；第二，表一大了性能直线下降，每个子查询都要重新扫一遍表或者依赖很难命中的索引；第三，一旦再加"累计到今天为止"这种纵向条件，子查询套子查询就会把人彻底绕晕。跑完那次报表之后，我就把"系统学窗口函数"这件事列进了学习清单，今天正好轮到它。

1.2 窗口函数和 GROUP BY 的本质区别

先把这个最核心的认知掰清楚。很多人学窗口函数时老糊涂，是因为还带着"分组就是 GROUP BY"的惯性思维。两者的差别其实一句话就能概括。

GROUP BY 是把多行压成一行。它就像一个收纳盒，把属于同一个组的所有行倒进去，最后只还给你一个总数。比如 8 条订单按部门分组，结果就只剩 3 行：A、B、C 各一行，你再也看不见里面任何一个员工。

窗口函数是"在原表旁边加一列"。它不改变行的数量，每一条明细还在，只是在旁边多出一个根据某个分组计算出来的值。这个"窗口"你可以理解成"透视框"：扫描每一行时，数据库会以这一行为基准，画出一个限定范围的行集合，在这个集合内做 SUM、AVG、排名，计算完再把结果填回这一行。

我做了一张对比表放在日记里，和菜鸟时期的自己做了个对照：

这个认知一旦建立，后面所有语法都是同一个语法：函数() OVER (PARTITION BY ... ORDER BY ...)，PARTITION BY 相当于"按什么分组"，ORDER BY 决定"窗口内怎么排序或累计"，整句的意思就是"对每一行，在指定分组内做一次计算"。

1.3 今天的实验环境和准备工作

学习窗口函数不用搞分布式集群，本地一个数据库足够。我准备了两个环境：一个是本地正式的 MySQL 8.0，用来验证语法和踩坑；另一个是临时拉起来的 DuckDB，用来做快速实验。DuckDB 对窗口函数支持很全，而且单机跑几百 GB CSV 都不费劲，特别适合核对结果，后面我用到时再细说。

为了让例子能被复现，我建了一张极简订单表。今天一整天的所有实验都围绕它：

CREATE TABLE sales_order ( order_id INT PRIMARY KEY, dept_id CHAR(1) NOT NULL, employee VARCHAR(20) NOT NULL, amount DECIMAL(10,2) NOT NULL, sale_date DATE NOT NULL ); INSERT INTO sales_order VALUES (1, 'A', '小王', 1200.00, '2026-01-05'), (2, 'A', '小李', 2200.00, '2026-01-08'), (3, 'A', '小王', 1800.00, '2026-01-12'), (4, 'B', '小张', 1500.00, '2026-01-09'), (5, 'B', '小陈', 2600.00, '2026-01-11'), (6, 'B', '小张', 900.00, '2026-01-16'), (7, 'C', '小刘', 1700.00, '2026-01-15'), (8, 'C', '小刘', 2300.00, '2026-01-20');

这 8 行数据量虽小，但覆盖了我要玩的三类典型情况：多行同组、组内多排序字段、以及不同员工交错出现。后面讲每个函数我都会用真实输出说话，而不是只贴语法。

提示：如果你跟着练，别用太大的表。学习窗口函数初期，手工能算出来的小表才是最好的实验台。数据一多，你根本分不清结果是数据库算错了，还是你自己理解错了。

2. 核心细节拆解：四类窗口函数的学习笔记

2.1 排序编号类：ROW_NUMBER、RANK 和 DENSE_RANK 差在哪

这一组是平时用得最多的。需求里那句"按销售额从高到低排个名"，就该交给它们。但它们三个的细微差别，我敢说很多人没真正搞懂。直接上我今天的实验：

SELECT employee, amount, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY amount DESC) AS row_no, RANK() OVER (PARTITION BY dept_id ORDER BY amount DESC) AS rank_no, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY amount DESC) AS dense_no FROM sales_order;

假设同部门的两个员工金额恰好相同，比如 A 部门小王和小李都是 2200 元。ROW_NUMBER 会毫不客气地给它们分别编为 1 和 2，完全无视并列；RANK 会让两人并列第 1，但下一个人直接跳到第 3；DENSE_RANK 同样让两人并列第 1，下一个人接着排第 2。

一句话记忆法：ROW_NUMBER 是"唯一编号，有先后没人情味"；RANK 是"奥运奖牌逻辑，金牌 1、金牌 1、铜牌 3"；DENSE_RANK 是"名次不空档，金牌 1、金牌 1、银牌 2"。

实际选哪个，取决于需求。需要给每行一个唯一标识用来分页、去重、取前 N 条明细时，选 ROW_NUMBER；需要展示"排名"给用户看，并列名次要空档（比如第二名缺失），选 RANK；只关心几档绩效等级，不需要空档时，选 DENSE_RANK。我当年直接取"前 3 名"却用 RANK，结果因为并列拿到 4 行，这个问题到今天不少初级数据分析师还在踩。

2.2 偏移访问类：LAG 和 LEAD 解决"跟前一行比"

排名完了，第二个高频需求是环比和差分："这个月跟上个月比涨了多少？""当前订单跟上一笔订单隔了几天？"这类问题都要访问"另一行"的值，LAG 和 LEAD 就是干这个的。

LAG 取窗口里当前行前面的行，LEAD 取后面的行。语法长这样：

SELECT dept_id, employee, sale_date, amount, LAG(amount, 1) OVER (PARTITION BY dept_id ORDER BY sale_date) AS prev_amount FROM sales_order;

这行的逻辑可以这样读：每个部门内部，按日期排好序之后，把上一行订单的金额放到当前行旁边。我在 A 部门的数据上跑出来的结果很直观：小王在 2026-01-05 的 prev_amount 是 NULL，因为他是部门第一单；小李 01-08 那单的 prev_amount 就是 1200；小王 01-12 那单的 prev_amount 则是 2200。

做环比增长率时，最标准的写法是先把差值算出来，再用 NULLIF 防除零：

SELECT dept_id, employee, sale_date, amount, LAG(amount, 1) OVER w AS prev_amount, ROUND( (amount - LAG(amount, 1) OVER w) * 100.0 / NULLIF(LAG(amount, 1) OVER w, 0), 2 ) AS growth_pct FROM sales_order WINDOW w AS (PARTITION BY dept_id ORDER BY sale_date);

MySQL 8.0 里可以用 WINDOW 子句把重复的窗口定义抽出来，减少一大段同样文字的复制粘贴。注意 LAG 的第二个参数是偏移步长，默认是 1；如果取倒数第二行就写 2。窗口内没有前一行时返回 NULL，所以报表里要记得用 COALESCE 把 NULL 显示成"无"或者 0，别让业务看到一堆空值。

2.3 聚合窗口与框架概念：SUM OVER 是今天的重头戏

如果把今天的知识按难度排序，排名类算入门，LAG 算进阶，那 SUM OVER 与框架（frame）就是真正的分水岭。我要拿它算"部门内累计销售额"：

SELECT dept_id, sale_date, amount, SUM(amount) OVER ( PARTITION BY dept_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS dept_cum_amount FROM sales_order ORDER BY dept_id, sale_date;

这里最关键的是最后的ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。它定义了一个"活动窗口"：从分组的第一行开始，一直到当前行结束。于是每一行的 dept_cum_amount，都是"截至这一行"的累计和。A 部门的结果是 1200、3400、5200，每一步都能对上手工计算。

为什么必须写这段？因为窗口框架是有默认行为的。如果 SUM OVER 只写了 PARTITION BY 没写 ORDER BY，那么整个分区就是一个大窗口，每行显示的 SUM 都是部门全量的总和。如果你想做"移动平均"或者"最近 3 笔订单的平均值"，就必须自定义边界：

SELECT dept_id, sale_date, amount, AVG(amount) OVER ( PARTITION BY dept_id ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS moving_avg_3 FROM sales_order;

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW的含义是：包括当前行，以及它前面的 2 行，共 3 行一起求平均。这个写法在做走势曲线的时候特别常用。请把框架四要素背下来：起始边界、结束边界、PRECEDING、FOLLOWING。边界既可以是行，也可以直接写当前行 CURRENT ROW，或者写无边界 UNBOUNDED。

2.4 分桶与取值类：NTILE、FIRST_VALUE 和 LAST_VALUE

最后我还补了两个相对冷门但关键时刻能救命的函数。

NTILE 的作用是把分组里的行尽量平均地切分成若干个桶，比如把员工按销售额分成四档：

SELECT dept_id, employee, amount, NTILE(4) OVER (PARTITION BY dept_id ORDER BY amount DESC) AS bucket_no FROM sales_order;

这个函数的典型场景是客户分层：前 25% 是 VIP，后 25% 是沉默户。它跟 RANK 的区别在于，RANK 关心"第几名"，NTILE 关心"第几档"。

FIRST_VALUE 和 LAST_VALUE 则是取窗口内第一行或最后一行的值。注意，LAST_VALUE 默认的框架结束边界是 CURRENT ROW，如果不主动改成 UNBOUNDED FOLLOWING，它取到的往往不是整个分组的最后一行，而是当前行。我第一遍跑的时候就犯了这个错误，结果每行返回的都是自己。正确写法是显式写满边界：

SELECT dept_id, employee, sale_date, amount, FIRST_VALUE(amount) OVER ( PARTITION BY dept_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS first_amount FROM sales_order;

这段练习结束后，我把四类函数的用途用一句话各写了一遍：排序类负责"第几名"，偏移类负责"旁边那行"，聚合类负责"范围内汇总"，取值类负责"边界上取一个值"。这句话现在直接记在我笔记第一页。

3. 边学边踩坑：三个翻车现场和排查思路

3.1 NULL 值把排名排乱了

我第一遍跑排名时，故意往数据里塞了一个金额为 NULL 的员工，想测试数据库的行为。结果 MySQL 8.0 和 DuckDB 给我的结果完全相反。

MySQL 的默认规则是 NULL 最小，按金额降序排列时，NULL 会被排到最后；而在 DuckDB 里，NULL 默认按"比任何非空值都大"处理，降序时 NULL 直接跳到第一名。同样是跑ORDER BY amount DESC，两个数据库给出的第一名不一样，这要是上线了，排名就全错了。

解决办法也很简单。MySQL 8.0 不支持NULLS LAST这种标准写法，得用一个小技巧：

SELECT employee, amount, ROW_NUMBER() OVER ( PARTITION BY dept_id ORDER BY (amount IS NULL), amount DESC ) AS rn FROM sales_order;

DuckDB 和 PostgreSQL 则可以直接写ORDER BY amount DESC NULLS LAST，语义清楚，不靠技巧。这个坑本身不难，但它提醒我一件事：不同数据库的窗口函数默认行为并不完全一致，写代码前一定要确认正在用的是哪套引擎。

3.2 忘记在窗口里写 ORDER BY，累计值变成了分组总和

这是今天最典型的脑补翻车。我一开始的想法是：既然查询最后有ORDER BY sale_date，窗口里的 SUM 是不是也会跟着日期一路累加上去？实际跑完才发现不是。

看这段"错误示范"：

SELECT dept_id, sale_date, amount, SUM(amount) OVER (PARTITION BY dept_id) AS dept_total FROM sales_order ORDER BY sale_date;

结果并不如我预期：订单还没按日期累积，每一行的 dept_total 都是整个部门的总额。原因在于，窗口函数的计算发生在外层 ORDER BY 之前，而且窗口 SUM 表达式里根本没有 ORDER BY 子句，数据库就认为"全分区是一个窗口"，于是每行都返回部门的全部金额。

想算累计，就一定要在 OVER 内部写ORDER BY sale_date，同时配合框架限定为从分区起点到当前行。外层 ORDER BY 只影响最终结果的展示顺序，跟窗口内的累计逻辑没有任何关系。这个坑我记性很深，因为它是"看起来和 SQL 语义低耦合、实际却完全不同"的典型。

3.3 同一天有多笔订单，累计值莫名跳高

第三个翻车最有意思。我在临时表里加了同部门同一天两笔订单，用了一段简化版累计 SQL：

SELECT dept_id, sale_date, amount, SUM(amount) OVER (PARTITION BY dept_id ORDER BY sale_date) AS cum_amount FROM sales_order;

我预期的结果是 01-08 第一笔累计 2200，第二笔累计 3400。但实际跑出来，两笔订单的 cum_amount 都是 3400。问题是：明明整体看是"一行一行往下累"，为什么第二笔会连第一笔一起算进去？

原因是默认框架。当 OVER 里有 ORDER BY 但没有显式 ROWS 子句时，数据库使用 RANGE 模式：所有排序键相同的行会被视作"同行"，即 peers。01-08 这两笔订单排序键完全相同，于是它们在整个框架里都属于"当前行范围"，一进来就都被收入窗口，导致两行的累计结果相同。

修复方式就是我在 2.3 里写的，显式把 ROWS 边界写清楚：

SUM(amount) OVER ( PARTITION BY dept_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )

我把这个教训提炼成一句话：有相同的排序字段时，不用 ROWS 就别说你懂累计。现在我只要看到"累计"两个字，第一反应就是检查有没有显式声明 ROWS BETWEEN。

3.4 性能与可读性的两个提醒

窗口函数确实好用，但它不是免费的。它通常需要对 PARTITION BY 和 ORDER BY 的列做一次全局排序，数据量大到几千万行时，这种排序会吃掉大量内存和临时磁盘空间。我的经验是，线上报表如果只是需要"部门总额 + 明细"这种需求，先用普通 JOIN 或物化视图评估成本；只有在逻辑复杂到 JOIN 写不出来时，才上窗口函数。同时，能通过索引覆盖排序键就尽量覆盖，比如(dept_id, sale_date)这个组合索引，在今天的累计例子里就能省掉一次外部显式排序。

可读性方面，别把窗口函数写成一本流水账。同一个窗口定义重复出现五六次时，一定要用 MySQL 的 WINDOW 子句或 PostgreSQL 的WINDOW w AS (...)抽取公共部分。我上个项目里见过一段 200 行的 SQL 没有 WINDOW，后面维护的人改一个字段，要搜索替换五个位置，迟早出事。

4. 学完立刻上手：两分钟复现一个分组 Top N 需求

4.1 Top N 的完整 SQL 与执行逻辑

下午我关掉实验脚本，用今天学的东西重写之前业务报表里那个"每个部门销售额前两名"的需求。以前我靠相关子查询吭哧吭哧写半天，现在一段 CTE 就搞定：

WITH ranked AS ( SELECT dept_id, employee, amount, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY amount DESC) AS rn FROM sales_order ) SELECT dept_id, employee, amount FROM ranked WHERE rn <= 2 ORDER BY dept_id, rn;

这里有个细节必须说清楚：为什么不能直接在 WHERE 里写ROW_NUMBER() OVER (...) <= 2？因为 WHERE 是在窗口函数计算之前执行的，数据库在过滤时根本还不知道这个编号是多少。正确做法就是先把带编号的结果放进 CTE，然后在外面过滤。这个"先算后筛"的步骤，是窗口函数用得熟不熟的分水岭。

跑出来的结果很稳。A 部门前两名是 2200 的小李和 1800 的小王，B 部门是 2600 的小陈和 1500 的小张，C 部门因为只有一个人，所以只有 2300 的小刘。拿到结果我又特意加了一条同金额并列的数据做测试，确认用的是 ROW_NUMBER 而不是 RANK，避免出现"前三名返回四行"的尴尬。

4.2 我自己怎么把今天的学习沉淀成可复用笔记

学了这么多，如果只是看完就关掉，明天必忘。学习日记的作用在这里就体现出来了。我今天的日记格式不是流水账，而是按五要素记录，这样三个月后翻回来仍然能快速定位。

我的学习日记模板是这样的：

这个模板最大的好处是"错题驱动"。以前我写日记总想把知识点面面俱到地抄一遍，结果抄完自己都不想看。现在每个知识点都对应一个真实的翻车场景，回忆时先想起场景，再想起解决方案，比背语法快得多。

4.3 明天的学习计划

今天把主框架打完了，但有两个后续问题我明确记在待办里。

第一，RANGE 模式下的时间窗口。我想继续研究 DuckDB 和 PostgreSQL 里ORDER BY sale_date RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW这类按时间段滑动的写法，它和 ROWS 的物理行数限制是完全不同的能力，对周期类统计特别有用。

第二，把窗口函数翻译成 pandas。我平时也写 Python，想把今天的 SQL 片段分别映射到groupby.transform和shift，这样以后做数分时多一套语言选择，也能相互校验结果。

5. 上手之后才真正想明白的几件事

学到最后，我不光会写窗口函数了，还把几个抽象概念彻底落地了。第一，窗口函数并不可怕，可怕的是没有理解"行的视角"。GROUP BY 是自上而下的汇总视角，窗口函数是逐行扫描的观察视角，想清楚自己要哪个视角，代码自然就出来了。第二，默认框架坑太多，任何涉及累计、平均、取值边界的需求，我都会刻意补上 ROWS 或 RANGE 定义，绝不偷懒。第三，学习日记最大的价值不是"今天学了多少"，而是"今天犯了什么错、为什么错、怎么避免再错"。我翻去年写的日记，最常回看的恰恰是那些报错记录，知识点早就忘了，教训还记得牢牢的。

今天这份 260126 的学习日记就到这。明天我会带着"按时间段滑动窗口"这个小目标继续，如果中途又踩出新坑，再来跟你们同步。