84
PostgreSQL 聚合函數講解 - 1 常用聚合函數
PostgreSQL支持較多的聚合函數, 以PostgreSQL 9.4為例, 支持例如一般性的聚合, 統計學科的聚合, 排序集聚合, 假象集聚合等.
本文將對一般性聚合函數舉例說明其功能和用法.
聚合函數有哪些，見 :
https://www.postgresql.org/docs/9.4/static/functions-aggregate.html
以上所有聚合函數, 當沒有行輸入時, 除了count返回0, 其他都返回null.
使用sum, array_agg時, 當沒有行輸入, 返回NULL可能有點別扭, 那麼你可以使用coalesce來替代NULL, 如coalesce(sum(x), 0)
coalesce(array_agg(x), '{}'::int[])
例子 :
聚合後得到數組, null將計入數組元素
postgres=# select array_agg(id) from (values(null),(1),(2)) as t(id);
array_agg
------------
{NULL,1,2}
(1 row)
算平均值是不計算null
postgres=# select avg(id) from (values(null),(1),(2)) as t(id);
avg
--------------------
1.5000000000000000
(1 row)
算bit與|或 時也不計算NULL
postgres=# select bit_and(id) from (values(null),(1),(2)) as t(id);
bit_and
---------
0
(1 row)
postgres=# select bit_or(id) from (values(null),(1),(2)) as t(id);
bit_or
--------
3
(1 row)
算布爾邏輯時也不計算NULL
postgres=# select bool_and(id) from (values(null),(true),(false)) as t(id);
bool_and
----------
f
(1 row)
every是bool_and的別名, 實際上是SQL標準中定義的.
postgres=# select every(id) from (values(null),(true),(false)) as t(id);
every
-------
f
(1 row)
SQL標準中還定義了any和some為bool_or的別名, 但是因為any和some還可以被解釋為子查詢, 所以在PostgreSQL中any和some的布爾邏輯聚合不可用.
postgres=# select any(id) from (values(null),(true),(false)) as t(id);
ERROR: syntax error at or near "any"
LINE 1: select any(id) from (values(null),(true),(false)) as t(id);
^
postgres=# select some(id) from (values(null),(true),(false)) as t(id);
ERROR: syntax error at or near "some"
LINE 1: select some(id) from (values(null),(true),(false)) as t(id);
^
bool_or的例子
postgres=# select bool_or(id) from (values(null),(true),(false)) as t(id);
bool_or
---------
t
(1 row)
計算非空的表達式個數, count帶表達式時, 不計算null
postgres=# select count(id) from (values(null),(1),(2)) as t(id);
count
-------
2
(1 row)
計算表達式(含空值)的個數, count(*)計算null, 注意count(*)是一個獨立的聚合函數. 請和count(express)區分開來.
postgres=# select count(*) from (values(null),(1),(2)) as t(id);
count
-------
3
(1 row)
postgres=# select count(*) from (values(null),(null),(1),(2)) as t(id);
count
-------
4
(1 row)
聚合後得到json, 不帶key的json聚合
postgres=# select json_agg(id) from (values(null),(true),(false)) as t(id);
json_agg
---------------------
[null, true, false]
(1 row)
聚合後得到json, 帶key的json聚合, 注意key不能為null, 否則報錯.
postgres=# select json_object_agg(c1,c2) from (values('a',null),('b',true),('c',false)) as t(c1,c2);
json_object_agg
-----------------------------------------
{ "a" : null, "b" : true, "c" : false }
(1 row)
postgres=# select json_object_agg(c1,c2) from (values(null,null),('b',true),('c',false)) as t(c1,c2);
ERROR: 22023: field name must not be null
LOCATION: json_object_agg_transfn, json.c:1959
計算最大最小值, max, min都不計算null
postgres=# select max(id) from (values(null),(1),(2)) as t(id);
max
-----
2
(1 row)
postgres=# select min(id) from (values(null),(1),(2)) as t(id);
min
-----
1
(1 row)
聚合後得到字符串, 字符串聚合
postgres=# select string_agg(c1,'***') from (values('a',null),('b',true),('c',false)) as t(c1,c2);
string_agg
------------
a***b***c
(1 row)
postgres=# select string_agg(id,'***') from (values(null),('digoal'),('zhou')) as t(id);
string_agg
---------------
digoal***zhou
(1 row)
計算總和, sum不計算null, 當所有行都是null時, 即沒有任何行輸入, 返回null.
postgres=# select sum(id) from (values(null),(1),(2)) as t(id);
sum
-----
3
(1 row)
postgres=# select sum(id::int) from (values(null),(null),(null)) as t(id);
sum
-----
(1 row)
聚合後得到xml
postgres=# select xmlagg(id::xml) from (values(null),('<foo>digoal</foo>'),('<bar/>')) as t(id);
xmlagg
-------------------------
<foo>digoal</foo><bar/>
(1 row)
某些聚合函數得到的結果可能和行的輸入順序有關, 例如array_agg, json_agg, json_object_agg, string_agg, and xmlagg, 以及某些自定義聚合函數. 如何來實現呢?
支持聚合函數中使用order by的PostgreSQL版本可以用如下語法 :
postgres=# select string_agg(id,'***' order by id) from (values(null),('digoal'),('zhou')) as t(id);
string_agg
---------------
digoal***zhou
(1 row)
postgres=# select string_agg(id,'***' order by id desc) from (values(null),('digoal'),('zhou')) as t(id);
string_agg
---------------
zhou***digoal
(1 row)
不支持聚合函數中使用order by的PostgreSQL版本, 可以用如下語法 :
SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
postgres=# select string_agg(id,'***') from (select id from (values(null),('digoal'),('zhou')) as t(id) order by id desc) t;
string_agg
---------------
zhou***digoal
(1 row)
[參考]
1. https://www.postgresql.org/docs/9.4/static/functions-aggregate.html
2. https://www.postgresql.org/docs/9.4/static/functions-xml.html
3. src/backend/utils/adt
這些函數的代碼在src/backend/utils/adt這裏可以查詢到, 對應各自的類型.
最後更新：2017-04-01 13:37:06