PostgreSQL: PostgreSQL窗口函数详解

Q: 多个窗口函数可以共用一个 OVER 子句吗？

PostgreSQL 支持 WINDOW 子句别名： WINDOW w AS (PARTITION BY ...) ，然后在多个函数中写 OVER w 。

最后更新：2026-08-26

1. 你将学到

窗口函数的 OVER 子句与执行逻辑
PARTITION BY 分区与 ORDER BY 排序
ROWS / RANGE / GROUPS 三种帧定义
排名函数：ROW_NUMBER / RANK / DENSE_RANK / NTILE
偏移函数：LAG / LEAD / FIRST_VALUE / LAST_VALUE / NTH_VALUE
累计求和与移动平均
窗口函数 vs 聚合函数的本质区别

2. 故事

Charlie 是一家 SaaS 平台的增长分析师。产品经理要求他输出一份用户留存报告：

每日新增用户数
第 7 天回访率（Day 7 Retention）
第 30 天回访率（Day 30 Retention）
每个用户的注册排名（同一天注册的第几人）

传统做法需要多条 SQL 配合临时表。Charlie 学会窗口函数后，一条 SQL 搞定全部统计——不用 GROUP BY 折叠行，每行都保留原始数据并附带计算结果。

3. Concept：窗口函数基础

(1) 什么是窗口函数

窗口函数对一组相关行（"窗口"）进行计算，但不折叠行——每一行都返回一个结果。这是与聚合函数最大的区别。

特性	聚合函数	窗口函数
行数变化	多行 → 一行	行数不变
语法	`SUM(col)`	`SUM(col) OVER (...)`
GROUP BY	必须	不需要
保留原始列	否	是
典型场景	汇总统计	排名、偏移、累计

(2) OVER 子句结构

SQL

function_name() OVER (
  [PARTITION BY expr]
  [ORDER BY expr [ASC|DESC] [NULLS FIRST|NULLS LAST]]
  [frame_clause]
)

flowchart TD
    A[OVER] --> B[PARTITION BY]
    B --> C[ORDER BY]
    C --> D[Frame Clause]
    D --> E{ROWS / RANGE / GROUPS}
    E --> F[ROWS BETWEEN ... AND ...]
    E --> G[RANGE BETWEEN ... AND ...]
    E --> H[GROUPS BETWEEN ... AND ...]
    B -.->|optional| C
    C -.->|optional| D
    style A fill:#e1f5fe
    style B fill:#fff9c4
    style C fill:#fff9c4
    style D fill:#c8e6c9

▶ 示例：最简窗口函数

SQL

SELECT
  order_id,
  customer_id,
  amount,
  SUM(amount) OVER () AS total_all
FROM orders;

TEXT 📖 仅展示

 order_id | customer_id | amount | total_all
----------+-------------+--------+-----------
      101 |           1 |  15000 |   750000
      102 |           1 |   8000 |   750000
      201 |           2 |  25000 |   750000

每一行都返回全表的 SUM，行数不变。

4. Concept：PARTITION BY 与 ORDER BY

(1) PARTITION BY 分区

PARTITION BY 将数据分成独立的"分区"，窗口函数在每个分区内独立计算。

子句	作用	类比
`PARTITION BY col`	按列分区	类似 GROUP BY，但不折叠
无 PARTITION BY	全表为一个分区	类似 GROUP BY 无分组列

▶ 示例：按客户分区求和

SQL

SELECT
  order_id,
  customer_id,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders
ORDER BY customer_id, order_id;

TEXT 📖 仅展示

 order_id | customer_id | amount | customer_total
----------+-------------+--------+----------------
      101 |           1 |  15000 |          23000
      102 |           1 |   8000 |          23000
      201 |           2 |  25000 |          25000
      301 |           3 |  12000 |          57000
      302 |           3 |  45000 |          57000

(2) ORDER BY 排序

ORDER BY 决定分区内行的排序，对排名函数和偏移函数至关重要。

场景	是否需要 ORDER BY	原因
ROW_NUMBER / RANK	必须	排名依赖顺序
LAG / LEAD	必须	前后行依赖顺序
SUM() OVER (PARTITION BY)	可选	无序时计算分区总和
FIRST_VALUE / LAST_VALUE	必须	首末值依赖顺序

▶ 示例：按金额排名

SQL

SELECT
  order_id,
  customer_id,
  amount,
  ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn_desc
FROM orders;

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(3) PARTITION BY + ORDER BY 组合

分区后排序，排名在每个分区内独立计算。

▶ 示例：每个客户的订单金额排名

SQL

SELECT
  order_id,
  customer_id,
  amount,
  ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY amount DESC
  ) AS rank_in_customer
FROM orders;

TEXT 📖 仅展示

 order_id | customer_id | amount | rank_in_customer
----------+-------------+--------+------------------
      102 |           1 |   8000 |                2
      101 |           1 |  15000 |                1
      201 |           2 |  25000 |                1
      302 |           3 |  45000 |                1
      301 |           3 |  12000 |                2

5. Concept：帧定义（Frame Clause）

(1) 三种帧类型

帧定义决定窗口函数"看到"哪些行参与计算。

帧类型	边界基于	适用场景
ROWS	物理行偏移	精确控制行数，如"前 3 行"
RANGE	逻辑值偏移	同值行一起处理，如"同金额"
GROUPS	同值分组偏移	PostgreSQL 特色，按 ORDER BY 值分组

(2) 帧边界关键字

关键字	含义
`UNBOUNDED PRECEDING`	分区第一行
`UNBOUNDED FOLLOWING`	分区最后一行
`CURRENT ROW`	当前行
`N PRECEDING`	前 N 行/值
`N FOLLOWING`	后 N 行/值

(3) 默认帧规则

有无 ORDER BY	默认帧
有 ORDER BY	`RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`
无 ORDER BY	`ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`

▶ 示例：ROWS 帧累计求和

SQL

SELECT
  order_date,
  amount,
  SUM(amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_sum
FROM daily_sales;

TEXT 📖 仅展示

 order_date  | amount | running_sum
-------------+--------+-------------
 2025-01-01  |   5000 |        5000
 2025-01-02  |   8000 |       13000
 2025-01-03  |   3000 |       16000
 2025-01-04  |  12000 |       28000

▶ 示例：ROWS 3行移动平均

SQL

SELECT
  order_date,
  amount,
  ROUND(AVG(amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ), 2) AS moving_avg_3
FROM daily_sales;

TEXT 📖 仅展示

  result  
----------
   42.50
(1 row)

▶ 示例：RANGE 帧按日期区间

SQL

SELECT
  order_date,
  amount,
  SUM(amount) OVER (
    ORDER BY order_date
    RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
  ) AS sum_last_7_days
FROM daily_sales;

TEXT 📖 仅展示

  result  
----------
   42.50
(1 row)

6. Concept：排名函数

(1) 四大排名函数对比

函数	同值处理	产出值	是否连续
ROW_NUMBER	严格递增	1,2,3,4	是
RANK	同值同号，跳号	1,1,3,4	否
DENSE_RANK	同值同号，不跳	1,1,2,3	是
NTILE(N)	均分N组	1,1,2,2,3,3	—

▶ 示例：ROW_NUMBER vs RANK vs DENSE_RANK

SQL

SELECT
  name,
  score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
  RANK()       OVER (ORDER BY score DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY score DESC) AS drnk
FROM students;

TEXT 📖 仅展示

 name   | score | rn | rnk | drnk
--------+-------+----+-----+------
 Alice  |    95 |  1 |   1 |    1
 Bob    |    90 |  2 |   2 |    2
 Charlie|    90 |  3 |   2 |    2
 Dave   |    85 |  4 |   4 |    3

(2) NTILE 分组

NTILE(N) 将有序行均分为 N 组，常用于四分位数分析。

▶ 示例：将客户按消费额分4组

SQL

SELECT
  customer_id,
  total_spent,
  NTILE(4) OVER (ORDER BY total_spent DESC) AS quartile
FROM customer_summary;

TEXT 📖 仅展示

 customer_id | total_spent | quartile
-------------+-------------+----------
          12 |      500000 |        1
           5 |      350000 |        1
           8 |      280000 |        2
          19 |      150000 |        2
           3 |       90000 |        3
          22 |       60000 |        3
          41 |       25000 |        4
          15 |        5000 |        4

7. Concept：偏移与值函数

(1) 偏移函数一览

函数	作用	典型用途
LAG(col, N, default)	当前行前第 N 行的值	环比增长
LEAD(col, N, default)	当前行后第 N 行的值	预测、对比
FIRST_VALUE(col)	窗口内第一个值	首单金额
LAST_VALUE(col)	窗口内最后一个值	末单金额
NTH_VALUE(col, N)	窗口内第 N 个值	第 N 笔订单

▶ 示例：LAG 计算日环比

SQL

SELECT
  order_date,
  daily_revenue,
  LAG(daily_revenue, 1) OVER (ORDER BY order_date) AS prev_day,
  daily_revenue - LAG(daily_revenue, 1) OVER (ORDER BY order_date) AS diff,
  ROUND(
    (daily_revenue - LAG(daily_revenue, 1) OVER (ORDER BY order_date))
    * 100.0 / NULLIF(LAG(daily_revenue, 1) OVER (ORDER BY order_date), 0),
  2) AS pct_change
FROM daily_revenue
ORDER BY order_date;

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ 示例：LEAD 查看下月数据

SQL

SELECT
  month,
  revenue,
  LEAD(revenue, 1) OVER (ORDER BY month) AS next_month_revenue
FROM monthly_revenue;

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) FIRST_VALUE / LAST_VALUE 注意事项

LAST_VALUE 的默认帧是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，不是整个分区。需要显式指定帧才能获取分区最后一行。

函数	默认帧	正确获取分区末行写法
FIRST_VALUE	到 CURRENT ROW（恰好正确）	无需修改
LAST_VALUE	到 CURRENT ROW（不是末行！）	加 `ROWS BETWEEN ... AND UNBOUNDED FOLLOWING`

▶ 示例：FIRST_VALUE 与 LAST_VALUE

SQL

SELECT
  order_id,
  customer_id,
  amount,
  FIRST_VALUE(amount) OVER (
    PARTITION BY customer_id ORDER BY order_date
  ) AS first_order_amount,
  LAST_VALUE(amount) OVER (
    PARTITION BY customer_id ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_order_amount
FROM orders;

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ 示例：NTH_VALUE 取第2笔订单

SQL

SELECT
  order_id,
  customer_id,
  amount,
  NTH_VALUE(amount, 2) OVER (
    PARTITION BY customer_id ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS second_order_amount
FROM orders;

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

8. Concept：累计求和与移动平均

(1) Running Sum 累计求和

帧定义	含义	SQL
默认帧	从分区头到当前行	`SUM() OVER (ORDER BY col)`
显式 ROWS	同上	`ROWS UNBOUNDED PRECEDING`
显式 RANGE	同值行一起算	`RANGE UNBOUNDED PRECEDING`

▶ 示例：按月累计营收

SQL

SELECT
  month,
  revenue,
  SUM(revenue) OVER (ORDER BY month) AS running_revenue
FROM monthly_revenue;

TEXT 📖 仅展示

  month   | revenue | running_revenue
----------+---------+----------------
 2025-01  |  500000 |         500000
 2025-02  |  620000 |        1120000
 2025-03  |  580000 |        1700000
 2025-04  |  710000 |        2410000

(2) Moving Average 移动平均

▶ 示例：7日移动平均

SQL

SELECT
  order_date,
  daily_revenue,
  ROUND(AVG(daily_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ), 2) AS ma_7day
FROM daily_revenue;

TEXT 📖 仅展示

  result  
----------
   42.50
(1 row)

(3) 分区累计

▶ 示例：每个客户累计消费

SQL

SELECT
  order_id,
  customer_id,
  order_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS cumulative_spent
FROM orders
ORDER BY customer_id, order_date;

TEXT 📖 仅展示

  result  
----------
   42.50
(1 row)

9. 窗口函数 vs 聚合函数

维度	聚合函数	窗口函数
行数	折叠为一行	保持原始行数
语法	`SUM(col)`	`SUM(col) OVER(...)`
GROUP BY	必须	不用
同行计算	一种维度	可定义多个不同窗口
性能	通常更快	需排序分区，稍慢
适用场景	汇总报表	排名、偏移、累计

▶ 示例：等价写法对比

聚合函数写法：

SQL

SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id;

TEXT 📖 仅展示

  result  
----------
   42.50
(1 row)

窗口函数写法（保留原始行）：

SQL

SELECT
  order_id,
  customer_id,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders;

10. Comprehensive Example

Charlie 的用户留存分析——统计每日新增用户及其 Day 7 / Day 30 回访率：

SQL

WITH first_login AS (
  SELECT
    user_id,
    MIN(login_date) AS first_date,
    ROW_NUMBER() OVER (PARTITION BY MIN(login_date) ORDER BY user_id) AS reg_rank
  FROM user_logins
  GROUP BY user_id
),
daily_new_users AS (
  SELECT
    first_date AS cohort_date,
    COUNT(*) AS new_users
  FROM first_login
  GROUP BY first_date
),
retention_base AS (
  SELECT
    fl.first_date AS cohort_date,
    fl.user_id,
    ul.login_date,
    ul.login_date - fl.first_date AS day_offset
  FROM first_login fl
  JOIN user_logins ul ON fl.user_id = ul.user_id
),
retention_count AS (
  SELECT
    cohort_date,
    day_offset,
    COUNT(DISTINCT user_id) AS retained_users
  FROM retention_base
  WHERE day_offset IN (0, 7, 30)
  GROUP BY cohort_date, day_offset
)
SELECT
  r.cohort_date,
  n.new_users,
  MAX(CASE WHEN r.day_offset = 0  THEN r.retained_users END) AS d0,
  MAX(CASE WHEN r.day_offset = 7  THEN r.retained_users END) AS d7,
  MAX(CASE WHEN r.day_offset = 30 THEN r.retained_users END) AS d30,
  ROUND(
    MAX(CASE WHEN r.day_offset = 7  THEN r.retained_users END) * 100.0
    / NULLIF(n.new_users, 0), 1
  ) AS day7_rate,
  ROUND(
    MAX(CASE WHEN r.day_offset = 30 THEN r.retained_users END) * 100.0
    / NULLIF(n.new_users, 0), 1
  ) AS day30_rate
FROM retention_count r
JOIN daily_new_users n ON r.cohort_date = n.cohort_date
GROUP BY r.cohort_date, n.new_users
ORDER BY r.cohort_date;

11. 执行流程

窗口函数在 SQL 执行顺序中的位置：

flowchart TD
    A[FROM] --> B[WHERE]
    B --> C[GROUP BY]
    C --> D[HAVING]
    D --> E["Window Functions<br/>OVER / PARTITION / ORDER / FRAME"]
    E --> F[SELECT]
    F --> G[DISTINCT]
    G --> H[ORDER BY]
    H --> I[LIMIT]

    style E fill:#c8e6c9
    style D fill:#fff9c4

步骤	子句	说明
1	FROM	确定数据源
2	WHERE	过滤行
3	GROUP BY	聚合分组
4	HAVING	过滤组
5	窗口函数	在过滤后的结果上计算
6	SELECT	选择输出列
7	DISTINCT	去重
8	ORDER BY	最终排序
9	LIMIT	限制行数

❓ 常见问题

Q 窗口函数可以出现在 WHERE 子句中吗？

A 不能。窗口函数在 WHERE 之后执行，WHERE 中无法引用窗口结果。需用子查询或 CTE 包裹后过滤。

Q ROW_NUMBER 和 RANK 有什么区别？

A ROW_NUMBER 严格递增（1,2,3,4），RANK 对同值赋予相同排名并跳号（1,1,3,4）。去重取 Top-1 用 ROW_NUMBER。

Q 为什么 LAST_VALUE 返回的不是分区最后一行？

A 默认帧是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。需显式写 ROWS BETWEEN ... AND UNBOUNDED FOLLOWING。

Q 多个窗口函数可以共用一个 OVER 子句吗？

A PostgreSQL 支持 WINDOW 子句别名：WINDOW w AS (PARTITION BY ...)，然后在多个函数中写 OVER w。

Q ROWS 和 RANGE 帧有什么区别？

A ROWS 按物理行偏移，RANGE 按逻辑值偏移。RANGE 会把 ORDER BY 同值的行视为同一个边界。大多数累计场景用 ROWS。

Q 窗口函数性能如何优化？

A 确保 PARTITION BY + ORDER BY 列上有索引；减少分区数量；避免大分区上的复杂帧计算；用 CTE 先过滤再计算窗口。

📖 小节

窗口函数不折叠行，每行返回计算结果，OVER 子句定义窗口
PARTITION BY 分区，ORDER BY 排序，帧定义控制计算范围
ROW_NUMBER / RANK / DENSE_RANK 三种排名行为不同，选择需注意
LAG / LEAD 访问前后行，FIRST_VALUE / LAST_VALUE 需注意默认帧
ROWS 按物理行偏移，RANGE 按逻辑值偏移，GROUPS 按分组偏移
累计求和与移动平均是窗口函数的典型应用
窗口函数在 WHERE/GROUP BY 之后执行，不能在 WHERE 中使用
WINDOW 子句可复用 OVER 定义，减少重复代码

📝 作业

⭐ 使用 ROW_NUMBER 查询每个客户金额最高的 2 笔订单
⭐ 使用 LAG 计算每个客户相邻两笔订单的金额差
⭐⭐ 计算每个 region 的日营收 7 日移动平均
⭐⭐ 使用 DENSE_RANK 和 NTILE(4) 将客户按消费总额分4个等级
⭐⭐⭐ 写一条 SQL 计算每日新增用户的 Day 1 / Day 7 / Day 30 回访率，要求用窗口函数替代自连接

Web-Tutorial 技术团队

由多位开发者共同维护的编程教程平台。每篇教程由对应领域的开发者编写和审核，确保内容准确可靠。如发现任何问题，欢迎向我们反馈。

关于我们

PostgreSQL: PostgreSQL窗口函数详解

1. 你将学到

2. 故事

3. Concept：窗口函数基础

(1) 什么是窗口函数

(2) OVER 子句结构

▶ 示例：最简窗口函数

4. Concept：PARTITION BY 与 ORDER BY

(1) PARTITION BY 分区

▶ 示例：按客户分区求和

(2) ORDER BY 排序

▶ 示例：按金额排名

(3) PARTITION BY + ORDER BY 组合

▶ 示例：每个客户的订单金额排名

5. Concept：帧定义（Frame Clause）

(1) 三种帧类型

(2) 帧边界关键字

(3) 默认帧规则

▶ 示例：ROWS 帧累计求和

▶ 示例：ROWS 3行移动平均

▶ 示例：RANGE 帧按日期区间

6. Concept：排名函数

(1) 四大排名函数对比

▶ 示例：ROW_NUMBER vs RANK vs DENSE_RANK

(2) NTILE 分组

▶ 示例：将客户按消费额分4组

7. Concept：偏移与值函数

(1) 偏移函数一览

▶ 示例：LAG 计算日环比

▶ 示例：LEAD 查看下月数据

(2) FIRST_VALUE / LAST_VALUE 注意事项

▶ 示例：FIRST_VALUE 与 LAST_VALUE

▶ 示例：NTH_VALUE 取第2笔订单

8. Concept：累计求和与移动平均

(1) Running Sum 累计求和

▶ 示例：按月累计营收

(2) Moving Average 移动平均

▶ 示例：7日移动平均

(3) 分区累计

▶ 示例：每个客户累计消费

9. 窗口函数 vs 聚合函数

▶ 示例：等价写法对比

10. Comprehensive Example

11. 执行流程

❓ 常见问题

📖 小节

📝 作业

🙏 帮我们做得更好