SQL 数据分析 · 指南 · 2026年10月29日 · 11 分钟阅读

SQL 窗口函数详解：ROW_NUMBER、RANK、LAG 与累计求和

窗口函数会为每一行计算一个值，计算依据是与它相关的一组行（即「窗口」），而且不会把这些行合并成一行。GROUP BY 会把 33,121 个航班压缩成六行状态统计；COUNT(*) OVER (PARTITION BY status) 则保留全部 33,121 个航班，并在每个航班旁边写上它所属状态的航班总数。就这一个思路，可以实现排名、分组取前 N、累计求和、移动平均、占比，以及和上一行的比较。

语法永远是同一个结构：

function(...) OVER (
  PARTITION BY ...   -- 可选：每个分组重新计算
  ORDER BY ...       -- 可选：窗口内行的顺序
  ROWS/RANGE ...     -- 可选：使用当前行前后的哪些行
)

所有例子都基于 Nomto SQL 课程的航空公司数据库（104 个机场、9 种机型、2026 年 7 月至 9 月的 33,121 个航班、262,788 笔订单，金额单位为美元），使用 PostgreSQL 语法。下面每个输出都是真实结果，你可以在 Nomto 的 SQL 练习场里运行任何一个查询来核对。

OVER ()：在每一行旁边放一个聚合值

最简单的窗口就是整个结果集。比较每种机型的航程和全机队平均航程：

SELECT model, range,
       ROUND(AVG(range) OVER ()) AS fleet_avg,
       range - ROUND(AVG(range) OVER ()) AS diff
FROM aircrafts
ORDER BY range DESC
LIMIT 5;

不用窗口函数的话，要把 fleet_avg 放到每一行上，就得写子查询，或者和一张只有一行的表做 JOIN。

占总数的比例

窗口函数在 GROUP BY 之后执行，所以可以把聚合函数套在窗口里。SUM(COUNT(*)) OVER () 就是所有分组计数之和：

SELECT status, COUNT(*) AS flights,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct
FROM flights
GROUP BY status
ORDER BY flights DESC;

100.0 很重要：如果都是整数，PostgreSQL 会做整数除法，大部分占比都会变成 0。

PARTITION BY：每个分组一个独立窗口

PARTITION BY 把行分成若干组，在每组内部计算窗口，效果类似 GROUP BY，但不合并行。下面计算每种机型内部各舱位的收入占比：

SELECT a.model, tf.fare_conditions,
       SUM(tf.amount) AS revenue,
       ROUND(100.0 * SUM(tf.amount) / SUM(SUM(tf.amount)) OVER (PARTITION BY a.model), 1) AS pct_of_model
FROM ticket_flights tf
JOIN flights f ON f.flight_id = tf.flight_id
JOIN aircrafts a ON a.aircraft_code = f.aircraft_code
WHERE a.aircraft_code IN ('773', 'SU9')
GROUP BY a.model, tf.fare_conditions
ORDER BY a.model, revenue DESC;

百分比在每种机型内部加起来是 100，而不是整张表加起来是 100。

ROW_NUMBER、RANK 和 DENSE_RANK 的区别

三者都按 OVER 里的 ORDER BY 给行编号，唯一的区别在于并列：

• ROW_NUMBER() 给每一行唯一的编号；如果不加决胜列，并列行的先后是随机的。
• RANK() 让并列的行同号，然后跳号：1、2、2、4。
• DENSE_RANK() 让并列的行同号，但不跳号：1、2、2、3。

按不同目的地的数量给机场排名。有意思的地方在第十名附近，那里有五个机场并列，都是 9 个目的地：

WITH d AS (
  SELECT departure_airport, COUNT(DISTINCT arrival_airport) AS destinations
  FROM flights
  GROUP BY departure_airport
), r AS (
  SELECT departure_airport, destinations,
         ROW_NUMBER() OVER (ORDER BY destinations DESC, departure_airport) AS row_num,
         RANK()       OVER (ORDER BY destinations DESC) AS rnk,
         DENSE_RANK() OVER (ORDER BY destinations DESC) AS dense_rnk
  FROM d
)
SELECT * FROM r
WHERE row_num BETWEEN 8 AND 15
ORDER BY row_num;

五个机场并列之后，RANK 直接跳到 15（四个名次被「占用」了），而 DENSE_RANK 接着排 11。ROW_NUMBER 里的 departure_airport 决胜列让结果可复现；没有它，IKT、KZN、PEE、SGC 和 TJM 换一次运行可能以任意顺序出现。

怎么选：

• 「恰好要 N 行」或「去重」：用 ROW_NUMBER。
• 「奥运会式」排名，并列共享名次、下一个名次跳过：用 RANK。
• 「前 3 个不同的值」，比如最高的三个票价，不管每个价格有多少行：用 DENSE_RANK。

分组取前 N 名

ROW_NUMBER 搭配 PARTITION BY 的经典用法：莫斯科每个机场航班最多的两个目的地。

WITH r AS (
  SELECT departure_airport, arrival_airport, COUNT(*) AS flights,
         ROW_NUMBER() OVER (PARTITION BY departure_airport
                            ORDER BY COUNT(*) DESC, arrival_airport) AS rn
  FROM flights
  WHERE departure_airport IN ('SVO', 'DME', 'VKO')
  GROUP BY departure_airport, arrival_airport
)
SELECT departure_airport, arrival_airport, flights, rn
FROM r
WHERE rn <= 2
ORDER BY departure_airport, rn;

有两点值得注意：

1. 不能在 WHERE 里筛选窗口函数。 WHERE 在窗口计算之前执行，所以 WHERE ROW_NUMBER() OVER (...) <= 2 会报错。先在 CTE 或子查询里算出编号，再在外层筛选。（有些数据库提供 QUALIFY 子句来做这件事，PostgreSQL 没有。）
2. 检查并列。 从伏努科沃（VKO）出发，BZK 和 LED 都是 183 个航班；ROW_NUMBER 把 BZK 排在前面，只是因为按字母排序的决胜列。如果并列的都应该显示，就用 RANK() ... <= 2，这样每组可能返回超过两行。

去重：每个键只留最新的一行

同样的模式可以让每个键只保留一行。下面取喀山（KZN）出发的每个航班号最近一次已到达的航班：

WITH ranked AS (
  SELECT flight_no, arrival_airport, scheduled_departure,
         ROW_NUMBER() OVER (PARTITION BY flight_no ORDER BY scheduled_departure DESC) AS rn
  FROM flights
  WHERE departure_airport = 'KZN' AND status = 'Arrived'
)
SELECT flight_no, arrival_airport, scheduled_departure
FROM ranked
WHERE rn = 1
ORDER BY flight_no
LIMIT 5;

累计求和

在聚合函数的 OVER 里加上 ORDER BY，它就变成累计值。8 月第一周每天的订单数及累计订单数：

WITH daily AS (
  SELECT CAST(book_date AS date) AS day, COUNT(*) AS bookings
  FROM bookings
  WHERE book_date >= '2026-08-01' AND book_date < '2026-08-08'
  GROUP BY CAST(book_date AS date)
)
SELECT day, bookings,
       SUM(bookings) OVER (ORDER BY day) AS running_total,
       LAG(bookings) OVER (ORDER BY day) AS prev_day,
       bookings - LAG(bookings) OVER (ORDER BY day) AS change
FROM daily
ORDER BY day;

再加上 PARTITION BY，累计值就会在每个分组（比如每个机场、每个月）重新开始。

LAG 和 LEAD：上一行与下一行

LAG(x) 返回窗口顺序中上一行的 x，LEAD(x) 返回下一行的。上表就是用 LAG 计算日环比变化。第一行没有上一行，所以结果是 NULL；LAG(bookings, 1, 0) 会改为返回 0，LAG(bookings, 7) 则往回看一周。

LEAD 回答的是「接下来会怎样」。航班 PG0579 多久飞一次，最后一班是哪天？

SELECT flight_no,
       CAST(scheduled_departure AS date) AS day,
       LEAD(CAST(scheduled_departure AS date)) OVER (ORDER BY scheduled_departure)
         - CAST(scheduled_departure AS date) AS days_to_next
FROM flights
WHERE flight_no = 'PG0579'
ORDER BY scheduled_departure DESC
LIMIT 3;

这是每周一班的航班；时刻表里的最后一班没有下一班。注意，窗口里的 ORDER BY（升序）和整个查询的 ORDER BY（降序）互不影响：先计算窗口，再对输出排序。

同时比较多个对象的行时，一定要分区：LEAD(...) OVER (PARTITION BY flight_no ORDER BY scheduled_departure)。不分区的话，一个航班号的最后一班会被拿来和下一个航班号的第一班比较。

窗口框架：ROWS 和 RANGE

框架决定函数能看到当前行前后的哪些行。有两点要记住。

默认框架。 有 ORDER BY 但没写框架时，默认是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。RANGE 把 ORDER BY 值相同的所有行当作一步，所以并列的行会得到相同的累计值：

WITH d AS (
  SELECT departure_airport, COUNT(DISTINCT arrival_airport) AS destinations
  FROM flights
  GROUP BY departure_airport
), w AS (
  SELECT departure_airport, destinations,
         SUM(destinations) OVER (ORDER BY destinations DESC) AS range_total,
         SUM(destinations) OVER (ORDER BY destinations DESC, departure_airport
                                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_total
  FROM d
)
SELECT * FROM w
WHERE destinations BETWEEN 9 AND 10
ORDER BY destinations DESC, departure_airport;

用 RANGE 时，五个并列机场一下子都跳到 261；用 ROWS 时，累计值一行一行地增长。如果你的累计值在相同时间戳上出现「台阶」，原因就在这里。想要逐行累计，就用 ROWS，并保证排序唯一。

移动平均。 像 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 这样的框架，就是一个 3 行的移动窗口：

WITH daily AS (
  SELECT CAST(book_date AS date) AS day, COUNT(*) AS bookings
  FROM bookings
  WHERE book_date >= '2026-08-01' AND book_date < '2026-08-08'
  GROUP BY CAST(book_date AS date)
)
SELECT day, bookings,
       ROUND(AVG(bookings) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS avg_3d
FROM daily
ORDER BY day;

前两行的平均值不足三天，因为在这个窗口里 8 月 1 日之前没有数据。ROWS 数的是行，不是天：如果 daily 里缺了某一天，所谓的「3 日」平均会悄悄跨越四个日历日。可以先用日期序列补齐缺口，或者在日期列上使用 RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW。

窗口函数在查询执行顺序中的位置

从逻辑上说，窗口在 FROM、WHERE、GROUP BY 和 HAVING 之后计算，在 ORDER BY 和 LIMIT 之前计算。由此可以推出三条规则：

• 窗口只能看到通过了 WHERE 的行；如果要对全部航班排名、但只显示其中一部分，就在子查询里排名，再在外层筛选。
• 窗口里可以使用聚合函数（SUM(COUNT(*)) OVER ()），因为分组已经完成了。
• 不能在 WHERE、GROUP BY 或 HAVING 里使用窗口函数；要把查询包一层。

WHERE、GROUP BY 和 HAVING 之间的关系，见 GROUP BY 和 HAVING 的区别。排名和「分组取前 N」也是最常见的 SQL 面试题 之一；想动手练习，可以做 SQL 练习题 里的第 14、15 题。

常见问题

窗口函数和 GROUP BY 有什么区别？

GROUP BY 每组返回一行。窗口函数返回每一个输入行，并附加一个在一组行上计算出的值。两者可以在同一个查询里一起用：窗口作用于分组后的结果。

RANK 和 DENSE_RANK 有什么区别？

两者都给并列的行相同的编号。并列之后，RANK 会跳号（1、2、2、4），DENSE_RANK 不跳号（1、2、2、3）。在上面机场的例子里，五个机场并列第 10 名，下一个机场按 RANK 是第 15 名，按 DENSE_RANK 是第 11 名。

为什么我的累计值在好几行上都一样？

因为默认框架是 RANGE，它把 ORDER BY 值相同的行视为同一组，一次性全部计入。改为指定 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，并让排序唯一。

可以在 WHERE 里使用窗口函数吗？

不可以。先在 CTE 或子查询里计算，再在外层查询里筛选。

ROW_NUMBER 一定要写 ORDER BY 吗？

PostgreSQL 允许 ROW_NUMBER() OVER ()，但这样编号是随机的。只要你希望结果可复现，就给它一个带唯一决胜列的 ORDER BY。

其他语言： Português (Brasil), Tiếng Việt, English, Español