SUMMARY: This article discusses grouping sets, cubes, and rollups in PostgreSQL:
1. GROUPING SET
2. CUBE
3. ROLLUP
In this blog post, we are going to see how to use grouping sets, cubes, and rollups in PostgreSQL to generate result sets. These are group-by options added in PostgreSQL 9.5 that allow us to aggregate data by different GROUP BY clauses at once.
GROUPING SET
Let’s start with an example of how to use grouping sets:
1. Connect to psql terminal:
/usr/pgsql-11/bin/psql -U postgres postgres
2. Create a table:
CREATE TABLE my_test (
id int,
salary int,
revised_salary int,
old_salary int,
leave int,
joining_date date
);
3. Insert rows:
insert into my_test values(10,5000,4000,1000,5,'2019-11-15');
insert into my_test values(10,6000,9000,2000,1,'2019-11-16');
insert into my_test values(10,7000,3000,4400,2,'2019-11-17');
insert into my_test values(20,8000,4000,6600,6,'2019-11-15');
insert into my_test values(20,9000,9400,8800,10,'2019-11-16');
insert into my_test values(20,2000,7800,9400,23,'2019-11-17');
insert into my_test values(30,4400,6600,4200,44,'2019-11-15');
insert into my_test values(30,1500,3600,4300,66,'2019-11-16');
insert into my_test values(30,2000,2600,4500,77,'2019-11-17');
4. Use the SELECT query to see the rows:
postgres=# select * from my_test ;
id | salary | revised_salary | old_salary | leave | joining_date
----+--------+----------------+------------+-------+--------------
10 | 5000 | 4000 | 1000 | 5 | 2019-11-15
10 | 6000 | 9000 | 2000 | 1 | 2019-11-16
10 | 7000 | 3000 | 4400 | 2 | 2019-11-17
20 | 8000 | 4000 | 6600 | 6 | 2019-11-15
20 | 9000 | 9400 | 8800 | 10 | 2019-11-16
20 | 2000 | 7800 | 9400 | 23 | 2019-11-17
30 | 4400 | 6600 | 4200 | 44 | 2019-11-15
30 | 1500 | 3600 | 4300 | 66 | 2019-11-16
30 | 2000 | 2600 | 4500 | 77 | 2019-11-17
(9 rows)
postgres=#
Using GROUP BY on the dataset, the user can arrange identical data into groups.
Query A
In the example below, we are grouping the columns “salary” and “leave.” We are performing a sum operation on the “salary,” “revised_salary,” “old_salary,” and “total_leave” columns using aggregate function sum() and using the GROUP BY clause on the “id” column.
postgres=# select id, sum(salary) as total_salary,
sum(revised_salary) as total_revised_salary ,
sum(old_salary) as total_old_salary,
sum(leave) as total_leave
from my_test
group by id
order by id;
id | total_salary | total_revised_salary | total_old_salary | total_leave
----+--------------+----------------------+------------------+------------------------
10 | 18000 | 16000 | 7400 | 8
20 | 19000 | 21200 | 24800 | 39
30 | 7900 | 12800 | 13000 | 187
(3 rows)
Query B
Similarly, we can use GROUP BY on the “joining_date” column to generate a report:
postgres=# select joining_date, sum(salary) as total_salary,
sum(revised_salary) as total_revised_salary,
sum(old_salary) as total_old_salary ,
sum(leave) as total_leave
from my_test
group by joining_date
order by joining_date;
joining_date | total_salary | total_revised_salary | total_old_salary | total_leave
--------------+--------------+----------------------+------------------+----------------------------
2019-11-15 | 17400 | 14600 | 11800 | 55
2019-11-16 | 16500 | 22000 | 15100 | 77
2019-11-17 | 11000 | 13400 | 18300 | 102
(3 rows)
Combining Query A and Query B
If we want to combine query A and query B, we would have had to write a complicated SQL query, which in turn would have meant that the table would have to scan twice. GROUPING SETS allow us to avoid such scenarios and can be used to specify multiple groupings in a single query.
This is how we can combine queries A and B using GROUPING SETS:
postgres=# select id,joining_date,sum(salary) as total_salary,
sum(revised_salary) as total_revised_salary ,
sum(old_salary) as total_old_salary ,
sum(leave) as total_leave
from my_test
group by grouping sets ( id, joining_date)
order by id;
id | joining_date | total_salary | total_revised_salary | total_old_salary | total_leave
----+--------------+--------------+----------------------+------------------+----------------------------
10 | | 18000 | 16000 | 7400 | 8
20 | | 19000 | 21200 | 24800 | 39
30 | | 7900 | 12800 | 13000 | 187
| 2019-11-16 | 16500 | 22000 | 15100 | 77
| 2019-11-15 | 7400 | 14600 | 11800 | 55
| 2019-11-17 | 11000 | 13400 | 18300 | 102
(6 rows)
We can also use empty parentheses (), which gives us the total value:
postgres=# select id,joining_date,sum(salary) as total_salary
from my_test
group by grouping sets ( id, joining_date,())
order by id;
id | joining_date | total_salary
----+--------------+--------------
10 | | 18000
20 | | 19000
30 | | 7900
| | 44900
| 2019-11-16 | 16500
| 2019-11-15 | 17400
| 2019-11-17 | 11000
(7 rows)
Here we get another row which contains the total of sum(id) — i.e., 180000 + 190000 + 7900 = 44900.
CUBE
CUBE is used mostly for reporting purposes. With the help of CUBE, the user can extend the functionality of GROUP BY clauses by calculating total values (the sum at each level).
CUBE in the GROUP BY clause is used to give all the possible combinations. CUBE essentially contains every possible rollup scenario for each node (as we will see in the next section).
CUBE(X,Y,Z) will create grouping sets like this:
(X,Y,Z)
( ,Y,Z)
(X,Y, )
(X, ,Y)
(X)
(Y)
(Z)
()
Example
We want a query which is having GROUP BY all possible combination of “id” and “joining_date” columns of table “my_test”:
postgres=# select id,joining_date,sum(salary) as total_salary
,sum(revised_salary) as total_revised_salary ,
sum(old_salary) as total_old_salary ,
sum(leave) as total_leave
from my_test
group by cube ( id, joining_date)
order by id;
id | joining_date | total_salary | total_revised_salary | total_old_salary | total_leave
----+--------------+--------------+----------------------+------------------+-------------
10 | | 18000 | 16000 | 7400 | 8
10 | 2019-11-17 | 7000 | 3000 | 4400 | 2
10 | 2019-11-16 | 6000 | 9000 | 2000 | 1
10 | 2019-11-15 | 5000 | 4000 | 1000 | 5
20 | 2019-11-16 | 9000 | 9400 | 8800 | 10
20 | | 19000 | 21200 | 24800 | 39
20 | 2019-11-15 | 8000 | 4000 | 6600 | 6
20 | 2019-11-17 | 2000 | 7800 | 9400 | 23
30 | 2019-11-15 | 4400 | 6600 | 4200 | 44
30 | | 7900 | 12800 | 13000 | 187
30 | 2019-11-16 | 1500 | 3600 | 4300 | 66
30 | 2019-11-17 | 2000 | 2600 | 4500 | 77
| 2019-11-17 | 11000 | 13400 | 18300 | 102
| 2019-11-16 | 16500 | 22000 | 15100 | 77
| 2019-11-15 | 17400 | 14600 | 11800 | 55
| | 44900 | 50000 | 45200 | 234
(16 rows)
Rollup
Just like CUBE, ROLLUP is also used primarily for reporting purposes. ROLLUP is an analytical function used for bidirectional folding/calculation of data in a table, which creates a hierarchical rollup.
Example
In this example, using ROLLUP(id, joining_date), the sum(sal) is shown initially grouped by “joining_date,” and then the same sum(sal) is shown grouped by “id.”
ROLLUP(id,joining_date) will generate GROUPING SETS like this:
(id,joining_date)
(id)
()
postgres=# select id,joining_date,sum(salary) as total_salary,
sum(revised_salary) as total_revised_salary ,
sum(old_salary) as total_old_salary ,
sum(leave) as total_leave
from my_test
group by rollup ( id, joining_date) order by 1, 2;
id | joining_date | total_salary | total_revised_salary | total_old_salary | total_leave
----+--------------+--------------+----------------------+------------------+-------------
10 | 2019-11-15 | 5000 | 4000 | 1000 | 5
10 | 2019-11-16 | 6000 | 9000 | 2000 | 1
10 | 2019-11-17 | 7000 | 3000 | 4400 | 2
10 | | 18000 | 16000 | 7400 | 8
20 | 2019-11-15 | 8000 | 4000 | 6600 | 6
20 | 2019-11-16 | 9000 | 9400 | 8800 | 10
20 | 2019-11-17 | 2000 | 7800 | 9400 | 23
20 | | 19000 | 21200 | 24800 | 39
30 | 2019-11-15 | 4400 | 6600 | 4200 | 44
30 | 2019-11-16 | 1500 | 3600 | 4300 | 66
30 | 2019-11-17 | 2000 | 2600 | 4500 | 77
30 | | 7900 | 12800 | 13000 | 187
| | 44900 | 50000 | 45200 | 234
(13 rows)
Hope it helps!
Relevant Blogs
EDB Tutorial: How to run a TPC-E-like benchmark easily
The TPC Benchmark(TM) E (TPC-E) was released back in 2007 as the successor to the TPC Benchmark(TM) C (TPC-C) for an OLTP type workload. To briefly recap, the TPC-E models...EDB Tutorial: How to Use Privilege Analysis to Identify [Un]Used Privileges
Have you ever searched for unnecessary granted privileges in your database? To find these, you need to know 1. The granted privileges 2. How many of the granted privileges have...EDB Tutorial: How To Get the Most Out of EDB Query Advisor
Enterprise-class databases require DBAs who are responsible for maximizing query performance. Therefore, when it comes to large scale deployment of databases, minimizing the work of DBA is important. One of...More Blogs
EDB Tutorial: How to Configure Databases for EDB JDBC SSL Factory Classes
The fact is, SSL configuration required by EDB JDBC SSL factory classes is considered confusing and challenging by many EDB customers. This stems from the concepts behind the SSL handshake...