37、PostgreSQL高级查询：窗口函数、CTE、递归查询、JSONB操作

说实话，PostgreSQL 在高级查询这块，真的把很多商业数据库都比下去了。我最早接触它是在一个电商项目里，那时候 MySQL 处理复杂报表查询已经让我有点头疼了。后来换到 PostgreSQL，才发现原来 SQL 可以写得这么优雅。

今天咱们就聊聊四个硬核话题：窗口函数、CTE、递归查询、还有 JSONB 操作。这些功能，说白了就是让你用更少的代码，干更多的活。

窗口函数：不丢细节的聚合

普通聚合函数，比如 SUM、AVG，一用就把多行压成一行。窗口函数不一样，它能在保留每一行数据的同时，算出聚合值。

我举个例子。假设我们有个销售表：

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    product TEXT,
    amount NUMERIC,
    sale_date DATE
);

INSERT INTO sales VALUES
(1, 'A', 100, '2024-01-01'),
(2, 'A', 200, '2024-01-02'),
(3, 'B', 150, '2024-01-01'),
(4, 'B', 250, '2024-01-02'),
(5, 'A', 300, '2024-01-03');

现在我想看每个产品的累计销售额。用窗口函数：

SELECT 
    product,
    sale_date,
    amount,
    SUM(amount) OVER (PARTITION BY product ORDER BY sale_date) AS running_total
FROM sales;

结果会保留每一行，同时多出一列 running_total，显示到当前行为止的累计值。我在项目中做财务流水对账时，这个功能帮了大忙。你想想看，如果用程序去算累计，还得写循环，多麻烦。

常用的窗口函数还有 ROW_NUMBER()、RANK()、DENSE_RANK()、LAG()、LEAD()。比如找每个产品销售额最高的那天：

SELECT * FROM (
    SELECT 
        product,
        sale_date,
        amount,
        ROW_NUMBER() OVER (PARTITION BY product ORDER BY amount DESC) AS rn
    FROM sales
) t WHERE rn = 1;

CTE：让复杂查询变清晰

CTE 就是公用表表达式，用 WITH 关键字定义。我个人习惯用它来拆解多层嵌套的子查询。你想想看，三层子查询嵌套在一起，读起来多痛苦。CTE 可以把每一层拆成独立的步骤。

WITH high_sales AS (
    SELECT product, SUM(amount) AS total
    FROM sales
    GROUP BY product
    HAVING SUM(amount) > 300
)
SELECT * FROM high_sales;

这只是一个简单的例子。实际项目中，我经常把 CTE 链起来用：

WITH 
step1 AS (
    SELECT product, SUM(amount) AS total
    FROM sales
    GROUP BY product
),
step2 AS (
    SELECT product, total,
           RANK() OVER (ORDER BY total DESC) AS rank
    FROM step1
)
SELECT * FROM step2 WHERE rank <= 3;

嗯，这里要注意：CTE 本质上就是个临时视图，不会物化数据。但如果同一个 CTE 被多次引用，PostgreSQL 会把它当作一个优化屏障，有时候反而影响性能。我曾经踩过这个坑，后来改用临时表才解决。

递归查询：搞定树形结构

递归 CTE 是 CTE 的进阶用法。它由两部分组成：一个非递归的初始查询，和一个递归部分，用 UNION ALL 连接。

举个例子，组织架构表：

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT,
    manager_id INT REFERENCES employees(id)
);

INSERT INTO employees VALUES
(1, '张三', NULL),
(2, '李四', 1),
(3, '王五', 1),
(4, '赵六', 2),
(5, '钱七', 2);

我想查张三下面所有下属，包括间接下属：

WITH RECURSIVE org_tree AS (
    -- 非递归部分：找到根节点
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE name = '张三'
    
    UNION ALL
    
    -- 递归部分：找下属
    SELECT e.id, e.name, e.manager_id, t.level + 1
    FROM employees e
    JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree;

递归查询在 BOM 物料清单、评论回复树、菜单权限树这些场景下特别实用。我记得有一次做知识库系统，分类层级有 7 层深，用递归 CTE 一条 SQL 就搞定了。换成程序递归，得写几十行代码。

JSONB 操作：灵活的数据存储

PostgreSQL 的 JSONB 支持，让我在关系型数据库里也能玩转 NoSQL 风格的数据。JSONB 是二进制格式，比 JSON 类型更快，还支持索引。

创建一个带 JSONB 字段的表：

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    attributes JSONB
);

INSERT INTO products VALUES
(1, '手机', '{"color": "黑色", "storage": "128GB", "price": 2999}'),
(2, '手机', '{"color": "白色", "storage": "256GB", "price": 3499}'),
(3, '电脑', '{"cpu": "i7", "ram": "16GB", "price": 6999}');

查询 JSONB 字段里的值：

-- 获取所有手机的存储容量
SELECT name, attributes->>'storage' AS storage
FROM products
WHERE attributes @> '{"color": "黑色"}';

-- 价格大于 3000 的产品
SELECT * FROM products
WHERE (attributes->>'price')::NUMERIC > 3000;

JSONB 还支持更新部分字段：

-- 把黑色手机的价格改为 2799
UPDATE products
SET attributes = jsonb_set(attributes, '{price}', '2799')
WHERE attributes @> '{"color": "黑色"}';

我在一个电商项目中，用 JSONB 存储商品的动态属性。不同类别的商品属性差异很大，如果用传统的关系模型，得建几十张属性表。用 JSONB 一张表就搞定了，而且查询性能还不错——只要建了 GIN 索引。

知识体系总览

下面这张图，把今天讲的四个知识点串起来了。你可以看到它们各自解决什么问题，以及在实际项目中如何配合使用。

这四个功能，单独用已经很强大，组合起来更是如虎添翼。比如在 CTE 里用窗口函数做排名，再结合 JSONB 字段过滤，一条 SQL 就能完成过去需要多步处理的任务。

好了，今天的内容就到这。这些技巧我在实际项目中反复用过，确实能提升开发效率。你可以在自己的 PostgreSQL 环境里试试，感受一下它们的威力。

---