PostgreSQL CUBE 用法与实例

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

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

PostgreSQL CUBE 语法

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

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

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

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

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

CUBE 子句比 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),
    (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
        |        0.99 |   341
        |        2.99 |   323
        |        4.99 |   336
        |             |  1000
(24 rows)

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

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

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

SELECT
  rating,
  rental_rate,
  count(*)
FROM
  film
GROUP BY
  CUBE (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
        |        0.99 |   341
        |        2.99 |   323
        |        4.99 |   336
        |             |  1000
(24 rows)

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

结论

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