开学后班主任要出一份“班级计算小能手”成绩报告。同样是 5 张成绩单，三个评委却给出了三种答案：小李按排队顺序发流水号，1、2、3 依次排开；王老师看见两个 92 分就都给“并列第二”，但下一个人直接跳到第四名；陈老师不然，他数了数“一共有几个不同的分数档”，于是 92 分并列第二后，下一个人依然拿第三名。还有个赵教练更特别，他不排名，专门翻出上一轮比赛录像做对比。这四位评委各自抱着不同的规则，同时站在一张成绩单旁边，各自在小卡片上写下一列批注——这正是 SQL 窗口函数的模样。窗口函数不会把很多行合并成一行，它只是蹲在你每一行旁边，用你周围的行帮你算出一个附加结果。就像运动会评委给每人发一张名次小卡，行数一个不少，卡片内容却因规则而异。这篇文章，我们就来把这四位评委的算盘一一拨给你看。

岗位关联

学会这三兄弟，写报表、做排名、算环比同比都会顺手很多。它们几乎是数据分析岗SQL面试的必考点，掌握后也能让你的查询从“能跑”进阶到“专业”。

目录
  1. 可视化演示
  2. 窗口函数入门
  3. ROW_NUMBER编号
  4. RANK排名规则
  5. DENSE_RANK对比
  6. LAG跨行引用
  7. NTILE分桶进阶
  8. 实战应用
  9. AI 应用场景
  10. Python 演示
  11. 常见误区
  12. 面试题

可视化演示

动态演示ROW_NUMBER/RAN的核心机制，通过采集/分析/决策/反馈四种模式展示关键概念与交互流程。

本节配有交互动效（进入视口后自动加载，约 3KB）。

ROW_NUMBER/RAN：ROWN ROW_NUMBER与分组排名 RANK与DENSE_RANK 核心机制可视化 ROW_NUMBER/RAN整体架构与关键组件

窗口函数入门

校运会的成绩报告终于开始设计了。班主任手上有 40 行成绩记录，想按班级排序并附上名次。他原来的老办法是 GROUP BY 班级 算出每班总分——但那只得到 2 行汇总，每个孩子的成绩不见了。这就好比把全班 40 张成绩单揉成一张班级大字报，个体信息全丢。而窗口函数完全相反：它搬来一叠空白名次卡，给成绩单里每一行都发一张，原始行一个不少。

这叠名次卡的核心道具叫 OVER。OVER 后面跟着两个可选小部件：PARTITION BY 负责把数据横向切块，相当于工作人员把选手按班级请到不同的候场区；ORDER BY 负责给每个区里的选手排先后顺序，相当于叫号员按成绩从高到低排好队。这两个小部件一起定义了“窗口”的范围，也就是评委抬眼能看到的那一小片区域——可以是全班，也可以只是本小组。

窗口函数在报表、去重、排名和跨行计算里无处不在。它跟 GROUP BY 最本质的区别是：GROUP BY 把多行折叠成一行，像把散落的积木叠成一座塔；窗口函数则是给每块积木贴一张标签，塔的高度不变，只是积木上多了文字。

评委团进场了，流水号发牌员、颁奖裁判、连续名次评委和录像回放教练各就各位。他们的规则各不相同，接下来我们一个个认识。

窗口函数(列)  OVER  (PARTITION BY  分组列  ORDER BY  排序列)\text{窗口函数}(列) \;\text{OVER}\; (\text{PARTITION BY}\; \text{分组列} \; \text{ORDER BY}\; \text{排序列})
公式解读：左边是计算动作（排名、编号、取值），右边括号里是“在哪个窗口里做”——先按分组列切块，再按排序列定先后。
  1. 窗口函数名告诉数据库要执行什么计算，比如 ROW_NUMBER() 要编号、RANK() 要排名、LAG() 要取上行值。
    ROW_NUMBER()/RANK()/LAG()\text{ROW\_NUMBER()} / \text{RANK()} / \text{LAG()}
  2. OVER 关键字窗口函数的灵魂。它宣告“接下来的计算不合并行，而是在每一行的窗口范围内进行”。
    OVER\text{OVER}
  3. PARTITION BY 分组列把整个表按某列切分成互不相干的小组，各组的编号、排名都从 1 重新开始，就像每个班级单独开会。
    PARTITION BY  class\text{PARTITION BY}\; \text{class}
  4. ORDER BY 排序列在每个小组内部规定排序规则，名次与编号都按这个顺序依次生成。
    ORDER BY  score  DESC\text{ORDER BY}\; \text{score} \; \text{DESC}

ROW_NUMBER编号

第一位评委是流水号发牌员小李。他的工作最简单：选手们在候场区排好队，他就从 1 开始挨个发号，绝不回头、绝不重复。哪怕两个选手成绩一模一样，他也只管按队列顺序发 1、2。在小李眼里，没有“并列”这回事，只有“先来后到”。

这就是 ROW_NUMBER() 的全部哲学：给每组内的每一行分配一个从 1 开始的唯一连续整数。它不关心值是否相等，只在乎 ORDER BY 排出来的先后。实际业务里它有三大高频用途。其一，分组编号：按客户分组、按下单时间排序，给每个客户的每次下单编上 1、2、3，之后想筛“每个客户第一单”就取编号为 1 的行。其二，数据去重：按业务主键分区、按更新时间倒序编号，然后删掉编号大于 1 的重复行，保留最新的那条。其三，分页查询：全表编号后取第 51 到 60 号，就是第 6 页的数据，配合 OFFSET 使用特别顺手。

去重的经典写法长这样：WITH t AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) AS rn FROM orders) SELECT * FROM t WHERE rn = 1。这就好比运动会报名表里混进了几份重复填写的表格，小李按“最后修改时间”排序后重新编号，只留下每份报名表的 1 号原件，其余全部扔进废纸篓。

小李的流水号牌虽然不通人情，但胜在独一无二。凡是需要“每个分组里挑一条记录”的场景，找他准没错。

ROW_NUMBER=上一个编号+1\text{ROW\_NUMBER} = \text{上一个编号} + 1
公式解读：它是一个严格递增的计数器，从 1 开始，每扫过一行就加 1，和具体成绩数值毫无关系。
  1. 初始编号每组第一条记录拿到编号 1，相当于排队第一位的选手领到 1 号牌。
    ROW_NUMBER=1(组内首行)\text{ROW\_NUMBER} = 1 \quad (\text{组内首行})
  2. 递增规则后续每条记录的编号都是前一行编号加 1，形成 1、2、3、4…的连续序列。
    ROW_NUMBERi=ROW_NUMBERi−1+1\text{ROW\_NUMBER}_{i} = \text{ROW\_NUMBER}_{i-1} + 1
  3. 与并列无关编号只看队列位置，不比较成绩大小，因此同分选手也会拿到不同编号。
    scorei=scorei+1  ⇒  RNi≠RNi+1\text{score}_i = \text{score}_{i+1} \;\Rightarrow\; \text{RN}_i \neq \text{RN}_{i+1}

RANK排名规则

第二位评委是颁奖裁判王老师。他手里拿的不是流水号牌，而是一叠“名次奖牌”。王老师的规矩是：成绩相同，就发同样的名次奖牌；但发完并列的奖牌之后，下一个人要跳过被占用的名次。比如成绩 95、92、92、87，前两名 92 分都拿第二名，87 分那位直接拿第四名——第三名凭空消失了。

为什么会跳号？因为 RANK() 的计算逻辑是“数一数有多少人的成绩严格高于我，再加一”。两个人并列 92 分，成绩高于他们的只有 95 分那一个人，所以两人都排第二（1 + 1 = 2）；87 分那位，成绩高于他的有 95、92、92 共三人，所以名次是 4。由此可见，跳号不是 bug，而是并列名次占用了多个位置的必然结果。

在使用场景上，RANK() 非常适合“锦标赛排名”和“业绩 PK”：运营团队要看谁冲到了榜首，前两名并列第一时，第三名就该知道自己是实际上的第三名，而不是被“温柔地”说成第二。它也在累计排名场景中有用——比如“统计成绩高于 90 分的人数”，本质上就是在数 RANK() 的分母。

王老师的奖牌规则确实有点“冷酷”：并列的人共享名次，后面的人就得让位。但正是这种冷酷，把竞争的激烈程度如实反映了出来。

RANKi=1+∑j < i1(sj>si)\text{RANK}_i = 1 + \sum_{j \,<\, i} \mathbb{1}(s_j > s_i)
公式解读：当前行的名次 = 1 + 组内成绩严格大于当前成绩的人数。人数里包含并列者，所以并列名次会占位并导致跳号。
  1. 成绩比较把当前成绩 s_i 依次与组内其他成绩 s_j 比较，统计“严格大于”的个数。
    1(sj>si)\mathbb{1}(s_j > s_i)
  2. 计数求和将所有“大于”的标记加起来，得到排在当前成绩之前的总人数。
    ∑j < i1(sj>si)\sum_{j \,<\, i} \mathbb{1}(s_j > s_i)
  3. 加上自身名次从 1 开始计数，所以在人数基础上加 1，得到最终名次。
    RANKi=1+比当前分数高的人数\text{RANK}_i = 1 + \text{比当前分数高的人数}

DENSE_RANK对比

第三位评委陈老师看不下去了。他觉得王老师的奖牌规则太浪费名次——明明只有四个人，却发到了第四名，中间空了个第三名。陈老师的规则是：数一数“一共有几个不同的成绩档”，然后按档位发名次。95 分是第一档，92 分是第二档，87 分是第三档，于是名次就是 1、2、2、3，一个号码都不浪费。

DENSE_RANK() 与 RANK() 的唯一差别就在跳号上：RANK() 的编号会随并列人数跳跃，DENSE_RANK() 永远保持名次连续。用数学语言讲，DENSE_RANK() 数的是“不同成绩档位的序号”，而不是“高于我的人数”。这就好比陈老师眼里只有“几个台阶”，而王老师眼里是“几个人踩在台阶上”。

选型时怎么考量？如果你要发金银铜奖，或者要给用户分 VIP 等级（V1、V2、V3），DENSE_RANK() 是更自然的选择——因为奖牌和等级的名额是有限的，不该因为并列就把铜奖变成“没有奖”。反过来，如果你要如实反映竞争激烈程度、观察自己和别人的实际差距，RANK() 的跳号反而更有信息量。很多报表把三列并排展示：RN 做行号、R 做竞赛名次、D 做等级标签，各司其职。

陈老师的连续名次看起来“温和”，但并没有歪曲事实——它只是换了一种统计口径。报表里放哪一列，取决于你想表达“竞争”，还是想表达“等级”。

DENSE_RANKi=不同成绩档位的序号=count(distinct(sj:sj≥si))\text{DENSE\_RANK}_i = \text{不同成绩档位的序号} = \text{count}(\text{distinct}(s_j : s_j \geq s_i))
公式解读：当前行的稠密名次 = 组内“大于等于当前成绩”的不同分值数量。因为统计的是不同分值，所以并列不再占额外名次。
  1. 筛选分值从组内挑出所有不低于当前成绩的分值，得到候选集合。
    distinct(sj:sj≥si)\text{distinct}(s_j : s_j \geq s_i)
  2. 去重计数对候选集合去重，数出有几个不同的分值。
    count(distinct(⋯ ))\text{count}(\text{distinct}(\cdots))
  3. 得到名次这个去重后的数量就是名次，因此并列者共享名次且不跳号。
    DENSE_RANK=去重后的分值个数\text{DENSE\_RANK} = \text{去重后的分值个数}

LAG跨行引用

第四位评委赵教练压根不参与排名。他抱着一台录像机，专门回放上一场比赛的画面。赵教练的问题永远是：“这孩子这次考了 92 分，上次考了多少？进步还是退步？”这就是 LAG() 的使命：跨行取值，让你在当前行看到前面某一行的数据。

LAG(列名, n) 返回当前行往前数第 n 行的指定列值，默认 n = 1。它的经典应用是环比计算：把本月销售额与上月销售额放在同一行，然后一除一减，增长率就出来了。SQL 写法是 LAG(gmv, 1) OVER (PARTITION BY dept ORDER BY month)——注意这里的 PARTITION BY dept 绝对不能省，否则 A 部门的 1 月会和 B 部门的 12 月强行配对，环比就彻底错了。要做同比，思路类似：按月份分区，让每年的 1 月跟上一年的 1 月对齐，再相减。

与 LAG() 相对的是 LEAD()，它访问的是当前行后面的行，相当于赵教练快进看下一场比赛的录像。两者联合，就能算移动平均、算连续行为间隔。比如分析用户回访：LAG(上次访问时间) OVER (PARTITION BY user_id ORDER BY visit_time)，再用当前访问时间减它，就得到回访间隔天数。整个分析像不像赵教练不断拖拽进度条，在时间轴上前后对照？

赵教练的录像机揭示了窗口函数的另一面：它不仅能“向上看”名次，还能“左右看”邻居。时序数据里的涨跌、间隔、趋势，全靠这一台回放机器。

环比增长率=xt−xt−1xt−1×100%\text{环比增长率} = \frac{x_t - x_{t-1}}{x_{t-1}} \times 100\%
公式解读：环比增长率 =（本期值 - 上期值）÷ 上期值。LAG() 负责把 x_{t-1} 搬到当前行来。
  1. 取上期值用 LAG(列, 1) 拿到当前行上一行的值，记为 x_{t-1}。
    LAG(x,1)=xt−1\text{LAG}(x, 1) = x_{t-1}
  2. 本期减上期当前值 x_t 减去上期值 x_{t-1}，得到增长量，正数涨、负数跌。
    xt−xt−1x_t - x_{t-1}
  3. 除以基数增长量除以上期值，再乘以 100%，得到可读的百分比。
    xt−xt−1xt−1×100%\frac{x_t - x_{t-1}}{x_{t-1}} \times 100\%

NTILE分桶进阶

分完名次，运动会进入颁奖环节。负责分奖品的大妈拿来一个大袋子，里面装着几箱橘子，要求“尽量均匀”地分给 40 个孩子。她的办法简单粗暴：先把孩子按名次排好队，然后把橘子按人头切成 N 份，每人一份，多出来的橘子从前面的桶开始逐个补发。NTILE(N) 干的就是这件事：把分组内的行按顺序尽量均匀地分进 N 个桶里，编号从 1 到 N。

分桶的规则是：行数除以桶数得商 q 和余数 r，那么前 r 个桶每桶 q+1 行，后面的桶每桶 q 行。比如 10 行数据分 4 桶：10 ÷ 4 = 2 余 2，于是前两桶各 3 行，后两桶各 2 行。这和 ROW_NUMBER 分页的差别在于：ROW_NUMBER 分页是“取固定页大小的连续行”，而 NTILE 是“把全部行按比例切成几大段”。前者适合“翻页浏览”，后者适合“分层分析”。

NTILE(4) 就是经典的四分位分析：把用户按消费额从高到低分成 Q1、Q2、Q3、Q4 四档，看看头部 25% 贡献了多少营收。同理 NTILE(10) 可做十分位、NTILE(100) 可做百分位。此外，NTILE 配合窗口函数还能做移动平均——虽然移动平均通常用 ROWS BETWEEN 窗口帧实现，但分桶思想仍然是“划定一个局部范围，在范围内计算”的直观体现。

分奖品的大妈不懂什么 SQL，但她手里的橘子桶数和 NTILE 算出来的一模一样——这就是“均匀”二字在数据库里的落地方式。

bucket=⌈k×nN⌉\text{bucket} = \left\lceil \frac{k \times n}{N} \right\rceil
公式解读：第 k 行（按序号从 0 或 1 计）会被分到第 ⌈k·n/N⌉ 桶，保证每桶行数差不超过 1。
  1. 确定总行数n 是组内的总行数，N 是想要的桶数，二者相除得到平均每桶行数。
    nN=q 余 r\frac{n}{N} = q \text{ 余 } r
  2. 计算分桶位置用行序号 k 乘以 n 再除以 N，并向上取整，得到该行所属的桶号。
    ⌈k×nN⌉\left\lceil \frac{k \times n}{N} \right\rceil
  3. 前余数桶多一行余数 r 决定了前 r 个桶多分到一行，最终每桶行数或为 q+1，或为 q。
    桶大小={q+1前 r 桶q其余桶桶大小 = \begin{cases} q+1 & \text{前 } r \text{ 桶} \\ q & \text{其余桶} \end{cases}

实战应用

运动会落幕，班主任要交三份报告：一份班级排行榜，一份成绩进步分析，一份重复报名清理表。这三份报告正好把四位评委的工作全部串起来。

第一份排行榜：按班级分区、按成绩排序，同时用 RANK() 给出竞赛名次、用 DENSE_RANK() 给出等级标签。第二份进步分析：用 LAG(score, 1) OVER (PARTITION BY student_id ORDER BY exam_date) 拿到上次成绩，本次减上次，正数即进步。第三份去重清理：用 ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY updated_at DESC) 给重复报名编号，只保留 1 号。三份报告都在同一张原始表上完成，表本身一行未动，全靠窗口函数在旁边默默加列。

这类实战在数据分析岗几乎天天发生：运营日报里的“各渠道 GMV 排行”用 RANK()；用户行为分析里的“相邻两次登录间隔”用 LAG()；订单表去重、列表分页用 ROW_NUMBER()；用户分层用 NTILE()。窗口函数就像瑞士军刀，不在于哪一把最锋利，而在于你清楚每一把的刀刃朝哪边。

班主任捧着三份报告走出教室时，四位评委还在原地待命。窗口函数的价值正在于此：不改变数据的模样，却让数据会说话——排名、对比、分层、去重，全都是报表人的日常利器。

综合报告=RANK()  OVER(PARTITION  BY  channel  ORDER  BY  gmv  DESC)⏟渠道排名+LAG(gmv)  OVER(PARTITION  BY  channel  ORDER  BY  month)⏟环比基准\text{综合报告} = \underbrace{\text{RANK}()\; \text{OVER}(PARTITION\; BY\; \text{channel} \; ORDER\; BY\; \text{gmv}\; DESC)}_{\text{渠道排名}} + \underbrace{\text{LAG}(\text{gmv})\; \text{OVER}(PARTITION\; BY\; \text{channel}\; ORDER\; BY\; \text{month})}_{\text{环比基准}}
公式解读：一张报表里可以同时出现多个窗口函数，各自计算互不干扰：一个负责给排名，一个负责取基准值。
  1. 排名列RANK() 按渠道分区、按 GMV 降序排名，得到每个渠道在当月榜单中的位次。
    RANK()  OVER(PARTITION BY channel  ORDER BY gmv DESC)\text{RANK}()\;\text{OVER}(\text{PARTITION BY channel} \;\text{ORDER BY gmv DESC})
  2. 基准列LAG(gmv, 1) 取同渠道上一个月的 GMV，作为下个月环比计算的基准。
    LAG(gmv,1)  OVER(PARTITION BY channel  ORDER BY month)\text{LAG}(\text{gmv}, 1)\;\text{OVER}(\text{PARTITION BY channel} \;\text{ORDER BY month})
  3. 合并输出排名列与基准列并列出现在查询结果中，读报表的人一眼就能看到“排名 + 涨跌”。
    SELECT 排名, 基准, ⋯ FROM 报表\text{SELECT 排名, 基准, }\cdots\text{ FROM 报表}

AI 应用场景

运营日报排行榜

阶段：数据准备与特征工程

解决的问题：运营团队每天要看各渠道的 GMV 排名变化，传统做法是导出 Excel 后用 RANK() 手动拉公式，数据一多就卡顿，而且每次都要重复操作。用 SQL 窗口函数一步到位，RANK() OVER (PARTITION BY 日期 ORDER BY GMV DESC) 直接产出当日榜单，配合 LAG() 还能自动标注“较昨日上升/下降几位”。

具体案例：某电商数据分析师每天上午 9 点跑一张 SQL，输出“各渠道昨日 GMV 排名 + 环比变化”推送到钉钉群。渠道负责人一眼就能看到自己的排名是升是降，省去了手工整理 20 个 Excel 的麻烦。

用户行为序列分析

阶段：行为序列建模

解决的问题：做用户生命周期分析时，需要知道“用户相邻两次活跃间隔多少天”。传统做法是在 Python 里按用户分组循环遍历，10 万用户跑几分钟。用 LAG(visit_date, 1) OVER (PARTITION BY user_id ORDER BY visit_date) 一行 SQL 就拿到上次活跃时间，间隔列直接生成。

具体案例：某增长团队分析“次日留存用户的行为特征”，先用 ROW_NUMBER() 给每个用户的访问序列编号，再筛出编号 1 和 2 的两次访问，用 LAG() 算出间隔，最后把这列间隔作为特征喂给用户流失预警模型。

时序数据的环比同比

阶段：指标监控与异常检测

解决的问题：时序指标监控里，环比、同比是最基础的涨跌信号。传统 BI 工具里的环比计算往往要自连接两次表，性能差且易错。LAG() 与窗口分区直接把“上期值”和“去年同期值”拉到同一行，计算成本和可读性都大幅优化。

具体案例：某 SaaS 公司监控每日活跃用户数，SQL 里用 LAG(dau, 1) OVER (ORDER BY date) 做环比、用 LAG(dau, 365) OVER (ORDER BY date) 做同比，超过阈值就触发告警，把异常波动从“事后发现”变成“实时感知”。

Python 演示

python66 行
from collections import defaultdict  def window_rank(rows, group_key, order_key, reverse=True):    groups = defaultdict(list)    for r in rows:        groups[r[group_key]].append(r)    out = []    for g in groups:        gs = sorted(groups[g], key=lambda x: x[order_key], reverse=reverse)        rn = 0        rk = 0        dr = 0        prev_score = None        for i, row in enumerate(gs):            score = row[order_key]            rn = i + 1            if prev_score is None or score != prev_score:                rk = i + 1                dr += 1            row['rn'] = rn            row['rk'] = rk            row['dr'] = dr            prev_score = score            out.append(row)    return out  def lag_ratio(rows, group_key, order_key, value_key):    groups = defaultdict(list)    for r in rows:        groups[r[group_key]].append(r)    for g in groups:        gs = sorted(groups[g], key=lambda x: x[order_key])        for i in range(1, len(gs)):            prev_val = gs[i - 1][value_key]            cur_val = gs[i][value_key]            gs[i]['mom'] = (cur_val - prev_val) / prev_val    return rows  def main():    scores = [        {'class': 'A', 'name': 'zhang', 'score': 95},        {'class': 'A', 'name': 'liu', 'score': 90},        {'class': 'A', 'name': 'wang', 'score': 90},        {'class': 'B', 'name': 'ming', 'score': 92},        {'class': 'B', 'name': 'hong', 'score': 92},    ]    ranked = window_rank(scores, 'class', 'score')    print('--- 排名 ---')    for row in ranked:        print(row)    sales = [        {'dept': 'A', 'month': 1, 'gmv': 100},        {'dept': 'A', 'month': 2, 'gmv': 120},        {'dept': 'B', 'month': 1, 'gmv': 80},        {'dept': 'B', 'month': 2, 'gmv': 90},    ]    print('--- 环比 ---')    for row in lag_ratio(sales, 'dept', 'month', 'gmv'):        print(row)  if __name__ == '__main__':    main()
目的：用 50 行代码模拟 SQL 中 ROW_NUMBER、RANK、DENSE_RANK 和 LAG 环比的核心计算逻辑，方便离线调试和理解原理。
  1. groups = defaultdict(list)按分组键把原始数据装进不同的桶，等价于 PARTITION BY 的分区效果。
  2. rn = i + 1组内第 i 行（从 0 开始）的 ROW_NUMBER 就是 i+1，天然连续唯一。
  3. if prev_score is None or score != prev_score:只有当成绩与上一条不同时才更新 RANK 和 DENSE_RANK 的计数。
  4. rk = i + 1RANK 用的是“当前行的绝对位置”，所以并列之后会跳号。
  5. dr += 1DENSE_RANK 只统计“出现了几个不同的分数档”，所以并列之后仍然连续。
  6. sorted(groups[g], key=lambda x: x[order_key])组内按月份升序排列，保证 LAG 取到的是真正的“上一个月”。
  7. gs[i]['mom'] = (cur_val - prev_val) / prev_val环比增长率的核心算式，对应 SQL 里 LAG(gmv,1) 后的除法。

常见误区

误区我希望两个 92 分都显示“并列第二”，于是用了 `ROW_NUMBER() OVER (ORDER BY score DESC)`，结果一个显示 2、一个显示 3，这函数是不是坏了？我用错了吗？

正解ROW_NUMBER() 的设计目标就是给每一行一个唯一序号，它天然不处理并列。就像流水号发牌员眼里没有“并列”概念，只有排队先后。想要并列排名，应该改用 RANK() 或 DENSE_RANK()。三者分工不同：RN 做行号、R 做竞赛名次、D 做连续等级。

为何常见：函数名里都带“排名”的意思，初学者很容易把“行号”和“名次”当成一回事，看到输出不并列就以为出了 bug。

误区我用 `RANK()` 排名，四个人的成绩是 95、92、92、87，结果名次是 1、2、2、4，第三名去哪了？是不是数据库的 bug？

正解跳号是 RANK() 的刻意设计，不是错误。因为 92 分并列占了第二名和第三名的位置——想象颁奖台上两个并列银牌得主站了同一个领奖台的第二级，第三级自然就空了。DENSE_RANK() 才是不跳号的版本，它按“分数档位”计数，1、2、2、3。

为何常见：人脑默认“名次应该连续”，所以看到数字跳跃会本能地怀疑计算有误，忽略了并列占位的数学含义。

误区我按月算各部门销售额环比，写了 `LAG(gmv, 1) OVER (ORDER BY month)`，结果 A 部门的 1 月销售额跟 B 部门的 12 月销售额比上了，环比数字完全离谱。

正解LAG() 的“上一行”是在整个窗口范围内定义的。如果窗口没有按部门分区，上一行就是“全表排序后的上一行”，跨部门配对自然发生。正确写法是 LAG(gmv, 1) OVER (PARTITION BY dept ORDER BY month)，先按部门切块，再在块内回看上一行——就像赵教练只看同一个选手的上一场录像，而不是别人的。

为何常见：初学者容易把 ORDER BY month 当成唯一约束，忽略了窗口的范围是 PARTITION BY 决定的，排序只是“窗口内部”的先后规则。

误区我用 `NTILE(4)` 把 10 行数据分桶，结果前两桶 3 行、后两桶 2 行，这不均匀，是不是实现有问题？

正解NTILE 保证的是“尽量均匀”，即任意两桶行数差不超过 1。10 行分 4 桶，平均每桶 2.5 行，不可能每桶都一样。它的分配规则是前 r = 10 mod 4 = 2 桶多拿一行，所以是 3、3、2、2。这和生活中分苹果一样：4 个人分 10 个苹果，只能两个拿 3 个、两个拿 2 个。

为何常见：看到“均匀”两个字就理解为“每桶完全相等”，忽略了整数行无法整除的现实约束。

误区报表里到底用 ROW_NUMBER 还是 RANK 还是 DENSE_RANK？我纠结半天，觉得只能选一个，其他的都没用。

正解它们完全可以同时出现在一张报表里，各司其职：RN 做行号用于分页去重，R 做“竞赛名次”传递竞争压力，D 做“等级标签”方便分层。就像运动会颁奖同时需要参赛编号、奖牌名次和等级证书，三份材料并存不冲突。

为何常见：教材通常把三者并列对比，容易让人误解为“互斥选项”，实际上它们是不同维度的标注工具，组合使用才是报表常态。

面试题

Q1. 同组内有两人成绩并列第一，ROW_NUMBER() 会给它们分配什么编号？

单选题 · easy

  • A.相同编号都是 1
  • B.不同编号 1 和 2← 正确答案
  • C.相同编号都是 2
  • D.随机分配两个编号

答案：B。ROW_NUMBER 是流水号发牌员，只按队列顺序发连续编号，根本不关心成绩是否相同。所以并列第一的两人会拿到 1 和 2。想要并列名次应改用 RANK 或 DENSE_RANK。

Q2. RANK() 对并列成绩的处理方式是什么？

单选题 · easy

  • A.并列同名次但不跳号
  • B.并列同名次且会跳号← 正确答案
  • C.并列也强制区分先后
  • D.并列名次取平均数值

答案：B。RANK 的核心规则是“同分同名次，但占位导致跳号”，如 1、1、3。它统计的是成绩严格高于当前行的人数再加一，并列者共享名次，后续名次跳过被占用的号。

Q3. DENSE_RANK() 与 RANK() 的核心区别是什么？

单选题 · mid

  • A.DENSE 不进行排序
  • B.DENSE 并列后不跳号← 正确答案
  • C.DENSE 只能用于整表
  • D.DENSE 返回百分比

答案：B。DENSE_RANK 按“不同分数档”计数，并列后名次依然连续，如 1、1、2、3；RANK 按“高于我的人数”计数，会跳号如 1、1、3。两者排序逻辑完全相同，区别只在是否跳过被并列占用的名次。

Q4. 用 LAG(amount, 1) 计算部门月度环比时，窗口应该怎么定义？

单选题 · mid

  • A.按月份分区再做排序
  • B.按部门分区再按时间排序← 正确答案
  • C.配合 DENSE 一起使用
  • D.先 GROUP BY 再排序

答案：B。LAG 的“上一行”必须在部门内部找，否则会跨部门配对。正确写法是 LAG(amount,1) OVER (PARTITION BY dept ORDER BY month)：先按部门切块，再在块内按时间排序，上一条才真正是“本部门上月”。

Q5. 订单表有重复记录，想按客户保留最新一条，最合适的窗口函数是什么？

单选题 · mid

  • A.RANK 取第一名次
  • B.LAG 回看时间差
  • C.ROW_NUMBER 编号取 1← 正确答案
  • D.NTILE 分桶过滤

答案：C。先按客户分区、按更新时间倒序排序，再用 ROW_NUMBER 编号，最后筛出编号为 1 的行即最新记录。RANK 会让并列时间出现多条 1 号，破坏去重目的；ROW_NUMBER 的唯一性才是去重利器。

Q6. 窗口函数与 GROUP BY 的最大区别是什么？

单选题 · easy

  • A.窗口函数压缩多行为一行
  • B.窗口函数保留每一行并附加列← 正确答案
  • C.窗口函数无法使用排序
  • D.窗口函数只能处理数字列

答案：B。GROUP BY 把多行折叠成汇总行，行数变少；窗口函数则像在每一行旁边贴一张名次卡，原始行数一个不少，只是新增了一列计算结果。这是理解窗口函数的关键起点。

Q7. NTILE(4) 把 10 行数据分桶，桶内行数分布是？

单选题 · hard

  • A.每桶均匀平均 2.5 行
  • B.前两桶 3 行后两桶 2 行← 正确答案
  • C.每桶 4 行最后有溢出
  • D.随机划分不做均匀处理

答案：B。10 ÷ 4 = 2 余 2，商是每桶基础行数，余数 r 决定前 r 桶各多 1 行。所以前两桶 3 行、后两桶 2 行。NTILE 只保证“尽量均匀”，即任意两桶行数差不超过 1。

Q8. 下列哪些场景适合使用 ROW_NUMBER()？

多选题 · mid

  • A.分页取第 50 到 60 行← 正确答案
  • B.去除重复的订单记录← 正确答案
  • C.给并列成绩做名次统计
  • D.标记每次会话的编号← 正确答案

答案：ABD。ROW_NUMBER 生成唯一连续序号，适合分页、去重、会话编号。并列成绩名次要用 RANK 或 DENSE_RANK，因为 ROW_NUMBER 不给并列行相同编号，无法表达“并列”语义。

Q9. 关于 LAG 与 LEAD 的描述，正确的有哪些？

多选题 · mid

  • A.LAG 读前行 LEAD 读后行← 正确答案
  • B.两者都需要 ORDER BY 才有意义← 正确答案
  • C.LEAD 可提前看到下一时刻← 正确答案
  • D.两者都只能偏移一行

答案：ABC。LAG 访问上一行、LEAD 访问下一行，二者都依赖 ORDER BY 定义“前后”。偏移量 n 可自由指定，并非只能取相邻行，所以 D 错误。两者配合可算移动平均、行为间隔等时序特征。

Q10. 成绩列表为 95、92、92、87，关于 RANK 与 DENSE_RANK 的结果，正确的是？

多选题 · hard

  • A.RANK 结果为 1、2、2、4← 正确答案
  • B.RANK 结果为 1、2、3、4
  • C.DENSE 结果为 1、2、2、3← 正确答案
  • D.RANK 与 DENSE 结果完全一样

答案：AC。两个 92 分并列第二，RANK 按“高于我的人数 + 1”计算，第三名位置被并列者占用，87 分为第四名，即 1、2、2、4；DENSE_RANK 按分数档计数，只有 95/92/87 三档，结果 1、2、2、3。两者差异正体现在是否跳号。