第 10 章：聚合函数与分组 🧮 - PostgreSQL入门

摘要：在之前的章节里，我们学习的都是如何处理单行数据——查询它、修改它、给它排序。但数据的真正威力，往往体现在聚合之中。

欢迎进入 SQL 的新篇章！

在之前的章节里，我们学习的都是如何处理单行数据——查询它、修改它、给它排序。但数据的真正威力，往往体现在聚合之中。

我们想知道的常常是这样的问题：

“我们一共有多少个朋友？”“我们认识最久的朋友，认识了多少年？”“我们朋友的平均年龄是多少？”“每个认识年份下，分别有几个朋友？”函数描述COUNT计算行数或非空值的数量。SUM计算一组数值的总和。AVG计算一组数值的平均值。MAX找出一组值中的最大值。MIN找出一组值中的最小值。

示例 1：我们一共有多少个朋友？
COUNT(*) 是最常用的，它会计算表中的总行数。

SELECT COUNT(*) AS "朋友总数" FROM friends;

结果：

朋友总数 6(1 row)

示例 2：我们认识朋友的总年数是多少？
SUM 会对指定的数值列进行求和。

SELECT SUM(years_known) AS "认识总年数" FROM friends;

结果（3+3+5+11+16+16 = 54）：

认识总年数 54(1 row)

示例 3：我们认识朋友最长和最短的年限分别是多少？
MAX 和 MIN 非常直观。

SELECT MAX(years_known) AS "最长年限", MIN(years_known) AS "最短年限"FROM friends;

结果：

最长年限 | 最短年限+ 16 | 3(1 row)

关于 COUNT 的一点说明:

COUNT(*): 计算所有行数，不管里面有没有 NULL 值。COUNT(column_name): 只计算指定列中非 NULL 值的数量。COUNT(DISTINCT column_name): 计算指定列中不重复的非 NULL 值的数量。

比如，SELECT COUNT(DISTINCT years_known) FROM friends; 会返回 4 (3, 5, 11, 16)。

聚合函数本身已经很强大了，但它们的真正威力需要和 GROUP BY 子句结合才能完全释放。

GROUP BY 可以将表中的行，按照某一列或多列的值进行分组，所有在这些列上具有相同值的行会被划分到同一个组里。然后，聚合函数就可以对每个组分别进行计算了！

基本语法:
GROUP BY 子句在 WHERE 之后，ORDER BY 之前。

SELECT column1, aggregate_function(column2)FROM table_nameWHERE conditionGROUP BY column1ORDER BY ...;

黄金法则 ⭐：当一个 SELECT 语句中同时包含聚合函数（如 SUM, COUNT）和普通列时，所有不在聚合函数里的普通列，都必须出现在 GROUP BY 子句中！

示例 4：统计每个“认识年数”下分别有几个朋友
这个问题，只用聚合函数是无法回答的。我们需要先按 years_known 进行分组。

SELECT years_known, COUNT(*) AS friend_countFROM friendsGROUP BY years_knownORDER BY years_known;

执行过程揭秘 :

FROM friends: 首先，拿到 friends 表的所有数据。GROUP BY years_known: 数据库开始分组。years_known 为 3 的行（李雷，韩梅梅）被分到一组。years_known 为 5 的行（老王）被分到一组。years_known 为 11 的行（张三）被分到一组。years_known 为 16 的行（赵四，刘能）被分到一组。SELECT years_known, COUNT(*): 现在，对上面形成的每一个组，分别执行 SELECT。对于“3年组”，years_known 是 3，COUNT(*) 是 2。对于“5年组”，years_known 是 5，COUNT(*) 是 1。…以此类推。ORDER BY years_known: 最后对生成的结果进行排序。

结果：

years_known | friend_count+ 3 | 2 5 | 1 11 | 1 16 | 2(4 rows)

看，我们得到了一个非常有价值的统计报告！

我们已经能按年分组统计人数了。现在，如果我想提出一个更进一步的问题：“只看那些人数超过 1 人的分组”，该怎么办？

我们可能会想当然地写：

-- 这是一个错误的示范！SELECT years_known, COUNT(*)FROM friendsGROUP BY years_knownWHERE COUNT(*) > 1; -- 错误！

执行它，数据库会无情报错！为什么？

因为 WHERE 子句是在分组之前（GROUP BY 之前）对原始的单行数据进行过滤的。在 WHERE 执行的时候，COUNT(*) 这个聚合结果根本还没算出来呢！

为了解决这个问题，SQL 提供了 HAVING 子句。

HAVING 子句专门用来对 GROUP BY 之后形成的分组结果进行过滤。

正确写法:

SELECT years_known, COUNT(*) AS friend_countFROM friendsGROUP BY years_knownHAVING COUNT(*) > 1ORDER BY years_known;