摘要

        本文为 PostgreSQL 从入门到精通系列第五篇，深入探讨 PostgreSQL 的高级查询技术。通过大量实战案例，详细讲解子查询、公共表达式（CTE）、窗口函数和高级聚合函数的用法与适用场景。掌握这些技术，能让你写出更简洁、更高效、更具可读性的复杂 SQL，从容应对各种数据分析与报表需求。

---

前言

        在掌握了基础的增删改查之后，我们经常会遇到更复杂的查询需求，比如：

• 如何找出每个部门的最高工资员工？
• 如何计算用户的累计消费金额？
• 如何对数据进行分组排名？

        这些问题用基础 SQL 很难高效解决，这时候就需要用到 PostgreSQL 的高级查询特性。本文将带你系统学习这些强大的工具，让你的 SQL 水平提升一个台阶。

---

一、子查询（Subquery）

        子查询是嵌套在其他 SQL 语句中的查询，它可以让你用一个查询的结果作为另一个查询的条件或数据源。

1.1 WHERE 子句中的子查询

-- 示例：查询所有发表过文章的用户
SELECT * FROM users
WHERE id IN (SELECT author_id FROM articles);

-- 示例：查询工资高于平均工资的员工
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

1.2 FROM 子句中的子查询（派生表）

-- 示例：查询每个部门的平均工资，并找出平均工资高于 10000 的部门
SELECT dept_name, avg_salary
FROM (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) AS dept_avg
JOIN departments d ON dept_avg.department_id = d.id
WHERE avg_salary > 10000;

1.3 SELECT 子句中的子查询

-- 示例：查询每个用户的用户名及其发表的文章数量
SELECT 
    username,
    (SELECT COUNT(*) FROM articles WHERE author_id = users.id) AS article_count
FROM users;

---

二、公共表达式（CTE - Common Table Expression）

CTE 使用 WITH 子句定义一个临时的结果集，它可以让复杂的查询更清晰、更易于维护，并且支持递归查询。

2.1 基础 CTE

-- 示例：使用 CTE 重写上面的子查询示例
WITH dept_avg AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT d.dept_name, da.avg_salary
FROM dept_avg da
JOIN departments d ON da.department_id = d.id
WHERE da.avg_salary > 10000;

2.2 递归 CTE（强大的树形结构查询）

-- 示例：查询组织架构树
WITH RECURSIVE org_tree AS (
    -- 锚点成员：查询顶级节点（CEO）
    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, ot.level + 1
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree ORDER BY level, id;

---

三、窗口函数（Window Function）

窗口函数是 PostgreSQL 中最强大的特性之一，它可以在不使用 GROUP BY 的情况下，对一组数据进行聚合计算，并保留原始数据的每一行。

3.1 排名函数

-- 示例：对所有员工按工资进行排名
SELECT 
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num, -- 连续排名，无并列
    RANK() OVER (ORDER BY salary DESC) AS rank,           -- 跳跃排名，有并列
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank -- 连续排名，有并列
FROM employees;

3.2 分组排名（PARTITION BY）

-- 示例：对每个部门的员工按工资进行排名
SELECT 
    department_id,
    name,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees;

3.3 聚合窗口函数

-- 示例：计算每个员工的工资与部门平均工资的差值
SELECT 
    name,
    department_id,
    salary,
    AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary,
    salary - AVG(salary) OVER (PARTITION BY department_id) AS salary_diff
FROM employees;

3.4 移动窗口函数

-- 示例：计算用户的累计消费金额（假设存在 orders 表）
SELECT 
    user_id,
    order_date,
    amount,
    SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS cumulative_amount
FROM orders;

---

四、高级聚合函数

PostgreSQL 提供了丰富的聚合函数，除了常见的 SUM, AVG, COUNT, MAX, MIN，还有一些更高级的聚合函数。

4.1 FILTER 子句

-- 示例：统计每个用户的总订单数和已完成订单数
SELECT 
    user_id,
    COUNT(*) AS total_orders,
    COUNT(*) FILTER (WHERE status = 'completed') AS completed_orders
FROM orders
GROUP BY user_id;

4.2 ROLLUP 和 CUBE（分组汇总）

-- 示例：按部门和职位统计员工数量，并进行汇总
SELECT 
    department_id,
    job_title,
    COUNT(*) AS employee_count
FROM employees
GROUP BY ROLLUP (department_id, job_title);

---

总结

子查询、CTE、窗口函数和高级聚合函数是 PostgreSQL 高级查询的核心工具。它们能够极大地提升你处理复杂数据查询的能力，让你的 SQL 代码更加简洁、高效和易于维护。

在实际项目中，灵活运用这些技术，可以轻松应对各种复杂的业务场景，如报表统计、数据分析、排行榜生成等。下一篇，我们将深入探讨 PostgreSQL 的索引优化技术。