...
Column1 (key) | Column2 (value) |
---|---|
1 | NULL |
1 | 1 1 |
2 | 2 |
1 3 | 3 |
3 | NULL |
4 | 5 |
The following query: SELECT key, value, GROUPING__ID, count from T1 GROUP BY key, value WITH ROLLUP
will have the following results.
|
|
|
|
---|---|---|---|
NULL | NULL | 0 | 6 |
1 | NULL | 1 | 2 |
1 | NULL | 3 | 1 |
1 | 1 | 3 | 1 |
1 | NULL | 1 | 1 |
2 | 2 | 3 | 1 |
3 | NULL | 1 | 2 |
3 | NULL | 3 | 1 |
3 | 3 | 3 | 1 |
4 | NULL | 1 | 1 |
4 | 5 | 3 | 1 |
Note that the third column is a bitvector of columns being selected.
For the first row, none of the columns are being selected.
Cubes and Rollups
The general syntax is CUBE ( ). It is used with the GROUP BY only. CUBE creates a subtotal of all possible combinations of the set of column in its argument. Once we compute a CUBE on a set of dimension, we can get answer to all possible aggregation questions on those dimensions.
...