快捷搜索:  as  test  1111  test aNd 8=8  test++aNd+8=8  as++aNd+8=8  as aNd 8=8

巴黎人下载app地址:PostgreSQL 聚合函数讲解常用聚合函数(3)



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

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 巴黎人下载app地址t(id);

sum

-----

(1 row)

聚合后获得xml

postgres=# select xmlagg(id::xml) from (values(null),('digoal'),('')) as t(id);

xmlag巴黎人下载app地址g

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

digoal

(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的PostgreS巴黎人下载app地址QL版本, 可以用如下语法 :

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)

^

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)巴黎人下载app地址,(null),(1),(2)) as t(id);

count

-------

4

(1 row)

聚合后获得json, 不带key的json聚合

postgres=# select json_agg(id) from 巴黎人下载app地址(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

免责声明:以上内容源自网络,版权归原作者所有,如有侵犯您的原创版权请告知,我们将尽快删除相关内容。

您可能还会对下面的文章感兴趣: