窗口函数执行顺序

WHERE里写rn=1为啥报错?

章节

  • 0:00 开场
  • 0:33 一句话回答
  • 0:55 痛点一:WHERE 筛 rn
  • 2:24 痛点二:和 GROUP BY 谁先
  • 3:40 痛点三:两个 ORDER BY
  • 4:23 原因一:逻辑执行顺序
  • 5:52 原因二:结果集上加列
  • 6:38 聚合 vs 窗口
  • 7:28 原因三:包一层
  • 8:15 补充:窗口边界
  • 9:44 补充:QUALIFY
  • 10:57 补充:同层多个窗口
  • 11:37 一棵决策树
  • 12:24 面试回答模板
  • 13:13 高频追问
  • 14:46 总结

文稿

开场

在 WHERE 里写一句 rn 等于 1,SQL 当场报错:这一步执行时,rn 根本还不存在。这是 SQL 面试里非常高频、也非常容易答错的一道题:窗口函数的执行顺序到底是什么?很多人张口就是:窗口函数就是分组排序,先 PARTITION BY,再 ORDER BY。这只解释了窗口内部怎么排,解释不了它在整条 SQL 里什么时候执行。面试官追问一句:为什么 WHERE 里不能直接写 rn 等于 1?窗口函数和 GROUP BY 谁先执行?很容易露馅。

一句话回答

一句话回答:窗口函数在 FROM、WHERE、GROUP BY、HAVING 之后执行,在最终的 ORDER BY 和 LIMIT 之前。它面对的,是已经过滤、已经聚合好的结果集,在上面再做分区、排序和计算。所以它不能写在 WHERE 里做筛选;要筛窗口结果,先把它算出来,再交给外层 SQL 过滤。

痛点一:WHERE 筛 rn

先看痛点一。需求:取每个用户 pay_amount 最大的一笔订单。示例数据是 6 笔订单:用户 1001 有四笔,支付数值 100、50、200、80;用户 1002 有两笔,120 和 60。很多人这么写:SELECT 里用 ROW_NUMBER,按 user_id 分区、按 pay_amount 倒序编号,起名 rn。WHERE 里先写 dt 过滤当天分区,再加一句 AND rn 等于 1。运行直接报错。因为 WHERE 执行的时候,SELECT 里的 rn 还没有被算出来。顺序是这样的:FROM 先拿到订单明细,WHERE 过滤当天分区,然后才到 SELECT 阶段计算 ROW_NUMBER。不同引擎报错文字不一样,有的说找不到 rn 这一列,有的说这里不能用窗口函数,根子都是执行顺序。正确写法是包一层子查询:内层照常过滤 dt,并在 SELECT 里算出 rn。外层再写 WHERE rn 等于 1,这时 rn 已经是内层结果里的一个普通字段。结果是:1001 留下 200 那一笔 O3,1002 留下 120 那一笔 O5。补一个工程细节:如果两笔 pay_amount 一样大,ROW_NUMBER 谁排第一可能不稳定,排序里再加 order_id 兜底。打个比方:WHERE 像进考场前查准考证,窗口函数像考完试后排名。你不能在进考场前说只让第一名进来,因为第一名还没考出来。

痛点二:和 GROUP BY 谁先

痛点二:把窗口函数和 GROUP BY 的顺序搞反,结果越算越怪。需求:看每个城市当天的 GMV,再给城市按 GMV 排名。正确逻辑是三步:先过滤当天订单,再按城市聚合出 GMV,最后对聚合后的城市 GMV 排名。数据量是这样变的:订单明细 1 亿行,WHERE 过滤当天剩 800 万行,GROUP BY 之后只剩 300 个城市,窗口函数在这 300 行上排名。如果以为窗口函数先于 GROUP BY,就会觉得它先对 800 万条明细排名,再聚合。实际不是这样。窗口函数看到的,是 GROUP BY 之后的结果。写法一:子查询里按城市 GROUP BY,算出 SUM pay_amount 作为 gmv;外层用 ROW_NUMBER,按 gmv 倒序排名。写法二更直接:同一层里 GROUP BY city,再写 RANK OVER,按 SUM pay_amount 倒序。这条 SQL 能跑通,恰好证明窗口函数在聚合之后执行:它排序用的 SUM,已经是每个城市一行的聚合值。打个比方:你要排班级平均分,肯定先算每个班的平均分,再给班级排名,而不是先给每个学生排名,再指望它自动变成班级排名。

痛点三:两个 ORDER BY

痛点三:把 OVER 里的 ORDER BY,和 SQL 最后的 ORDER BY 混为一谈。OVER 里写 PARTITION BY user_id、ORDER BY pay_time,决定的是每个分区里按什么顺序编号。SQL 最后写 ORDER BY pay_amount 倒序,决定的是最终结果怎么展示。比如用户 1001:按 pay_time 编号,O1 到 O4 是第 1 到第 4 笔;最后按 pay_amount 倒序展示,第一行是 O3,它的序号仍然是 3。窗口里的 ORDER BY 是怎么算,最后的 ORDER BY 是怎么展示;长得像,位置和作用完全不同。前面专题讲过,Hive 里最终的 ORDER BY 是全局排序,数据量大要慎用;OVER 里的 ORDER BY 只在每个分区内排。

原因一:逻辑执行顺序

核心论证,原因一:SQL 不是按书写顺序执行的。我们写的顺序是 SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY、LIMIT。逻辑执行顺序大致是:第一步 FROM 和 JOIN,确定数据来源;第二步 WHERE,过滤明细行;第三步 GROUP BY 分组;第四步聚合函数,算 SUM、COUNT、MAX、MIN;第五步 HAVING,过滤分组结果;第六步窗口函数,对过滤和聚合后的结果做分区排序计算;第七步 SELECT 输出列;第八步 DISTINCT 去重;第九步 ORDER BY 最终排序;第十步 LIMIT 截取结果。Snowflake 和 BigQuery 的官方文档,给出的顺序也是:WHERE、GROUP BY、HAVING,然后才是窗口,再到 DISTINCT、ORDER BY、LIMIT。这就解释了两个现象:WHERE 里不能用窗口函数,因为它还没执行;窗口函数可以基于聚合结果排名,因为它排在 GROUP BY 和 HAVING 后面。再补一条更完整的规则:PostgreSQL 文档写明,窗口函数只能出现在 SELECT 列表和最终的 ORDER BY 里,WHERE、GROUP BY、HAVING 里都不能直接写。注意,这里说的是逻辑顺序。优化器真正执行时会做谓词下推、列裁剪,物理顺序可以调整,但结果必须和逻辑顺序一致。SQL 的书写顺序是给人看的,逻辑执行顺序才是语义上真正生效的顺序。

原因二:结果集上加列

原因二:窗口函数处理的,是已经成型的结果集。它不像 WHERE 那样决定哪些明细行能进来,也不像 GROUP BY 那样把多行压成一行。它更像在已有结果集旁边,追加一列计算结果。比如当天订单过滤好以后,窗口函数可以补上:每个用户的第几笔订单、每个城市的 GMV 排名、每个用户上一笔订单的时间、每个用户的累计支付。原来四列:user_id、order_id、pay_amount、pay_time;开窗之后多出 rn、last_pay_time、cum_pay,原来几行,现在还是几行。顺带一提,OVER 里的 PARTITION BY 只是逻辑分组,既不是分区表的分区,也不是前面专题讲过的 DISTRIBUTE BY。

聚合 vs 窗口

普通聚合和窗口函数,最大的区别可以列成一张表。是否减少行数:GROUP BY 会,多行变一行;窗口函数不会,通常保留原行数。计算对象:GROUP BY 针对每个分组,窗口函数针对每个窗口分区。输出形态:GROUP BY 是分组后的汇总结果,窗口函数是明细行加窗口计算列。适合场景:GROUP BY 算城市 GMV、用户总消费;窗口函数做排名、累计、环比、取上一行。保留明细字段:GROUP BY 不方便,非分组字段不能直接保留,窗口函数可以。执行位置:GROUP BY 在前,窗口函数在后。打个比方:GROUP BY 像把全班试卷收上来算平均分,最后每个班只剩一行;窗口函数像在每张试卷旁边写上班内排名,试卷本身还在。

原因三:包一层

原因三:想筛窗口结果,必须再包一层。既然窗口函数在 WHERE 后面才算,就要先把窗口结果变成一份临时结果,再在外层过滤。常见写法有两种。写法一是子查询:FROM 后面的括号里算出 rn,起别名 t,外层 WHERE rn 等于 1。写法二是 CTE:WITH order_rank AS,把同样的逻辑放进去;下面从 order_rank 里取 rn 等于 1 的行,可读性更好。流程是:内层 FROM 加 WHERE,内层 SELECT 算出 rn,形成临时结果;rn 变成普通字段,外层 WHERE 才能筛它。一句话:窗口函数的结果,必须先落到中间结果里,才能被外层 WHERE 当普通字段过滤。

补充:窗口边界

下面是面试官可能延伸问的补充概念。第一个:ROWS 和 RANGE,窗口边界到底框住哪些行。窗口函数不只有 PARTITION BY 和 ORDER BY,还有一个常被忽略的边界,比如 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。意思是:从当前分区的第一行,一直算到当前行,常用来算累计值。比如对 pay_amount 做 SUM OVER,按 user_id 分区、按 pay_time 排序,加上这个边界,得到 cum_pay_amount。用户 1001 的四笔:100、50、200、80,累计依次是 100、150、350、430。大白话:PARTITION BY 决定按谁分考场,ORDER BY 决定考场里怎么排队,ROWS BETWEEN 决定往前往后看几个人。再看 RANGE:如果只写 ORDER BY、不写边界,默认并不是 ROWS。Hive 文档、Spark 3.5 源码和 PostgreSQL 文档都一致:只写 ORDER BY 时,默认边界就是下面这一句。RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。RANGE 按排序值划边界,和当前行 pay_time 相同的行会被当成同一档,一起算进来。假设 1001 的第二笔和第三笔都是 10:30 付款:ROWS 逐行累计是 150、350;RANGE 下这两行都显示 350。所以要逐行累计,就显式写 ROWS,别依赖默认值。没有 ORDER BY 时,默认框住整个分区。

补充:QUALIFY

第二个补充:QUALIFY,有些数据库可以直接筛窗口结果。它专门用来过滤窗口函数的结果,逻辑位置就在窗口函数之后、DISTINCT 之前。写法是:SELECT 里照常算 rn,WHERE 写 dt 条件,最后加一句 QUALIFY rn 等于 1,不用再包子查询。ClickHouse 文档说得很直白:WHERE 在窗口函数计算之前执行,QUALIFY 在它之后执行。BigQuery 的规则是:QUALIFY 只保留条件为 TRUE 的行,而且查询里必须有窗口函数。哪些引擎支持?常见说法是 Hive 和很多传统 MySQL 场景不支持,这里按版本修正。Snowflake、BigQuery、ClickHouse 支持;Databricks 从 Runtime 10.4 LTS 起支持。Hive 3 的语法里没有 QUALIFY,Hive 4.0 起已经支持;开源 Spark 3.5 不支持,Spark 4.2 起才加入。MySQL 8.0 不支持。Doris 4.0 起支持;StarRocks 3.0 起能写,但只支持窗口函数跟一个整数比较这种受限形式。所以面试更稳妥的说法是:通用写法用子查询或 CTE;确认引擎和版本支持,再用 QUALIFY。

补充:同层多个窗口

第三个补充:同一层里的多个窗口函数,会不会互相影响?同一层 SELECT 里的多个窗口函数,通常基于同一个输入结果集计算,不能直接互相引用别名。你不能在同一个 SELECT 里先算 rn,再马上用 rn 算另一个窗口结果;想复用,还是要包一层。列成四种场景:同时算 ROW_NUMBER 和 RANK,可以,放在同一个 SELECT。WHERE rn 等于 1,不可以,用子查询、CTE 或 QUALIFY。用 rn 再参与新计算,通常不可以,到外层 SQL 再算;最终 ORDER BY rn,通常可以,因为最终排序在 SELECT 之后。

一棵决策树

用一棵决策树收住。写窗口函数 SQL,先问:要不要先过滤明细行?要,就先写 WHERE,过滤 dt、状态、城市这些明细条件;不要,就直接进入分组或开窗。接着问:需不需要先聚合?需要,比如城市 GMV 排名,就先 GROUP BY,算出 SUM、COUNT。不需要,比如订单明细排名,就直接对明细结果开窗。两条路最后都用窗口函数做排名、累计或环比。再问:要不要筛窗口结果?要,比如 rn 等于 1 或 Top N,就包子查询或 CTE,外层 WHERE 过滤。引擎支持 QUALIFY,也可以直接用它;不需要筛,就直接 SELECT 输出。最后都由最终的 ORDER BY 和 LIMIT,控制展示顺序和行数。

面试回答模板

面试怎么答?可以这样说:窗口函数不是按书写顺序执行的。它在 FROM、WHERE、GROUP BY、HAVING 之后,最终 ORDER BY、LIMIT 之前,对已经过滤和聚合后的结果集做分区排序计算。所以第一,WHERE 里不能直接写 rn 等于 1,因为 WHERE 执行时 ROW_NUMBER 还没算出来。第二,窗口函数可以基于聚合结果排名,比如先按城市算 GMV,再对城市 GMV 排名。第三,窗口里的 ORDER BY 决定窗口怎么算,SQL 最后的 ORDER BY 只决定结果怎么展示。实战中要筛窗口结果,我一般用子查询或 CTE,内层算 ROW_NUMBER,外层 WHERE rn 等于 1。如果引擎支持 QUALIFY,比如 Snowflake、BigQuery、Hive 4,也可以直接用它过滤。

高频追问

追问一:窗口函数为什么不能写在 WHERE 里?WHERE 的职责是过滤明细行,它发生在窗口函数之前;此时 ROW_NUMBER、RANK 的结果都还没算,数据库当然不知道 rn 是什么。通用解法:内层先算窗口函数,把 rn 变成普通字段,外层再用 WHERE rn 等于 1 过滤。追问二:窗口函数和 GROUP BY 谁先执行?逻辑上 GROUP BY 先,窗口函数后,它处理的是 WHERE、GROUP BY、HAVING 之后的结果集。城市 GMV 排名,就是先按城市 GROUP BY 算出 GMV,再用 ROW_NUMBER OVER,按 gmv 倒序排名;窗口函数不会绕过聚合去拿原始明细。追问三:窗口函数里的 ORDER BY 和最后的 ORDER BY 有什么区别?窗口里的 ORDER BY 是计算规则的一部分,决定分区内按什么顺序编号、累计、取上一行。SQL 最后的 ORDER BY 是展示规则,只决定最终结果集怎么排。比如按 user_id 分区、按 pay_time 编号,是给每个用户的付款排先后;最后按 pay_amount 倒序,只是把输出从大到小排。追问四:为什么窗口函数保留明细,而 GROUP BY 会压缩行数?GROUP BY 的目标是汇总:一个城市一天 100 万笔订单,最后变成一行城市 GMV。窗口函数的目标是增强明细,通常不改变行数,而是在每行旁边加一列排名、累计值或上一行的值。所以既想保留订单明细,又想看用户内排名,用窗口函数比 GROUP BY 更合适。

总结

最后总结:FROM、WHERE 先取数过滤,GROUP BY、HAVING 再聚合筛组,窗口函数接着在成型的结果集上追加计算列。所以 WHERE 里筛不了 rn:要么包一层,要么在支持的引擎里用 QUALIFY。记住一句话:窗口函数是给结果集加列,不是给明细行设门槛。

看完去做题