2.7. 聚合函数

和大多数其它关系数据库产品一样,PostgreSQL支持聚合函数。 一个聚合函数从多个输入行中计算出一个结果。比如,我们有在一个行集合上计算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); 错误

不过这个方法不能运转,因为聚合函数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, max(temp_lo)
 FROM weather
 GROUP BY city;

 city | max
---------------+-----
 Hayward | 37
 San Francisco | 46
(2 rows)

这样每个城市一个输出。每个聚合结果都是在匹配该城市的行上面计算的。 我们可以用HAVING过滤这些分组:

SELECT city, max(temp_lo)
 FROM weather
 GROUP BY city
 HAVING max(temp_lo) < 40;

 city | max
---------+-----
 Hayward | 37
(1 row)

这样就只给出那些temp_lo值曾经有低于 40 度的城市。最后, 如果我们只关心那些名字以"S"开头的城市,我们可以用:

SELECT city, max(temp_lo)
 FROM weather
 WHERE city LIKE 'S%'(1)
 GROUP BY city
 HAVING max(temp_lo) < 40;

理解聚合和SQL的WHERE和HAVING 子句之间的关系非常重要。WHERE和HAVING的基本区别如下: WHERE在分组和聚合计算之前选取输入行(它控制哪些行进入聚合计算), 而HAVING在分组和聚合之后选取输出行。因此,WHERE 子句不能包含聚合函数;因为试图用聚合函数判断那些行将要输入给聚合运算是没有意义的。 相反,HAVING子句总是包含聚合函数。当然,你可以写不使用聚合的HAVING 子句,但这样做没什么好处,因为同样的条件用在WHERE阶段会更有效。

在前面的例子里,我们可以在WHERE里应用城市名称限制,因为它不需要聚合。 这样比在HAVING里增加限制更加高效,因为我们避免了为那些未通过 WHERE检查的行进行分组和聚合计算。