PostgreSQL CUBE 用法与实例

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

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

PostgreSQL `CUBE` 语法

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

sql

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 示例数据库中， film 表存储了 DVD 商店中的所有影片。

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

sql

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

bash

 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 来简化，如下：

sql

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

bash

 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。

PostgreSQL CUBE 用法与实例 ​

PostgreSQL CUBE 语法 ​

PostgreSQL GROUP BY 实例 ​

结论 ​

PostgreSQL CUBE 用法与实例

PostgreSQL `CUBE` 语法

PostgreSQL `GROUP BY` 实例

结论