PostgreSQL 高级查询编写

介绍

在这个实验中，你将通过探索高级技术来提升你的 PostgreSQL 查询编写技能。你将学习在 WHERE 子句中使用子查询，以便基于另一个查询的结果来过滤数据。

本实验将指导你定义和使用公共表表达式（Common Table Expressions，CTEs），以提高查询的可读性和模块化。此外，你还将应用窗口函数（window functions），如 ROW_NUMBER，来跨行集合执行计算。最后，你将掌握使用 GROUP BY 和 HAVING 子句来分组和过滤数据，从而从你的数据集中提取有意义的见解。

Skills Graph

在 WHERE 子句中编写子查询

在这一步中，你将学习如何在 SQL 查询的 WHERE 子句中使用子查询。子查询，也称为内部查询或嵌套查询，是嵌入在另一个查询中的查询。子查询用于返回将在主查询中用作条件的数据，以进一步限制要检索的数据。

理解 WHERE 子句中的子查询

WHERE 子句中的子查询通常用于将列的值与子查询的结果进行比较。子查询首先执行，然后其结果被外部查询使用。

基本语法：

SELECT column1, column2
FROM table_name
WHERE column_name IN (SELECT column_name FROM another_table WHERE condition);

在这里，子查询 (SELECT column_name FROM another_table WHERE condition) 返回一组值。然后，外部查询从 table_name 中选择 column_name 在该集合中的行。

场景：

假设你有两个表：employees（员工）和 departments（部门）。employees 表包含有关员工的信息，包括他们的 employee_id（员工 ID）、employee_name（员工姓名）和 department_id（部门 ID）。departments 表包含有关部门的信息，包括他们的 department_id（部门 ID）和 department_name（部门名称）。

我们想要找到所有在 'Sales'（销售）部门工作的员工。

步骤 1：创建表并插入数据

首先，使用 postgres 用户连接到 PostgreSQL 数据库：

sudo -u postgres psql

接下来，创建 departments 表：

CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(50)
);

将一些示例数据插入到 departments 表中：

INSERT INTO departments (department_name) VALUES
('Sales'),
('Marketing'),
('Engineering');

现在，创建 employees 表：

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(50),
    department_id INTEGER REFERENCES departments(department_id)
);

将一些示例数据插入到 employees 表中：

INSERT INTO employees (employee_name, department_id) VALUES
('Alice', 1),
('Bob', 2),
('Charlie', 1),
('David', 3),
('Eve', 2);

步骤 2：编写子查询

现在，让我们编写查询，以使用 WHERE 子句中的子查询来查找所有在 'Sales'（销售）部门工作的员工。

SELECT employee_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = 'Sales');

解释：

• 子查询 (SELECT department_id FROM departments WHERE department_name = 'Sales') 从 departments 表中选择 department_name 为 'Sales' 的 department_id。在这种情况下，它将返回 1。
• 然后，外部查询从 employees 表中选择 department_id 在子查询返回的集合（仅为 1）中的 employee_name。

步骤 3：执行查询并查看结果

在你的 psql 终端中执行查询。你应该看到以下输出：

 employee_name
---------------
 Alice
 Charlie
(2 rows)

这表明 Alice 和 Charlie 是在 'Sales'（销售）部门工作的员工。

步骤 4：将 EXISTS 与子查询一起使用

在 WHERE 子句中使用子查询的另一种方法是使用 EXISTS 运算符。 EXISTS 运算符测试子查询中行的存在。如果子查询返回任何行，则返回 true，否则返回 false。

以下是使用 EXISTS 获得相同结果的示例：

SELECT employee_name
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.department_id = e.department_id
    AND d.department_name = 'Sales'
);

此查询实现与前一个查询相同的结果，但使用 EXISTS 运算符而不是 IN。

解释：

• 子查询 SELECT 1 FROM departments d WHERE d.department_id = e.department_id AND d.department_name = 'Sales' 检查是否存在一个部门名称为 'Sales'，并且具有与当前员工相同的 department_id。
• 如果子查询返回任何行（表示存在一个 'Sales' 部门，其 department_id 相同），则 EXISTS 运算符返回 true，并选择员工的姓名。

在你的 psql 终端中执行查询。你应该看到与之前相同的输出：

 employee_name
---------------
 Alice
 Charlie
(2 rows)

你现在已成功地在 WHERE 子句中使用子查询，以根据另一个表中的条件来过滤数据。你还学习了如何将 EXISTS 运算符与子查询一起使用。

定义和使用 CTE

在这一步中，你将学习如何在 PostgreSQL 中定义和使用公共表表达式（Common Table Expression，CTE）。CTE 是一个临时的、命名的结果集，你可以在单个 SELECT、INSERT、UPDATE 或 DELETE 语句中引用它。CTE 有助于将复杂的查询分解为更简单、更易读的部分。

理解 CTE

CTE 使用 WITH 子句定义。它们仅在查询执行期间存在。

基本语法：

WITH cte_name AS (
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT column1, column2
FROM cte_name
WHERE condition;

在这里，cte_name 是你给 CTE 的名称。括号内的 SELECT 语句定义了 CTE 的结果集。然后，外部 SELECT 语句查询 CTE，就像它是一个常规表一样。

场景：

继续使用上一步中的 employees（员工）和 departments（部门）表，让我们使用 CTE 查找员工姓名及其对应的部门名称。

步骤 1：验证表和数据

确保 employees 和 departments 表存在，并且包含上一步中的数据。你可以通过在 psql 终端中运行以下查询来验证这一点：

SELECT * FROM departments;
SELECT * FROM employees;

如果表或数据丢失，请参阅上一步以创建它们并插入数据。

步骤 2：定义 CTE

现在，让我们定义一个名为 EmployeeDepartments 的 CTE，它连接 employees 和 departments 表以检索员工姓名及其部门名称。

WITH EmployeeDepartments AS (
    SELECT
        e.employee_name,
        d.department_name
    FROM
        employees e
    JOIN
        departments d ON e.department_id = d.department_id
)
SELECT employee_name, department_name
FROM EmployeeDepartments;

解释：

• WITH EmployeeDepartments AS (...) 子句定义了名为 EmployeeDepartments 的 CTE。
• 括号内的 SELECT 语句将 employees 表（别名为 e）与 departments 表（别名为 d）在 department_id 列上连接起来。
• 然后，外部 SELECT 语句从 EmployeeDepartments CTE 中检索 employee_name 和 department_name。

步骤 3：执行查询并查看结果

在你的 psql 终端中执行查询。你应该看到以下输出：

 employee_name | department_name
---------------+-----------------
 Alice         | Sales
 Bob           | Marketing
 Charlie       | Sales
 David         | Engineering
 Eve           | Marketing
(5 rows)

这显示了所有员工的姓名及其对应的部门名称。

步骤 4：使用 CTE 进行过滤

你还可以使用 CTE 来过滤数据。例如，让我们使用 EmployeeDepartments CTE 查找所有在 'Sales'（销售）部门工作的员工。

WITH EmployeeDepartments AS (
    SELECT
        e.employee_name,
        d.department_name
    FROM
        employees e
    JOIN
        departments d ON e.department_id = d.department_id
)
SELECT employee_name
FROM EmployeeDepartments
WHERE department_name = 'Sales';

解释：

• 此查询与前一个查询类似，但它在外部 SELECT 语句中添加了一个 WHERE 子句，以过滤结果，仅包括在 'Sales'（销售）部门工作的员工。

在你的 psql 终端中执行查询。你应该看到以下输出：

 employee_name
---------------
 Alice
 Charlie
(2 rows)

这表明 Alice 和 Charlie 是在 'Sales'（销售）部门工作的员工。

你现在已成功定义和使用 CTE 来连接表和过滤数据。CTE 可以大大提高复杂 SQL 查询的可读性和可维护性。

应用窗口函数（例如，ROW_NUMBER）

在这一步中，你将学习如何在 PostgreSQL 中应用窗口函数。窗口函数对与当前行相关的一组表行执行计算。它们类似于聚合函数，但与聚合函数不同，窗口函数不会将行分组到单个输出行中。相反，它们为结果集中的每一行提供一个值。

理解窗口函数

窗口函数使用 OVER() 子句来定义用于计算的行窗口。 OVER() 子句可以包含 PARTITION BY 和 ORDER BY 子句，以进一步定义窗口。

基本语法：

SELECT
    column1,
    column2,
    WINDOW_FUNCTION(column3) OVER (
        [PARTITION BY column4]
        [ORDER BY column5]
    ) AS alias_name
FROM
    table_name;

• WINDOW_FUNCTION：窗口函数的名称（例如，ROW_NUMBER、RANK、SUM、AVG）。
• OVER()：指定函数在其上运行的窗口。
• PARTITION BY：将行划分为分区，并且窗口函数独立应用于每个分区。
• ORDER BY：定义每个分区中行的顺序。
• alias_name：计算出的窗口函数结果的别名。

场景：

继续使用前几步中的 employees（员工）和 departments（部门）表，让我们使用 ROW_NUMBER() 窗口函数，根据员工的 employee_name（员工姓名），在其各自的部门内为每个员工分配一个唯一的排名。

步骤 1：应用 ROW_NUMBER() 窗口函数

现在，让我们编写一个查询，该查询使用 ROW_NUMBER() 窗口函数来为每个部门内的每个员工分配一个排名。

SELECT
    employee_name,
    department_id,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY employee_name) AS employee_rank
FROM
    employees;

解释：

• ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY employee_name)：这是窗口函数。
  • ROW_NUMBER()：为窗口中的每一行分配一个唯一的顺序整数。
  • PARTITION BY department_id：根据 department_id（部门 ID）将行划分为分区。这意味着将为每个部门单独进行排名。
  • ORDER BY employee_name：指定每个分区中行的排名顺序。在这种情况下，员工按其 employee_name（员工姓名）的字母顺序排名。
• employee_rank：这是赋予窗口函数结果的别名。

步骤 2：执行查询并查看结果

在你的 psql 终端中执行查询。你应该看到以下输出：

 employee_name | department_id | employee_rank
---------------+---------------+---------------
 Alice         |             1 |             1
 Charlie       |             1 |             2
 Bob           |             2 |             1
 Eve           |             2 |             2
 David         |             3 |             1
(5 rows)

这显示了每个员工的姓名、他们的部门 ID 以及他们在部门内的排名。例如，Alice 在部门 1 中排名第 1，而 Charlie 在部门 1 中排名第 2。

步骤 3：将窗口函数与 CTE 结合使用

你还可以在 CTE 中使用窗口函数，以使你的查询更有条理。让我们使用 CTE 重写先前的查询。

WITH RankedEmployees AS (
    SELECT
        employee_name,
        department_id,
        ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY employee_name) AS employee_rank
    FROM
        employees
)
SELECT
    employee_name,
    department_id,
    employee_rank
FROM
    RankedEmployees;

此查询产生与先前查询相同的结果，但是它使用 CTE 来封装窗口函数计算。

在你的 psql 终端中执行查询。你应该看到与之前相同的输出。

使用 GROUP BY 和 HAVING 进行分组和过滤

在这一步中，你将学习如何在 PostgreSQL 中使用 GROUP BY 和 HAVING 子句来分组行和过滤分组后的结果。 GROUP BY 子句将指定列中具有相同值的行分组为摘要行，例如查找每个部门中的员工人数。 HAVING 子句用于根据指定的条件过滤这些分组的行。

理解 GROUP BY 和 HAVING

• GROUP BY： 此子句将一个或多个列中具有相同值的行分组为摘要行。你通常使用聚合函数（例如，COUNT、SUM、AVG、MIN、MAX）来计算每个组的值。
• HAVING： 此子句过滤由 GROUP BY 子句创建的组。它类似于 WHERE 子句，但它作用于组而不是单个行。

基本语法：

SELECT
    column1,
    aggregate_function(column2)
FROM
    table_name
WHERE
    condition
GROUP BY
    column1
HAVING
    aggregate_function(column2) condition;

• column1：要分组的列。
• aggregate_function(column2)：应用于每个组的 column2 的聚合函数。
• WHERE：在分组之前过滤行。
• GROUP BY：根据 column1 中的值对行进行分组。
• HAVING：在分组之后，根据聚合函数的结果过滤组。

场景：

继续使用 employees（员工）和 departments（部门）表，让我们使用 GROUP BY 和 HAVING 查找拥有超过 1 个员工的部门。

步骤 1：按部门分组并计算员工人数

首先，让我们编写一个查询，按部门对员工进行分组，并计算每个部门中的员工人数。

SELECT
    d.department_name,
    COUNT(e.employee_id) AS employee_count
FROM
    employees e
JOIN
    departments d ON e.department_id = d.department_id
GROUP BY
    d.department_name;

解释：

• SELECT d.department_name, COUNT(e.employee_id) AS employee_count：这会选择部门名称和每个部门的员工 ID 计数。
• FROM employees e JOIN departments d ON e.department_id = d.department_id：这会在 department_id（部门 ID）列上连接 employees（员工）和 departments（部门）表。
• GROUP BY d.department_name：这会按部门名称对行进行分组，因此 COUNT() 函数将计算每个部门中的员工人数。

步骤 2：执行查询并查看结果

在你的 psql 终端中执行查询。你应该看到以下输出：

 department_name | employee_count
-----------------+----------------
 Engineering     |              1
 Marketing       |              2
 Sales           |              2
(3 rows)

这显示了每个部门中的员工人数。

步骤 3：使用 HAVING 进行过滤

现在，让我们添加一个 HAVING 子句来过滤结果，仅包括拥有超过 1 个员工的部门。

SELECT
    d.department_name,
    COUNT(e.employee_id) AS employee_count
FROM
    employees e
JOIN
    departments d ON e.department_id = d.department_id
GROUP BY
    d.department_name
HAVING
    COUNT(e.employee_id) > 1;

解释：

• 此查询与前一个查询相同，但是它添加了一个 HAVING 子句：
  • HAVING COUNT(e.employee_id) > 1：这会过滤组，仅包括员工 ID 计数大于 1 的部门。

步骤 4：执行查询并查看结果

在你的 psql 终端中执行查询。你应该看到以下输出：

 department_name | employee_count
-----------------+----------------
 Marketing       |              2
 Sales           |              2
(2 rows)

这仅显示了拥有超过 1 个员工的部门（Marketing 和 Sales）。

你现在已成功使用 GROUP BY 和 HAVING 子句来分组行，并根据条件过滤分组后的结果。

总结

在这个实验中，你学习了如何编写高级 PostgreSQL 查询。你首先使用 WHERE 子句中的子查询，根据另一个查询的结果来过滤数据。这涉及到理解子查询的语法和应用，以便将列值与内部查询返回的一组值进行比较。

然后，你继续定义和利用公共表表达式（Common Table Expressions, CTE），以提高查询的可读性和模块化。CTE 允许你将复杂的查询分解为更简单、更易于管理的部分。

接下来，你应用了诸如 ROW_NUMBER 之类的窗口函数，以跨行集执行计算。窗口函数类似于聚合函数，但是它们为结果集中的每一行提供一个值，而不是将行分组到单个输出行中。

最后，你掌握了使用 GROUP BY 和 HAVING 子句对数据进行分组和过滤，从而从数据集中提取有意义的见解。 GROUP BY 子句将指定列中具有相同值的行分组为摘要行，而 HAVING 子句根据指定的条件过滤这些分组的行。