^

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   

标签：