Rollup and cube of Oracle analysis functions

Introduction to using cube Functions

To introduce the cube function, let's take a look at another example of using rollup.
06:53:00 SQL> select area_code, bill_month, sum (local_fare) local_fare
06:53:37 2 from t
06:53:38 3 group by rollup (area_code, bill_month)
06:53:49 4/

Area_code bill_month local_fare
---------------------------------------
5761 200405 13060433.89
5761 200406 13318931.01
5761 200407 13710265.93
5761 200408 14135782.21
5761 54225413.04
5762 200405 12643792.11
5762 200406 12795060.65
5762 200407 13224298.12
5762 200408 13376468.72
5762 52039619.60
5763 200405 16649778.91
5763 200406 17120515.71
5763 200407 17487493.32
5763 200408 17928757.08
5763 69186545.02
5764 200405 12487791.94
5764 200406 13295187.67
5764 200407 13444093.76
5764 200408 13929695.09
5764 53156768.46
5765 200405 25057737.47
5765 200406 26058461.31
5765 200407 26301881.40
5765 200408 27130639.01
5765 104548719.19
333157065.31

26 rows selected.

Elapsed: 00:00:00. 00

The system only summarizes the data in the result set according to the first rollup parameter area_code, but does not summarize and analyze bill_month. The cube function is designed for this purpose.
Next, let's take a look at the results of using the cube function.

06:58:02 SQL> select area_code, bill_month, sum (local_fare) local_fare
06:58:30 2 from t
06:58:32 3 group by cube (area_code, bill_month)
06:58:42 4 order by area_code, bill_month nulls last
06:58:57 5/

Area_code bill_month local_fare
---------------------------------------
5761 200405 13060.43
5761 200406 13318.93
5761 200407 13710.27
5761 200408 14135.78
5761 54225.41
5762 200405 12643.79
5762 200406 12795.06
5762 200407 13224.30
5762 200408 13376.47
5762 52039.62
5763 200405 16649.78
5763 200406 17120.52
5763 200407 17487.49
5763 200408 17928.76
5763 69186.54
5764 200405 12487.79
5764 200406 13295.19
5764 200407 13444.09
5764 200408 13929.69
5764 53156.77
5765 200405 25057.74
5765 200406 26058.46
5765 200407 26301.88
5765 200408 27130.64
5765 104548.72
200405 79899.53
200406 82588.15
200407 84168.03
200408 86501.34
333157.05

30 rows selected.

Elapsed: 00:00:00. 01

We can see that the output result of the cube function is several rows more than that of rollup. This is the summary statistical result of the cube function based on bill_month.

Further exploration of rollup and cube Functions

From the above results, we can easily find that the row corresponding to each statistical data will show null,
How can we identify the summary based on that field,
At this time, Oracle's grouping function was unveiled.
If the current summary record is obtained using this field, the grouping function returns 1; otherwise, 0 is returned.

1 select decode (grouping (area_code), 1, 'all region', to_char (area_code) area_code,
2 decode (grouping (bill_month), 1, 'all month', bill_month) bill_month,
3 sum (local_fare) local_fare
4 From t
5 group by cube (area_code, bill_month)
6 * order by area_code, bill_month nulls last
07:07:29 SQL>/

Area_code bill_month local_fare
---------------------------------------
5761 200405 13060.43
5761 200406 13318.93
5761 200407 13710.27
5761 200408 14135.78
5761 all month 54225.41
5762 200405 12643.79
5762 200406 12795.06
5762 200407 13224.30
5762 200408 13376.47
5762 all month 52039.62
5763 200405 16649.78
5763 200406 17120.52
5763 200407 17487.49
5763 200408 17928.76
5763 all month 69186.54
5764 200405 12487.79
5764 200406 13295.19
5764 200407 13444.09
5764 200408 13929.69
5764 all month 53156.77
5765 200405 25057.74
5765 200406 26058.46
5765 200407 26301.88
5765 200408 27130.64
5765 all month 104548.72
All areas 200405 79899.53
All areas 200406 82588.15
All areas 200407 84168.03
All areas 200408 86501.34
All area all month 333157.05

30 rows selected.

Elapsed: 00:00:00. 01
07:07:31 SQL>

We can see that all the null values are now well distinguished by the grouping function, so we can use the rollup, cube, and grouping functions to make data statistics much easier.