这是《PostgreSQL 零基础实战指南》的独立章节版。本章从概念、实操和生产排查三个视角展开，代码块保留了原书可直接运行的版本。 PostgreSQL 的高级 SQL 能力适合做报表、批处理和复杂业务建模。本章覆盖分组集、递归查询、LATERAL、行转列、自定义函数和性能边界。
4.1 分组集
SELECT city, status, count(*), sum(amount)
FROM orders o
JOIN users u ON u.id = o.user_id
GROUP BY GROUPING SETS (
(city, status),
(city),
(),
(status)
);
等价能力：
ROLLUP (city, status)
CUBE (city, status)
识别小计行：
SELECT grouping(city) AS city_grouping, city, sum(amount)
FROM orders
GROUP BY ROLLUP (city);
4.2 递归 CTE
组织树：
WITH RECURSIVE org_tree AS (
SELECT id, parent_id, name, 1 AS level
FROM departments
WHERE parent_id IS NULL
UNION ALL
SELECT d.id, d.parent_id, d.name, t.level + 1
FROM departments d
JOIN org_tree t ON d.parent_id = t.id
)
SELECT * FROM org_tree;
注意：
- 必须有终止条件；
- 防止环引用；
- 深度较大时关注执行成本；
- 可以用 path 数组辅助调试。
4.3 LATERAL
每个用户最近三笔订单：
SELECT u.id, u.name, o.id AS order_id, o.amount
FROM users u
LEFT JOIN LATERAL (
SELECT id, amount
FROM orders
WHERE user_id = u.id
ORDER BY created_at DESC
LIMIT 3
) o ON true;
LATERAL 允许子查询引用左侧表字段，常用于 TopN per group 和按组展开。
4.4 行转列
SELECT city,
sum(amount) FILTER (WHERE status = 'PAID') AS paid_amount,
sum(amount) FILTER (WHERE status = 'CANCELLED') AS cancelled_amount,
count(*) FILTER (WHERE status = 'CREATED') AS created_count
FROM orders
GROUP BY city;
FILTER 比多个 CASE 更清晰：
sum(CASE WHEN status = 'PAID' THEN amount ELSE 0 END)
4.5 DISTINCT ON
SELECT DISTINCT ON (user_id)
user_id, id, amount, created_at
FROM orders
ORDER BY user_id, created_at DESC;
每个用户返回一行，取 ORDER BY 中该组第一行。需要其他最新字段时可配合窗口函数。
4.6 生成系列
补齐时间轴：
SELECT generate_series(
date_trunc('day', now() - interval '6 days'),
date_trunc('day', now()),
interval '1 day'
)::date AS day;
补零报表：
SELECT d.day, coalesce(sum(o.amount), 0) AS gmv
FROM generate_series(
current_date - 6,
current_date,
interval '1 day'
) AS d(day)
LEFT JOIN orders o
ON o.created_at::date = d.day::date
GROUP BY d.day
ORDER BY d.day;
4.7 表表达式
VALUES：
SELECT *
FROM (VALUES
('CREATED', 1),
('PAID', 2)
) AS status_map(status, sort_no);
UNNEST：
SELECT unnest(ARRAY[1,2,3]) AS n;
4.8 自定义函数
CREATE OR REPLACE FUNCTION normalize_phone(input text)
RETURNS text
LANGUAGE sql
IMMUTABLE
AS $$
SELECT regexp_replace(coalesce(input, ''), '[^0-9]', '', 'g')
$$;
标示：
| 属性 | 含义 |
|---|---|
| IMMUTABLE | 输入相同结果必相同，可优化 |
| STABLE | 事务内稳定 |
| VOLATILE | 默认，可能有副作用 |
高并发热点路径慎用 PL/pgSQL 循环，能用集合 SQL 就不用逐行函数。
4.9 性能边界
高级 SQL 仍要看执行计划：
EXPLAIN ANALYZE
WITH RECURSIVE ...
常见问题：
- 递归无界；
- LATERAL 子查询无法使用索引；
- 窗口函数排序成本高；
- CTE 物化后外层无法下推；
- 大结果集 FILTER 聚合内存高。
本章小结
高级 SQL 让许多复杂业务逻辑在数据库内一次完成。GROUPING SETS、递归 CTE、LATERAL、FILTER 和 DISTINCT ON 是 PostgreSQL 特色能力。表达力越强，越需要用执行计划约束成本。
思考题
- ROLLUP 和 CUBE 的区别是什么？
- 递归 CTE 如何防止死循环？
- LATERAL 适合什么查询？
- FILTER 和 CASE 聚合哪个更清晰？
- 函数 IMMUTABLE 有什么优化意义？