在 Postgres 中使用 CTE

在 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 可以显著提升数据分析和处理的能力。