postgresql, named grouping sets?

Question

Is there a way to name grouping sets? For each grouping set (explicitly defined or generated with rollup or cube as per https://www.postgresql.org/docs/devel/static/queries-table-expressions.html), I'd like to somehow specify a name in a result column. Here is an ugly instant demo of what I'm trying to do, with the name just being a list of the grouped columns:

select *, array_to_string(array_remove(array[case when "A" is null then null else 'A' end,
                                             case when "B" is null then null else 'B' end,
                                             case when "C" is null then null else 'C' end
                                            ],null),',') as grouping_set
from (values  ('a','b','c'),
              ('aa','bb','cc'),
              ('aaa',null,'ccc')) as foo("A","B","C")
group by rollup(1,2,3);


  A  | B  |  C  | grouping_set
-----+----+-----+--------------
 a   | b  | c   | A,B,C
 a   | b  |     | A,B
 a   |    |     | A
 aa  | bb | cc  | A,B,C
 aa  | bb |     | A,B
 aa  |    |     | A
 aaa |    | ccc | A,C   <--------- should be A,B,C
 aaa |    |     | A     <--------- should be A,B
 aaa |    |     | A 
     |    |     |

But notice there's a problem with two of the rows, flagged with the arrows: both of those include column B in the grouping but not in the name because B is null in those groups.

Any ideas or better ways of going about this?

score 7 · Answer 1 · edited May 23 '17 at 12:09

SELECT "A", "B", "C",
       CASE GROUPING("A", "B", "C")
          WHEN 0 THEN 'A,B,C'
          WHEN 1 THEN 'A,B'
          WHEN 3 THEN 'A'
          ELSE ''
       END AS grouping_set
FROM (VALUES ('a','b','c'),
             ('aa','bb','cc'),
             ('aaa',null,'ccc')
     ) AS foo("A","B","C")
GROUP BY ROLLUP(1,2,3);

Relevant documentation on grouping sets as per Clodoaldo Neto's answer.

Clodoaldo Neto · Accepted Answer · 2017-01-25T16:20:19.673

Grouping Operations

Grouping operations are used in conjunction with grouping sets (see Section 7.2.4) to distinguish result rows. The arguments to the GROUPING operation are not actually evaluated, but they must match exactly expressions given in the GROUP BY clause of the associated query level. Bits are assigned with the rightmost argument being the least-significant bit; each bit is 0 if the corresponding expression is included in the grouping criteria of the grouping set generating the result row, and 1 if it is not.

select *, grouping("A","B","C") as grouping_set
from (values
    ('a','b','c'),
    ('aa','bb','cc'),
    ('aaa',null,'ccc')
) as foo("A","B","C")
group by rollup(1,2,3);
  A  | B  |  C  | grouping_set 
-----+----+-----+--------------
 a   | b  | c   |            0
 a   | b  |     |            1
 a   |    |     |            3
 aa  | bb | cc  |            0
 aa  | bb |     |            1
 aa  |    |     |            3
 aaa |    | ccc |            0
 aaa |    |     |            1
 aaa |    |     |            3
     |    |     |            7

postgresql, named grouping sets?

2 Answers2