窗口函数

窗口函数是 PG 的「招牌能力」之一：它在不折叠行的前提下，为每一行计算一个「跨多行的聚合/排名」值。和 GROUP BY 把多行压成一行不同，窗口函数保留每一行——所以你既能看到明细，又能看到「同行在分组里的排名、累计、同比」。

1. 基本语法：OVER (PARTITION BY ... ORDER BY ...)

窗口函数基本形态

SELECT
  category,
  name,
  price,
  AVG(price) OVER (PARTITION BY category) AS 分类均价,
  ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS 分类内排名
FROM core.products;

PARTITION BY 决定「和哪些行算一组」，ORDER BY 决定组内顺序（排名/累计依赖它）。

2. 排名函数

函数	行为
`ROW_NUMBER()`	组内从 1 连续编号（并列也各占一号）
`RANK()`	并列同名次，之后跳号（1,1,3）
`DENSE_RANK()`	并列同名次，之后不跳号（1,1,2）

3. 实战一：每组取「最新/Top N」记录

「每个用户最近一笔订单」用窗口函数极简（这也是第 9 章 LATERAL 的替代写法）：

每个用户最近一笔订单

SELECT username, order_no, created_at
FROM (
  SELECT u.username, o.order_no, o.created_at,
         ROW_NUMBER() OVER (PARTITION BY o.user_id ORDER BY o.created_at DESC) AS rn
  FROM core.orders o
  JOIN core.users u ON u.id = o.user_id
) t
WHERE rn = 1;

4. LAG / LEAD：访问「前后行」

常用于环比、差值、时间序列：

LAG/LEAD 环比

SELECT created_at::date AS 日期,
       COUNT(*) AS 订单数,
       LAG(COUNT(*)) OVER (ORDER BY created_at::date) AS 昨日订单数,
       COUNT(*) - LAG(COUNT(*)) OVER (ORDER BY created_at::date) AS 环比增量
FROM core.orders
GROUP BY created_at::date
ORDER BY 日期;

LAG(col, n) 取前 n 行，LEAD(col, n) 取后 n 行；没有前/后行时返回 NULL（可用第三参给默认值）。

5. 聚合窗口与窗口帧

窗口里的聚合（如 SUM() OVER）默认对整个分区求和；更精细地，你可以用窗口帧限定「算到哪」：

累计求和（窗口帧）

SELECT created_at::date AS 日期,
       COUNT(*) AS 当日,
       SUM(COUNT(*)) OVER (ORDER BY created_at::date
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 累计
FROM core.orders
GROUP BY created_at::date
ORDER BY 日期;

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 表示「从分区第一行到当前行」——这就是累计求和。还有 RANGE/GROUPS 等更语义化的帧定义。

6. 何时用窗口函数 vs GROUP BY vs LATERAL

要保留明细又想看分组统计/排名 → 窗口函数。
只要汇总、不要明细 → GROUP BY。
每组取前 N 且逻辑简单 → 窗口函数或 LATERAL 都行。

🎯动手

给 core.products 加一列「同分类内按价格降序的排名」和「同分类内价格与均价之差」，并只保留每个分类里价格最高的那一个商品。提示：ROW_NUMBER() 取 rn=1。