官术网_书友最值得收藏!

  • Mastering PostgreSQL 9.6
  • Hans Jurgen Schonig
  • 575字
  • 2021-07-09 19:57:24

Applying grouping sets

The GROUP BY clause will turn many rows into one row per group. However, if you do reporting in real life, you might also be interested in the overall average. One additional line might be needed.

Here is how this can be achieved:

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

ROLLUP will inject an additional line, which will contain the overall average. If you do reporting, it is highly likely that a summary line will be needed. Instead of running two queries, PostgreSQL can provide the data running just a single query.

Of course, this kind of operation can also be used if you are grouping by more than just one column:

test=# SELECT   region, country, avg(production) FROM t_oil WHERE country IN ('USA', 'Canada', 'Iran', 'Oman') GROUP BY ROLLUP (region, country); 
region | country | avg
---------------+---------+-----------------------
Middle East | Iran | 3631.6956521739130435
Middle East | Oman | 586.4545454545454545
Middle East | | 2142.9111111111111111
North America | Canada | 2123.2173913043478261
North America | USA | 9141.3478260869565217
North America | | 5632.2826086956521739
| | 3906.7692307692307692
(7 rows)

In this example, PostgreSQL will inject three lines into the result set. One line will be injected for Middle East, one for North America. On top of that we will get a line for the overall averages. If you are building a web application the current result is ideal because you can easily build a GUI to drill into the result set by filtering out the null values.

ROLLUP is nice in case you instantly want to display a result. I always used it to display final results to end users. However, if you are doing reporting, you might want to pre-calculate more data to ensure more flexibility. The CUBE keyword is what you might have been looking for:

test=# SELECT   region, country, avg(production) FROM    t_oil WHERE   country IN ('USA', 'Canada', 'Iran', 'Oman') GROUP BY CUBE (region, country); 
region | country | avg
---------------+---------+-----------------------
Middle East | Iran | 3631.6956521739130435
Middle East | Oman | 586.4545454545454545
Middle East | | 2142.9111111111111111
North America | Canada | 2123.2173913043478261
North America | USA | 9141.3478260869565217
North America | | 5632.2826086956521739
| | 3906.7692307692307692
| Canada | 2123.2173913043478261
| Iran | 3631.6956521739130435
| Oman | 586.4545454545454545
| USA | 9141.3478260869565217
(11 rows)

Note that even more rows have been added to the result. CUBE will create the same data as: GROUP BY region, country + GROUP BY region + GROUP BY country + the overall average. So, the whole idea is to extract many results and various levels of aggregation at once. The resulting cube contains all possible combinations of groups.

ROLLUP and CUBE are really just convenience features on top of GROUPING SETS clause. With the GROUPING SETS clause, you can explicitly list the aggregates you want:

test=# SELECT   region, country, avg(production)                                                                                                 FROM    t_oil                                                                                                                                    WHERE   country IN ('USA', 'Canada', 'Iran', 'Oman')                                                                                             GROUP BY GROUPING SETS ( (), region, country); 
region | country | avg
---------------+---------+-----------------------
Middle East | | 2142.9111111111111111
North America | | 5632.2826086956521739
| | 3906.7692307692307692
| Canada | 2123.2173913043478261
| Iran | 3631.6956521739130435
| Oman | 586.4545454545454545
| USA | 9141.3478260869565217
(7 rows)

In this, I went for three grouping sets: the overall average, GROUP BY region and GROUP BY country. If you want regions and countries combined, use (region, country).

主站蜘蛛池模板: 根河市| 桦甸市| 子洲县| 禹州市| 西宁市| 白水县| 乳源| 陇西县| 本溪市| 洞头县| 新巴尔虎左旗| 平乐县| 永顺县| 高阳县| 普定县| 克什克腾旗| 景泰县| 二连浩特市| 酉阳| 上林县| 宜兴市| 会宁县| 宁武县| 黄冈市| 广西| 镶黄旗| 田林县| 页游| 利津县| 福泉市| 车致| 新乡县| 晴隆县| 浪卡子县| 营口市| 秦皇岛市| 舟山市| 苏州市| 达尔| 绥棱县| 东辽县|