GROUPING SETS
GROUPING SETS
在 PostgreSQL 中,GROUPING SETS
是 GROUP BY
子句的参数。是一种用于生成多个分组汇总的语法,它可以将一组列用于分组,生成多个子汇总,并将这些子汇总合并到单个结果集中
比如要想在一个销售报表中既有每个年份销售额的行,又有每个部门销售额的行,您可以在 GROUP BY
子句中使用 GROUPING SETS
实现它
GROUPING SETS 语法
在 GROUP BY
子句中使用 GROUPING SETS
,请使用如下的语法:
SELECT ...
FROM table_name
[WHERE ...]
GROUP BY
GROUPING SETS (
(group_expr_list_1)
[, (group_expr_list_2), ...]
)
;
GROUPING SETS
是GROUP BY
子句的参数,它必须在GROUP BY
关键字的后面(group_expr_list_N) 是一个用于分组的表达式列表,其中可以包含零个或者多个列或表达式,就像在
GROUP BY
子句中直接使用的那些列一样您可以为
GROUPING SETS
指定一个或者多个分组表达式,每个分组表达式产生的结果集都将合并到最终的结果集中(group_expr_list_N) 中没有任何列或者表达式时,即 (),所有符合条件的行都被聚合到一个分组中
ROUPING SETS 示例
我们将使用 PostgreSQL Sakila 示例数据库 里的数据表进行演示
要从 film 表中查找每部影片评级的影片数量,请使用如下的语法:
SELECT rating, count(*)
FROM film
GROUP BY rating
ORDER BY rating;
rating | count
--------+-------
G | 178
PG | 194
PG-13 | 223
R | 195
NC-17 | 210
(5 行记录)
要从 film 表中查找每个租金的影片数量,请使用如下语句:
SELECT rental_rate, count(*)
FROM film
GROUP BY rental_rate
ORDER BY rental_rate;
rental_rate | count
-------------+-------
0.99 | 341
2.99 | 323
4.99 | 336
(3 行记录)
要想在一个报表中包含上面的两个报表,您可以使用 UNION ALL 将上面的两个结果集合并起来,如下:
SELECT rating, NULL rental_rate, count(*)
FROM film
GROUP BY rating
UNION ALL
SELECT NULL rating, rental_rate, count(*)
FROM film
GROUP BY rental_rate
ORDER BY rating, rental_rate;
rating | rental_rate | count
--------+-------------+-------
G | | 178 ┐
PG | | 194 │
PG-13 | | 223 │ > ranting
R | | 195 │
NC-17 | | 210 ┘
| 0.99 | 341 ┐
| 2.99 | 323 │ > rental_rate
| 4.99 | 336 ┘
(8 行记录)
但是上面使用 UNION ALL
的语句看起来很复杂。如果能用一个简单的语句做到这一切就太棒了。PostgreSQL GROUPING SETS
可以帮我们实现:
SELECT rating, rental_rate, count(*)
FROM film
GROUP BY GROUPING SETS ((rating), (rental_rate))
ORDER BY rating, rental_rate;
rating | rental_rate | count
--------+-------------+-------
G | | 178 ┐
PG | | 194 │
PG-13 | | 223 │ > ranting
R | | 195 │
NC-17 | | 210 ┘
| 0.99 | 341 ┐
| 2.99 | 323 │ > rental_rate
| 4.99 | 336 ┘
(8 行记录)
所以您可以将 GROUPING SETS
简单的理解为对多个分组结果集执行了 UNION ALL
操作
如果您想在上面的报表中添加一行以显示总影片数量,您可以在 GROUPING SETS
子句里的第三个参数填上一个空的分组表达式 ()
,如下:
SELECT rating, rental_rate, count(*)
FROM film
GROUP BY GROUPING SETS ((rating), (rental_rate), ())
ORDER BY rating, rental_rate;
rating | rental_rate | count
--------+-------------+-------
G | | 178
PG | | 194
PG-13 | | 223
R | | 195
NC-17 | | 210
| 0.99 | 341
| 2.99 | 323
| 4.99 | 336
| | 1000
(9 行记录)
为了应对更加复杂的需求, PostgreSQL 还提供了 ROLLUP 和 CUBE 以简化 GROUPING SETS