PostgreSQL ROLLUP 用法与实例

本文介绍了如何在 PostgreSQL 中的 GROUP BY 子句中使用 ROLLUP 以在一次查询中生成多个维度的报表。

在 PostgreSQL 中， ROLLUP 是 GROUP BY 子句的参数，允许您在一次查询中生成多个维度的报表。 在某些特定的场景下， ROLLUP 可以简化 GROUPING SETS。

PostgreSQL ROLLUP 语法

下面的语法说明了如何在 GROUP BY 子句中使用 ROLLUP：

SELECT ...
FROM table_name
[WHERE ...]
GROUP BY
  ROLLUP (
    group_expr_1
    [, group_expr_2, ...]
  )
;

这里， ROLLUP 是 GROUP BY 子句的参数，它必须在 GROUP BY 关键字的后面。

ROLLUP 子句都可以使用 GROUPING SETS 子句实现，比如：

  • ROLLUP(a, b) 等效于 GROUPING SETS((a,b), (a), ())。
  • ROLLUP(a, b, c) 等效于 GROUPING SETS((a,b,c), (a,b), (a), ())。

ROLLUP 子句比 GROUPING SETS 子句更加的简单和易读。但是，它的的适应性不如 GROUPING SETS 广泛。

PostgreSQL GROUP BY 实例

我们将使用 Sakila 示例数据库 中的表进行演示，请您先在 PostgreSQL 中安装 Sakila 示例数据库。

在 Sakila 示例数据库中， film 表存储了 DVD 商店中的所有影片。

要从 film 表中查找每个影片评级中每个租金的影片的数量，以及每个影片评级中的影片的数量，以及所有影片的总数量，您可以使用如下带有 GROUPING SETS 的语句：

SELECT
  rating,
  rental_rate,
  count(*)
FROM
  film
GROUP BY
  GROUPING SETS (
    (rating, rental_rate),
    (rating),
    ()
  )
ORDER BY
  rating, rental_rate;

 rating | rental_rate | count
--------+-------------+-------
 G      |        0.99 |    64  ┐
 G      |        2.99 |    59  │───┐
 G      |        4.99 |    55  ┘   │───┐
 G      |             |   178  <───┘   │
 PG     |        0.99 |    62  ┐       │
 PG     |        2.99 |    64  │───┐   │
 PG     |        4.99 |    68  ┘   │───│
 PG     |             |   194  <───┘   │
 PG-13  |        0.99 |    72  ┐       │
 PG-13  |        2.99 |    74  │───┐   │
 PG-13  |        4.99 |    77  ┘   │───│
 PG-13  |             |   223  <───┘   │
 R      |        0.99 |    70  ┐       │
 R      |        2.99 |    60  │───┐   │
 R      |        4.99 |    65  ┘   │───│
 R      |             |   195  <───┘   │
 NC-17  |        0.99 |    73  ┐       │
 NC-17  |        2.99 |    66  │───┐   │
 NC-17  |        4.99 |    71  ┘   │───│
 NC-17  |             |   210  <───┘   │
        |             |  1000  <───────┘
(21 rows)

这里，我们在 GROUPING SETS 中使用了 3 个表达式，如下：

  • (rating, rental_rate): 按照影片评级和租金统计影片的数量。
  • (rating): 按照影片评级统计数量。
  • (): 统计所有影片的数量。

在这种情况下， 这个 GROUPING SETS 可以使用 ROLLUP 来简化，如下：

SELECT
  rating,
  rental_rate,
  count(*)
FROM
  film
GROUP BY
  ROLLUP (rating, rental_rate)
ORDER BY
  rating, rental_rate;

 rating | rental_rate | count
--------+-------------+-------
 G      |        0.99 |    64
 G      |        2.99 |    59
 G      |        4.99 |    55
 G      |             |   178
 PG     |        0.99 |    62
 PG     |        2.99 |    64
 PG     |        4.99 |    68
 PG     |             |   194
 PG-13  |        0.99 |    72
 PG-13  |        2.99 |    74
 PG-13  |        4.99 |    77
 PG-13  |             |   223
 R      |        0.99 |    70
 R      |        2.99 |    60
 R      |        4.99 |    65
 R      |             |   195
 NC-17  |        0.99 |    73
 NC-17  |        2.99 |    66
 NC-17  |        4.99 |    71
 NC-17  |             |   210
        |             |  1000
(21 rows)

在上面的语句中，我们使用 ROLLUP (rating, rental_rate) 代替了 GROUPING SETS ((rating, rental_rate), (rating), ())。这让语句更加简单，可读性更好。

结论

在 PostgreSQL 中的 GROUP BY 子句中，您可以在特定的场景下使用 ROLLUP 简化 GROUPING SETS。