Postgresql GROUPING SETS、CUBE和ROLLUP

group sets
select * from test;
id | name
—–+———
1 | apple
2 | apple
3 | banana
4 | orange
5 | pumpkin
2 | apple
2 | apple
5 | orange
3 | apple
100 | apple
(10 rows)

select *,sum(id) from test group by grouping sets((id),(name),());
id | name | sum
—–+———+—–
1 | | 1
2 | | 6
3 | | 6
4 | | 4
5 | | 10
100 | | 100
| | 127
| apple | 110
| banana | 3
| orange | 9
| pumpkin | 5

分别按各个字段进行分组操作，为()则不进行分组

cube

CUBE ( a, b, c ) =GROUPING SETS (( a, b, c ),( a, b ),( a,c ),(a),( b, c ),(b),(c),())
如下，如果分组中是一个列表，那么这个列表一个分组单元来对待
CUBE ( (a, b), (c,d) ) = GROUPING SETS ((a,b,c,d),(a,b),(c,d),())

如：
hank=> select *,sum(id) from test group by grouping sets((id,name),(id),(name),());
id | name | sum
—–+———+—–
1 | apple | 1
1 | | 1
2 | apple | 6
2 | | 6
3 | apple | 3
3 | banana | 3
3 | | 6
4 | orange | 4
4 | | 4
5 | orange | 5
5 | pumpkin | 5
5 | | 10
100 | apple | 100
100 | | 100
| | 127
| apple | 110
| banana | 3
| orange | 9
| pumpkin | 5
(19 rows)

hank=> select *,sum(id) from test group by cube(id,name);
id | name | sum
—–+———+—–
1 | apple | 1
1 | | 1
2 | apple | 6
2 | | 6
3 | apple | 3
3 | banana | 3
3 | | 6
4 | orange | 4
4 | | 4
5 | orange | 5
5 | pumpkin | 5
5 | | 10
100 | apple | 100
100 | | 100
| | 127
| apple | 110
| banana | 3
| orange | 9
| pumpkin | 5
(19 rows)

rollup

rollup(a,b,c)=grouping set((a,b,c),(a,b),(a),())
和cube一样，这里（b,c）也是看做一个分组单元
ROLLUP ( a, (b, c), d )=grouping sets((a,b,c,d),(a,b,c),(a),())

select * from test;
id | name
—–+———
1 | apple
2 | apple
3 | banana
4 | orange
5 | pumpkin
2 | apple
2 | apple
5 | orange
3 | apple
100 | apple
(10 rows)

select *,sum(id) from test group by rollup(id,name);
id | name | sum
—–+———+—–
1 | apple | 1
1 | | 1
2 | apple | 6
2 | | 6
3 | apple | 3
3 | banana | 3
3 | | 6
4 | orange | 4
4 | | 4
5 | orange | 5
5 | pumpkin | 5
5 | | 10
100 | apple | 100
100 | | 100
| | 127

select *,sum(id) from test group by grouping sets((id,name),(id),());
id | name | sum
—–+———+—–
1 | apple | 1
1 | | 1
2 | apple | 6
2 | | 6
3 | apple | 3
3 | banana | 3
3 | | 6
4 | orange | 4
4 | | 4
5 | orange | 5
5 | pumpkin | 5
5 | | 10
100 | apple | 100
100 | | 100
| | 127
(15 rows)