PostgreSQL - GROUP BY

PostgreSQL - GROUP BY 子句

PostgreSQL 的 GROUP BY 子句是一个强大的功能，它与 SELECT 语句结合使用，用于将一个或多个列中具有相同值的多行收集并汇总为单个摘要行。其主要目的是对这些分组执行聚合计算，例如计数、求和或求平均值。

在 SELECT 查询中，GROUP BY 子句位于 WHERE 子句之后，HAVING 或 ORDER BY 子句之前。

核心概念

要有效使用 GROUP BY，您必须理解两个关键组成部分：

分组列 (Grouping Columns)： 这些是用于创建分组的列。所有在分组列中具有相同值的行都将归入一个组。
聚合函数 (Aggregate Functions)： 这些函数对一组值执行计算并返回单个值。常见的聚合函数包括 COUNT()（计数）、SUM()（求和）、AVG()（求平均值）、MAX()（最大值）和 MIN()（最小值）。

语法

GROUP BY 子句的基本语法如下：

SELECT
    column1,
    column2,
    aggregate_function(column3)
FROM
    table_name
WHERE
    [conditions]
GROUP BY
    column1, column2
ORDER BY
    column1, column2;

GROUP BY 的黄金法则： SELECT 列表中任何未包含在聚合函数中的列，必须包含在 GROUP BY 子句中。否则将导致语法错误。

实际示例

我们使用一个更实用的 employees 表作为示例。假设它具有以下结构和数据：

-- 表：employees
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary NUMERIC(10, 2)
);

INSERT INTO employees (name, department, salary) VALUES
('Alice', 'Engineering', 90000),
('Bob', 'Engineering', 85000),
('Charlie', 'HR', 60000),
('David', 'Sales', 75000),
('Eve', 'Sales', 78000),
('Frank', 'HR', 62000);

现在，让我们找出每个部门的员工数量、平均工资和总工资。

SELECT
    department,
    COUNT(*) AS number_of_employees,
    AVG(salary)::NUMERIC(10, 2) AS average_salary,
    SUM(salary) AS total_salary_pool
FROM
    employees
GROUP BY
    department
ORDER BY
    department;

此查询生成一个清晰的聚合摘要：

 department  | number_of_employees | average_salary | total_salary_pool
-------------+-----------------------+----------------+---------------------
 Engineering |                     2 |       87500.00 |            175000.00
 HR          |                     2 |       61000.00 |            122000.00
 Sales       |                     2 |       76500.00 |            153000.00
(3 rows)

使用 HAVING 过滤分组

虽然 WHERE 子句在分组之前过滤行，但 HAVING 子句在聚合之后过滤分组。这对于询问有关分组本身的问题非常有用。

例如，让我们只查找平均工资大于 $70,000 的部门。

SELECT
    department,
    AVG(salary)::NUMERIC(10, 2) AS average_salary
FROM
    employees
GROUP BY
    department
HAVING
    AVG(salary) > 70000
ORDER BY
    average_salary DESC;

结果现在将只包含满足 HAVING 条件的部门：

 department  | average_salary
-------------+----------------
 Engineering |       87500.00
 Sales       |       76500.00
(2 rows)

常见陷阱和调试

错误：列 ”…” 必须出现在 GROUP BY 子句中或用于聚合函数中。 这是最常见的错误。这意味着您的 SELECT 列表中有一个列既不是聚合函数，也不在 GROUP BY 列表中。将其添加到 GROUP BY 子句即可解决问题。
混淆 WHERE 与 HAVING： 请记住，WHERE 在分组之前过滤单行。HAVING 在聚合之后过滤整个组。您不能在 WHERE 子句中使用 SUM() 等聚合函数。