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

相关文章

Java实现文件图片的预览和下载功能

《Java实现文件图片的预览和下载功能》这篇文章主要为大家详细介绍了如何使用Java实现文件图片的预览和下载功能,文中的示例代码讲解详细,感兴趣的小伙伴可以跟随小编一起学习一下... Java实现文件(图片)的预览和下载 @ApiOperation("访问文件") @GetMapping("

SpringKafka消息发布之KafkaTemplate与事务支持功能

《SpringKafka消息发布之KafkaTemplate与事务支持功能》通过本文介绍的基本用法、序列化选项、事务支持、错误处理和性能优化技术,开发者可以构建高效可靠的Kafka消息发布系统,事务支... 目录引言一、KafkaTemplate基础二、消息序列化三、事务支持机制四、错误处理与重试五、性能优

SpringIntegration消息路由之Router的条件路由与过滤功能

《SpringIntegration消息路由之Router的条件路由与过滤功能》本文详细介绍了Router的基础概念、条件路由实现、基于消息头的路由、动态路由与路由表、消息过滤与选择性路由以及错误处理... 目录引言一、Router基础概念二、条件路由实现三、基于消息头的路由四、动态路由与路由表五、消息过滤

Spring Boot 3.4.3 基于 Spring WebFlux 实现 SSE 功能(代码示例)

《SpringBoot3.4.3基于SpringWebFlux实现SSE功能(代码示例)》SpringBoot3.4.3结合SpringWebFlux实现SSE功能,为实时数据推送提供... 目录1. SSE 简介1.1 什么是 SSE?1.2 SSE 的优点1.3 适用场景2. Spring WebFlu

基于SpringBoot实现文件秒传功能

《基于SpringBoot实现文件秒传功能》在开发Web应用时,文件上传是一个常见需求,然而,当用户需要上传大文件或相同文件多次时,会造成带宽浪费和服务器存储冗余,此时可以使用文件秒传技术通过识别重复... 目录前言文件秒传原理代码实现1. 创建项目基础结构2. 创建上传存储代码3. 创建Result类4.

Python+PyQt5实现多屏幕协同播放功能

《Python+PyQt5实现多屏幕协同播放功能》在现代会议展示、数字广告、展览展示等场景中,多屏幕协同播放已成为刚需,下面我们就来看看如何利用Python和PyQt5开发一套功能强大的跨屏播控系统吧... 目录一、项目概述:突破传统播放限制二、核心技术解析2.1 多屏管理机制2.2 播放引擎设计2.3 专

一文详解SpringBoot响应压缩功能的配置与优化

《一文详解SpringBoot响应压缩功能的配置与优化》SpringBoot的响应压缩功能基于智能协商机制,需同时满足很多条件,本文主要为大家详细介绍了SpringBoot响应压缩功能的配置与优化,需... 目录一、核心工作机制1.1 自动协商触发条件1.2 压缩处理流程二、配置方案详解2.1 基础YAML

使用PyTorch实现手写数字识别功能

《使用PyTorch实现手写数字识别功能》在人工智能的世界里,计算机视觉是最具魅力的领域之一,通过PyTorch这一强大的深度学习框架,我们将在经典的MNIST数据集上,见证一个神经网络从零开始学会识... 目录当计算机学会“看”数字搭建开发环境MNIST数据集解析1. 认识手写数字数据库2. 数据预处理的

Python实战之屏幕录制功能的实现

《Python实战之屏幕录制功能的实现》屏幕录制,即屏幕捕获,是指将计算机屏幕上的活动记录下来,生成视频文件,本文主要为大家介绍了如何使用Python实现这一功能,希望对大家有所帮助... 目录屏幕录制原理图像捕获音频捕获编码压缩输出保存完整的屏幕录制工具高级功能实时预览增加水印多平台支持屏幕录制原理屏幕

Python实现自动化表单填写功能

《Python实现自动化表单填写功能》在Python中,自动化表单填写可以通过多种库和工具实现,本文将详细介绍常用的自动化表单处理工具,并对它们进行横向比较,可根据需求选择合适的工具,感兴趣的小伙伴跟... 目录1. Selenium简介适用场景示例代码优点缺点2. Playwright简介适用场景示例代码