PHP小码农

Appearance

窗口函数使用场景 ​

前面几节我们逐个拆解了窗口函数的语法：OVER 子句、排名函数、分析函数、聚合窗口函数、帧子句。你已经知道每个零件怎么用，但真正的难点在于——拿到一个业务需求时，判断该不该用窗口函数，用哪一个，窗口怎么定义。

本节用 8 个完整的实战场景回答这个问题。每个场景都给出业务问题、建表造数、可直接运行的 SQL、预期输出和关键点，覆盖报表、去重、同比、帕累托、移动平均、会话划分、多窗口对比、WINDOW 复用这些最常见的工作。学完本节，你会形成一套稳定的判断习惯。

前置知识

阅读本节前，建议先了解：FRAME 子句（ROWS / RANGE / GROUPS）

基础概念 ​

在动手之前，先把本节反复出现的术语对齐：

概念术语说明
组内 Top-NTop-N per Group每个分组内取排名前 N 的行，是窗口函数最经典的用法
去重保留最新Deduplication同一业务键只保留一行，通常保留时间最新或金额最大的
环比Period-over-Period本期与上一期对比，用 lag 取上一期值
同比Year-over-Year本期与去年同期对比，lag 的偏移量是周期数（12 个月）
帕累托Pareto按贡献降序累计占比，验证「20% 的项贡献 80% 的结果」
移动平均Moving Average固定窗口内的平均值，用于平滑波动
会话划分Sessionization按事件间隔阈值把连续行为切成一个个会话
窗口复用Named Window用 WINDOW 子句给窗口定义命名，多次引用避免重复书写
窗口继承Window Inheritance新窗口引用已命名窗口，在其基础上追加 ORDER BY
断点标记Change Point判定为新会话起始的那一行，用 1 标记，再累计成会话号

选型判断：窗口函数还是别的写法 ​

很多人第一反应是用 GROUP BY 或自连接，其实窗口函数能覆盖的场景远比想象中多。先看这张对照表：

需求特征推荐方案原因
明细行 + 组内统计同屏窗口函数GROUP BY 会把行折叠掉，拿不到明细
每组只留一行（去重/取最新）窗口函数 + 子查询过滤DISTINCT ON 也可，但窗口函数更通用
组内排名、Top-N窗口函数自连接做 Top-N 要处理并列，写法冗长且慢
组内累计、组内占比窗口函数自连接算累计是 O(n²)
跨行取值（上一行/下一行）lag / lead自连接需要 +1 关联，容易漏行
只要每组的汇总值，不要明细GROUP BY聚合行数更少，更快
两表按「各自分组序号」对齐窗口函数 + 关联自连接无法表达序号对齐
需要引用另一表的行（逐行取子集）LATERAL见后续 LATERAL 章节

核心判断标准：要不要保留明细行。要保留 → 窗口函数；不要 → GROUP BY。

语法与用法 ​

窗口函数的最小可用形态，是在 SELECT 列表里给函数加 OVER：

sql
-- 为每个城市按金额降序编号，保留全部明细行
SELECT
  city,
  order_no,
  amount,
  row_number() OVER (PARTITION BY city ORDER BY amount DESC) AS 城市内序号
FROM orders o
JOIN users u ON u.id = o.user_id
ORDER BY city, 城市内序号;

关键点：OVER 里的 PARTITION BY 决定分组，ORDER BY 决定组内顺序，两者共同决定每一行「能看到什么」。

详细说明 ​

场景分类总览 ​

8 个场景按用途分成四类：

类别场景核心函数
筛选类组内 Top-N、去重保留最新row_number / rank / dense_rank
对比类环比同比、移动平均lag / avg
汇总类累计与占比、帕累托sum
结构类会话划分、多窗口对比、WINDOW 复用lag + sum / 多个 OVER / WINDOW

排名函数的取舍 ​

组内取 Top-N 时，row_number、rank、dense_rank 的选择会直接影响结果：

函数并列处理序号示例（金额 100, 90, 90, 80）适用场景
row_number并列也强行分先后1, 2, 3, 4只要 N 条，并列也要截断
rank并列同名次，跳号1, 2, 2, 4保留全部并列的第一名
dense_rank并列同名次，不跳号1, 2, 2, 3名次压缩，如「前 3 名」

判断口诀：取「前 N 条记录」用 row_number；取「前 N 个名次」用 rank 或 dense_rank。

用 CTE 把窗口结果变成可过滤的字段 ​

窗口函数不能写在 WHERE 里，因为 WHERE 先于窗口计算执行。标准做法是包一层 CTE：

sql
-- 先算序号，再过滤
WITH ranked AS (
  SELECT
    id,
    user_id,
    amount,
    row_number() OVER (PARTITION BY user_id ORDER BY amount DESC, id) AS rn
  FROM orders
  WHERE status <> 'cancelled'
)
SELECT * FROM ranked WHERE rn <= 3;

实战示例 ​

场景 1：组内 Top-N（每个城市金额前 3 的订单） ​

业务问题：运营要看每个城市金额最高的 3 笔订单，用于复盘大客户分布。

sql
-- 建演示表
CREATE TABLE demo_topn_users (
  id       integer PRIMARY KEY,
  username text    NOT NULL,
  city     text    NOT NULL
);

CREATE TABLE demo_topn_orders (
  id      bigserial PRIMARY KEY,
  user_id integer       NOT NULL,
  order_no text         NOT NULL,
  amount  numeric(10,2) NOT NULL
);

INSERT INTO demo_topn_users (id, username, city) VALUES
  (1, 'alice', 'beijing'),
  (2, 'bob',   'beijing'),
  (3, 'carol', 'shanghai'),
  (4, 'dave',  'shanghai');

INSERT INTO demo_topn_orders (user_id, order_no, amount) VALUES
  (1, 'BJ-1001', 500.00),
  (1, 'BJ-1002', 300.00),
  (2, 'BJ-1003', 300.00),
  (2, 'BJ-1004', 200.00),
  (1, 'BJ-1005', 100.00),
  (3, 'SH-2001', 400.00),
  (4, 'SH-2002', 400.00),
  (3, 'SH-2003', 250.00),
  (4, 'SH-2004', 150.00);

-- 每组取前 3
WITH ranked AS (
  SELECT
    u.city,
    o.order_no,
    o.amount,
    row_number() OVER (PARTITION BY u.city ORDER BY o.amount DESC, o.id) AS rn
  FROM demo_topn_orders o
  JOIN demo_topn_users u ON u.id = o.user_id
)
SELECT city, order_no, amount, rn
FROM ranked
WHERE rn <= 3
ORDER BY city, rn;
text
   city   | order_no | amount | rn
----------+----------+--------+----
 beijing  | BJ-1001  | 500.00 |  1
 beijing  | BJ-1002  | 300.00 |  2
 beijing  | BJ-1003  | 300.00 |  3
 shanghai | SH-2001  | 400.00 |  1
 shanghai | SH-2002  | 400.00 |  2
 shanghai | SH-2003  | 250.00 |  3

关键点：ORDER BY amount DESC, id 里的 id 是「打破并列的兜底键」。少了它，两笔 300 元的订单谁排第 2 是不确定的，结果每次执行可能不同。想让并列共享名次就换 rank/dense_rank。

场景 2：组内去重保留最新 ​

业务问题：订单表因重推产生了重复 order_no，需要每个 order_no 只保留 created_at 最新的一条。

sql
CREATE TABLE demo_dup_orders (
  id         bigserial PRIMARY KEY,
  order_no   text          NOT NULL,
  user_id    integer       NOT NULL,
  amount     numeric(10,2) NOT NULL,
  created_at timestamptz   NOT NULL
);

INSERT INTO demo_dup_orders (order_no, user_id, amount, created_at) VALUES
  ('NO-001', 1, 100.00, '2026-04-01 10:00:00+08'),
  ('NO-001', 1, 100.00, '2026-04-01 10:05:00+08'),  -- 重复，保留这条
  ('NO-002', 1, 200.00, '2026-04-01 11:00:00+08'),
  ('NO-003', 2, 300.00, '2026-04-02 09:00:00+08'),
  ('NO-003', 2, 300.00, '2026-04-02 09:30:00+08'),  -- 重复，保留这条
  ('NO-003', 2, 300.00, '2026-04-02 09:10:00+08');

-- 方式 A：窗口函数 + 子查询（推荐，通用）
SELECT count(*) AS 去重前 FROM demo_dup_orders;

WITH dedup AS (
  SELECT
    id,
    row_number() OVER (
      PARTITION BY order_no
      ORDER BY created_at DESC, id DESC
    ) AS rn
  FROM demo_dup_orders
)
SELECT count(*) AS 去重后
FROM dedup
WHERE rn = 1;
text
 去重前
--------
      6

 去重后
--------
      3

用窗口函数删除物理重复行（先备份再执行）：

sql
-- 保留每组最新一条，其余删除
DELETE FROM demo_dup_orders
WHERE id IN (
  SELECT id FROM (
    SELECT
      id,
      row_number() OVER (
        PARTITION BY order_no
        ORDER BY created_at DESC, id DESC
      ) AS rn
    FROM demo_dup_orders
  ) t
  WHERE rn > 1
);

对照写法 DISTINCT ON（PostgreSQL 特有，更短但只能取行，不便于先统计）：

sql
-- 等价的 DISTINCT ON 写法
SELECT DISTINCT ON (order_no) *
FROM demo_dup_orders
ORDER BY order_no, created_at DESC, id DESC;

关键点：窗口函数方式可以先用 count(*) 校验去重前后行数，再执行删除，更安全。DISTINCT ON 要求 ORDER BY 首列与 DISTINCT ON 表达式一致，写法紧凑但可读性弱一些。两者都必须在排序键里加 id 兜底，否则同一时间戳的重复行保留哪条不确定。

场景 3：环比 / 同比 ​

业务问题：算出每月销售额相对上月（环比）和去年同月（同比）的增长率。

sql
CREATE TABLE demo_monthly_sales (
  month   date          NOT NULL,  -- 每月 1 号
  amount  numeric(12,2) NOT NULL,
  PRIMARY KEY (month)
);

INSERT INTO demo_monthly_sales (month, amount) VALUES
  ('2025-12-01',  900.00),
  ('2026-01-01', 1000.00),
  ('2026-02-01', 1200.00),
  ('2026-03-01',  900.00),
  ('2026-04-01', 1500.00);

-- 环比：lag(amount, 1)，同比：lag(amount, 12)
SELECT
  to_char(month, 'YYYY-MM') AS 月份,
  amount                    AS 本月,
  lag(amount, 1)  OVER (ORDER BY month) AS 上月,
  lag(amount, 12) OVER (ORDER BY month) AS 去年同月,
  round(
    100.0 * (amount - lag(amount, 1) OVER (ORDER BY month))
          / lag(amount, 1) OVER (ORDER BY month),
    2
  ) AS 环比增速,
  round(
    100.0 * (amount - lag(amount, 12) OVER (ORDER BY month))
          / nullif(lag(amount, 12) OVER (ORDER BY month), 0),
    2
  ) AS 同比增速
FROM demo_monthly_sales
ORDER BY month;
text
  月份   |  本月   |  上月   | 去年同月 | 环比增速 | 同比增速
---------+---------+---------+----------+----------+----------
 2025-12 |  900.00 |         |          |          |
 2026-01 | 1000.00 |  900.00 |          |    11.11 |
 2026-02 | 1200.00 | 1000.00 |          |    20.00 |
 2026-03 |  900.00 | 1200.00 |          |   -25.00 |
 2026-04 | 1500.00 |  900.00 |          |    66.67 |

关键点：

  • 首月没有「上月」，lag 返回 NULL，增速自然为 NULL，这是正确行为，不需要填 0；
  • 分母要用 nullif(x, 0) 防除零，否则上月为 0 时整条查询会报 division by zero；
  • 同比的偏移量是 12（月粒度），如果是日粒度、逐年比就是 365，需按业务周期设定；
  • 数据不连续（缺月）时 lag 取到的是「上一行」而非「上一个月」，此时应先用 generate_series 补齐月份再算。

场景 4：累计与占比报表（含帕累托） ​

业务问题：按销售额从高到低列出各月，给出累计金额、累计占比，找出贡献 80% 的关键月份。

sql
-- 沿用 demo_monthly_sales，按金额降序做帕累托
SELECT
  to_char(month, 'YYYY-MM') AS 月份,
  amount                    AS 金额,
  sum(amount) OVER (ORDER BY amount DESC, month) AS 累计金额,
  round(100.0 * amount / sum(amount) OVER (), 2) AS 单项占比,
  round(
    100.0 * sum(amount) OVER (ORDER BY amount DESC, month)
          / sum(amount) OVER (),
    2
  ) AS 累计占比
FROM demo_monthly_sales
ORDER BY amount DESC, month;
text
  月份   |  金额   | 累计金额 | 单项占比 | 累计占比
---------+---------+----------+----------+----------
 2026-04 | 1500.00 |  1500.00 |    27.27 |    27.27
 2026-02 | 1200.00 |  2700.00 |    21.82 |    49.09
 2026-01 | 1000.00 |  3700.00 |    18.18 |    67.27
 2025-12 |  900.00 |  4600.00 |    16.36 |    83.64
 2026-03 |  900.00 |  5500.00 |    16.36 |   100.00

关键点：

  • 累计求和依赖帧：ORDER BY amount DESC 后默认帧就是「起点到当前行」，所以 sum 直接得到累计值，不用显式写帧；
  • 分母 sum(amount) OVER () 没有 ORDER BY，帧是整个分区，得到总额 5500；
  • 累计占比首次超过 80% 的那一行就是「帕累托分界线」，可用 CTE 再筛一层；
  • 排序键有并列（两条 900）时必须补 month 作兜底键，否则累计顺序不稳定，占比会跳变。

场景 5：移动平均与平滑 ​

业务问题：日销售额波动大，用 3 日移动平均看趋势。

sql
CREATE TABLE demo_daily_sales (
  stat_date date          NOT NULL,
  amount    numeric(10,2) NOT NULL,
  PRIMARY KEY (stat_date)
);

INSERT INTO demo_daily_sales (stat_date, amount) VALUES
  ('2026-03-01', 100.00),
  ('2026-03-02', 200.00),
  ('2026-03-03', 150.00),
  ('2026-03-04', 250.00),
  ('2026-03-05', 300.00);

SELECT
  stat_date,
  amount,
  round(avg(amount) OVER (
    ORDER BY stat_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ), 2) AS 三日移动平均
FROM demo_daily_sales
ORDER BY stat_date;
text
 stat_date  | amount  | 三日移动平均
------------+---------+--------------
 2026-03-01 |  100.00 |       100.00
 2026-03-02 |  200.00 |       150.00
 2026-03-03 |  150.00 |       150.00
 2026-03-04 |  250.00 |       200.00
 2026-03-05 |  300.00 |       233.33

关键点：

  • 窗口还没攒满 3 天时（前两行），avg 只对已有行求平均，不是除以 3。若业务要求「不足 3 天不出数」，需在外层用 row_number() 或 count(*) OVER (...) 过滤；
  • 「3 日」用 ROWS 只有在日期连续时才成立。日期有缺失时，ROWS 2 PRECEDING 会跨缺口取到更早的行，应改用 RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW；
  • avg 的分母是帧内行数，若想按固定天数当分母，得写 sum(x) OVER (...) / 3。

场景 6：连续活跃天数 / 会话划分 ​

业务问题：把用户行为按「间隔超过 30 分钟即视为新会话」切成会话，输出每条事件所属的会话号。

sql
CREATE TABLE demo_user_events (
  id         bigserial   PRIMARY KEY,
  user_id    integer     NOT NULL,
  event_time timestamptz NOT NULL
);

INSERT INTO demo_user_events (user_id, event_time) VALUES
  (1, '2026-05-01 09:00:00+08'),
  (1, '2026-05-01 09:10:00+08'),
  (1, '2026-05-01 09:20:00+08'),
  (1, '2026-05-01 10:30:00+08'),
  (1, '2026-05-01 10:40:00+08'),
  (1, '2026-05-01 14:00:00+08');

-- 第一步：用 lag 算与上一条的间隔，标记会话断点
WITH gaps AS (
  SELECT
    id,
    user_id,
    event_time,
    extract(epoch FROM (
      event_time - lag(event_time) OVER (PARTITION BY user_id ORDER BY event_time)
    )) / 60 AS 距上条分钟,
    CASE
      WHEN event_time - lag(event_time) OVER (
        PARTITION BY user_id ORDER BY event_time
      ) > INTERVAL '30 minutes'
        OR lag(event_time) OVER (
             PARTITION BY user_id ORDER BY event_time
           ) IS NULL
      THEN 1 ELSE 0
    END AS 新会话标记
  FROM demo_user_events
),
-- 第二步：累计断点标记得到会话号
sessions AS (
  SELECT
    id,
    user_id,
    event_time,
    距上条分钟,
    sum(新会话标记) OVER (
      PARTITION BY user_id ORDER BY event_time, id
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS 会话号
  FROM gaps
)
SELECT
  to_char(event_time, 'HH24:MI') AS 时间,
  距上条分钟,
  会话号
FROM sessions
ORDER BY event_time;
text
 时间  | 距上条分钟  | 会话号
-------+-------------+--------
 09:00 |             |      1
 09:10 |          10 |      1
 09:20 |          10 |      1
 10:30 |          70 |      2
 10:40 |          10 |      2
 14:00 |         200 |      3

关键点：

  • 这是窗口函数最经典的「两步法」：先标记断点，再累计断点。累计必须用 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，保证按行顺序累加；
  • 不能把 lag 和 sum 嵌套在一个 OVER 里（sum(lag(...)) OVER (...) 会报 window function calls cannot be nested），所以必须拆成两个 CTE；
  • 首行没有「上一条」，间隔为 NULL，所以断点判断要显式处理 IS NULL 的情况，否则首行不会被标记为会话起点；
  • 排序键加 id 兜底，防止同一秒内多条事件导致顺序不确定。

场景 7：同一张表多个窗口对比 ​

业务问题：一张城市季度销售表，要在一行里同时给出「城市排名」「本季度占本城市比例」「本季度占全站比例」。

sql
CREATE TABLE demo_city_sales (
  city    text          NOT NULL,
  quarter text          NOT NULL,
  amount  numeric(10,2) NOT NULL
);

INSERT INTO demo_city_sales (city, quarter, amount) VALUES
  ('beijing',   'Q1', 300.00),
  ('beijing',   'Q2', 200.00),
  ('shanghai',  'Q1', 250.00),
  ('shanghai',  'Q2', 150.00),
  ('guangzhou', 'Q1', 200.00),
  ('guangzhou', 'Q2', 100.00);

-- 先用 CTE 求出城市总额，再在同一行里叠加多个窗口
WITH city_total AS (
  SELECT city, sum(amount) AS city_amount
  FROM demo_city_sales
  GROUP BY city
)
SELECT
  s.city,
  s.quarter,
  s.amount,
  rank() OVER (ORDER BY t.city_amount DESC)          AS 城市排名,
  round(100.0 * s.amount / t.city_amount, 2)         AS 城市内占比,
  round(100.0 * s.amount / sum(s.amount) OVER (), 2) AS 全站占比
FROM demo_city_sales s
JOIN city_total t USING (city)
ORDER BY 城市排名, s.city, s.quarter;
text
    city    | quarter | amount  | 城市排名 | 城市内占比 | 全站占比
------------+---------+---------+----------+------------+----------
 beijing    | Q1      |  300.00 |        1 |      60.00 |    25.00
 beijing    | Q2      |  200.00 |        1 |      40.00 |    16.67
 shanghai   | Q1      |  250.00 |        2 |      62.50 |    20.83
 shanghai   | Q2      |  150.00 |        2 |      37.50 |    12.50
 guangzhou  | Q1      |  200.00 |        3 |      66.67 |    16.67
 guangzhou  | Q2      |  100.00 |        3 |      33.33 |     8.33

关键点：

  • 同一个 SELECT 里可以叠加任意多个 OVER，它们互不影响，各自独立计算；
  • 「按城市总额排名」这种需求，排名依据是聚合后的值，而聚合值不能直接作为同一层窗口的 ORDER BY 表达式来排名，所以要先 GROUP BY 收进 CTE，再在外层 JOIN 进来做窗口；
  • 三个窗口的 PARTITION BY / ORDER BY 各不相同，PostgreSQL 会分别做排序；如果排序键刚好一致，可以合并排序省一次开销；
  • 城市内占比 用的是 CTE 里的 city_amount 做分母，全站占比 用的是 sum(amount) OVER () 做分母，两个窗口互不干扰。

场景 8：WINDOW 子句命名复用与继承 ​

业务问题：同一查询里多个窗口共享 PARTITION BY city，只是 ORDER BY 不同，逐个写会非常啰嗦。

sql
-- WINDOW 子句：命名窗口，并让后续窗口继承
SELECT
  city,
  quarter,
  amount,
  sum(amount) OVER w_city                       AS 城市总额,
  row_number() OVER w_city_amount_desc          AS 城市内金额序号,
  round(100.0 * amount / sum(amount) OVER w_city, 2) AS 城市内占比
FROM demo_city_sales
WINDOW
  w_city AS (PARTITION BY city),
  w_city_amount_desc AS (w_city ORDER BY amount DESC, quarter)
ORDER BY city, 城市内金额序号;
text
    city    | quarter | amount  | 城市总额 | 城市内金额序号 | 城市内占比
------------+---------+---------+----------+----------------+------------
 beijing    | Q1      |  300.00 |   500.00 |              1 |      60.00
 beijing    | Q2      |  200.00 |   500.00 |              2 |      40.00
 guangzhou  | Q1      |  200.00 |   300.00 |              1 |      66.67
 guangzhou  | Q2      |  100.00 |   300.00 |              2 |      33.33
 shanghai   | Q1      |  250.00 |   400.00 |              1 |      62.50
 shanghai   | Q2      |   150.00 |   400.00 |              2 |      37.50

关键点：

  • WINDOW 子句写在 FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY 之间，SELECT 里直接写窗口名；
  • 继承规则：引用已命名窗口时只能追加 ORDER BY（或帧子句），不能改写 PARTITION BY。写 w2 AS (w_city PARTITION BY quarter) 会报 cannot override PARTITION BY clause of window "w_city"；
  • 被引用的窗口必须在 WINDOW 列表里先出现，顺序不能颠倒；
  • 配合上面场景 7 可见，多个窗口定义只要 PARTITION BY 一致，PostgreSQL 通常会复用同一次排序，性能不会因为多写几个 OVER 而线性劣化。

选型决策表 ​

把本节所有场景浓缩成一张「什么时候用什么」的速查表：

需求首选写法备选写法不推荐
每组取前 N 条row_number + CTELATERAL ... LIMIT N自连接算序号
每组取前 N 名（保留并列）rank / dense_rankrow_number 截断用 = 1 只取第一名
按业务键去重保留最新row_number 子查询DISTINCT ON自连接找最大 id
明细 + 组内汇总同屏聚合窗口函数子查询关联聚合结果多次查询拼装
环比 / 同比lag自连接 / 关联上月应用层循环计算
累计求和 / 占比sum(...) OVER (ORDER BY ...)自连接累计（O(n²)）应用层累加
移动平均avg(...) OVER (ROWS/RANGE ...)子查询窗口应用层滑窗
会话划分 / 连续天数lag + sum 两步 CTE递归 CTE自连接逐级比对
多个不同窗口同一行多个 OVERWINDOW 命名复用拆多条 SQL
窗口定义重复且 PARTITION BY 相同WINDOW + 继承原样重复写 OVER—
每组只要一行汇总GROUP BY窗口 + DISTINCT窗口函数硬套
逐行调用另一表并取多列LATERAL相关子查询（只能单列）窗口函数（跨表无力）

注意事项 ​

  1. 窗口函数不能出现在 WHERE 里。WHERE 在窗口计算之前执行，必须用 CTE 或子查询包一层：

    sql
    -- 错误！
SELECT *, row_number() OVER (ORDER BY amount) AS rn
FROM orders
WHERE row_number() OVER (ORDER BY amount) <= 3;

  2. 组内 Top-N 的排序键一定要加兜底列。否则并列行顺序不确定，每次执行结果可能不同：

    sql
    -- 不推荐：并列行顺序不确定
row_number() OVER (PARTITION BY city ORDER BY amount DESC)

-- 推荐：加 id 兜底
row_number() OVER (PARTITION BY city ORDER BY amount DESC, id)

  3. 同比环比的分母必须防除零，用 nullif(x, 0) 包住。

  4. 数据不连续时 lag 取的是「上一行」而非「上一期」。缺月场景要先用 generate_series 补齐时间轴。

  5. lag 的默认值是 NULL。需要填 0 或填其他值时要显式写第三个参数：

    sql
    lag(amount, 1, 0) OVER (ORDER BY month) AS 上月

  6. 累计求和依赖默认帧，一旦手写了 ORDER BY 又配错帧，累计会变成总额或只算当前行：

    sql
    -- 正确：默认帧即「起点到当前行」，得到累计
sum(amount) OVER (ORDER BY stat_date)

-- 错误！ROWS BETWEEN CURRENT ROW AND CURRENT ROW 只算自己
sum(amount) OVER (ORDER BY stat_date ROWS BETWEEN CURRENT ROW AND CURRENT ROW)

  7. 窗口函数不能嵌套作为参数，sum(lag(x) OVER (...)) OVER (...) 会直接报错，必须拆 CTE。

  8. DISTINCT 不能与窗口函数直接混用：count(DISTINCT x) OVER (...) 会报错，需要先子查询去重再窗口。

  9. WINDOW 继承不能覆盖 PARTITION BY，只能追加 ORDER BY。

  10. 移动平均在数据不足窗口大小时仍会出数，若业务要求「攒满窗口才输出」，需额外判断帧内行数。

  11. row_number 的去重删除要在事务里先验证行数再执行，避免误删。

最佳实践 ​

  1. 先问「要不要保留明细行」，这是窗口函数与 GROUP BY 的分水岭。

  2. 把窗口计算收进 CTE，外层只做过滤和排序，让每个 CTE 职责单一、可独立调试：

    sql
    WITH ranked AS (
  SELECT
    order_no,
    amount,
    row_number() OVER (PARTITION BY user_id ORDER BY amount DESC, id) AS rn
  FROM orders
)
SELECT * FROM ranked WHERE rn <= 3;

  3. 同一 PARTITION BY 的多个窗口用 WINDOW 子句命名，减少重复、方便统一维护：

    sql
    WINDOW
  w_city AS (PARTITION BY city),
  w_city_desc AS (w_city ORDER BY amount DESC, quarter)

  4. 为窗口的 PARTITION BY + ORDER BY 建复合索引，让 WindowAgg 免去排序：

    窗口定义建议索引
    PARTITION BY user_id ORDER BY amount DESC(user_id, amount DESC)
    PARTITION BY city ORDER BY amount DESC, id(city, amount DESC, id)
    ORDER BY stat_date(stat_date)

  5. 帕累托分析用 sum(...) OVER () 做分母，不要写死总额，数据更新后无需改 SQL。

  6. 会话划分坚持「两步法」：先 lag 标记断点，再 sum 累计，中间用 CTE 隔开。

  7. 时间语义的滑动一律用 RANGE + INTERVAL，位置语义才用 ROWS。

  8. 用 EXPLAIN (ANALYZE, BUFFERS) 复核窗口节点，重点看 WindowAgg 前是否有 Sort、rows 是否符合分区预期：

    bash
    psql -d demo -c "EXPLAIN (ANALYZE, BUFFERS) SELECT city, row_number() OVER (PARTITION BY city ORDER BY amount DESC) FROM demo_city_sales;"

下一节

继续学习：LATERAL 基础

参考链接 ​