Postgresql General-Purpose Aggregate Functions

Postgresql 普通聚合函数

Function	Argument Type(s)	Return Type	Partial Mode	Description
array_agg(expression)	any non-array type	array of the argument type	No	input values, including nulls, concatenated into an array
array_agg(expression)	any array type	same as argument data type	No	input arrays concatenated into array of one higher dimension (inputs must all have same dimensionality, and cannot be empty or NULL)
avg(expression)	smallint, int, bigint, real, double precision, numeric, or interval	numeric for any integer-type argument, double precision for a floating-point argument, otherwise the same as the argument data type	Yes	the average (arithmetic mean) of all input values
bit_and(expression)	smallint, int, bigint, or bit	same as argument data type	Yes	the bitwise AND of all non-null input values, or null if none
bit_or(expression)	smallint, int, bigint, or bit	same as argument data type	Yes	the bitwise OR of all non-null input values, or null if none
bool_and(expression)	bool	bool	Yes	true if all input values are true, otherwise false
bool_or(expression)	bool	bool	Yes	true if at least one input value is true, otherwise false
count(*)		bigint	Yes	number of input rows
count(expression)	any	bigint	Yes	number of input rows for which the value ofexpression is not null
every(expression)	bool	bool	Yes	equivalent to bool_and
json_agg(expression)	any	json	No	aggregates values as a JSON array
jsonb_agg(expression)	any	jsonb	No	aggregates values as a JSON array
json_object_agg(name,value)	(any, any)	json	No	aggregates name/value pairs as a JSON object
jsonb_object_agg(name,value)	(any, any)	jsonb	No	aggregates name/value pairs as a JSON object
max(expression)	any numeric, string, date/time, network, or enum type, or arrays of these types	same as argument type	Yes	maximum value of expression across all input values
min(expression)	any numeric, string, date/time, network, or enum type, or arrays of these types	same as argument type	Yes	minimum value of expression across all input values
string_agg(expression,delimiter)	(text, text) or (bytea, bytea)	same as argument types	No	input values concatenated into a string, separated by delimiter
sum(expression)	smallint, int, bigint, real, double precision, numeric, interval, or money	bigint for smallint or int arguments, numeric for bigint arguments, otherwise the same as the argument data type	Yes	sum of expression across all input values
xmlagg(expression)	xml	xml	No	concatenation of XML values (see also Section 9.14.1.7)

测试表：

 select id from test  where id<10; 

 id 

----

  1

  2

  3

  4

  5

  6

  7

  8

  9

求平均值，最大，以及最小值函数

select avg(id),max(id),min(id)  from test where id<10;

       avg         | max | min 

--------------------+-----+-----

 5.0000000000000000 |   9 |   1

统计小于和以及小于10的计数

select sum(id),count(*) from test  where id<10;

 sum | count 

-----+-------

  45 |     9

把小于10的数值变为一个数组

select array_agg(id)  from test where id<10;

      array_agg      

---------------------

 {1,2,3,4,5,6,7,8,9}

用--分隔，组成一个字符串，并按id降序排列

select string_agg(id::text,'--' order by id desc ) from test where id<10;

        string_agg         

---------------------------

 9--8--7--6--5--4--3--2--1

计算bit的与和或

select bit_and(id),bit_or(id) from test where id<10;    

 bit_and | bit_or 

---------+--------

       0 |     15

计算布尔值的与和或,every等同于bool_and

select bool_and(val),bool_or(val),every(val) from (values(true),(true),(false)) as t(val);

 bool_and | bool_or | every 

----------+---------+-------

 f        | t       | f

(1 row)

聚合后不带key的json，注意这里id是value，等同于json_agg

 select jsonb_agg(id) from test where id<10;

          jsonb_agg          

-----------------------------

 [1, 2, 3, 4, 5, 6, 7, 8, 9]

聚合后带key的json，注意这里id是key,等同于json_object_agg(name, value)

 select * from test where id<10;

 id | c1 

----+----

  1 |  8

  2 |  3

  3 |  3

  4 |  1

  5 |  5

  6 |  8

  7 |  3

  8 |  0

  9 |  3

(9 rows)

select json_object_agg(id,c1) from test where id<10;

                                   json_object_agg                                   

-------------------------------------------------------------------------------------

 { "1" : 8, "2" : 3, "3" : 3, "4" : 1, "5" : 5, "6" : 8, "7" : 3, "8" : 0, "9" : 3 }

xmlagg聚合为xml

select xmlagg(val::xml) from (values('<html>orange</html>'),('<foo>apple</foo>'),('<bar/>')) as test(val); 

                  xmlagg                   

-------------------------------------------

 <html>orange</html><foo>apple</foo><bar/>

参考：

https://www.postgresql.org/docs/9.6/static/functions-aggregate.html