PostgreSQL 聚合表达式 FILTER , order , within group 用法

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
简介:

标签

PostgreSQL , 聚合 , filter , order , within group


背景

PostgreSQL的分析功能还是比较强大的,例如支持多维分析,支持4大类聚合,支持窗口查询,支持递归查询等。

4大类聚合的用法请参考

《PostgreSQL aggregate function 1 : General-Purpose Aggregate Functions》

《PostgreSQL aggregate function 2 : Aggregate Functions for Statistics》

《PostgreSQL aggregate function 3 : Aggregate Functions for Ordered-Set》

《PostgreSQL aggregate function 4 : Hypothetical-Set Aggregate Functions》

多维分析请参考

《Greenplum 最佳实践 - 多维分析的使用(CUBE, ROLLUP, GROUPING SETS in GreenPlum and Oracle)》

《PostgreSQL 9.5 new feature - Support GROUPING SETS, CUBE and ROLLUP.》

窗口查询请参考

《时序数据合并场景加速分析和实现 - 复合索引,窗口分组查询加速,变态递归加速》

《快速入门PostgreSQL应用开发与管理 - 4 高级SQL用法》

递归查询请参考

《快速入门PostgreSQL应用开发与管理 - 3 访问数据》

本文主要介绍一下聚合表达式的高级用法

aggregate_name (expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]  
  
aggregate_name (ALL expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]  
  
aggregate_name (DISTINCT expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]  
  
aggregate_name ( * ) [ FILTER ( WHERE filter_clause ) ]  
  
aggregate_name ( [ expression [ , ... ] ] ) WITHIN GROUP ( order_by_clause ) [ FILTER ( WHERE filter_clause ) ]  

例子

1. 我们在分组后,需要查出分组中复合条件的count,以及分组的count。

postgres=# create table test(id int, c1 int);  
CREATE TABLE  
postgres=# insert into test select generate_series(1,10000), random()*10;  
INSERT 0 10000  
  
postgres=# select * from test limit 10;  
 id | c1   
----+----  
  1 | 10  
  2 |  4  
  3 |  6  
  4 |  1  
  5 |  4  
  6 |  9  
  7 |  9  
  8 |  7  
  9 |  5  
 10 |  4  
(10 rows)  
postgres=# select count(*), count(*) filter (where id<1000) from test group by c1;  
 count | count   
-------+-------  
  1059 |   118  
   998 |   109  
   999 |   101  
  1010 |    95  
   468 |    48  
   544 |    43  
   964 |   107  
   956 |   103  
  1021 |    87  
   977 |   101  
  1004 |    87  
(11 rows)  

2. 我们需要按顺序,将多条记录,聚合为一个字符串或数组,同样也可以加filter,只聚合复合条件的记录

postgres=# select string_agg(id::text, '-' order by id) filter (where id<100) from test group by c1;  
                string_agg                   
-------------------------------------------  
 35-65-74-97  
 4-12-19-31-36-40-85-89-90-98-99  
 17-18-22-42-43-44-58-59-64-70-75-83-84  
 11-14-15-16-21-30-41-54-62-67-73-80-81-94  
 2-5-10-51-79-93-96  
 9-26-45-46-47-61  
 3-27-28-37-48-55-56-68-69-77-92  
 8-20-24-33-34-49-50-60-63-66-78-91  
 25-39-53-57-71-76-82-87-95  
 6-7-29-32-38-72-86-88  
 1-13-23-52  
(11 rows)  

3. 我们需要去每个分组,某个字段中值

postgres=# select percentile_cont(0.5) within group (order by id) from test group by c1;  
 percentile_cont   
-----------------  
          4911.5  
            5210  
            4698  
          4699.5  
            4955  
          5061.5  
            5115  
            5176  
          4897.5  
            5087  
            4973  
(11 rows)  

4. 去过滤条件后的中值

postgres=# select percentile_cont(0.5) within group (order by id) filter (where id<100) from test group by c1;  
 percentile_cont   
-----------------  
            69.5  
              40  
              58  
            47.5  
              51  
            45.5  
              55  
            49.5  
              71  
              35  
              18  
(11 rows)  

小结

PostgreSQL 的分析方法很全面,建议用户可以多了解一下我在开始时给出的链接,帮助提高生产力。

参考

https://www.postgresql.org/docs/9.6/static/sql-expressions.html#SYNTAX-AGGREGATES

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
存储 SQL 监控
16PostgreSQL 本地分区表的用法和优化|学习笔记
快速学习16PostgreSQL 本地分区表的用法和优化
880 0
16PostgreSQL 本地分区表的用法和优化|学习笔记
|
SQL 安全 关系型数据库
17PostgreSQL shared nothing分布式用法讲解|学习笔记(三)
快速学习17PostgreSQL shared nothing分布式用法讲解
265 0
17PostgreSQL shared nothing分布式用法讲解|学习笔记(三)
|
SQL 存储 移动开发
PostgreSQL psql的使用,SQL语法,数据类型,递归SQL用法(四)|学习笔记
快速学习3 PostgreSQL psql的使用,SQL语法,数据类型,递归SQL用法(四)
458 0
 PostgreSQL psql的使用,SQL语法,数据类型,递归SQL用法(四)|学习笔记
|
Java 关系型数据库 MySQL
整合Mybatis、Servlet、Mysql、Axios、Filter、Session写一个入门级项目:非常适合初接触JavaWeb的小白白来进阶
​ 本篇文章结构大体还是和上篇文章 Mybatis+Servlet+Mysql 整合的一个小项目一致,但增加了axios、Filter、session。在数据库层面涉及到了增、查、改,一个代码量不算多的小项目,但十分有助于初学者的学习。​ 博主在编写项目的同时,发现自己对Axios、Filter的理解并不好,通过本项目,打扎实了自己的基础。​ 开发此小项目之前,我对同学说,我异步请求用的很少,殊不知自己一直在用异步请求,反而同步请求用的很少很少了。​ 在编写项目之时,被axios post传参困扰了很久很久
整合Mybatis、Servlet、Mysql、Axios、Filter、Session写一个入门级项目:非常适合初接触JavaWeb的小白白来进阶
|
SQL JSON 关系型数据库
postgresql 高级用法
postgresql 高级用法
|
SQL 关系型数据库 数据库
3 PostgreSQL psql的使用,SQL语法,数据类型,递归SQL用法(三)|学习笔记
快速学习3 PostgreSQL psql的使用,SQL语法,数据类型,递归SQL用法(三)
354 0
|
SQL XML 移动开发
【学习资料】第14期快速入门PostgreSQL应用开发与管理 - 4 高级SQL用法
大家好,这里是快速入门PostgreSQL应用开发与管理 - 4 高级SQL用法
|
关系型数据库 PostgreSQL
【重新发现PostgreSQL之美】- 48 聚合、窗口过滤器
大家好,这里是重新发现PostgreSQL之美 - 48 聚合、窗口过滤器
|
SQL 分布式计算 并行计算
PostgreSQL 并行计算解说 之2 - parallel filter
标签 PostgreSQL , cpu 并行 , smp 并行 , 并行计算 , gpu 并行 , 并行过程支持 背景 PostgreSQL 11 优化器已经支持了非常多场合的并行。简单估计,已支持27余种场景的并行计算。 parallel seq scan parallel index scan parallel index only sc
292 0
|
关系型数据库 分布式数据库 PolarDB
《阿里云产品手册2022-2023 版》——PolarDB for PostgreSQL
《阿里云产品手册2022-2023 版》——PolarDB for PostgreSQL
358 0

相关产品

  • 云原生数据库 PolarDB
  • 云数据库 RDS PostgreSQL 版