Shulou(Shulou.com)06/01 Report--

Some partners will find that the data statistics report will often make a summary of the column at the end, so which functions are used to achieve the summary in oracle? Today, let's talk about the distinction between the rollup function and the cube function. First, create a table tmp1 with the following data:

So, let's take a look at what the data compiled by cube looks like.

Select tweets classrooms address, (t_number) t_number from tmp1 group by cube (tweets classbooks address)

Some partners may have found that the data collected by the cube function is equivalent to the data of all possibilities. Yes, in fact, this sql statement is equivalent to the following union all statement:

Select null,null,sum (t_number) t_number from tmp1

Union all

Select null,t_address,sum (t_number) t_number from tmp1 group by t_address

Union all

Select tincture classbook t_number from tmp1 group by t_class (t_number)

Union all

Select tweets classrooms address sum (t_number) t_number from tmp1 group by tweets classrooms tweets address

So, now let's take a look at what the data compiled by rollup looks like.

Select tweets classrooms address, (t_number) t_number from tmp1 group by rollup (tweets classbooks address)

Some partners may have found that the data collected by the rollup function can also be realized by using the union all statement:

Select null,null,sum (t_number) t_number from tmp1

Union all

Select tincture classbook t_number from tmp1 group by t_class (t_number)

Union all

Select tweets classrooms address sum (t_number) t_number from tmp1 group by tweets classrooms tweets address

* * Summary: * * if you use group by rollup, you will first GROUP BY (A, B, C), then GROUP BY (A, B), then GROUP BY (A), and finally GROUP BY the whole table. Rollup means "roll up", which can also help us to understand that group by rollup is to grouping the selected column one column at a time from right to left until all the columns are removed (that is, the full table grouping). For n-parameter rollup, there is an one-time grouping. Cube means cubic. For each parameter of cube, it can be understood as a dimension that participates in grouping and does not participate in grouping, and then the set of values of all dimensions is the set of grouping. For n parameters of cube, there are 2 ^ n times of grouping. If you use group by cube, you will first GROUP BY (A, B, C), then (A, B), (A, C), (A), (B, C), (B), (C), and finally GROUP BY the whole table, with a total of 2 ^ 3 = 8 grouping. In addition, when null values also exist in the actual table, how do you distinguish between the null values generated by the cube and rollup operators? At this time, we can use the grouping function to distinguish the null values of the t_class column. Here we will give a simple example to distinguish the null values of the t_class column. The sql is as follows: select tasking classbook classful address, (t_number) tweak numbering grouping (t_class) from tmp1

Group by rollup (tweeted classrecently recently address)

You can see from the figure that there are two numbers 0 and 1 in the grouping (t_class) column, where 1 represents the null value caused by the rollup operator, and the remaining null values are the null values of the fact data. In fact, grouping is an aggregate function that produces an additional column with a value of 1 when rows are added with the cube or rollup operator, and 0. 0 when the rows are not generated by cube or rollup.

I hope it can be helpful to all of you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.