39. 窗口函数

窗口函数（Window Function）是 SQLite 3.25.0（2018 年）才加入的能力，参考的是 PostgreSQL 的行为。它的标志性特征是一个 OVER(...) 子句——只要有 OVER，就是窗口函数；没有 OVER，就是普通聚合或标量函数。这是区分它和普通 GROUP BY 聚合最关键的判断点。

为什么需要窗口函数？打个比方：普通聚合 SUM(amount) 配 GROUP BY user_id，会把同一个用户的多笔订单”压成一行”汇总值，你同时就看不到每一笔订单的明细了。但很多时候你要的是”保留每一行，再额外算一个累计总和”——比如”显示每笔订单，以及该用户到这一笔为止累计花了多少”。这种”逐行算、但不折叠行”的需求，正是窗口函数的主场。

一、OVER 子句的三件套

一个窗口函数调用形如：函数() OVER (PARTITION BY 列1 ORDER BY 列2 框架)。

• PARTITION BY：把结果集按某列”分舱”。每个舱（分区）内部独立计算，互不影响。不写就表示整张表是一个大分区。
• ORDER BY：在分区内规定”行的先后顺序”，排名和累计都依赖它。
• 框架（frame-spec）：限定”计算时看哪些行”，比如”从分区开头到当前行”。

用我们的 orders(id INTEGER PRIMARY KEY, user_id INTEGER, amount REAL, created_at TEXT) 表来演示。先看每笔订单，以及该用户截止当前的累计消费：

SELECT user_id,
       id AS order_id,
       amount,
       SUM(amount) OVER (
         PARTITION BY user_id
         ORDER BY id
       ) AS running_total
FROM orders;

这里 SUM(...) OVER (...) 就是聚合窗口函数：没有 OVER 时 SUM 是普通聚合（折叠行），加上 OVER 后它变成”对每一行，算它所在窗口的合计”，行数不变。PARTITION BY user_id 让每个用户各算各的，ORDER BY id 让累计按订单顺序增长。

如果想按时间顺序累计（更贴近真实业务），把 ORDER BY id 换成 ORDER BY created_at 即可；created_at 是 TEXT 存的 ISO-8601，字典序即时间序，排序正确。还有一种常见需求是”每个用户截至当前笔，平均客单价”：

SELECT user_id,
       id AS order_id,
       amount,
       ROUND(AVG(amount) OVER (
         PARTITION BY user_id
         ORDER BY created_at
       ), 2) AS avg_so_far
FROM orders;

注意这里 AVG(...) 同样不折叠行，只是对”从分区开头到当前行”这一段求平均。理解”框架”是理解窗口函数的核心：当前行和它之前的所有行构成一个滑动窗口，函数就在窗口内算。所谓”同级行（peer）“指 ORDER BY 值相同的行，默认框架会把同级行一起算进去，所以相同 created_at 的行会得到相同的累计值——这通常正是你想要的行为。

二、排名函数

SQLite 内置了 11 个窗口函数，最常用的是排名三兄弟：row_number()、rank()、dense_rank()。

• row_number()：按 ORDER BY 顺序从 1 开始给每行一个唯一序号，不考虑并列。
• rank()：有并列时跳号（1,1,3…）。
• dense_rank()：有并列时不跳号（1,1,2…）。

SELECT user_id,
       amount,
       row_number() OVER (ORDER BY amount DESC) AS rn,
       rank()       OVER (ORDER BY amount DESC) AS r,
       dense_rank() OVER (ORDER BY amount DESC) AS dr
FROM orders;

row_number() 也常用于”每个用户取最新一笔订单”这类需求：先按用户分区、按时间倒序编号，再外层 WHERE rn = 1。

其余内置窗口函数还有 percent_rank()、cume_dist()、ntile(N)（把分区均分成 N 组）、lag(expr, offset) 与 lead(expr, offset)（取前/后 N 行的值）、first_value()、last_value()、nth_value()。其中 lag/lead 特别适合”对比本期与上期”。

再给两个贴近实战的例子。用 ntile(4) 把用户按消费额均分成”四象限”：

SELECT user_id,
       SUM(amount) AS total,
       ntile(4) OVER (ORDER BY SUM(amount) DESC) AS quartile
FROM orders
GROUP BY user_id;

用 percent_rank() 看某用户在全体中的相对位置（0.0 最低，1.0 最高）：

SELECT user_id,
       SUM(amount) AS total,
       percent_rank() OVER (ORDER BY SUM(amount)) AS pct
FROM orders
GROUP BY user_id;

窗口函数 vs GROUP BY 怎么选？ 一句话：如果你既要”逐行明细”又要”分组统计量”，用窗口函数；如果你只要”每组一行汇总”，用 GROUP BY。例如”看每笔订单 + 该用户总消费”，GROUP BY 做不到（它会把明细并成一行），必须窗口函数。反过来”只要每个用户总消费”，GROUP BY 更简单也更快。两者不互斥，常常先 GROUP BY 子查询、再在外层套窗口函数做排名。

三、命名窗口与框架写法

当一条 SQL 里多个窗口函数用相同的 OVER 定义，可以把它抽到 WINDOW 子句里起名复用，写在 HAVING 之后、ORDER BY 之前：

SELECT user_id,
       row_number() OVER win AS rn,
       SUM(amount)   OVER win AS total
FROM orders
WINDOW win AS (PARTITION BY user_id ORDER BY id);

框架除了默认的”到当前行”，还能显式写，例如”只看当前行前后各一行”：

SUM(amount) OVER (
  ORDER BY id
  ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
)

框架类型有三：ROWS（按行数）、RANGE（按 ORDER BY 值的范围）、GROUPS（按同级组）。边界可以是 UNBOUNDED PRECEDING（开头）、CURRENT ROW（当前行）、N PRECEDING/FOLLOWING、UNBOUNDED FOLLOWING（结尾）。还有 EXCLUDE 子句可排除当前行或同级行，较进阶，了解即可。

四、适用场景与类比小结

窗口函数适合：排名榜单、累计求和/滚动平均、同比环比（lag/lead）、分组内取 Top-N、去重保留明细等。它和普通聚合的本质区别一句话概括：聚合是”把多行变成一行”，窗口函数是”在每一行旁贴一个基于一组行的计算结果，行数不变”。

可以把它想成”火车的车窗”：你坐在自己的座位（当前行）上，窗户外能看到同一节车厢（分区）里从车头到你现在位置的一段风景（框架），窗口函数就是把这段风景算出的指标写在你座位旁的便利贴上。你还是你（行不丢），但多了一张有用的小抄。