Redrock Postgres 搜索 英文
版本: 9.3 / 9.4 / 9.5 / 9.6 / 10 / 11 / 12 / 13 / 14 / 15 / 16

2.7. 聚合函数 #

与大多数其他关系数据库产品一样,PostgreSQL 支持聚合函数。聚合函数从多个输入行计算单个结果。例如,有一些聚合函数可以计算一组行上的countsumavg(平均值)、max(最大值)和min(最小值)。

作为一个示例,我们可以使用以下方法找到任何地方的最高低温读数:

SELECT max(temp_lo) FROM weather;
 max
-----
  46
(1 row)

如果我们想知道该读数出现在哪个城市(或哪些城市),我们可能会尝试

SELECT city FROM weather WHERE temp_lo = max(temp_lo);     WRONG

但这不起作用,因为聚合max不能在WHERE子句中使用。(此限制存在,因为WHERE子句决定了哪些行将包含在聚合计算中;因此,显然需要在计算聚合函数之前对其进行评估。)但是,通常情况下,可以重新表述查询以实现所需结果,这里使用子查询

SELECT city FROM weather
    WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
     city
---------------
 San Francisco
(1 row)

这是可以的,因为子查询是一个独立的计算,它独立于外部查询计算自己的聚合。

聚合与GROUP BY子句结合使用时也非常有用。例如,我们可以使用以下方法获取每个城市中读数的数量和观测到的最高低温:

SELECT city, count(*), max(temp_lo)
    FROM weather
    GROUP BY city;
     city      | count | max
---------------+-------+-----
 Hayward       |     1 |  37
 San Francisco |     2 |  46
(2 rows)

它为我们提供每个城市的输出行。每个聚合结果都是根据与该城市匹配的表行计算的。我们可以使用HAVING过滤这些分组的行

SELECT city, count(*), max(temp_lo)
    FROM weather
    GROUP BY city
    HAVING max(temp_lo) < 40;
  city   | count | max
---------+-------+-----
 Hayward |     1 |  37
(1 row)

它为我们提供相同的结果,仅适用于所有temp_lo值低于 40 的城市。最后,如果我们只关心名称以S开头的城市,我们可以这样做

SELECT city, count(*), max(temp_lo)
    FROM weather
    WHERE city LIKE 'S%'            -- (1)
    GROUP BY city;
     city      | count | max
---------------+-------+-----
 San Francisco |     2 |  46
(1 row)

(1)

LIKE运算符执行模式匹配,并在第 9.7 节中进行了解释。

了解聚合与 SQLWHEREHAVING 子句之间的交互非常重要。 WHEREHAVING 之间的基本区别在于: WHERE 在计算组和聚合之前选择输入行(因此,它控制哪些行进入聚合计算),而 HAVING 在计算组和聚合之后选择组行。因此, WHERE 子句不得包含聚合函数;尝试使用聚合来确定哪些行将成为聚合的输入毫无意义。另一方面, HAVING 子句始终包含聚合函数。(严格来说,你可以编写一个不使用聚合的 HAVING 子句,但这很少有用。同样的条件可以在 WHERE 阶段更有效地使用。)

在前面的示例中,我们可以在 WHERE 中应用城市名称限制,因为它不需要聚合。这比将限制添加到 HAVING 中更有效,因为我们避免对所有未通过 WHERE 检查的行执行分组和聚合计算。

选择进入聚合计算的行的一种方法是使用 FILTER,它是一个按聚合选项

SELECT city, count(*) FILTER (WHERE temp_lo < 45), max(temp_lo)
    FROM weather
    GROUP BY city;
     city      | count | max
---------------+-------+-----
 Hayward       |     1 |  37
 San Francisco |     1 |  46
(2 rows)

FILTERWHERE 非常相似,不同之处在于它仅从其附加到的特定聚合函数的输入中删除行。此处, count 聚合仅计算 temp_lo 低于 45 的行;但 max 聚合仍然应用于所有行,因此它仍然找到 46 的读数。