PostgreSQL GROUP BY 用法与实例

在 PostgreSQL 中， GROUP BY 子句用于将行根据指定的字段或者表达式进行分组。

有时候，我们需要将结果集按照某个维度进行汇总。这在统计数据的时候经常用到，考虑以下的场景：

按班级求取平均成绩。
按学生汇总总分。
按年或者月份统计销售额。
按国家或者地区统计用户数量。

这些正是 GROUP BY 子句发挥作用的地方。

PostgreSQL `GROUP BY` 语法

GROUP BY 子句是 SELECT 语句的可选子句。要对 SELECT 语句中的行进行分组，请按照如下语法使用 GROUP BY 子句：

SELECT column1[, column2, ...], aggregate_function(ci)
FROM table
[WHERE clause]
GROUP BY column1[, column2, ...];
[HAVING clause]

说明：

column1[, column2, ...] 是分组依据的字段，至少一个字段，可以多个字段。
aggregate_function(ci) 是聚合函数，用来汇总。这是可选的。您可以会用到以下聚合函数：
- sum(): 计算组内数据的总和
- avg(): 计算组内数据的平均值
- max(): 计算组内数据的最大值
- MIN(): 计算组内数据的最小值
- count(): 计算组内的行数
SELECT 后的字段必须是分组字段中的字段。
WHERE 子句是可选的，用来过在分组之前过滤行。
HAVING 子句是可选的，用来过滤分组数据。

PostgreSQL `GROUP BY` 实例

我们将使用 Sakila 示例数据库中的表进行演示，请您先在 PostgreSQL 中安装 Sakila 示例数据库。

简单的 GROUP BY 实例

我们使用 GROUP BY 子句查看 actor 表中的姓氏列表。

SELECT last_name
FROM actor
GROUP BY last_name;

  last_name
--------------
 AKROYD
 BRIDGES
 HUNT
 GIBSON
 ALLEN
 SUVARI
 HESTON
 MONROE
 ...
 WILSON
(121 rows)

本例中，使用 GROUP BY 子句按照 last_name 字段对数据进行分组。

本例的输出结果与以下使用 DISTINCT 的 SQL 输出结果完全一样：

SELECT DISTINCT last_name FROM actor;

GROUP BY 与聚合函数实例

我们使用 GROUP BY 子句和聚合函数 count() 查看 actor 表中的姓氏列表以及每个姓氏的数量。

SELECT last_name, count(*)
FROM actor
GROUP BY last_name
ORDER BY count(*) DESC;

  last_name   | count
--------------+-------
 KILMER       |     5
 TEMPLE       |     4
 NOLTE        |     4
 WILLIAMS     |     3
 PECK         |     3
 HOPKINS      |     3
 DAVIS        |     3
 HARRIS       |     3
 DEGENERES    |     3
 ...
 CLOSE        |     1
(121 rows)

本例中，执行的顺序如下：

首先使用 GROUP BY 子句按照 last_name 字段对 actor 表中的所有的行进行分组。也就是每个姓氏一组。
然后使用聚合函数 count(*) 汇总每个姓氏的行数。
最后使用 ORDER BY 子句按照 count(*) 降序排列。这样，数量最多的姓氏排在最前面。

同样，如果我们想从 film 表中查找每个影片等级的影片数量，请使用如下语句：

SELECT rating, count(*)
FROM film
GROUP BY rating
ORDER BY count(*) DESC;

 rating | count
--------+-------
 PG-13  |   223
 NC-17  |   210
 R      |   195
 PG     |   194
 G      |   178
(5 rows)

GROUP BY, LIMIT, 聚合函数实例

以下实例使用 GROUP BY 子句，LIMIT 子句和聚合函数 sum() 从 payment 表中查找消费金额排名前 10 位的客户。

SELECT customer_id, sum(amount) total
FROM payment
GROUP BY customer_id
ORDER BY total DESC
LIMIT 10;

 customer_id | total
-------------+--------
         526 | 221.55
         148 | 216.54
         144 | 195.58
         178 | 194.61
         137 | 194.61
         459 | 186.62
         469 | 177.60
         468 | 175.61
         236 | 175.58
         181 | 174.66
(10 rows)

本例中，执行的顺序如下：

首先使用 GROUP BY 子句按照 customer_id 字段对数据进行分组，也就是按照客户分组。
然后使用聚合函数 sum(amount) 对每组中的所有行的 amount 字段求和，并使用 total 作为列别名。
然后使用 ORDER BY 子句按照 total 降序排列。
最后使用 LIMIT 10 子句返回前 10 个记录行。

GROUP BY 和 HAVING 实例

以下实例使用 GROUP BY 子句，HAVING 子句和聚合函数 sum() 从 payment 表中查找总消费金额在 180 美元以上的客户。

SELECT customer_id, sum(amount) total
FROM payment
GROUP BY customer_id
HAVING sum(amount) > 180
ORDER BY total DESC;

 customer_id | total
-------------+--------
         526 | 221.55
         148 | 216.54
         144 | 195.58
         178 | 194.61
         137 | 194.61
         459 | 186.62
(6 rows)

本例中，执行的顺序如下：

首先使用 GROUP BY 子句按照 customer_id 字段对数据进行分组，也就是按照客户分组。
然后使用聚合函数 sum(amount) 对每组中的所有行的 amount 字段求和，并使用 total 作为列别名。
然后使用 HAVING 子句指定只有 sum(amount) 大于 180 的行才会被返回。
最后使用 ORDER BY 子句按照 total 降序排列。

HAVING 子句用来过滤 GROUP BY 分组的数据，需要一个逻辑表达式作为条件，其中逻辑表达式中的列名或表达式只能使用分组使用的列，表达式，或者应用于分组列或表达式的聚合函数。

结论

在本文中，我们介绍了在 PostgreSQL 中使用 GROUP BY 子句将结果集根据指定的列或者表达式进行分组。以下是 GROUP BY 子句的要点：

GROUP BY 子句用于将结果集根据指定的字段或者表达式进行分组。
GROUP BY 子句至少需要一个分组字段或表达式。
HAVING 子句是可选的，它用来过滤分组数据。
GROUP BY 子句经常用于数据统计汇总，通常使用聚合函数。

PostgreSQL GROUP BY 语法

PostgreSQL GROUP BY 实例

简单的 GROUP BY 实例

GROUP BY 与聚合函数实例

GROUP BY, LIMIT, 聚合函数实例

GROUP BY 和 HAVING 实例

结论

本页目录

PostgreSQL `GROUP BY` 语法

PostgreSQL `GROUP BY` 实例