Appearance
PostgreSQL WITH 子句详解
WITH 子句（也称为公共表表达式 CTE - Common Table Expressions）是 PostgreSQL 中一个非常强大的功能，它可以让我们创建临时的命名结果集，使复杂查询变得更加清晰和易于维护。
什么是 WITH 子句？
TIP
把 WITH 子句想象成在查询前定义一个"临时视图"。就像在做菜之前先准备好所有配料一样，WITH 子句让我们先准备好需要的数据集，然后在主查询中使用它们。
WITH 子句允许我们在一个查询中定义一个或多个临时的命名子查询，这些子查询的结果可以在主查询中被引用，就像引用普通的表一样。
基本语法
sql
WITH cte_name AS (
-- 子查询定义
SELECT ...
)
SELECT ...
FROM cte_name;多个 CTE 的语法
sql
WITH
cte1 AS (
SELECT ...
),
cte2 AS (
SELECT ...
)
SELECT ...
FROM cte1
JOIN cte2 ON ...;实际业务场景案例
📊 场景1：电商平台订单数据分析
业务背景：电商平台需要分析每个客户的消费情况，包括总订单数、总消费金额，并识别出高价值客户（消费超过 10000 元的客户）。
假设我们有以下订单数据：
sql
-- 订单表数据示例
CREATE TABLE orders (
order_id INT,
customer_id INT,
customer_name VARCHAR(50),
order_amount DECIMAL(10,2),
order_date DATE
);
-- 插入示例数据
INSERT INTO orders VALUES
(1, 101, 'Alice', 1500.00, '2024-01-15'),
(2, 102, 'Bob', 800.00, '2024-01-20'),
(3, 101, 'Alice', 2200.00, '2024-02-10'),
(4, 103, 'Charlie', 15000.00, '2024-02-15'),
(5, 102, 'Bob', 950.00, '2024-03-01'),
(6, 101, 'Alice', 7500.00, '2024-03-10'),
(7, 104, 'David', 600.00, '2024-03-15'),
(8, 103, 'Charlie', 3200.00, '2024-04-01');sql
-- 使用 WITH 子句分析客户消费情况
WITH customer_stats AS (
-- 计算每个客户的基础统计信息
SELECT
customer_id,
customer_name,
COUNT(*) AS total_orders, -- 总订单数
SUM(order_amount) AS total_spent, -- 总消费金额
AVG(order_amount) AS avg_order_value, -- 平均订单金额
MAX(order_date) AS last_order_date -- 最后一次购买日期
FROM orders
GROUP BY customer_id, customer_name
),
high_value_customers AS (
-- 识别高价值客户（消费超过10000元）
SELECT
customer_id,
customer_name,
total_spent,
total_orders,
'VIP客户' AS customer_type
FROM customer_stats
WHERE total_spent > 10000
)
SELECT
cs.customer_name,
cs.total_orders,
cs.total_spent,
cs.avg_order_value,
cs.last_order_date,
COALESCE(hvc.customer_type, '普通客户') AS customer_type
FROM customer_stats cs
LEFT JOIN high_value_customers hvc ON cs.customer_id = hvc.customer_id
ORDER BY cs.total_spent DESC;sql
-- 不使用 WITH 的复杂嵌套查询
SELECT
cs.customer_name,
cs.total_orders,
cs.total_spent,
cs.avg_order_value,
cs.last_order_date,
CASE
WHEN cs.total_spent > 10000 THEN 'VIP客户'
ELSE '普通客户'
END AS customer_type
FROM (
SELECT
customer_id,
customer_name,
COUNT(*) AS total_orders,
SUM(order_amount) AS total_spent,
AVG(order_amount) AS avg_order_value,
MAX(order_date) AS last_order_date
FROM orders
GROUP BY customer_id, customer_name
) cs
ORDER BY cs.total_spent DESC;查询结果：
customer_name | total_orders | total_spent | avg_order_value | last_order_date | customer_type
--------------|--------------|-------------|-----------------|-----------------|---------------
Charlie | 2 | 18200.00 | 9100.00 | 2024-04-01 | VIP客户
Alice | 3 | 11200.00 | 3733.33 | 2024-03-10 | VIP客户
Bob | 2 | 1750.00 | 875.00 | 2024-03-01 | 普通客户
David | 1 | 600.00 | 600.00 | 2024-03-15 | 普通客户技术价值：✅ WITH 子句将复杂的业务逻辑分解为多个清晰的步骤，先计算客户统计信息，再识别高价值客户，最后合并结果，大大提高了查询的可读性和维护性。
🏢 场景2：组织架构层级查询（递归 CTE）
业务背景：公司需要查询某个部门经理下的所有下属员工（包括间接下属），构建完整的组织架构树。
假设我们有以下员工数据：
sql
-- 员工表数据示例
CREATE TABLE employees (
emp_id INT,
name VARCHAR(50),
position VARCHAR(50),
manager_id INT,
department VARCHAR(50)
);
-- 插入示例数据
INSERT INTO employees VALUES
(1, 'CEO王总', 'CEO', NULL, '总裁办'),
(2, '技术总监李总', 'CTO', 1, '技术部'),
(3, '销售总监张总', 'Sales Director', 1, '销售部'),
(4, '后端团队负责人刘工', 'Backend Lead', 2, '技术部'),
(5, '前端团队负责人陈工', 'Frontend Lead', 2, '技术部'),
(6, '销售经理王经理', 'Sales Manager', 3, '销售部'),
(7, '后端工程师小明', 'Backend Developer', 4, '技术部'),
(8, '后端工程师小红', 'Backend Developer', 4, '技术部'),
(9, '前端工程师小李', 'Frontend Developer', 5, '技术部'),
(10, '销售代表小张', 'Sales Rep', 6, '销售部');使用递归 WITH 子句查询组织架构：
sql
-- 递归查询：找出技术总监下的所有下属
WITH RECURSIVE org_hierarchy AS (
-- 基础情况：技术总监本人
SELECT
emp_id,
name,
position,
manager_id,
department,
0 AS level, -- 层级（0表示起始点）
name AS hierarchy_path -- 层级路径
FROM employees
WHERE name = '技术总监李总'
UNION ALL
-- 递归情况：查找每一层的下属
SELECT
e.emp_id,
e.name,
e.position,
e.manager_id,
e.department,
oh.level + 1,
oh.hierarchy_path || ' -> ' || e.name
FROM employees e
INNER JOIN org_hierarchy oh ON e.manager_id = oh.emp_id
)
SELECT
REPEAT(' ', level) || name AS org_structure, -- 用缩进显示层级
position,
department,
level,
hierarchy_path
FROM org_hierarchy
ORDER BY level, name;查询结果：
org_structure | position | department | level | hierarchy_path
----------------------------|-------------------|------------|-------|----------------------------------
技术总监李总 | CTO | 技术部 | 0 | 技术总监李总
后端团队负责人刘工 | Backend Lead | 技术部 | 1 | 技术总监李总 -> 后端团队负责人刘工
前端团队负责人陈工 | Frontend Lead | 技术部 | 1 | 技术总监李总 -> 前端团队负责人陈工
后端工程师小明 | Backend Developer | 技术部 | 2 | 技术总监李总 -> 后端团队负责人刘工 -> 后端工程师小明
后端工程师小红 | Backend Developer | 技术部 | 2 | 技术总监李总 -> 后端团队负责人刘工 -> 后端工程师小红
前端工程师小李 | Frontend Developer| 技术部 | 2 | 技术总监李总 -> 前端团队负责人陈工 -> 前端工程师小李技术价值：⚡️ 递归 WITH 子句能够处理具有层次结构的数据，一次查询就能获取完整的组织架构树，无需多次查询或复杂的应用程序逻辑。
📈 场景3：时间序列数据分析
业务背景：电商平台需要分析每月的销售趋势，包括当月销售额、累计销售额、以及与上月的环比增长率。
假设我们有以下销售数据：
sql
-- 月度销售数据示例
CREATE TABLE monthly_sales (
sales_month DATE,
total_amount DECIMAL(12,2)
);
-- 插入示例数据
INSERT INTO monthly_sales VALUES
('2024-01-01', 150000.00),
('2024-02-01', 180000.00),
('2024-03-01', 165000.00),
('2024-04-01', 220000.00),
('2024-05-01', 195000.00),
('2024-06-01', 280000.00);sql
-- 使用 WITH 子句进行时间序列分析
WITH sales_with_trends AS (
SELECT
sales_month,
total_amount,
-- 计算累计销售额
SUM(total_amount) OVER (
ORDER BY sales_month
ROWS UNBOUNDED PRECEDING
) AS cumulative_sales,
-- 计算上月销售额（用于环比计算）
LAG(total_amount) OVER (
ORDER BY sales_month
) AS prev_month_sales
FROM monthly_sales
),
sales_analysis AS (
SELECT
sales_month,
total_amount,
cumulative_sales,
prev_month_sales,
-- 计算环比增长率
CASE
WHEN prev_month_sales IS NOT NULL THEN
ROUND(
((total_amount - prev_month_sales) / prev_month_sales * 100), 2
)
ELSE
NULL
END AS month_over_month_growth,
-- 计算季度标识
CONCAT('Q', EXTRACT(QUARTER FROM sales_month)) AS quarter
FROM sales_with_trends
)
SELECT
TO_CHAR(sales_month, 'YYYY-MM') AS month,
TO_CHAR(total_amount, 'FM999,999,999.00') AS monthly_sales,
TO_CHAR(cumulative_sales, 'FM999,999,999.00') AS cumulative_sales,
COALESCE(month_over_month_growth || '%', '基准月') AS growth_rate,
quarter,
-- 销售趋势标识
CASE
WHEN month_over_month_growth > 10 THEN '📈 强劲增长'
WHEN month_over_month_growth > 0 THEN '📊 稳定增长'
WHEN month_over_month_growth < 0 THEN '📉 下降趋势'
ELSE '➖ 基准月'
END AS trend_indicator
FROM sales_analysis
ORDER BY sales_month;查询结果：
month | monthly_sales | cumulative_sales | growth_rate | quarter | trend_indicator
--------|---------------|------------------|-------------|---------|----------------
2024-01 | 150,000.00 | 150,000.00 | 基准月 | Q1 | ➖ 基准月
2024-02 | 180,000.00 | 330,000.00 | 20.00% | Q1 | 📈 强劲增长
2024-03 | 165,000.00 | 495,000.00 | -8.33% | Q1 | 📉 下降趋势
2024-04 | 220,000.00 | 715,000.00 | 33.33% | Q2 | 📈 强劲增长
2024-05 | 195,000.00 | 910,000.00 | -11.36% | Q2 | 📉 下降趋势
2024-06 | 280,000.00 | 1,190,000.00 | 43.59% | Q2 | 📈 强劲增长技术价值：💡 WITH 子句让我们能够分步骤构建复杂的分析逻辑，先计算基础指标（累计、环比），再进行趋势分析，使复杂的时间序列分析变得清晰易懂。
WITH 子句的优势
✅ 提高可读性
- 将复杂查询分解为多个简单的步骤
- 每个 CTE 都有明确的业务含义
- 便于团队协作和代码维护
✅ 提高性能
- PostgreSQL 可以优化 CTE 的执行计划
- 避免重复计算相同的子查询
- 在某些情况下比嵌套子查询更高效
✅ 支持递归查询
- 处理树形结构数据（如组织架构、分类体系）
- 图遍历和路径查找
- 数据血缘关系追踪
注意事项与最佳实践
WARNING
WITH 子句的一些限制和注意事项
⚠️ 递归 CTE 的限制
sql
-- 需要防止无限递归
WITH RECURSIVE infinite_loop AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM infinite_loop -- 可能导致无限递归
)
SELECT * FROM infinite_loop LIMIT 10; -- 务必加上 LIMIT⚠️ CTE 物化行为
在 PostgreSQL 12+ 中，CTE 默认会被物化（即先计算完整结果），这可能影响性能：
sql
-- 如果不需要物化，可以使用 NOT MATERIALIZED
WITH non_materialized AS NOT MATERIALIZED (
SELECT expensive_function(id) FROM large_table
)
SELECT * FROM non_materialized WHERE condition;📋 最佳实践
TIP
WITH 子句使用建议
- 命名要有意义：CTE 名称应该清晰表达其业务含义
- 逻辑分层：按照业务逻辑层次组织多个 CTE
- 注释说明：为复杂的 CTE 添加注释说明
- 性能考虑：对于大数据集，注意 CTE 的物化行为
复杂递归 CTE 示例：计算员工层级和薪资汇总
sql
-- 复杂递归示例：计算每个管理者下属的总薪资
WITH RECURSIVE manager_hierarchy AS (
-- 基础情况：所有员工的基本信息
SELECT
emp_id,
name,
position,
salary,
manager_id,
1 AS level,
ARRAY[emp_id] AS path, -- 防止循环引用
salary AS total_team_salary
FROM employees
WHERE manager_id IS NULL -- 从顶级管理者开始
UNION ALL
-- 递归情况：添加下属并累计薪资
SELECT
e.emp_id,
e.name,
e.position,
e.salary,
e.manager_id,
mh.level + 1,
mh.path || e.emp_id, -- 记录路径防止循环
e.salary
FROM employees e
INNER JOIN manager_hierarchy mh ON e.manager_id = mh.emp_id
WHERE NOT (e.emp_id = ANY(mh.path)) -- 防止循环引用
),
team_salary_summary AS (
-- 计算每个管理者团队的总薪资
SELECT
manager_id,
SUM(total_team_salary) AS total_managed_salary
FROM manager_hierarchy
WHERE manager_id IS NOT NULL
GROUP BY manager_id
)
SELECT
mh.name AS manager_name,
mh.position,
mh.level,
COALESCE(tss.total_managed_salary, 0) AS team_total_salary,
mh.salary AS manager_salary
FROM manager_hierarchy mh
LEFT JOIN team_salary_summary tss ON mh.emp_id = tss.manager_id
ORDER BY mh.level, mh.name;总结
WITH 子句是 PostgreSQL 中处理复杂查询的强大工具，它不仅让 SQL 查询变得更加清晰易读，还能有效处理递归和层次化数据。通过合理使用 WITH 子句，我们可以：
- 🎯 简化复杂业务逻辑：将复杂查询分解为多个易理解的步骤
- 🔄 处理递归结构：轻松查询树形和图形数据结构
- 📊 提升代码质量：增强 SQL 的可读性和可维护性
- ⚡️ 优化查询性能：在适当场景下提供更好的执行效率
掌握 WITH 子句，能让你在面对复杂的数据分析需求时游刃有余，写出既高效又优雅的 SQL 查询！