了解 PostgreSQL 中的 GROUP BY（附示例）

作者：Sarah Conway

无论您使用的是 PostgreSQL 还是 TimescaleDB，使用 GROUP BY 子句的行为完全相同：它允许您从 SELECT 语句 中以一列或多列为一组返回行。它会将所有列出的列中具有相同值的所有行分组到一个组行中。这将消除任何冗余，并且在与聚合函数（例如 SUM()、AVG()、COUNT()、MIN() 或 MAX()）一起使用时特别有用。

GROUP BY 的行为与窗口函数非常相似，但有一个显著区别：使用 GROUP BY 时，行会被折叠成组，因此无法再单独访问每个字段。 窗口函数 允许您引用每个特定记录的信息，以及查看窗口函数的结果。

简而言之，当您希望消除重复行、聚合结果集以及在执行计算之前压缩行时，您需要使用 GROUP BY。

作为整个 SELECT 语句的一部分，此子句的（简化）语法如下

SELECT <expressions> 
FROM <tables> 
GROUP BY <condition>;

GROUP BY 子句本身的完整语法为

GROUP BY [ ALL | DISTINCT ] grouping_element [, ...]

列的指定顺序无关紧要。

当然，此子句的使用也可以是复杂的 SELECT 语句的一部分。以下完整语法可用作参考

SELECT [ ALL | DISTINCT | DISTINCT ON (<distinct_expressions>) ]
<expressions>
FROM <tables>
[WHERE <conditions>]
[GROUP BY <expressions>]
[HAVING <condition>]
[ORDER BY <expression> [ ASC | DESC | USING <operator> ] [ NULLS FIRST FIRST | NULLS LASTLAST ]]
[LIMIT [ <number_rows> | ALL]
[OFFSET OFFSET <offset_value> [ ROW | ROWS ]]
[FETCH FETCH { FIRST FIRST | NEXT } [ <fetch_rows> ] { ROW | ROWS } ONLY]
[FOR { UPDATE | SHARE } OF <table> [ NOWAIT ]];

您可以在 SELECT 语句中将 GROUP BY 与任何可能的子句组合使用。它在 FROM 和 WHERE 子句之后、HAVING、DISTINCT、ORDER BY 和 LIMIT 之前进行评估。

示例

以下示例使用一个名为 customers 的基本表，其中包含以下虚构数据

在不使用聚合函数的情况下使用 PostgreSQL GROUP BY

GROUP BY 子句的最简单形式是在单个表上使用它而不使用聚合函数，如下所示

SELECT customer_id FROM orders GROUP BY customer_id;

当以这种方式使用时，它的工作方式类似于 SELECT DISTINCT，因为它将从返回的结果集中删除重复的行。在我们的例子中，我们选择返回所有订单并按 customer_id 进行分组；这意味着我们实际上是选择只返回已下订单的客户的 customer_id。

结果

注意：以上述方式使用 GROUP BY 而不将其与聚合函数配对时，为了便于阅读，建议改用 SELECT DISTINCT，因为它在功能上服务于相同的目的，但措辞更简洁。例如，上述查询将被大大简化，并改写为 SELECT DISTINCT customer_id FROM orders;

将 PostgreSQL GROUP BY 与聚合函数一起使用

聚合函数可以与 GROUP BY 配对使用，以获得用于分析或信息目的的快速且精简的结果集。检索这些汇总和计算的结果是 GROUP BY 的主要用例。

一个按单列对结果进行分组的简单示例是计算每个州的客户数量

SELECT COUNT(customer_id), state FROM customers GROUP BY state;

结果

将 PostgreSQL GROUP BY 与 JOIN 一起使用

要列出每个客户下的订单数量，您可以连接两个表并按客户姓名对结果进行分组。在本例中，创建了一个名为 orders 的表，其中包含以下虚构数据

我们将选择每个客户的名字和姓氏的列，以及每个客户的订单数量，并使用 GROUP BY 对多列中的这些结果进行排序。

SELECT customers.firstname, customers.lastname, COUNT(orders.order_id)
FROM orders
LEFT JOIN customers ON orders.customer_id = customers.customer_id
GROUP BY firstname, lastname;

结果

将 PostgreSQL GROUP BY 与 JOIN 和 ORDER BY 一起使用

继续以上一个示例为基础，我们还可以通过添加 ORDER BY 按升序或降序对结果进行排序。升序 (ASC) 顺序是默认顺序。否则，您可以指定 DESC，以便在结果集中首先显示下订单最多的客户，如下所示

SELECT customers.firstname, customers.lastname, COUNT(orders.order_id)
FROM orders
LEFT JOIN customers ON orders.customer_id = customers.customer_id
GROUP BY firstname, lastname
ORDER BY count DESC;

注意：如果尝试运行类似于此的查询以进行分析，则使用 LIMIT 子句也可能有助于获得性能更高、效率更高的结果。

结果

将 PostgreSQL GROUP BY 与 HAVING 一起使用

要筛选分组的行，您可以指定 HAVING 子句，以从结果集中删除不满足指定条件的组。此子句出现在所有数据分组和聚合之后，这意味着在返回所有行之后，不满足指定条件的记录将从结果集中删除。

作为引用我们已经创建的两个表和我们已经尝试过的查询的示例，我们可以组合先前的查询以返回每个州下订单超过一个的客户数量

SELECT customers.firstname, customers.lastname, customers.state, COUNT(orders.order_id) AS total_number_of_orders 
FROM customers 
LEFT JOIN orders 
ON customers.customer_id = orders.customer_id 
GROUP BY firstname, lastname, state 
HAVING COUNT(orders.order_id) > 1;

结果

将 PostgreSQL GROUP BY 与 WHERE 一起使用

那 WHERE 子句呢？在 SELECT 语句中，需要在 GROUP BY 之前使用它，因为在执行 HAVING 之前，记录会使用 WHERE 进行过滤。因此，与 HAVING 相比，使用 WHERE 会导致更快的结果。这两个语句可以在同一个查询中使用；但是，如果 WHERE 子句本身就可以提供所需的结果，则应优先使用此子句以确保高效设计的查询。

如果我们想查找名称与“Cheyenn”密切匹配的客户的订单数量，我们将使用以下 SQL 语句

SELECT customers.firstname, customers.lastname, customers.state, COUNT(orders.order_id) AS total_number_of_orders 
FROM customers 
LEFT JOIN orders 
ON customers.customer_id = orders.customer_id 
WHERE firstname LIKE 'Cheyen%' 
GROUP BY firstname, lastname, state;

结果

后续步骤

要详细了解 GROUP BY 子句及其在 PostgreSQL 中的使用方法，您可以查看 PostgreSQL 关于 SELECT（以及 GROUP BY 子句）和 聚合表达式 的文档。表表达式 部分的文档也包含与 GROUP BY 子句相关的信息。

通过将 GROUP BY 与 GROUPING SETS、CUBE 和 ROLLUP 相结合，可以实现更复杂的操作。有关这三个概念的更多信息，请参阅 PostgreSQL 官方文档。

如果您想在原生优化的、高性能的 PostgreSQL 数据库中体验 GROUP BY，同时享受自动数据分区和列式压缩带来的性能提升和存储空间减少的优势，请 立即创建免费的 Timescale 帐户。