结构化查询语言(Structured Query Language): 分组

在本章中，我们将超越单行操作，描述针对行组的语句。在 SQL 中，这种“行组”（或行集）是通过 GROUP BY 子句构建的，并可通过 HAVING 子句进一步处理。

构建分组

首先，我们必须建立根据分组来过滤行的标准。为此，可以利用相关表中一个或多个列的内容。如果这些列的值相同，这些行属于同一组。例如，表 person 中的 lastname 列。在我们的示例中，可以假设具有相同 lastname 的人构成一个家庭。因此，如果我们希望查看家庭的信息，就应该使用此列作为分组标准。这种分组允许我们对整个组（例如家庭）提出问题，例如：

存在哪些家庭？
有多少个家庭？
每个家庭有多少人？

这些问题针对的是整个组，而不是单个行。

在 SQL 语法中，分组标准通过 GROUP BY 关键字指定，由一个或多个列名组成：

SELECT ...             -- 与通常的 SELECT 相同
FROM   ...             -- 来源表，可选地包括 JOIN
GROUP BY <列名>        -- 还可以包含多个列名
...                    -- SELECT 命令的其他可选元素
;

以下是一个关于家庭的具体示例：

SELECT lastname
FROM   person
GROUP BY lastname;

此查询从 10 行数据中提取出 7 个“家庭名称”。例如，有多个人姓 “Goldstein” 或 “de Winter”。

DISTINCT 与 GROUP BY 的比较

我们可以通过在不使用 GROUP BY 的 SELECT 中添加 DISTINCT 关键字，检索相同的 7 个“家庭名称”：

SELECT DISTINCT lastname
FROM   person;

区别在于：

DISTINCT 仅用于去除重复值，无法对结果集中的其他行和列进行计算。
GROUP BY 不仅去重，还将中间结果集分为多个组，并支持对这些组进行进一步的计算。

以下示例验证了 GROUP BY 的这一特性：

SELECT lastname, avg(weight)  -- avg() 是计算数字平均值的函数
FROM   person
GROUP BY lastname;

结果显示了 7 个家庭名称，以及每个家庭的平均体重。在单人家庭中，组的平均体重自然等于这个人的体重。

多列分组

如果需要，可以基于多列进行分组。例如，将多列的组合视为分组规则：

-- 基于一列分组：以 place_of_birth 分组，得到 6 行
SELECT place_of_birth, count(*)
FROM   person
GROUP BY place_of_birth;

-- 基于两列分组：place_of_birth 和 lastname 分组，得到 8 行，其中 Richland 和 SF 各出现两次
SELECT place_of_birth, lastname, count(*)
FROM   person
GROUP BY place_of_birth, lastname;

检查分组

定义分组后，可以提取每组的更多信息。例如，每个家庭（行组）中有多少人？

SELECT lastname, count(*)  -- count() 是统计行数的函数
FROM   person
GROUP BY lastname;

此查询显示我们的示例数据库中有一个家庭有 3 个成员，另一个家庭有 2 个成员，其余家庭各有 1 个成员。

背后执行逻辑：

提取表 person 的所有 10 行（查询中没有 WHERE 子句）。
按照 lastname 列的值将这些行分为 7 个组。
将每组的所有行传递给 SELECT 子句。
对于每组生成一行结果（在实际数据库中，每组可能包含数千行）。

由于每组只生成一行结果，无法显示组中可能变化的列值（如 firstname）。只能显示在所有行中一致的列值（如分组标准列 lastname）。

关于非分组列的信息

尽管我们无法直接显示组中变化的列，但可以通过特定函数对这些列的信息进行汇总。例如，avg() 函数对每组的数值进行平均计算：

SELECT lastname, avg(weight)
FROM   person
GROUP BY lastname;

在包含 GROUP BY 子句的命令中，avg() 函数会对每组计算一个值，而不是对所有行计算一个值。因此，可以将这些函数的结果与分组列的值一起显示。

注意：在分组查询中尝试显示非分组列（如 firstname）时，DBMS 通常会抛出错误。用户需要使用支持分组操作的聚合函数来获取相关信息。

示例说明

以下是一个使用 GROUP BY 进行分组并结合聚合函数的完整示例：

SELECT lastname, count(*) as members, avg(weight) as avg_weight
FROM   person
GROUP BY lastname
HAVING count(*) > 1  -- 仅显示有多名成员的家庭
ORDER BY avg_weight DESC;

通过这些操作，我们能够对分组数据进行深度分析并生成更有价值的统计信息。

以下是这种函数的一个不完整列表：count()、max()、min()、sum() 和 avg()。并非所有函数都属于此类，例如函数 concat()（用于连接两个字符串），它针对单行操作，每行产生一个值。

-- 使用自定义公式计算 avg()
SELECT lastname, 
       sum(weight) / count(weight) AS "Mean weight 1", 
       avg(weight) AS "Mean weight 2"
FROM   person
GROUP BY lastname;

聚焦于指定的组

我们已经了解了 WHERE 子句，它定义了哪些表行将成为结果集的一部分。HAVING 子句则在组级别上具有相同的意义，用于确定哪些组将成为结果集的一部分。

HAVING 与 GROUP BY 的语法：

SELECT ...
FROM   ...
GROUP BY <列名>
HAVING <条件>; -- 指定一个可用于组的条件

示例 1：仅检索成员数多于 1 的家庭：

SELECT lastname
FROM   person
GROUP BY lastname    -- 根据 lastname 分组
HAVING count(*) > 1; -- 组内成员数大于 1

结果中不再包含仅有一个成员的家庭。

示例 2：聚焦于名字长度超过 4 个字符的组：

SELECT lastname
FROM   person
GROUP BY lastname
HAVING max(length(firstname)) > 4; -- max() 为每个组的所有行返回一个最高值

结果显示了以下五个家庭：Baker、de Winter、Goldstein、Rich 和 Stefanos。但这些结果中并未显示名字长度大于 4 的单行记录。

请注意，上述结果与直接查询名字长度大于 4 的人的结果非常不同：

-- 名字长度超过 4 的人：结果包含 6 行
SELECT lastname, firstname
FROM   person
WHERE  length(firstname) > 4;
-- 没有 GROUP BY 和 HAVING。WHERE 不能替代 HAVING！

差异解析：在 de Winter 家庭中，有两个人的名字长度超过 4 字符：James 和 Victor。因为没有使用 GROUP BY，所以两行记录分别单独显示。

总结

HAVING 子句决定了哪些组可以成为结果集的一部分，而 WHERE 子句则是针对行的过滤操作。

完整的 SQL 子句顺序如下：

SELECT ...
FROM   ...
WHERE  ...
GROUP BY ...
HAVING   ...
ORDER BY ...
;

执行流程：

WHERE：基于行过滤数据。
GROUP BY：按指定列对数据分组。
HAVING：基于组的条件过滤数据。
ORDER BY：对结果排序。
SELECT：最终选择要显示的数据。