PostgreSQL: 如何使用 GROUPING SETS、CUBE 和 ROLLUP 进行汇总统计

Question

我有下面这段代码，是我在MySQL 5.6中编写的：

INSERT INTO Totals
SELECT 
  Zone,
  State,
  COUNT(Sponsored),
  COUNT(Enrolled),
  COUNT(PickedUp)
FROM MasterData
GROUP BY Zone, StateName
  WITH ROLLUP;

那会产生以下摘要总数：

Zone 1        Sponsored Enrolled Picked Up
  Alaska          0        0        0
  Arizona         1        3        1
  California      3        6        0
  Colorado        0        4        2
  Guam            0        0        0
  Hawaii          0        1        0
  (totals)        4        14       3
Zone 2
  Idaho           1        0        0
  Montana         0        1        1
  Nevada          0        0        1
  New Mexico      0        1        4
  North Dakota    4        8        4
  Oregon          0        0        1
  South Dakota    0        1        0
  Utah            0        1        0
  Washington      0        1        1
  Wyoming         0        1        1
  (totals)        5        14       13
  (gr. totals)    9        28       16

我正在迁移到PostgreSQL 9.5，并需要生成这些相同类型的总计。我有一种感觉，新实现的（自9.5版本以来）GROUPING SETS、CUBE和ROLLUP函数可能会有所帮助，但我不知道如何使用它们来获取这些结果。据我所读，这些函数应该使生成这样的汇总报告变得更加容易。

- Michael Sheaver

Answer 1

请参阅文档：

SELECT
  Zone,
  State,
  COUNT(Sponsored),
  COUNT(Enrolled),
  COUNT(PickedUp)
FROM MasterData
GROUP BY rollup(Zone, State);

  zone  |     state     | sum | sum | sum
--------+---------------+-----+-----+-----
 Zone 1 |  Alaska       |   0 |   0 |   0
 Zone 1 |  Arizona      |   1 |   3 |   1
 Zone 1 |  California   |   3 |   6 |   0
 Zone 1 |  Colorado     |   0 |   4 |   2
 Zone 1 |  Guam         |   0 |   0 |   0
 Zone 1 |  Hawaii       |   0 |   1 |   0
 Zone 1 |               |   4 |  14 |   3
 Zone 2 |  Idaho        |   1 |   0 |   0
 Zone 2 |  Montana      |   0 |   1 |   1
 Zone 2 |  Nevada       |   0 |   0 |   1
 Zone 2 |  New Mexico   |   0 |   1 |   4
 Zone 2 |  North Dakota |   4 |   8 |   4
 Zone 2 |  Oregon       |   0 |   0 |   1
 Zone 2 |  South Dakota |   0 |   1 |   0
 Zone 2 |  Utah         |   0 |   1 |   0
 Zone 2 |  Washington   |   0 |   1 |   1
 Zone 2 |  Wyoming      |   0 |   1 |   1
 Zone 2 |               |   5 |  14 |  13
        |               |   9 |  28 |  16
(19 rows)

- Laurenz Albe

---

非常感谢Laurenz在这里抽出时间来帮助我！昨天我盯着文档页面看了很长时间，结果我几乎做到了；最后一部分不太正确： GROUP BY Zone，StateName WITH ROLLUP; - Michael Sheaver