PostgreSQL CUBE

In PostgreSQL, the CUBE clause is a parameter of the GROUP BY clause that allows you to generate a report on multiple dimensions in one query. As with ROLLUP, in some specific cases, the CUBE can simplify GROUPING SETS.

PostgreSQL CUBE syntax

The following syntax shows how to use CUBE in the GROUP BY clause:

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

Here, the CUBE clause is the parameter of the GROUP BY clause, it must follow the GROUP BY keyword.

A CUBE clause can be implemented by a GROUPING SETS clause, for example:

• The CUBE(a, b) is equivalent to GROUPING SETS((a,b), (a), (b), ()).
• The CUBE(a, b, c) is equivalent to GROUPING SETS((a,b,c), (a,b), (a,c), (a), (b,c), (b), (c), ()).

CUBE Clauses are simpler and easier to read than GROUPING SETS clauses . However, its adaptability is not as good as GROUPING SETS.

PostgreSQL GROUP BY Examples

We will use the tables in the Sakila sample database for demonstration, please install the Sakila sample database in PostgreSQL first.

In the Sakila sample database, the film table stores all the films in the DVD store.

To find the number of films for each film rating and rental, the number of films for each film rating, the number of films for each film rental, and the total number of all films, you can use the following statement with 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)

Here, we use 3 expressions in the GROUPING SETS clause, as follows:

• (rating, rental_rate): Count the number of films for each film rating and rental.
• (rating): Count the number for each film rating.
• (rental_rate): Count the number for each film rental.
• (): Count the number of all videos.

In this case, the GROUPING SETS clause can be simplified by a CUBE clause, as follows:

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)

In the above statement, we used CUBE (rating, rental_rate) instead GROUPING SETS ((rating, rental_rate), (rating), (rental_rate),()). This makes the statement simpler and more readable.

Conclusion

In PostgreSQL GROUP BY clauses, you can use CUBE to simplify GROUPING SETS in specific cases.