PostgreSQL 10 - 分组集合和排序集合

grouping sets

很多人都熟悉GROUP BY和HAVING。但是，你熟悉CUBE、ROLLUP和GROUPING SETS吗？

先加载例子数据

我们使用BP能源报告的数据。

数据结构是

CREATE TABLE t_oil (
region text,
country text,
year int,
production int,
consumption int
);

导入数据：

postgres=# COPY t_oil FROM  './oil_ext.txt';
COPY 644
时间：22.798 ms

其中包含1965-2010年间，两个地区的14个国家的数据：

postgres=# SELECT region, avg(production) FROM t_oil GROUP BY region;
    region     |          avg          
---------------+-----------------------
 Middle East   | 1992.6036866359447005
 North America | 4541.3623188405797101
(2 行记录)

使用分组集合

GROUP BY会返回很多行，每组一行。但是，你也可能还对整体平均感兴趣。

postgres=# SELECT region, avg(production) FROM t_oil GROUP BY ROLLUP (region);
    region     |          avg          
---------------+-----------------------
 Middle East   | 1992.6036866359447005
 North America | 4541.3623188405797101
               | 2607.5139860139860140
(3 行记录)

ROLLUP会注入新的一行，它包含整体平均值。如果你做报表，这很像摘要（summary）行。不需要执行两次查询，PostgreSQL就返回了需要的全部数据。但是你要注意，PostgreSQL的不同版本，可能返回不同的顺序。9.6以前，PostgreSQL不得不做大量的排序工作。从10.0开始，可以使用hash，提升了性能：

postgres=# explain SELECT region, avg(production) FROM t_oil GROUP BY ROLLUP (region);
                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
MixedAggregate (cost=0.00..17.31 rows=3 width=44)
    Hash Key: region
    Group Key: ()
    -> Seq Scan on t_oil (cost=0.00..12.44 rows=644 width=16)
(4 行记录)