PostgreSQL 高级查询 — 窗口函数、CTE、递归

这是本系列的最后一章,讲三个让 SQL "起飞"的高级特性:窗口函数(不压缩行做聚合)、公共表表达式(CTE)(让复杂查询可读)、递归查询(处理树形数据)。掌握它们,你能用一句 SQL 完成原本需要写应用代码的工作。

1. 窗口函数(Window Functions)

窗口函数是 SQL 最优雅的特性之一——它在不压缩行数的前提下做聚合 / 排序 / 跨行引用。聚合函数(SUM/COUNT)会把多行压成一行,窗口函数则保留所有行,只在每行上"附加"一个聚合结果。

-- === 窗口函数(Window Functions)==
-- 在不"压缩行"的前提下做聚合 / 排序 / 跨行引用
-- 语法:函数() OVER (PARTITION BY ... ORDER BY ...)

-- 准备数据
CREATE TABLE sales (
    id    SERIAL,
    rep   TEXT,         -- 销售员
    region TEXT,        -- 区域
    amount NUMERIC      -- 销售额
);
INSERT INTO sales (rep, region, amount) VALUES
    ('Alice', 'North', 100),
    ('Alice', 'North', 150),
    ('Bob',   'South', 200),
    ('Bob',   'South', 50),
    ('Carol', 'North', 300);

-- 1. ROW_NUMBER():给每行编号(可按分区)
SELECT
    rep, region, amount,
    ROW_NUMBER() OVER (ORDER BY amount DESC) AS global_rank,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS region_rank
FROM sales;
--  rep   | region | amount | global_rank | region_rank
-- -------+--------+--------+-------------+------------
--  Carol | North  | 300    | 1           | 1
--  Bob   | South  | 200    | 2           | 1
--  Alice | North  | 150    | 3           | 2
--  Alice | North  | 100    | 4           | 3
--  Bob   | South  | 50     | 5           | 2

-- 2. RANK() / DENSE_RANK():排名(并列时行为不同)
SELECT rep, amount,
    RANK()       OVER (ORDER BY amount DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY amount DESC) AS dense
FROM sales;
-- amount=100 和 amount=100 并列时:
--   RANK 会跳过下一个号(1, 2, 2, 4)
--   DENSE_RANK 不跳号(1, 2, 2, 3)

-- 3. LAG() / LEAD():引用前一行 / 后一行的值
SELECT
    rep, amount,
    LAG(amount, 1)        OVER (PARTITION BY rep ORDER BY id) AS prev_amount,
    LEAD(amount, 1)       OVER (PARTITION BY rep ORDER BY id) AS next_amount,
    amount - LAG(amount, 1) OVER (PARTITION BY rep ORDER BY id) AS diff
FROM sales;
-- 经典用途:算环比增长、比较前后订单

-- 4. FIRST_VALUE / LAST_VALUE / NTH_VALUE
SELECT DISTINCT region,
    FIRST_VALUE(rep) OVER (PARTITION BY region ORDER BY amount DESC) AS top_rep
FROM sales;
-- 每个区域销售额最高的销售员

-- 5. 累计聚合(SUM / AVG / COUNT 配合 OVER)
SELECT
    rep, amount,
    SUM(amount) OVER (PARTITION BY rep ORDER BY id) AS running_total,
    AVG(amount) OVER (PARTITION BY rep)            AS rep_avg
FROM sales;
-- running_total 是按 rep 分组的累计求和

常用窗口函数分两类:

经典应用:排行榜、Top N per group、环比增长、移动平均。

2. 窗口函数的"帧"(Frame)

帧(Frame)控制"当前行参与计算的范围"。理解帧,才能精确控制窗口聚合的行为。

-- === 窗口函数的"帧"(Frame)==
-- 帧:窗口内当前行参与计算的范围
-- 默认:RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

-- 显式指定帧
SELECT
    rep, id, amount,
    -- 帧为"从开始到当前行"(默认行为,累计)
    SUM(amount) OVER (
        PARTITION BY rep ORDER BY id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative,
    -- 帧为"当前行和前一行"(滑动 3 行平均)
    AVG(amount) OVER (
        PARTITION BY rep ORDER BY id
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS moving_avg_3,
    -- 帧为"整个分区"
    SUM(amount) OVER (PARTITION BY rep) AS total_for_rep
FROM sales;

-- 帧的几种写法:
--   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW   (从开头到当前行)
--   ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING           (前后各 2 行)
--   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  (整个分区)
--   RANGE BETWEEN INTERVAL '1 day' PRECEDING AND CURRENT ROW  (按值范围,适合时间)

三种最常用的帧:

3. 公共表表达式(CTE / WITH)

CTE 让你把一个子查询"命名"后供主查询使用。它最大的价值是可读性——把嵌套多层的子查询拆成线性步骤,像写故事一样逐步推导。

-- === 公共表表达式(Common Table Expression, CTE)==
-- WITH 子句定义"临时视图",让复杂查询更可读、可复用

-- 简单 CTE:把一个查询"命名"后供主查询使用
WITH active_users AS (
    SELECT id, name, email FROM users WHERE deleted_at IS NULL
),
vip_users AS (
    SELECT * FROM active_users WHERE role = 'vip'
)
SELECT * FROM vip_users ORDER BY name;

-- 配合聚合:先算每个用户的订单总额,再筛 VIP
WITH user_totals AS (
    SELECT
        user_id,
        SUM(total) AS grand_total,
        COUNT(*)   AS order_count
    FROM orders
    GROUP BY user_id
)
SELECT
    u.name,
    ut.grand_total,
    ut.order_count
FROM users u
JOIN user_totals ut ON u.id = ut.user_id
WHERE ut.grand_total > 1000
ORDER BY ut.grand_total DESC;

-- 配合数据操作:用 RETURNING + CTE 一次性完成"删除并归档"
WITH deleted AS (
    DELETE FROM users WHERE deleted_at IS NOT NULL
    RETURNING id, name, email
)
INSERT INTO archived_users (id, name, email)
SELECT id, name, email FROM deleted;

-- MATERIALIZED 关键字:物化 CTE(只算一次,多次引用时提速)
WITH heavy AS MATERIALIZED (
    SELECT user_id, complex_calc(...) FROM big_table
)
SELECT * FROM heavy WHERE user_id = 1
UNION ALL
SELECT * FROM heavy WHERE user_id = 2;

CTE 的两个常用场景:

Postgres 12+ 引入了 MATERIALIZED 关键字:CTE 默认是内联的(优化器决定如何执行),用 MATERIALIZED 强制物化(只算一次,适合多次引用的复杂 CTE)。

4. 递归 CTE(处理树形数据)

递归 CTE 是 Postgres 处理层级数据的杀器。组织架构、产品分类、评论回复链、文件目录——这些"自引用"的树形数据,一句递归 CTE 就能查出整棵子树。

-- === 递归 CTE(处理树形 / 图形数据)===
-- 经典场景:组织架构(员工-经理)、产品分类(父子类)、评论的回复链

-- 准备数据:员工表,有 manager_id 自引用
CREATE TABLE employees (
    id         SERIAL PRIMARY KEY,
    name       TEXT,
    manager_id INT REFERENCES employees(id)
);
INSERT INTO employees (name, manager_id) VALUES
    ('CEO', NULL),       -- id=1
    ('VP_Eng', 1),       -- id=2
    ('VP_Sales', 1),     -- id=3
    ('Dev_Lead', 2),     -- id=4
    ('Sales_Lead', 3),   -- id=5
    ('Dev_A', 4),        -- id=6
    ('Dev_B', 4);        -- id=7

-- 递归 CTE:找出某员工的所有下属(多层)
WITH RECURSIVE org_chain AS (
    -- 基础查询:从 CEO 开始
    SELECT id, name, manager_id, 0 AS depth
    FROM employees
    WHERE name = 'CEO'

    UNION ALL

    -- 递归查询:逐层向下找
    SELECT e.id, e.name, e.manager_id, oc.depth + 1
    FROM employees e
    JOIN org_chain oc ON e.manager_id = oc.id
)
SELECT id, name, depth FROM org_chain ORDER BY depth, id;
--  id |    name     | depth
-- ----+-------------+-------
--   1 | CEO         | 0
--   2 | VP_Eng      | 1
--   3 | VP_Sales    | 1
--   4 | Dev_Lead    | 2
--   5 | Sales_Lead  | 2
--   6 | Dev_A       | 3
--   7 | Dev_B       | 3

-- 经典应用:
--   1. 评论的回复树(找出某条评论下的所有回复)
--   2. 文件路径解析(从根目录递归到当前文件夹)
--   3. 朋友的朋友(N 度关系,社交图查询)
--   4. 财务科目的层级汇总

-- 注意:递归必须有终止条件(基础查询),否则会无限循环
-- PostgreSQL 默认有 max_recursion_depth 限制

递归 CTE 的结构:

务必加终止条件(基础查询的 WHERE),否则会无限递归。Postgres 默认有递归深度限制,但更好的做法是确保查询逻辑一定会收敛。

5. LATERAL JOIN

LATERAL 是 Postgres 的特色语法,让 JOIN 右边的子查询能引用左边表的列。最经典的场景是"每组取 Top N"——传统写法需要 ROW_NUMBER,有了 LATERAL 一句话搞定。

-- === LATERAL JOIN(Postgres 特色)==
-- 让 JOIN 右边的子查询能引用左边表的列
-- 类似于"for each row in left, run subquery"

-- 场景:每个用户取最近 3 条订单
SELECT u.name, recent_orders.*
FROM users u
CROSS JOIN LATERAL (
    SELECT id, total, created_at
    FROM orders
    WHERE orders.user_id = u.id
    ORDER BY created_at DESC
    LIMIT 3
) AS recent_orders;

-- 等价的窗口函数写法(性能可能不同)
SELECT name, id, total, created_at FROM (
    SELECT
        u.name,
        o.id, o.total, o.created_at,
        ROW_NUMBER() OVER (PARTITION BY u.id ORDER BY o.created_at DESC) AS rn
    FROM users u
    JOIN orders o ON u.id = o.user_id
) t
WHERE rn <= 3;

-- LATERAL 的优势:
--   1. 比 ROW_NUMBER 更直观
--   2. 在某些场景下优化器能给出更好的执行计划
--   3. 子查询可以是非常复杂的逻辑(函数调用、聚合等)

-- 注意:CROSS JOIN LATERAL 会过滤掉没有订单的用户
--       LEFT JOIN LATERAL ... ON true 才会保留所有左边行

LATERAL vs 窗口函数:

6. 实战:综合查询示例

把窗口函数 + CTE + 递归组合起来,可以解决非常复杂的分析问题。比如"计算每个用户每月的订单总额,以及环比增长率,只显示增长超过 20% 的月份":

WITH monthly_totals AS (
    SELECT
        user_id,
        date_trunc('month', created_at) AS month,
        SUM(total) AS total
    FROM orders
    WHERE created_at > now() - interval '1 year'
    GROUP BY user_id, month
),
with_growth AS (
    SELECT
        user_id, month, total,
        LAG(total) OVER (PARTITION BY user_id ORDER BY month) AS prev_total
    FROM monthly_totals
)
SELECT
    user_id,
    month,
    total,
    round((total - prev_total) / prev_total * 100, 2) AS growth_pct
FROM with_growth
WHERE prev_total IS NOT NULL
  AND prev_total > 0
  AND (total - prev_total) / prev_total > 0.20
ORDER BY growth_pct DESC;

这种"漏斗式"分析(过滤 → 聚合 → 计算 → 再过滤)在数据看板里极其常见。CTE 让它一气呵成、易读易改。

7. 其他高级特性速览

小结 — 系列结语

恭喜!本系列 13 篇到这里就全部结束了。从最基础的安装、SQL 语法,到 JSONB、索引、事务、高级查询——你现在应该具备了在真实项目中独立使用 PostgreSQL 的能力。

下一步建议:

掌握 PostgreSQL,你不仅能写出更可靠的后端代码,还能理解数据库到底在替你做什么——这是工程师从"会用"到"精通"的分水岭。

← 上一篇 PostgreSQL 事务与 MVCC

← 返回 PostgreSQL 教程目录

✈️💬