在 PostgreSQL 中，公共表表达式（Common Table Expressions，简称 CTE）是 SQL 查询中一种强大的工具，用于提高查询的可读性和组织性。CTE 允许用户在执行复杂查询时将其拆解成更简单的部分，从而使查询更易于理解和维护。本文将详细介绍在 PostgreSQL 中使用 CTE，包括其基本语法、示例操作、应用场景以及注意事项。
1. 基本概念
1.1 什么是 CTE？
CTE 是一个临时结果集，它在执行查询时被定义并使用。CTE 的定义部分类似于创建一个视图，但其作用范围仅限于定义 CTE 的查询语句。CTE 可以用于简化复杂查询、增强代码的可读性，并支持递归查询。
2. CTE 的基本语法
CTE 的基本语法如下：
WITH cte_name AS (
SELECT column1, column2, ...
FROM table_name
WHERE conditions
)
SELECT column1, column2, ...
FROM cte_name
WHERE conditions;
WITH cte_name AS (...)：定义一个名为cte_name的 CTE，其中包含一个查询语句。SELECT column1, column2, ... FROM cte_name WHERE conditions：使用 CTE 的结果进行进一步的查询。
3. 示例操作
3.1 基本示例
假设我们有一个 employees 表，包含员工的信息。我们希望从表中获取所有工资高于 50000 的员工，并且这些员工的部门名称是“销售部”。
创建表的示例：
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
name VARCHAR(100),
salary NUMERIC,
department VARCHAR(100)
);
INSERT INTO employees (name, salary, department) VALUES
('Alice', 55000, 'Sales'),
('Bob', 48000, 'Marketing'),
('Charlie', 60000, 'Sales'),
('David', 45000, 'HR');
使用 CTE 获取工资高于 50000 的员工：
WITH high_salary_employees AS (
SELECT employee_id, name, salary, department
FROM employees
WHERE salary > 50000
)
SELECT name, salary
FROM high_salary_employees
WHERE department = 'Sales';
结果：
name | salary
--------+--------
Alice | 55000
Charlie | 60000
在这个示例中，high_salary_employees 是一个 CTE，它包含了工资高于 50000 的员工。接下来的查询从 CTE 中筛选出部门为“销售部”的员工。
3.2 使用 CTE 进行递归查询
CTE 支持递归查询，这对于处理层次结构数据（如组织结构图）非常有用。递归 CTE 包括两个部分：基础查询和递归查询。
示例：
假设我们有一个 employees 表，记录了员工的管理层级。我们希望找出某位员工的所有下属。
创建表的示例：
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
name VARCHAR(100),
manager_id INT
);
INSERT INTO employees (name, manager_id) VALUES
('CEO', NULL),
('Manager1', 1),
('Manager2', 1),
('Employee1', 2),
('Employee2', 2),
('Employee3', 3);
使用递归 CTE 查找某位员工的下属：
WITH RECURSIVE subordinates AS (
SELECT employee_id, name, manager_id
FROM employees
WHERE name = 'CEO'
UNION ALL
SELECT e.employee_id, e.name, e.manager_id
FROM employees e
INNER JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT name
FROM subordinates;
结果：
name
---------
CEO
Manager1
Manager2
Employee1
Employee2
Employee3
在这个示例中，subordinates CTE 递归地查找“CEO”下的所有员工，包括直接和间接下属。
4. 应用场景
4.1 数据汇总和分析
CTE 可以用于复杂的数据汇总和分析，简化查询逻辑。通过将查询分解为多个 CTE，可以逐步处理数据，逐步构建最终结果。
示例：
WITH sales_data AS (
SELECT salesperson, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY salesperson
),
ranked_sales AS (
SELECT salesperson, total_sales,
RANK() OVER (ORDER BY total_sales DESC) AS sales_rank
FROM sales_data
)
SELECT salesperson, total_sales, sales_rank
FROM ranked_sales
WHERE sales_rank <= 10;
在这个示例中，sales_data 计算每个销售员的总销售额，ranked_sales 计算销售员的排名，最终查询返回前 10 名销售员。
4.2 数据清理和转换
CTE 可以用于数据清理和转换任务，例如在 ETL 过程中的数据预处理。通过使用 CTE，可以在最终的查询中处理和转换数据。
示例：
WITH cleaned_data AS (
SELECT TRIM(name) AS cleaned_name,
UPPER(email) AS cleaned_email
FROM raw_data
WHERE email IS NOT NULL
)
INSERT INTO processed_data (name, email)
SELECT cleaned_name, cleaned_email
FROM cleaned_data;
在这个示例中，cleaned_data CTE 处理和清理原始数据，然后将清理后的数据插入到目标表中。
5. 注意事项
5.1 性能考虑
CTE 在复杂查询中可以提高代码的可读性，但可能会影响性能，特别是在使用递归 CTE 时。要注意优化查询，例如使用适当的索引。
优化建议：
- 使用索引：在 CTE 参与的列上创建索引，以提高查询性能。
- 避免不必要的递归：尽量避免不必要的递归查询，以减少性能开销。
5.2 CTE 的作用范围
CTE 的作用范围仅限于定义 CTE 的查询。CTE 定义后，其结果集可以在后续的查询中使用，但不能在其他 CTE 中再次引用。
示例：
WITH first_cte AS (
SELECT column1 FROM table1
),
second_cte AS (
SELECT column1 FROM first_cte -- 错误，不能在 second_cte 中引用 first_cte
UNION
SELECT column2 FROM table2
)
SELECT * FROM second_cte;
6. 总结
CTE 是 PostgreSQL 中一个强大的查询工具，能够帮助用户简化复杂查询、提高代码的可读性和维护性。通过 CTE，用户可以轻松地定义和使用临时结果集，支持递归查询和数据清理。掌握 CTE 的使用方法和最佳实践，对于提高 SQL 查询的效率和组织性至关重要。在实际应用中，合理使用 CTE 可以显著提升数据分析和处理的能力。
