聚集函数从一组输入值计算单一结果。内建聚集函数列在 表 9.47 和 表 9.48 中。 聚集函数的特殊语法考虑在 第 4.2.7 节 中说明。 更多介绍信息请参阅 第 2.7 节。

It should be noted that except for count, these functions return a null value when no rows are selected. In particular, sum of no rows returns null, not zero as one might expect, and array_agg returns null rather than an empty array when there are no input rows. The coalesce function can be used to substitute zero or an empty array for null when necessary.

SELECT b1 = ANY((SELECT b2 FROM t2 ...)) FROM t1 ...;

SELECT count(*) FROM sometable;

The aggregate functions array_agg, json_agg, string_agg, and xmlagg, as well as similar user-defined aggregate functions, produce meaningfully different result values depending on the order of the input values. This ordering is unspecified by default, but can be controlled by writing an ORDER BY clause within the aggregate call, as shown in 第 4.2.7 节. Alternatively, supplying the input values from a sorted subquery will usually work. For example:

SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;

But this syntax is not allowed in the SQL standard, and is not portable to other database systems.

表 9.48 shows aggregate functions typically used in statistical analysis. (These are separated out merely to avoid cluttering the listing of more-commonly-used aggregates.) Where the description mentions N, it means the 输入行数 for which all the input expressions are non-null. In all cases, null is returned if the computation is meaningless, for example when N is zero.