PostgreSQL 高级查询实战：窗口函数、CTE 与 JSONB 的性能优化指南

某电商平台的运营团队要求「每个品类中销量 Top 10 的商品，以及它们在各自子类目中的排名」。开发者的第一个方案是写 3 层嵌套子查询，跑了 8 秒还没出结果——换成窗口函数后，同样的查询只用了 120ms。PostgreSQL 的窗口函数、CTE 递归查询和 JSONB 操作，是大多数开发者知道但从未真正掌握的三大高级特性。据 Stack Overflow 2025 开发者调查，PostgreSQL 连续第五年成为最受欢迎的数据库，但超过 60% 的开发者只用到了它 30% 的查询能力。

📌 **记住：**掌握这三大特性，你写的 SQL 就能从「能跑」进化到「跑得快、写得优雅、维护方便」。这不是炫技，而是实打实的生产力提升。

📊 一、窗口函数：告别子查询，优雅处理分组计算

窗口函数（Window Function）是 SQL 中最被低估的能力之一。它能在不折叠行的情况下，对每行数据附加聚合或排名信息——这意味着你不需要 GROUP BY 也能计算分组统计。

1.1 核心语法与常见函数

窗口函数的语法结构是 函数名() OVER (PARTITION BY 列 ORDER BY 列)，其中 PARTITION BY 定义分组，ORDER BY 定义排序。

-- 核心示例：计算每个部门员工的薪资排名和部门平均薪资
SELECT
    name,
    department,
    salary,
    -- 排名函数：相同值并列，后续名次跳跃
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    -- 密集排名：相同值并列，后续名次连续
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_dense_rank,
    -- 行号：严格递增，无并列
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_row_num,
    -- 聚合窗口：计算部门平均薪资（不折叠行）
    AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary,
    -- 计算与部门平均的差值
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees
ORDER BY department, dept_rank;

⚠️ 警告：RANK() 和 DENSE_RANK() 的区别经常被搞混。排名 (90, 85, 85, 80) 用 RANK() 得到 (1, 2, 2, 4)，用 DENSE_RANK() 得到 (1, 2, 2, 3)。面试中这是高频考点，生产环境中选错会导致数据统计偏差。

三个核心排名函数的对比：

1.2 高级窗口帧（Frame）实战

窗口帧让你精确控制计算范围——比如「最近 7 天的移动平均」或「累计求和」。

-- 滑动窗口实战：计算每日销售额、7日移动平均、累计销售额
SELECT
    sale_date,
    daily_amount,
    -- 7日移动平均（前6天 + 当天）
    AVG(daily_amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d,
    -- 累计求和（从第一天到当前行）
    SUM(daily_amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_sum,
    -- 与前一天对比的变化率
    (daily_amount - LAG(daily_amount, 1) OVER (ORDER BY sale_date))
        / NULLIF(LAG(daily_amount, 1) OVER (ORDER BY sale_date), 0) * 100
    AS day_over_day_pct
FROM daily_sales
ORDER BY sale_date;

💡 提示：ROWS BETWEEN 是物理行数，RANGE BETWEEN 是逻辑值范围。日期相关计算推荐用 RANGE，因为可能存在缺失日期。例如 RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW 会自动跳过缺失日期。

1.3 性能对比：窗口函数 vs 子查询

同一个业务需求，两种写法的性能差异惊人：

-- ❌ 低效写法：使用关联子查询获取每个员工的部门薪资排名
SELECT e.name, e.department, e.salary,
    (SELECT COUNT(*) + 1
     FROM employees e2
     WHERE e2.department = e.department
       AND e2.salary > e.salary
    ) AS dept_rank
FROM employees e
ORDER BY e.department, dept_rank;

-- ✅ 高效写法：使用窗口函数
SELECT name, department, salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees
ORDER BY department, dept_rank;

性能对比数据（10 万条员工记录）：

⚡ **关键结论：**窗口函数只扫描一次数据，而关联子查询对每行都要执行一次内部查询。当数据量超过 10 万行时，窗口函数的优势会指数级放大。

🔄 二、CTE 递归查询：优雅处理树形与图结构数据

CTE（Common Table Expression，公用表表达式）通过 WITH 关键字定义临时结果集。普通 CTE 只是子查询的语法糖，但递归 CTE 是处理层级数据的终极武器——组织架构树、评论嵌套、文件目录、物流路径图，都可以用它优雅表达。

2.1 递归 CTE 的工作原理

递归 CTE 由两部分组成：锚点成员（Anchor，起始查询）和递归成员（Recursive，引用自身）。执行过程是反复迭代，直到递归成员返回空集。

-- 实战：查询某员工的所有上级（沿管理链向上追溯）
WITH RECURSIVE manager_chain AS (
    -- 锚点成员：从指定员工开始
    SELECT id, name, manager_id, 1 AS depth,
           name::TEXT AS chain
    FROM employees
    WHERE id = 42  -- 起始员工

    UNION ALL

    -- 递归成员：向上查找上级
    SELECT e.id, e.name, e.manager_id, mc.depth + 1,
           mc.chain || ' → ' || e.name
    FROM employees e
    INNER JOIN manager_chain mc ON e.id = mc.manager_id
    WHERE mc.depth < 10  -- 防止无限递归
)
SELECT depth, chain
FROM manager_chain
ORDER BY depth;

结果示例：

⚠️ **警告：**递归 CTE 必须有终止条件，否则会无限循环直到内存耗尽。生产环境中务必设置 WHERE depth < N 或类似的行数限制。PostgreSQL 还提供了 SET max_recursive_iterations = 100 作为全局安全阀。

2.2 子树查询：获取部门及其所有下级

-- 实战：获取某个部门及其所有子部门（多级嵌套）
WITH RECURSIVE dept_tree AS (
    -- 锚点：指定部门
    SELECT id, name, parent_id, 0 AS level,
           ARRAY[id] AS path
    FROM departments
    WHERE id = 5  -- 从某个顶级部门开始

    UNION ALL

    -- 递归：查找直接子部门
    SELECT d.id, d.name, d.parent_id, dt.level + 1,
           dt.path || d.id
    FROM departments d
    INNER JOIN dept_tree dt ON d.parent_id = dt.id
    WHERE NOT d.id = ANY(dt.path)  -- 防止循环引用
)
SELECT
    REPEAT('  ', level) || name AS tree_view,
    level,
    path,
    array_to_path(path) AS full_path
FROM dept_tree
ORDER BY path;

💡 提示：WHERE NOT d.id = ANY(dt.path) 是防止循环引用的关键。如果数据来源不可靠（如用户输入），这行代码能防止数据库陷入死循环。

2.3 递归查询的性能优化

递归 CTE 在深层级数据上的性能需要特别关注。以下是三个核心优化手段：

优化一：添加索引

-- 递归查询的关联列必须有索引
CREATE INDEX idx_employees_manager_id ON employees (manager_id);
CREATE INDEX idx_departments_parent_id ON departments (parent_id);

优化二：限制递归深度

-- 始终设置合理的深度上限
WHERE depth < 20  -- 大多数组织架构不超过 20 层

优化三：用 SEARCH 子句控制遍历顺序

-- 深度优先遍历（默认）—— 适合菜单渲染
WITH RECURSIVE tree AS (...)
SEARCH DEPTH FIRST BY id SET ordercol

-- 广度优先遍历 —— 适合层级统计
WITH RECURSIVE tree AS (...)
SEARCH BREADTH FIRST BY id SET ordercol

性能数据（10 万节点的部门树，深度 8 层）：

📦 三、JSONB 操作：在关系型数据库中玩转文档查询

PostgreSQL 的 JSONB（Binary JSON）类型不是简单的文本存储——它是二进制格式，支持 GIN 索引，查询性能接近原生列。这意味着你可以在不引入 MongoDB 的前提下，获得文档数据库的灵活性。

3.1 JSONB 基础操作与路径查询

-- 创建含 JSONB 列的表（存储用户的动态配置）
CREATE TABLE user_configs (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    config JSONB NOT NULL DEFAULT '{}',
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 插入嵌套 JSON 数据
INSERT INTO user_configs (user_id, config) VALUES
(1, '{
    "theme": {"mode": "dark", "font_size": 14},
    "notifications": {"email": true, "sms": false, "push": true},
    "preferences": {"language": "zh-CN", "timezone": "Asia/Shanghai"}
}'),
(2, '{
    "theme": {"mode": "light", "font_size": 16},
    "notifications": {"email": false, "sms": true, "push": false},
    "preferences": {"language": "en-US", "timezone": "America/New_York"}
}');

-- 路径查询：使用 -> 获取 JSON 对象，->> 获取文本值
SELECT
    user_id,
    config -> 'theme' ->> 'mode' AS theme_mode,
    config -> 'theme' -> 'font_size' AS font_size_json,   -- 返回 JSONB
    config ->> 'theme' AS theme_text,                       -- 返回 TEXT
    config #> '{notifications,email}' AS email_jsonb,       -- 多级路径
    config #>> '{preferences,language}' AS lang             -- 多级路径（文本）
FROM user_configs;

-- 条件查询：筛选深嵌套字段
SELECT user_id
FROM user_configs
WHERE config -> 'notifications' ->> 'email' = 'true'
  AND config -> 'theme' ->> 'mode' = 'dark';

⚠️ 警告：-> 返回 JSONB 类型，->> 返回 TEXT 类型。做条件比较时务必用 ->>，否则会因为类型不匹配导致索引失效。这是 JSONB 查询最常见的性能陷阱。

3.2 JSONB 高级查询：数组、存在性与聚合

-- 包含数组的 JSONB 查询
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    attrs JSONB NOT NULL
);

INSERT INTO products (name, attrs) VALUES
('iPhone 16 Pro', '{
    "brand": "Apple",
    "colors": ["钛原色", "蓝钛色", "白钛色", "黑钛色"],
    "specs": {"storage": [128, 256, 512, 1024], "weight": 199},
    "tags": ["新品", "旗舰", "拍照"]
}'),
('Galaxy S26', '{
    "brand": "Samsung",
    "colors": ["幻影黑", "幻影银", "幻影紫"],
    "specs": {"storage": [256, 512], "weight": 168},
    "tags": ["新品", "旗舰"]
}');

-- @> 包含操作符：检查 JSON 是否包含指定结构
SELECT name FROM products WHERE attrs @> '{"brand": "Apple"}';
SELECT name FROM products WHERE attrs -> 'tags' ? '拍照';
SELECT name FROM products WHERE attrs -> 'colors' ?| ARRAY['幻影黑', '幻影银'];

-- @? JSONPath 查询（PostgreSQL 12+）
SELECT name FROM products
WHERE attrs @? '$.specs.storage[*] ? (@ > 500)';  -- 有超过 500GB 存储的版本

-- JSONB 聚合：统计所有标签的出现次数
SELECT tag, COUNT(*) AS product_count
FROM products, jsonb_array_elements_text(attrs -> 'tags') AS tag
GROUP BY tag
ORDER BY product_count DESC;

JSONB 操作符速查表：

3.3 JSONB 索引与性能优化

JSONB 查询的性能关键在于索引选择。不同查询模式需要不同的索引类型：

-- 方式一：GIN 索引（最通用，支持 @>, ?, ?|, ?& 操作符）
CREATE INDEX idx_config_gin ON user_configs USING GIN (config);
-- 默认使用 jsonb_ops，适用于大多数场景

-- 方式二：GIN 索引（jsonb_path_ops，更小更快，但只支持 @>）
CREATE INDEX idx_config_path_ops ON user_configs USING GIN (config jsonb_path_ops);
-- 适用于频繁使用 @> 包含查询的场景

-- 方式三：表达式索引（精确命中特定路径）
CREATE INDEX idx_theme_mode ON user_configs ((config -> 'theme' ->> 'mode'));
-- 适用于频繁查询特定嵌套字段的场景

性能对比（100 万条用户配置记录）：

⚡ **关键结论：**如果你的查询模式固定（比如总是查 config ->> 'email'），用表达式索引，体积最小、速度最快。如果查询路径不固定，用 GIN 索引。两者都有？组合使用。jsonb_path_ops 比默认的 jsonb_ops 索引体积小约 60%，查询速度快 30%，但只支持 @> 操作符——大多数场景下够用了。

3.4 JSONB 更新操作

-- 局部更新：只修改嵌套对象中的某个字段（不用读出整个 JSON 再写回）
UPDATE user_configs
SET config = jsonb_set(
    config,
    '{theme,mode}',           -- 路径
    '"light"'::jsonb,         -- 新值
    false                     -- true=不存在时创建，false=忽略
)
WHERE user_id = 1;

-- 删除嵌套键
UPDATE user_configs
SET config = config #- '{notifications,sms}'
WHERE user_id = 1;

-- 追加数组元素
UPDATE user_configs
SET config = jsonb_set(
    config,
    '{notifications,channels}',
    (config -> 'notifications' -> 'channels') || '"wechat"'::jsonb,
    true  -- 不存在时自动创建
)
WHERE user_id = 1;

-- 合并顶层对象（|| 操作符）
UPDATE user_configs
SET config = config || '{"new_feature": true}'::jsonb
WHERE user_id = 1;

💡 提示：jsonb_set 的第四个参数控制「路径不存在时是否创建」。设为 true 时相当于 upsert，非常实用。但要注意，如果路径中间的某个节点不是对象类型，会抛出错误。

🎯 四、综合实战：三种特性的联合应用

真正的威力在于将三种特性结合使用。以下是一个完整的电商场景示例：

-- 需求：找出每个品类中评分最高的 Top 3 商品，
-- 并统计其属性中的标签分布

WITH ranked_products AS (
    -- 用窗口函数计算品类内排名
    SELECT
        p.id,
        p.name,
        p.category,
        p.rating,
        p.attrs,
        ROW_NUMBER() OVER (
            PARTITION BY p.category
            ORDER BY p.rating DESC
        ) AS rank_in_category
    FROM products p
    WHERE p.status = 'active'
),
top_products AS (
    -- 筛选 Top 3
    SELECT * FROM ranked_products WHERE rank_in_category <= 3
),
category_tags AS (
    -- 从 Top 3 的 JSONB 属性中提取标签并聚合
    SELECT
        category,
        tag,
        COUNT(*) AS tag_count
    FROM top_products,
         jsonb_array_elements_text(attrs -> 'tags') AS tag
    GROUP BY category, tag
)
SELECT
    tp.category,
    tp.name,
    tp.rating,
    tp.attrs -> 'tags' AS tags,
    ct.tag AS popular_tag,
    ct.tag_count
FROM top_products tp
LEFT JOIN category_tags ct
    ON tp.category = ct.category
ORDER BY tp.category, tp.rank_in_category, ct.tag_count DESC;

⚡ **关键结论：**窗口函数负责排名筛选，CTE 负责逻辑分层，JSONB 负责文档数据提取——三者各司其职、配合默契。这种写法不仅性能好，而且代码可读性远超嵌套子查询的写法。

✅ 五、最佳实践与避坑指南

窗口函数注意事项：

• ✅ 窗口函数只能用在 SELECT 和 ORDER BY 子句中，不能用在 WHERE 或 GROUP BY 中
• ✅ 需要在 WHERE 之后过滤窗口函数结果？用 CTE 或子查询包一层
• ❌ 不要在小数据集上滥用窗口函数，简单的 GROUP BY + JOIN 可能更直观

CTE 递归查询注意事项：

• ✅ 必须设置递归深度上限，防止无限循环
• ✅ 关联列必须有索引，否则每次递归都是全表扫描
• ✅ 用 path 数组防止循环引用，尤其是处理用户生成的数据
• ❌ 不要用递归 CTE 做数值生成，generate_series() 更高效

JSONB 使用建议：

• ✅ 设计数据模型时，「变化的、稀疏的、嵌套的」字段用 JSONB，「固定的、高频查询的」字段用原生列
• ✅ 纯包含查询用 jsonb_path_ops 索引，体积更小速度更快
• ❌ 不要把所有字段都塞进 JSONB——失去了类型约束和外键关系，查询优化器也更难工作
• ❌ 不要用 JSONB 存储需要频繁 JOIN 的数据，关系型列 + 外键永远是首选

📝 总结

PostgreSQL 的窗口函数、CTE 递归查询和 JSONB 操作，分别解决了分组计算、层级遍历和半结构化数据三个经典难题。掌握它们的关键不在于记住语法，而在于理解什么时候该用什么：

相关工具推荐：

• 🔧 jsjson.com JSON 格式化工具 — 在线验证和格式化 JSON 数据
• 🔧 jsjson.com SQL 优化指南 — 了解更多 SQL 查询优化技巧
• 🔧 pgAdmin / DBeaver — PostgreSQL 可视化管理工具
• 🔧 EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) — 查询性能分析必备命令