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 |    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 行记录)

普通的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 行记录)

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语句时的结果完全相同

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),())

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),())

实际应用

我遇到一个需求,需要在分组统计总和之外附加所有组的总和,命名为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 行记录)
评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值