PostgreSQL 9.5: what's new? Part 3. GROUPING SETS, CUBE, ROLLUP

We continue to get acquainted with new features in PostgreSQL 9.5.
Part 1. INSERT ... ON CONFLICT DO NOTHING / UPDATE and ROW LEVEL SECURITY
Part 2. TABLESAMPLE
Today we consider multiple groups in one query. This feature was described in the SQL-99 standard. It is convenient to use it if you need to make several queries to the same table, differing only in the GROUP BY clause . For this, GROUPING SETS, ROLLUP, CUBE modifiers are specified as a grouping element after the GROUP BY keyword.
Let's take a closer look at how this works.

Suppose we have a scheme in which there is data on payments, each payment has a type, a city in which this payment was made and the amount of the payment. The city necessarily has a country and, optionally, a region. Below are the requests to create such a scheme and fill it with data.

Suppose we want to get statistics on the amount of payments in each city and in each country. Previously, it was necessary to write a query of the form:

 (SELECT sum(amount), c.country_id, NULL as city_id FROM payment AS p INNER JOIN city AS c ON p.city_id=c.id GROUP BY c.country_id ORDER BY c.country_id) UNION ALL (SELECT sum(amount), NULL, p.city_id FROM payment AS p GROUP BY p.city_id ORDER BY p.city_id) 

From version 9.5 it is easier to write a similar request like this:

 SELECT sum(amount), c.country_id, p.city_id FROM payment AS p INNER JOIN city AS c ON p.city_id = c.id GROUP BY GROUPING SETS(c.country_id, p.city_id); 

As you can see, the GROUPING SETS as a result of the query returns data as follows: in each row of one of the columns listed in brackets corresponds to the value, while the remaining columns (from the list in brackets) are filled with NULL. Columns not listed in GROUPING SETS are calculated as usual.

To get the full amount (without grouping), you can use the empty grouping - () . With empty grouping, all fields participating in GROUPING SETS are NULL:

 SELECT sum(amount), p.city_id, c.country_id FROM payment AS p INNER JOIN city AS c ON p.city_id = c.id GROUP BY GROUPING SETS(p.city_id, C.country_id, ()); 

Now let's try to get the amount of payments in the context of cities, regions and countries:

 SELECT sum(amount), p.city_id, c.state_id, c.country_id FROM payment AS p INNER JOIN city AS c ON p.city_id = c.id GROUP BY GROUPING SETS(p.city_id, c.state_id, c.country_id); 

Strange, we did not make an empty grouping, but received a string in which all fields are NULL. In fact, this happened because the state_id field was not filled in in Moscow and Kiev, therefore GROUPING SETS rightly did the grouping by state_id = NULL . This is easily verified by running the following query:

 SELECT sum(amount) FROM payment WHERE city_id IN (1, 9); 

Yes, our assumption turned out to be true and the amounts coincided.

Well, where did this strange line come from, we figured it out, but how to distinguish in the next query, which of the lines contains the full amount, and which group is state_id = NULL ?

 SELECT sum(amount), p.city_id, c.state_id, c.country_id FROM payment AS p INNER JOIN city AS c ON p.city_id = c.id GROUP BY GROUPING SETS(p.city_id, c.state_id, c.country_id, ()); 

So in fact, the total value will be greater, you say, and you will be right. Of course, in this query, you can understand that the line with the larger amount is the full amount. However, if the table had not only positive values, but also negative ones, it would be more difficult to determine the full amount. Well, or if you use another aggregate function:

 SELECT avg(amount), p.city_id, c.state_id, c.country_id FROM payment AS p INNER JOIN city AS c ON p.city_id = c.id GROUP BY GROUPING SETS(p.city_id, c.state_id, c.country_id, ()); 

Which line corresponds to the average amount of payment in the table, and which - the average amount in Kiev and Moscow?
Fortunately, there is a solution: the new grouping () function, with the help of it we can find out whether a particular column is participating in a given row in a grouping. If grouping (column_name) returns 0, then column_name participates in the grouping, if 1 - does not participate:

 SELECT avg(amount), p.city_id, c.state_id, c.country_id, grouping(c.state_id) FROM payment AS p INNER JOIN city AS c ON p.city_id = c.id GROUP BY GROUPING SETS(p.city_id, c.state_id, c.country_id, ()); 

In fact, grouping returns a bitmask for the columns listed in it:

 SELECT avg(amount), p.city_id, c.state_id, c.country_id, grouping(p.city_id, c.state_id, c.country_id) FROM payment AS p INNER JOIN city AS c ON p.city_id = c.id GROUP BY GROUPING SETS(p.city_id, c.state_id, c.country_id, ()); 

So it is not very clear, we will bring the result to the bit (3) type :

 SELECT avg(amount), p.city_id, c.state_id, c.country_id, grouping(p.city_id, c.state_id, c.country_id) :: BIT(3) FROM payment AS p INNER JOIN city AS c ON p.city_id = c.id GROUP BY GROUPING SETS(p.city_id, c.state_id, c.country_id, ()); 

You can also use common grouping and grouping together in a single query using GROUPING SETS :

 SELECT avg(amount), c.country_id, p.payment_type_id, p.city_id, c.state_id FROM payment AS p INNER JOIN city AS c ON p.city_id = c.id GROUP BY c.country_id, p.payment_type_id, GROUPING SETS(p.city_id, c.state_id, ()); 

You can combine the columns inside the GROUPING SETS into groups

 SELECT avg(amount), c.country_id, p.payment_type_id, p.city_id, c.state_id FROM payment AS p INNER JOIN city AS c ON p.city_id = c.id GROUP BY GROUPING SETS((p.payment_type_id, c.country_id), ( c.state_id, p.city_id)); 

We now turn to CUBE . CUBE is something like a multiple GROUPING SETS .
CUBE returns data for all possible combinations of columns listed inside. That is, for the case of CUBE ( c1, c2, c3 ) (where c1, c2, c3 are the column names), the following combinations will be returned:
(c1, null, null)
(null, c2, null)
(null, null, c3)
(c1, c2, null)
(c1, null, c3)
(null, c2, c3)
(c1, c2, c3)
(null, null, null)
Example:

 SELECT sum(amount), p.payment_type_id, c.country_id, p.city_id FROM payment AS p INNER JOIN city AS c ON p.city_id = c.id GROUP BY CUBE(p.payment_type_id, c.country_id, p.city_id); 

As in GROUPING SETS, you can do grouping inside:

 SELECT sum(amount), p.payment_type_id, c.country_id, p.city_id FROM payment AS p INNER JOIN city AS c ON p.city_id = c.id GROUP BY CUBE(p.payment_type_id, (c.country_id, p.city_id)); 

ROLLUP is the same as CUBE - something like multiple GROUPING SETS , with the difference that ROLLUP generates combinations, removing columns one by one from the end. Thus, ROLLUP ( c1, c2, c3, c4 ) will return the following combinations:
(c1, c2, c3, c4)
(c1, c2, c3, null)
(c1, c2, null, null)
(c1, null, null, null)
(null, null, null, null)
Example:

 SELECT sum(amount), p.payment_type_id, c.country_id, p.city_id FROM payment AS p INNER JOIN city AS c ON p.city_id = c.id GROUP BY ROLLUP(p.payment_type_id, c.country_id, p.city_id); 

In conclusion, I want to say that, besides the convenience of writing, these queries are potentially (not yet merit), work faster, since to execute a query you need only one table scan instead of several that are performed in the case of UNION ALL .
')
Thanks for attention!