PostgreSQL数据库技术 第 2 篇：SQL高级查询与数据操作

本篇属于「PostgreSQL数据库技术」学习系列，建议先阅读前序文章以获得最佳学习效果。

全文摘要

本文将带你深入掌握SQL的高级查询技术，帮助你处理复杂的数据分析需求。你将学到多表连接的各种类型与应用场景、子查询的嵌套使用技巧、聚合函数与分组的强大功能、窗口函数的排名与数据分析能力、公用表表达式CTE的优雅写法、视图的安全封装，以及事务处理的深入理解。通过阅读本文，你将能够编写复杂的业务查询，优化查询性能，处理并发访问问题。

学习目标

阅读完本文后，你将能够：

• 多表查询：熟练使用INNER/LEFT/RIGHT/FULL JOIN处理关联数据
• 嵌套查询：理解子查询的执行逻辑，优化子查询性能
• 数据分析：使用聚合函数和GROUP BY进行数据统计
• 高级分析：使用窗口函数解决排名、累积求值等复杂问题
• 代码优化：使用CTE简化复杂查询，提高代码可读性
• 数据封装：创建视图简化查询，提供安全的数据访问接口
• 并发控制：理解事务隔离级别，避免常见的并发问题

---

一、多表连接：关联查询的核心

在实际应用中，数据通常分布在多个表中。多表连接（JOIN）是SQL最强大的功能之一，它允许我们从多个相关表中组合数据。

连接的基本概念

连接操作的目的是基于两个表之间的相关字段，将它们的数据组合在一起。这个相关字段通常是外键关系，但也可以是任何有逻辑关联的字段。

假设场景：我们有一个简单的电商数据库模型：

• users表：用户信息（id, username, email）
• orders表：订单信息（id, user_id, order_date, total）
• order_items表：订单明细（id, order_id, product_id, quantity, price）
• products表：产品信息（id, name, category, price）

INNER JOIN：内连接

内连接是最常用的连接类型，只返回两个表中匹配的行。

-- 基本语法
SELECT users.username, orders.id, orders.order_date, orders.total
FROM users
INNER JOIN orders ON users.id = orders.user_id;
 
-- 简写（省略INNER）
SELECT users.username, orders.id, orders.order_date, orders.total
FROM users
JOIN orders ON users.id = orders.user_id;
 
-- 使用表别名简化查询
SELECT u.username, o.id, o.order_date, o.total
FROM users u
JOIN orders o ON u.id = o.user_id;
 
-- 多表连接：查询订单的详细信息
SELECT
    u.username,
    o.id AS order_id,
    o.order_date,
    oi.quantity,
    p.name AS product_name,
    oi.price
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

LEFT JOIN：左外连接

左外连接返回左表的所有行，即使右表没有匹配的行。右表没有匹配的行时，右表的列显示为NULL。

-- 查询所有用户及其订单（包括没有订单的用户）
SELECT
    u.username,
    u.email,
    o.id AS order_id,
    o.order_date,
    o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
 
-- 找出没有订单的用户
SELECT u.username, u.email
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;
 
-- 统计每个用户的订单数量（包括没有订单的用户）
SELECT
    u.username,
    COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

RIGHT JOIN：右外连接

右外连接与左外连接相反，返回右表的所有行。实际应用中较少使用，因为可以用LEFT JOIN通过交换表位置实现。

-- 右连接示例（不推荐，通常改用左连接）
SELECT u.username, o.id, o.order_date
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;
 
-- 改为左连接（推荐）
SELECT u.username, o.id, o.order_date
FROM orders o
LEFT JOIN users u ON u.id = o.user_id;

FULL JOIN：全外连接

全外连接返回两个表的所有行，无论是否匹配。没有匹配的行在对方的列显示为NULL。

-- 全连接示例
SELECT u.username, o.id, o.order_date
FROM users u
FULL JOIN orders o ON u.id = o.user_id;

flowchart TB
    subgraph Joins[四种连接类型对比]
        direction LR

        subgraph Inner[INNER JOIN 内连接]
            direction TB
            A[表A匹配行] --> R[结果集<br/>只返回匹配的行]
            B[表B匹配行] --> R
        end

        subgraph Left[LEFT JOIN 左连接]
            direction TB
            A2[表A所有行] --> R2[结果集<br/>包含表A所有行<br/>不匹配时NULL]
            B2[表B匹配行] --> R2
        end

        subgraph Right[RIGHT JOIN 右连接]
            direction TB
            A3[表A匹配行] --> R3[结果集<br/>包含表B所有行<br/>不匹配时NULL]
            B3[表B所有行] --> R3
        end

        subgraph Full[FULL JOIN 全连接]
            direction TB
            A4[表A所有行] --> R4[结果集<br/>包含两表所有行<br/>不匹配时NULL]
            B4[表B所有行] --> R4
        end
    end

    style Inner fill:#c8e6c9
    style Left fill:#fff9c4
    style Right fill:#ffcdd2
    style Full fill:#e3f2fd

图表讲解：这张图直观地比较了四种连接类型的差异——这是选择正确连接类型的参考指南。

INNER JOIN是最严格的连接类型，只返回两个表中都有匹配的行。如果我们只想查询有订单的用户，使用INNER JOIN是合适的。INNER JOIN是默认的连接类型，在大多数情况下使用。

LEFT JOIN返回左表的所有行，即使右表没有匹配。这在需要”即使关联数据不存在也要显示主记录”的场景下非常有用。例如，显示所有用户及其订单（包括没有订单的用户），使用LEFT JOIN。没有订单的用户在订单相关列显示NULL。

RIGHT JOIN与LEFT JOIN相反，返回右表的所有行。实际应用中很少使用，因为可以通过交换表位置用LEFT JOIN实现。图示用红色标记是为了提醒这种不对称性，实际编码时应优先使用LEFT JOIN。

FULL JOIN返回两个表的所有行，无论是否匹配。这在我们需要完整看到两表数据的场景下有用。例如，找出哪些用户没有订单、哪些订单没有对应用户（数据异常）。FULL JOIN返回的数据量最大，在数据处理和数据质量检查中很有用。

自连接

自连接是将表与自身连接，用于比较同一表中的行。需要使用表别名区分同一表的两个”实例”。

-- 创建员工表示例
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id),
    department TEXT
);
 
-- 插入示例数据
INSERT INTO employees (name, manager_id, department) VALUES
    ('张三', NULL, 'IT部'),
    ('李四', 1, 'IT部'),
    ('王五', 1, 'IT部'),
    ('赵六', 2, '销售部'),
    ('孙七', 2, '销售部');
 
-- 自连接：查询员工及其经理
SELECT
    e.name AS employee_name,
    e.department,
    m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
 
-- 自连接：查找同部门的同事
SELECT
    e1.name AS employee1,
    e2.name AS employee2,
    e1.department
FROM employees e1
JOIN employees e2 ON e1.department = e2.department AND e1.id < e2.id;

交叉连接与条件连接

交叉连接（CROSS JOIN）：返回两个表的笛卡尔积（每一行与每一行的组合）。如果没有WHERE条件，结果集大小是两表行数的乘积。

-- 交叉连接示例（慎用，结果可能非常大）
SELECT u.username, p.name
FROM users u
CROSS JOIN products p;
 
-- 带条件的交叉连接（等同于INNER JOIN）
SELECT u.username, p.name
FROM users u
CROSS JOIN products p
WHERE u.id = p.user_id;

USING子句：当连接的两个表有相同名称的列时，可以使用USING简化连接条件。

-- 使用ON
SELECT orders.id, users.username
FROM orders
JOIN users ON orders.user_id = users.id;
 
-- 使用USING（当两表都有user_id时）
SELECT orders.id, username
FROM orders
JOIN users USING (user_id);

---

二、子查询：嵌套查询的艺术

子查询是嵌套在其他查询中的查询。子查询可以出现在SELECT、FROM、WHERE、HAVING子句中，为复杂查询提供了强大的表达能力。

WHERE子句中的子查询

标量子查询：返回单个值的子查询，通常与比较运算符配合使用。

-- 查找价格高于平均价格的产品
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);
 
-- 查找与"张三"同部门的所有员工
SELECT name, department
FROM employees
WHERE department = (SELECT department FROM employees WHERE name = '张三');

IN子查询：检查值是否在子查询返回的列表中。

-- 查询有订单的用户
SELECT username, email
FROM users
WHERE id IN (SELECT DISTINCT user_id FROM orders);
 
-- 等价于JOIN方式（通常性能更好）
SELECT DISTINCT u.username, u.email
FROM users u
JOIN orders o ON u.id = o.user_id;

EXISTS子查询：检查子查询是否返回任何行，只关心”存在”而非”具体值”。

-- 查询有订单的用户
SELECT username, email
FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
 
-- 查询没有订单的用户
SELECT username, email
FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

EXISTS通常比IN更高效，因为它找到第一个匹配就停止。IN需要扫描整个列表。当子查询结果集很大时，优先考虑EXISTS。

FROM子句中的子查询

FROM子句中的子查询创建临时结果集，可以像表一样使用。这种子查询被称为”派生表”。

-- 查找每个用户的最新订单
SELECT recent_orders.username, recent_orders.order_date, recent_orders.total
FROM (
    SELECT
        u.username,
        o.id,
        o.order_date,
        o.total,
        ROW_NUMBER() OVER (PARTITION BY u.id ORDER BY o.order_date DESC) AS rn
    FROM users u
    JOIN orders o ON u.id = o.user_id
) recent_orders
WHERE recent_orders.rn = 1;

相关子查询

相关子查询引用外部查询的列，为外部查询的每一行执行一次。相关子查询通常性能较差，应谨慎使用。

-- 查找价格高于同类产品平均价格的产品
SELECT p1.name, p1.category, p1.price
FROM products p1
WHERE p1.price > (
    SELECT AVG(p2.price)
    FROM products p2
    WHERE p2.category = p1.category
);
 
-- 用JOIN改写（性能更好）
SELECT p1.name, p1.category, p1.price
FROM products p1
JOIN (
    SELECT category, AVG(price) AS avg_price
    FROM products
    GROUP BY category
) p2 ON p1.category = p2.category
WHERE p1.price > p2.avg_price;

ANY、ALL操作符

ANY和ALL与子查询配合使用，提供灵活的比较语义。

-- ANY：满足任一条件即可
SELECT name, price
FROM products
WHERE price > ANY (SELECT price FROM products WHERE category = 'Electronics');
 
-- 等价于
SELECT name, price
FROM products
WHERE price > (SELECT MIN(price) FROM products WHERE category = 'Electronics');
 
-- ALL：满足所有条件
SELECT name, price
FROM products
WHERE price > ALL (SELECT price FROM products WHERE category = 'Electronics');
 
-- 等价于
SELECT name, price
FROM products
WHERE price > (SELECT MAX(price) FROM products WHERE category = 'Electronics');

sequenceDiagram
    participant Client as 客户端
    participant DB as 数据库引擎
    participant Outer as 外层查询
    participant Inner as 内层查询

    Note over Client,Inner: 相关子查询的执行过程

    Client->>DB: 执行查询（包含相关子查询）

    Outer->>Outer: 从users表取第一行<br/>user_id=1
    Outer->>Inner: 执行子查询<br/>WHERE user_id=1
    Inner-->>Outer: 返回结果：存在
    Outer->>Outer: 判断条件满足<br/>保留该行

    Outer->>Outer: 从users表取第二行<br/>user_id=2
    Outer->>Inner: 执行子查询<br/>WHERE user_id=2
    Inner-->>Outer: 返回结果：不存在
    Outer->>Outer: 判断条件不满足<br/>丢弃该行

    Outer->>Inner: 继续处理下一行...

    Note over Client,Inner: 子查询为外部查询的每一行执行一次

图表讲解：这张时序图展示了相关子查询的执行过程——这是理解子查询性能的关键。

相关子查询的特殊之处在于它引用了外部查询的列（如user_id）。这意味着子查询不能只执行一次，而是为外部查询的每一行执行一次。从性能角度看，这类似于嵌套循环，时间复杂度是O(n*m)，其中n是外部查询的行数，m是子查询的行数。

执行过程如下：数据库引擎从users表读取第一行，将user_id=1传递给子查询；子查询执行，检查orders表中是否有user_id=1的记录；子查询返回结果（存在或不存在）；外层查询根据子查询结果判断是否保留这一行。

这个过程为外部查询的每一行重复。如果users表有1000行，子查询执行1000次。这就是为什么相关子查询可能性能很差。

优化方法包括：使用JOIN替代相关子查询（如果可能）；使用EXISTS替代IN（当子查询结果集大时）；在子查询中建立适当的索引；改用窗口函数（如COUNT() OVER ()）。

---

三、聚合函数与分组统计

聚合函数对一组值进行计算，返回单个值。结合GROUP BY子句，可以按照分组进行统计分析，这是SQL数据分析的核心功能。

常用聚合函数

-- COUNT：计数
SELECT COUNT(*) AS total_rows FROM orders;           -- 所有行，包括NULL
SELECT COUNT(order_date) AS not_null_dates FROM orders;  -- 非NULL值
SELECT COUNT(DISTINCT user_id) AS unique_users FROM orders;  -- 唯一值计数
 
-- SUM：求和
SELECT SUM(total) AS total_sales FROM orders;
SELECT SUM(quantity) AS total_items FROM order_items;
 
-- AVG：平均值
SELECT AVG(price) AS avg_price FROM products;
SELECT AVG(total) AS avg_order_value FROM orders;
 
-- MIN/MAX：最小值/最大值
SELECT MIN(order_date) AS first_order, MAX(order_date) AS last_order
FROM orders;
SELECT MIN(price) AS min_price, MAX(price) AS max_price
FROM products;

GROUP BY子句

GROUP BY将结果集按照一个或多个列分组，聚合函数对每个组返回一个值。

-- 按单个列分组：统计每个用户的订单数
SELECT
    user_id,
    COUNT(*) AS order_count,
    SUM(total) AS total_spent
FROM orders
GROUP BY user_id
ORDER BY total_spent DESC;
 
-- 按多个列分组：按部门和职位统计员工数
SELECT
    department,
    position,
    COUNT(*) AS employee_count
FROM employees
GROUP BY department, position
ORDER BY department, employee_count DESC;

HAVING子句

HAVING用于过滤分组结果，类似于WHERE，但WHERE过滤行，HAVING过滤分组。

-- 查找订单总数超过5的用户
SELECT
    u.username,
    COUNT(o.id) AS order_count
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username
HAVING COUNT(o.id) > 5
ORDER BY order_count DESC;
 
-- WHERE vs HAVING的执行顺序
-- WHERE：在分组前过滤行（更高效）
-- HAVING：在分组后过滤分组
 
-- 示例：查找2024年的大客户（总消费超过10000）
SELECT
    u.username,
    SUM(o.total) AS total_spent
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.order_date >= '2024-01-01'  -- 分组前过滤
GROUP BY u.id, u.username
HAVING SUM(o.total) > 10000           -- 分组后过滤
ORDER BY total_spent DESC;

GROUPING SETS、CUBE、ROLLUP

PostgreSQL提供高级分组功能，生成多个层级的聚合结果。

-- GROUPING SETS：指定多个分组级别
SELECT
    department,
    position,
    COUNT(*) AS employee_count
FROM employees
GROUP BY GROUPING SETS (
    (department, position),  -- 部门和职位都分组
    (department),            -- 只按部门分组
    ()                       -- 总计
);
 
-- ROLLUP：生成分层的汇总（从细到粗）
SELECT
    region,
    city,
    SUM(sales) AS total_sales
FROM sales_data
GROUP BY ROLLUP (region, city);
-- 等价于 GROUPING SETS ((region, city), (region), ())
 
-- CUBE：生成所有可能的组合
SELECT
    region,
    city,
    product_category,
    SUM(sales) AS total_sales
FROM sales_data
GROUP BY CUBE (region, city, product_category);
-- 等价于 GROUPING SETS (
--     (region, city, product_category),
--     (region, city),
--     (region, product_category),
--     (city, product_category),
--     (region),
--     (city),
--     (product_category),
--     ()
-- )

---

四、窗口函数：高级数据分析

窗口函数（Window Functions）是PostgreSQL最强大的功能之一，它允许我们在不聚合行的情况下执行跨行计算。窗口函数结合聚合函数和排名函数，可以解决很多复杂的分析问题。

窗口函数的基本语法

窗口函数的基本语法是：函数名 OVER (窗口规范)。窗口规范定义了函数如何分区窗口、如何排序窗口、窗口的范围。

-- 窗口函数的基本语法
函数名 OVER (
    [PARTITION BY 分区列]
    [ORDER BY 排序列]
    [窗口框架]
)

PARTITION BY：将结果集分成多个分区，函数在每个分区内独立计算。

ORDER BY：在每个分区内对行排序，窗口函数按照这个顺序计算。

窗口框架：定义当前行相对于分区的范围（ROWS BETWEEN … AND …）。

排名函数

-- 创建示例表
CREATE TABLE student_scores (
    student_name TEXT,
    subject TEXT,
    score INTEGER
);
 
-- 插入数据
INSERT INTO student_scores VALUES
    ('张三', '数学', 85), ('张三', '语文', 92),
    ('李四', '数学', 78), ('李四', '语文', 88),
    ('王五', '数学', 92), ('王五', '语文', 95);
 
-- ROW_NUMBER()：连续排名（1, 2, 3, 4, ...）
-- RANK()：跳跃排名（1, 2, 2, 4, ...）- 并列时占用后续名次
-- DENSE_RANK()：密集排名（1, 2, 2, 3, ...）- 并列时不占用后续名次
SELECT
    student_name,
    subject,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
    RANK() OVER (ORDER BY score DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM student_scores
ORDER BY score DESC;
 
-- 按科目分组排名
SELECT
    subject,
    student_name,
    score,
    RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS subject_rank
FROM student_scores
ORDER BY subject, rank;

聚合窗口函数

-- 计算移动平均
SELECT
    date,
    sales,
    AVG(sales) OVER (
        ORDER BY date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM sales_data;
 
-- 累积求和
SELECT
    date,
    sales,
    SUM(sales) OVER (
        ORDER BY date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_sales
FROM sales_data;

偏移量函数

-- LAG：访问前面行的值
-- LEAD：访问后面行的值
SELECT
    date,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY date) AS prev_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY date) AS revenue_change,
    LEAD(revenue, 1) OVER (ORDER BY date) AS next_revenue
FROM monthly_revenue;
 
-- FIRST_VALUE、LAST_VALUE：访问分区第一行/最后一行的值
SELECT
    department,
    employee_name,
    salary,
    FIRST_VALUE(employee_name) OVER (
        PARTITION BY department
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS highest_paid_employee
FROM employees;

实际应用案例

flowchart TB
    subgraph WindowFunctions[窗口函数应用场景]
        direction TB

        subgraph Ranking[排名分析]
            direction TB
            R1[销售排名<br/>RANK DENSE_RANK]
            R2[成绩排名<br/>ROW_NUMBER]
            R3[前N名筛选<br/>过滤 rank <= N]
        end

        subgraph Aggregation[聚合分析]
            direction TB
            A1[移动平均<br/>AVG OVER ROWS]
            A2[累积求和<br/>SUM OVER ROWS]
            A3[同比增长<br/>同比率计算]
        end

        subgraph Offset[偏移分析]
            direction TB
            O1[环比增长<br/>当前-上一行]
            O2[年度比较<br/>同比当前-去年]
            O3[填补缺失值<br/>COALESCE 当前值 LAG]
        end
    end

    style Ranking fill:#c8e6c9
    style Aggregation fill:#fff9c4
    style Offset fill:#ffcdd2

图表讲解：这张图展示了窗口函数的主要应用场景——这些是实际业务中经常遇到的分析需求。

排名分析是窗口函数最常见的应用。例如，计算销售员的业绩排名（并列时是否占用名次）、学生的成绩排名、产品的销售额排名。使用不同的排名函数可以实现不同的排名语义：RANK用于”锦标赛排名”（并列时跳跃），DENSE_RANK用于”班级排名”（并列时不跳跃），ROW_NUMBER用于生成唯一序号。

聚合分析允许我们计算移动平均（平滑数据波动）、累积求和（计算总计）、同比增长（与去年同期比较）。这些计算使用普通聚合函数很难实现，因为聚合函数会将所有行压缩成一行。窗口函数的优势是保留原始行，同时添加聚合计算结果。

偏移分析用于比较当前行与前后行的差异，典型应用是计算环比增长率（与上个月比较）、同比（与去年同月比较）、填补缺失值（使用前一个非空值）。LAG和LEAD函数使得这些分析变得简单，不需要自连接。

窗口函数的真正威力在于组合使用。例如，计算每个部门的销售额排名、同比增长率、移动平均，可以在一个查询中完成，而不需要多个子查询或临时表。

---

五、集合操作：合并查询结果

集合操作符（UNION、INTERSECT、EXCEPT）用于合并多个查询的结果集。注意参与集合操作的查询必须返回相同数量的列，且对应列的数据类型兼容。

UNION：合并结果集

UNION合并两个查询的结果集，自动去除重复行。UNION ALL保留所有行（包括重复），性能更好。

-- UNION：合并并去重
SELECT username FROM admins
UNION
SELECT username FROM regular_users;
 
-- UNION ALL：合并不去重（更快）
SELECT username FROM admins
UNION ALL
SELECT username FROM regular_users;
 
-- 实际应用：合并不同时期的数据
SELECT user_id, order_date, total
FROM orders_2023
UNION ALL
SELECT user_id, order_date, total
FROM orders_2024;

INTERSECT：交集

INTERSECT返回两个查询结果集的交集（同时出现在两个结果集中的行）。

-- 查找既是VIP用户又是活跃用户的人
SELECT user_id FROM vip_users
INTERSECT
SELECT user_id FROM active_users;
 
-- 实际应用：查找两个表共有的用户
SELECT user_id FROM users_2023
INTERSECT
SELECT user_id FROM users_2024;

EXCEPT：差集

EXCEPT返回第一个查询中有但第二个查询中没有的行。

-- 查找有2023年订单但没有2024年订单的用户
SELECT user_id FROM orders_2023
EXCEPT
SELECT user_id FROM orders_2024;
 
-- 实际应用：找出丢失的客户（曾经购买但今年没有购买）
SELECT customer_id FROM customers
EXCEPT
SELECT DISTINCT customer_id FROM orders_this_year;

---

六、公用表表达式（CTE）

公用表表达式（Common Table Expression，CTE）是临时命名的结果集，在查询中可以像表一样引用。CTE使复杂查询更易读、更易维护。

CTE的基本语法

-- 基本语法
WITH cte_name AS (
    cte_query
)
main_query;
 
-- 单个CTE示例
WITH avg_prices AS (
    SELECT category, AVG(price) AS avg_price
    FROM products
    GROUP BY category
)
SELECT p.name, p.category, p.price, c.avg_price
FROM products p
JOIN avg_prices c ON p.category = c.category
WHERE p.price > c.avg_price;

多个CTE

-- 多个CTE用逗号分隔
WITH
user_order_counts AS (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
),
high_value_users AS (
    SELECT user_id, SUM(total) AS total_spent
    FROM orders
    GROUP BY user_id
    HAVING SUM(total) > 10000
)
SELECT
    u.username,
    o.order_count,
    h.total_spent
FROM users u
JOIN user_order_counts o ON u.id = o.user_id
JOIN high_value_users h ON u.id = h.user_id;

递归CTE

递归CTE可以处理树形结构、层级数据等复杂查询。

-- 查询组织架构中的所有员工（包括下属）
WITH RECURSIVE employee_tree AS (
    -- 非递归部分：根节点
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
 
    UNION ALL
 
    -- 递归部分：查找下级员工
    SELECT e.id, e.name, e.manager_id, et.level + 1
    FROM employees e
    JOIN employee_tree et ON e.manager_id = et.id
)
SELECT * FROM employee_tree ORDER BY level, name;

CTE vs 子查询

CTE相比子查询的优势：

• 可读性更好：可以命名中间结果集，逻辑更清晰
• 性能更优：查询优化器可以更好地优化
• 代码复用：CTE可以在同一查询中多次引用
• 支持递归：处理层级数据

-- 使用CTE（推荐）
WITH dept_stats AS (
    SELECT department, COUNT(*) AS emp_count, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT
    department,
    emp_count,
    avg_salary,
    (SELECT AVG(avg_salary) FROM dept_stats) AS overall_avg
FROM dept_stats;
 
-- 使用子查询（不推荐）
SELECT
    department,
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary,
    (SELECT AVG(avg_salary)
     FROM (SELECT department, AVG(salary) AS avg_salary
           FROM employees GROUP BY department) dept) AS overall_avg
FROM employees
GROUP BY department;

---

七、视图：虚拟表

视图（View）是虚拟表，不存储数据，只存储查询定义。视图简化复杂查询，提供数据的安全封装。

创建和使用视图

-- 创建简单视图
CREATE VIEW user_orders AS
SELECT
    u.id AS user_id,
    u.username,
    u.email,
    o.id AS order_id,
    o.order_date,
    o.total
FROM users u
JOIN orders o ON u.id = o.user_id;
 
-- 使用视图
SELECT * FROM user_orders WHERE user_id = 1;
 
-- 创建带聚合的视图（需要WITH CHECK OPTION）
CREATE VIEW department_stats AS
SELECT
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary,
    SUM(salary) AS total_salary
FROM employees
GROUP BY department;

视图的优势

简化查询：将复杂查询封装为视图，用户只需查询视图。

-- 原始复杂查询
SELECT u.username, o.order_date, oi.quantity, p.name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.order_date >= '2024-01-01';
 
-- 使用视图（简化）
CREATE VIEW recent_order_details AS
SELECT u.username, o.order_date, oi.quantity, p.name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;
 
SELECT * FROM recent_order_details
WHERE order_date >= '2024-01-01';

数据安全：视图可以限制用户访问敏感数据。

-- 创建只包含必要字段的视图
CREATE VIEW public_user_info AS
SELECT id, username, email
FROM users;
 
-- 授予用户访问视图的权限，而不是基础表
GRANT SELECT ON public_user_info TO app_user;

逻辑独立性：基础表结构变化时，可以修改视图定义，不需要修改应用代码。

物化视图

PostgreSQL支持物化视图（Materialized View），它实际存储数据，可以刷新。

-- 创建物化视图
CREATE MATERIALIZED VIEW sales_summary AS
SELECT
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*) AS order_count,
    SUM(total) AS total_sales
FROM orders
GROUP BY DATE_TRUNC('month', order_date);
 
-- 刷新物化视图
REFRESH MATERIALIZED VIEW sales_summary;
 
-- 查询物化视图（非常快，因为数据已预计算）
SELECT * FROM sales_summary ORDER BY month;

---

八、事务深入理解

事务是保证数据一致性的核心机制。在第1篇我们介绍了事务的基本概念，这里我们深入探讨事务的隔离级别和并发控制。

事务隔离级别

SQL标准定义了四个隔离级别，从低到高依次是：

隔离级别	脏读	不可重复读	幻读	说明
READ UNCOMMITTED	可能	可能	可能	最低隔离，几乎不用
READ COMMITTED	不可能	可能	可能	PostgreSQL默认
REPEATABLE READ	不可能	不可能	可能
SERIALIZABLE	不可能	不可能	不可能	最高隔离，串行执行

脏读（Dirty Read）：读取未提交的数据。

不可重复读（Non-repeatable Read）：同一事务内两次读取同一数据，结果不同。

幻读（Phantom Read）：同一事务内两次执行相同查询，返回的行数不同。

-- 设置事务隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 
-- 查看当前隔离级别
SHOW transaction_isolation;
 
-- 在事务中设置隔离级别
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 执行查询
COMMIT;

锁机制

PostgreSQL使用多种锁机制保护并发访问：

表级锁：

• ACCESS SHARE锁：SELECT语句获取
• ROW EXCLUSIVE锁：INSERT、UPDATE、DELETE获取
• SHARE锁：CREATE INDEX CONCURRENTLY获取
• EXCLUSIVE锁：LOCK TABLE语句获取

行级锁：

• FOR UPDATE：SELECT … FOR UPDATE获取行锁，用于更新
• FOR SHARE：SELECT … FOR SHARE获取共享锁，用于读取
• FOR KEY SHARE：用于外键检查

-- 显式锁定行
BEGIN;
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- 此时其他事务试图修改id=1的行将被阻塞
UPDATE users SET email = '[email protected]' WHERE id = 1;
COMMIT;
 
-- 检查锁状态
SELECT * FROM pg_locks WHERE NOT granted;

死锁与处理

死锁是两个或多个事务互相等待对方持有的锁，形成循环等待。

-- 死锁检测（PostgreSQL自动检测）
-- 死锁发生时，PostgreSQL会选择一个事务作为牺牲品，回滚该事务
ERROR: could not serialize access due to concurrent update
 
-- 处理死锁：应用程序应该捕获错误并重试

死锁的预防方法：

• 让事务按相同顺序访问表和行
• 尽量缩短事务持有锁的时间
• 尽早提交事务
• 使用合适的隔离级别

sequenceDiagram
    participant T1 as 事务A
    participant T2 as 事务B
    participant DB as 数据库

    Note over T1,DB: 死锁场景

    T1->>DB: 1. BEGIN
    T1->>DB: 2. UPDATE users SET ... WHERE id = 1<br/>获取行锁(id=1)
    DB->>DB: 锁定id=1

    T2->>DB: 3. BEGIN
    T2->>DB: 4. UPDATE users SET ... WHERE id = 2<br/>获取行锁(id=2)
    DB->>DB: 锁定id=2

    T1->>DB: 5. UPDATE users SET ... WHERE id = 2<br/>等待id=2的锁
    DB->>T1: 阻塞等待

    T2->>DB: 6. UPDATE users SET ... WHERE id = 1<br/>等待id=1的锁
    DB->>T2: 阻塞等待

    Note over T1,DB: 死锁形成！A等待B，B等待A

    DB->>T2: 检测到死锁
    DB->>T2: 回滚事务B（牺牲品）
    DB->>T1: 解除阻塞，继续执行

    T1->>DB: 7. 提交事务A
    DB->>DB: 释放所有锁

    T2->>T2: 8. 应用程序捕获错误<br/>重试事务B

图表讲解：这张时序图展示了死锁的形成和解决过程——这是理解并发控制的关键场景。

死锁发生在两个事务互相等待对方持有的锁时。事务A先锁定id=1的行，事务B随后锁定id=2的行。然后事务A试图锁定id=2的行，但该行已被事务B锁定，所以事务A等待。事务B也试图锁定id=1的行，但该行已被事务A锁定，所以事务B也等待。现在形成循环等待：A等待B，B等待A。

PostgreSQL的死锁检测机制定期检测这种循环等待。一旦检测到死锁，PostgreSQL会选择一个”牺牲品”（通常是修改较少数据的事务），回滚该事务并释放其持有的锁。被回滚的事务会收到错误信息，应用程序应该捕获这个错误并重试事务。

死锁的预防是更好的策略。通过让事务按固定顺序访问表和行，可以避免循环等待。例如，总是按id从小到大的顺序更新行。缩短事务持有锁的时间也能减少死锁概率，因为锁的持有时间越短，冲突的可能性越小。

---

九、核心概念总结

概念	说明	应用场景
INNER JOIN	只返回匹配的行	查询有关系的实体
LEFT JOIN	返回左表所有行	包含无关联的主记录
窗口函数	不聚合行的跨行计算	排名、移动平均、累积求和
CTE	命名的临时结果集	简化复杂查询
视图	虚拟表，存储查询定义	数据封装、简化访问
隔离级别	事务之间的隔离程度	平衡并发与一致性
死锁	事务循环等待	需要应用层处理
物化视图	存储数据的视图	预计算，提高查询速度

---

十、本篇总结

本文深入讲解了SQL的高级查询技术，包括多表连接、子查询、聚合函数、窗口函数、集合操作、CTE、视图和事务隔离级别。这些技术让你能够：

• 使用各种连接类型处理关联数据
• 编写复杂的嵌套查询和相关子查询
• 执行数据统计和分析
• 使用窗口函数解决排名和累积分析问题
• 使用CTE简化复杂查询
• 创建视图封装数据访问
• 理解事务隔离级别和并发控制

下一篇将探讨数据库设计的深层次问题，包括数据规范化、约束机制、索引原理和性能优化基础。

---

下篇预告

第3篇将深入讲解数据库设计的艺术，带你学习数据规范化理论、约束的深入应用、表分区策略，以及如何设计高性能、可扩展的数据库结构。

---

常见问题解答

Q1：什么时候使用子查询，什么时候使用JOIN？

答：一般情况下，JOIN的效率高于子查询，特别是相关子查询。

JOIN让数据库优化器选择执行计划，而子查询（特别是相关子查询）可能执行多次。但如果子查询结果集很小，或者只返回单个值（如聚合比较），子查询可能更清晰。

EXISTS子查询通常比IN子查询更高效，特别是当子查询结果集大时。实际编写查询时，可以先用子查询清晰地表达逻辑，然后考虑是否可以用JOIN或窗口函数改写优化。关键是理解查询的语义，选择最清晰、最高效的实现方式。

---

Q2：窗口函数和GROUP BY有什么区别，什么时候用哪个？

答：窗口函数和GROUP BY都是对数据进行聚合，但关键区别是：GROUP BY将多行压缩成一行，窗口函数保留原始行。

例如，计算部门平均工资并保留员工详情，只能用窗口函数；如果只需要每个部门的平均工资，用GROUP BY更简单。另一个区别是窗口函数可以计算”移动平均”、“累积求和”等跨行计算，GROUP BY无法实现。

性能方面，窗口函数不一定比GROUP BY慢，有时甚至更快，因为不需要排序和哈希聚合。选择时考虑：是否需要保留明细行？是否需要跨行计算？回答这些可以决定使用哪种技术。

---

Q3：什么是”幻读”，为什么需要SERIALIZABLE隔离级别？

答：幻读是指在同一事务中，两次执行相同的查询，返回的行数不同。

这通常由其他事务插入或删除满足条件的新行导致。例如，事务A查询所有年龄大于30的用户，得到10行；事务B插入5个新用户（年龄大于30）；事务A再次查询，得到15行，这就是幻读。

幻读与不可重复读的区别是：不可重复读是已有行的值改变，幻读是新行的出现或消失。SERIALIZABLE是最高隔离级别，通过严格的锁定保证事务完全隔离，就像串行执行一样。但SERIALIZABLE的并发性最差，性能最低，只在有严格一致性要求的场景下使用。

---

Q4：视图和物化视图有什么区别，什么时候使用物化视图？

答：视图是虚拟表，只存储查询定义，不存储数据。

每次查询视图时，数据库执行底层的查询。物化视图实际存储查询结果，占用存储空间，但查询非常快，因为数据已预计算。

视图适合简化复杂查询、提供数据安全封装，数据实时更新。物化视图适合预计算复杂聚合、提高查询性能、数据定期刷新（如每天一次）。物化视图的缺点是数据不是实时的，需要手动或自动刷新。物化视图特别适合报表、仪表板等查询频繁但数据变化不频繁的场景。PostgreSQL的REFRESH MATERIALIZED VIEW命令可以刷新物化视图。

---

Q5：为什么递归CTE可以处理树形结构，它如何工作？

答：递归CTE是CTE的一种特殊形式，它引用自身，形成递归查询。

递归CTE由两部分组成：非递归部分（锚成员）和递归部分（递归成员）。非递归部分提供初始结果集，递归部分迭代地引用CTE自身，不断添加新行，直到没有新行产生。

这类似于编程中的递归函数：需要基准情况（非递归部分）和递归步骤（递归部分）。递归CTE可以遍历树形结构、图结构、层级数据。例如，查询组织架构中的所有员工（包括下属）、计算物料的BOM（物料清单）、查找路径关系。递归CTE的深度受postgresql.conf中的max_stack_depth参数限制。使用递归CTE时，应该有终止条件，避免无限递归。

---

更新时间：2026年3月2日 系列：PostgreSQL数据库技术 标签：#PostgreSQL 高级查询 窗口函数 CTE 多表连接 事务隔离 JOIN