Postgresql 中文操作指南

2.7. Aggregate Functions #

与大多数其他关系数据库产品一样，PostgreSQL 支持 aggregate functions。一个聚合函数从多个输入行计算一个单一结果。例如，有一些聚合函数计算 count、sum、avg（平均值）、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 子句确定将哪些行包括在总计计算中；因此显然必须在计算总计函数之前对其进行评估。）但是，通常可以重新表述查询以实现所需的结果，这里通过使用 subquery：

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)

了解总计与 SQL 的 WHERE 和 HAVING 子句之间的交互非常重要。WHERE 和 HAVING 之间的根本区别在于：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)

FILTER 与 WHERE 非常相似，但它仅从其附加到的特定总计函数的输入中移除行。此处，count 总计仅计算 temp_lo 低于 45 的行；但 max 总计仍应用于所有行，因此它仍找到 46 的读数。