PostgreSQL 分组集合新功能(GROUPING SETS,CUBE,ROLLUP)

2024-02-14 21:38

本文主要是介绍PostgreSQL 分组集合新功能(GROUPING SETS,CUBE,ROLLUP),希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

PostgreSQL 分组集合新功能(GROUPING SETS,CUBE,ROLLUP)

实验环境

操作系统:windows 10 家庭中文版数据库系统: PostgreSQL 9.6.2

说明

postgresql从9.5版本开始新加入了group by的分组集合功能,提供了GROUPING SETS,CUBE,ROLLUP参数,使用方式与oracle完全一致,下面是实际测试说明

构建测试环境

创建表t并插入测试数据:

create table t(id int,name varchar(20),class int,score int);insert into t values(1,'math',1,90);
insert into t values(2,'math',2,80);
insert into t values(3,'math',1,70);
insert into t values(4,'chinese',2,60);
insert into t values(5,'chinese',1,50);
insert into t values(6,'chinese',2,60);
insert into t values(7,'physical',1,70);
insert into t values(8,'physical',2,80);
insert into t values(9,'physical',1,90);

结果:

test=# select * from t;id |   name   | class | score
----+----------+-------+-------1 | math     |     1 |    902 | math     |     2 |    803 | math     |     1 |    704 | chinese  |     2 |    605 | chinese  |     1 |    506 | chinese  |     2 |    607 | physical |     1 |    708 | physical |     2 |    809 | physical |     1 |    90
(9 行记录)

普通的group by

根据name和class字段求和:

test=# select name,class,sum(score)
test-# from t
test-# group by name,class
test-# order by name,class
test-# ;name   | class | sum
----------+-------+-----chinese  |     1 |  50chinese  |     2 | 120math     |     1 | 160math     |     2 |  80physical |     1 | 160physical |     2 |  80
(6 行记录)

grouping set

GROUPING SETS的每个子列表可以指定零个或多个列或表达式,并且与其直接在GROUP BY子句中的解释方式相同。 一个空的分组集合意味着所有的行都被聚合到一个组中(即使没有输入行存在,也是输出)。

test=# select name,class,sum(score)
test-# from t
test-# group by grouping sets((name),(class),())
test-# order by name,class
test-# ;name   | class | sum
----------+-------+-----chinese  |       | 170math     |       | 240physical |       | 240|     1 | 370|     2 | 280|       | 650
(6 行记录)

顺带一提,默认的group by语句相当于grouping set在grouping set后的参数填上所有group by的字段。如下:

test=# select name,class,sum(score)
test-# from t
test-# group by grouping sets((name,class))
test-# order by name,class
test-# ;name   | class | sum
----------+-------+-----chinese  |     1 |  50chinese  |     2 | 120math     |     1 | 160math     |     2 |  80physical |     1 | 160physical |     2 |  80
(6 行记录)

与不使用grouping set语句时的结果完全相同

rollup

* rollup((a),(b),(c))等价于grouping sets((a,b,c),(a,b),(a),()) *

test=# select name,class,sum(score)
test-# from t
test-# group by rollup((name),(class))
test-# order by name,class
test-# ;name   | class | sum
----------+-------+-----chinese  |     1 |  50chinese  |     2 | 120chinese  |       | 170math     |     1 | 160math     |     2 |  80math     |       | 240physical |     1 | 160physical |     2 |  80physical |       | 240|       | 650
(10 行记录)

等价于:

grouping sets((name,class),(name),())

cube

* cube((a),(b),(c))等价于grouping sets((a,b,c),(a,b),(a,c),(a),(b,c),(b),(c),()) *

test=# select name,class,sum(score)
test-# from t
test-# group by cube((name),(class))
test-# order by name,class
test-# ;name   | class | sum
----------+-------+-----chinese  |     1 |  50chinese  |     2 | 120chinese  |       | 170math     |     1 | 160math     |     2 |  80math     |       | 240physical |     1 | 160physical |     2 |  80physical |       | 240|     1 | 370|     2 | 280|       | 650
(12 行记录)

等价于:

grouping sets((name,class),(name),(class),())

实际应用

我遇到一个需求,需要在分组统计总和之外附加所有组的总和,命名为total:

test=# select coalesce(name,'total') as name,
test-# coalesce(class,0) as class,
test-# coalesce(sum(score),0) as sum_score,
test-# coalesce(round(avg(score),2),0) as avg_score
test-# from t
test-# group by grouping sets((name,class),())
test-# order by name,class
test-# ;name   | class | sum_score | avg_score
----------+-------+-----------+-----------chinese  |     1 |        50 |     50.00chinese  |     2 |       120 |     60.00math     |     1 |       160 |     80.00math     |     2 |        80 |     80.00physical |     1 |       160 |     80.00physical |     2 |        80 |     80.00total    |     0 |       650 |     72.22
(7 行记录)

这篇关于PostgreSQL 分组集合新功能(GROUPING SETS,CUBE,ROLLUP)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/709631

相关文章

Python的time模块一些常用功能(各种与时间相关的函数)

《Python的time模块一些常用功能(各种与时间相关的函数)》Python的time模块提供了各种与时间相关的函数,包括获取当前时间、处理时间间隔、执行时间测量等,:本文主要介绍Python的... 目录1. 获取当前时间2. 时间格式化3. 延时执行4. 时间戳运算5. 计算代码执行时间6. 转换为指

最详细安装 PostgreSQL方法及常见问题解决

《最详细安装PostgreSQL方法及常见问题解决》:本文主要介绍最详细安装PostgreSQL方法及常见问题解决,介绍了在Windows系统上安装PostgreSQL及Linux系统上安装Po... 目录一、在 Windows 系统上安装 PostgreSQL1. 下载 PostgreSQL 安装包2.

Android实现两台手机屏幕共享和远程控制功能

《Android实现两台手机屏幕共享和远程控制功能》在远程协助、在线教学、技术支持等多种场景下,实时获得另一部移动设备的屏幕画面,并对其进行操作,具有极高的应用价值,本项目旨在实现两台Android手... 目录一、项目概述二、相关知识2.1 MediaProjection API2.2 Socket 网络

Redis消息队列实现异步秒杀功能

《Redis消息队列实现异步秒杀功能》在高并发场景下,为了提高秒杀业务的性能,可将部分工作交给Redis处理,并通过异步方式执行,Redis提供了多种数据结构来实现消息队列,总结三种,本文详细介绍Re... 目录1 Redis消息队列1.1 List 结构1.2 Pub/Sub 模式1.3 Stream 结

MySQL索引的优化之LIKE模糊查询功能实现

《MySQL索引的优化之LIKE模糊查询功能实现》:本文主要介绍MySQL索引的优化之LIKE模糊查询功能实现,本文通过示例代码给大家介绍的非常详细,感兴趣的朋友一起看看吧... 目录一、前缀匹配优化二、后缀匹配优化三、中间匹配优化四、覆盖索引优化五、减少查询范围六、避免通配符开头七、使用外部搜索引擎八、分

Android实现悬浮按钮功能

《Android实现悬浮按钮功能》在很多场景中,我们希望在应用或系统任意界面上都能看到一个小的“悬浮按钮”(FloatingButton),用来快速启动工具、展示未读信息或快捷操作,所以本文给大家介绍... 目录一、项目概述二、相关技术知识三、实现思路四、整合代码4.1 Java 代码(MainActivi

SpringBoot集成Milvus实现数据增删改查功能

《SpringBoot集成Milvus实现数据增删改查功能》milvus支持的语言比较多,支持python,Java,Go,node等开发语言,本文主要介绍如何使用Java语言,采用springboo... 目录1、Milvus基本概念2、添加maven依赖3、配置yml文件4、创建MilvusClient

使用Python开发一个带EPUB转换功能的Markdown编辑器

《使用Python开发一个带EPUB转换功能的Markdown编辑器》Markdown因其简单易用和强大的格式支持,成为了写作者、开发者及内容创作者的首选格式,本文将通过Python开发一个Markd... 目录应用概览代码结构与核心组件1. 初始化与布局 (__init__)2. 工具栏 (setup_t

SpringValidation数据校验之约束注解与分组校验方式

《SpringValidation数据校验之约束注解与分组校验方式》本文将深入探讨SpringValidation的核心功能,帮助开发者掌握约束注解的使用技巧和分组校验的高级应用,从而构建更加健壮和可... 目录引言一、Spring Validation基础架构1.1 jsR-380标准与Spring整合1

Mysql如何将数据按照年月分组的统计

《Mysql如何将数据按照年月分组的统计》:本文主要介绍Mysql如何将数据按照年月分组的统计方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql将数据按照年月分组的统计要的效果方案总结Mysql将数据按照年月分组的统计要的效果方案① 使用 DA