【PostgresSQL】分组统计后计算所有组的总和

分组集合新功能（GROUPING SETS,CUBE,ROLLUP）0、说明postgresql从9.5版本开始新加入了group by的分组集合功能，提供了GROUPING SETS,CUBE,ROLLUP参数，使用方式与oracle完全一致，下面是实际测试说明1、实验环境操作系统：windows 10 家庭中文版数据库系统： PostgreSQL 9.6.22、构建测试环境创建表t并插入测试数

请叫我背影男神

7893人浏览 · 2022-04-08 14:16:58

请叫我背影男神 · 2022-04-08 14:16:58 发布

分组集合新功能（GROUPING SETS,CUBE,ROLLUP）

0、说明

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

1、实验环境

操作系统：windows 10 家庭中文版

数据库系统： PostgreSQL 9.6.2

2、构建测试环境

创建表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 |    90

  2 | math     |     2 |    80

  3 | math     |     1 |    70

  4 | chinese  |     2 |    60

  5 | chinese  |     1 |    50

  6 | chinese  |     2 |    60

  7 | physical |     1 |    70

  8 | physical |     2 |    80

  9 | physical |     1 |    90

(9 行记录)

3、普通的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 |  50

 chinese  |     2 | 120

 math     |     1 | 160

 math     |     2 |  80

 physical |     1 | 160

 physical |     2 |  80

(6 行记录)

4、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  |       | 170

 math     |       | 240

 physical |       | 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 |  50

 chinese  |     2 | 120

 math     |     1 | 160

 math     |     2 |  80

 physical |     1 | 160

 physical |     2 |  80

(6 行记录)

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

5、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 |  50

 chinese  |     2 | 120

 chinese  |       | 170

 math     |     1 | 160

 math     |     2 |  80

 math     |       | 240

 physical |     1 | 160

 physical |     2 |  80

 physical |       | 240

          |       | 650

(10 行记录)

等价于：

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

6、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 |  50

 chinese  |     2 | 120

 chinese  |       | 170

 math     |     1 | 160

 math     |     2 |  80

 math     |       | 240

 physical |     1 | 160

 physical |     2 |  80

 physical |       | 240

          |     1 | 370

          |     2 | 280

          |       | 650

(12 行记录)

等价于：

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

7、实际应用

我遇到一个需求，需要在分组统计总和之外附加所有组的总和，命名为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.00

 chinese  |     2 |       120 |     60.00

 math     |     1 |       160 |     80.00

 math     |     2 |        80 |     80.00

 physical |     1 |       160 |     80.00

 physical |     2 |        80 |     80.00

 total    |     0 |       650 |     72.22

(7 行记录)