PostgreSQL 聚合函数讲解 - 1 常用聚合函数

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介:
PostgreSQL支持较多的聚合函数, 以PostgreSQL 9.4为例, 支持例如一般性的聚合, 统计学科的聚合, 排序集聚合, 假象集聚合等.
本文将对一般性聚合函数举例说明其功能和用法.
聚合函数有哪些,见 : 
http://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. http://www.postgresql.org/docs/9.4/static/functions-aggregate.html
2. http://www.postgresql.org/docs/9.4/static/functions-xml.html
3. src/backend/utils/adt
这些函数的代码在src/backend/utils/adt这里可以查询到, 对应各自的类型.
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
9月前
|
关系型数据库 大数据 PostgreSQL
PostgreSQL16-新特性-并行聚合
PostgreSQL16-新特性-并行聚合
102 0
|
关系型数据库 PostgreSQL
PostgreSQL listagg within group (order by) 聚合兼容用法 string_agg ( order by) - 行列变换,CSV构造...
标签 PostgreSQL , order-set agg , listagg , string_agg , order 背景 listagg — Rows to Delimited Strings The listagg function transforms values from a g...
5816 0
|
关系型数据库 PostgreSQL
【重新发现PostgreSQL之美】- 48 聚合、窗口过滤器
大家好,这里是重新发现PostgreSQL之美 - 48 聚合、窗口过滤器
|
SQL 分布式计算 并行计算
PostgreSQL 并行计算解说 之9 - parallel 自定义并行聚合
标签 PostgreSQL , cpu 并行 , smp 并行 , 并行计算 , gpu 并行 , 并行过程支持 背景 PostgreSQL 11 优化器已经支持了非常多场合的并行。简单估计,已支持27余种场景的并行计算。 parallel seq scan parallel index scan
554 0
|
SQL 关系型数据库 PostgreSQL
PostgreSQL 空间聚合性能 - 行政区、电子围栏 空间聚合 - 时间、空间热力图
标签 PostgreSQL , 空间聚合 , 空间热力图 , 行政区 , 电子围栏 背景 某个时间区间(或其他条件),出现在某些围栏、行政区(多边形信息)中的对象(空间点信息)有多少,按颜色深浅渲染这些多边形。
2395 0
|
物联网 关系型数据库 流计算
PostgreSQL pipelinedb 流计算插件 - IoT应用 - 实时轨迹聚合
标签 PostgreSQL , IoT , 轨迹聚合 , pipelinedb , 流计算 , 实时聚合 背景 IoT场景,车联网场景,共享单车场景,人的行为位点等,终端实时上报的是孤立的位点,我们需要将其补齐成轨迹。
1776 0

相关产品

  • 云原生数据库 PolarDB