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

This article is about how to understand the CUBE in the Oracle grouping function. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article.

Oracle's CUBE function is very similar to that of ROLLUP, and it is also a good hand in the field of data statistical analysis.

1. First, let's take a look at the statistical effect of ROLLUP.

1) create a test table group_test

SECOOLER@ora11g > create table group_test (group_id int, job varchar2 (10), name varchar2 (10), salary int)

Table created.

2) initialize data

Insert into group_test values (10 Bruce',1000 codingstones)

Insert into group_test values (10 million programmers, programmers, 1000)

Insert into group_test values (10 million Architects, 'Gideon',1000)

Insert into group_test values (10 million director, 'Hill',1000)

Insert into group_test values (20 Jason',2000 codingbirds)

Insert into group_test values (20 million programmers, 20 programmers, 2 thousand)

Insert into group_test values (20century architect, 'Martin',2000)

Insert into group_test values (20 director director, 'Michael',2000)

Insert into group_test values (30 minutes codingstone, 'Rebecca',3000)

Insert into group_test values (30 million programmers, 30 programmers, 30 programmers, 3000)

Insert into group_test values (30 minutes architect, 'Richard',3000)

Insert into group_test values (30 minutes director, 'Sabrina',3000)

Insert into group_test values (40 million codingbirds, Samuel',4000)

Insert into group_test values (40 million programmers, 40 girls, 4000 susyages)

Insert into group_test values (40 million Architects, 'Tina',4000)

Insert into group_test values (40 director director, 'Wendy',4000)

Commit

3) the data after initialization is as follows:

SECOOLER@ora11g > set pages 100

SECOOLER@ora11g > select * from group_test

GROUP_ID JOB NAME SALARY

--

10 Coding Bruce 1000

10 Programmer Clair 1000

10 Architect Gideon 1000

10 Director Hill 1000

20 Coding Jason 2000

20 Programmer Joey 2000

20 Architect Martin 2000

20 Director Michael 2000

30 Coding Rebecca 3000

30 Programmer Rex 3000

30 Architect Richard 3000

30 Director Sabrina 3000

40 Coding Samuel 4000

40 Programmer Susy 4000

40 Architect Tina 4000

40 Director Wendy 4000

16 rows selected.

4) the statistical effect of ROLLUP

Sec@ora10g > select group_id,job,grouping (GROUP_ID), grouping (JOB), sum (salary) from group_test group by rollup (group_id,job)

GROUP_ID JOB GROUPING (GROUP_ID) GROUPING (JOB) SUM (SALARY)

--

10 Coding 00 1000

10 Director 00 1000

10 Architect 00 1000

10 Programmer 00 1000

10 0 1 4000

20 Coding 00 2000

20 Director 00 2000

20 Architect 00 2000

20 Programmer 00 2000

20 0 1 8000

30 Coding 00 3000

30 Director 00 3000

30 Architect 00 3000

30 Programmer 00 3000

30 0 1 12000

40 Coding 00 4000

40 Director 00 4000

40 Architect 00 4000

40 Programmer 00 4000

40 0 1 16000

1 1 40000

21 rows selected.

two。 Further experience the charm of CUBE

Sec@ora10g > select group_id,job,grouping (GROUP_ID), grouping (JOB), sum (salary) from group_test group by cube (group_id,job) order by 1

GROUP_ID JOB GROUPING (GROUP_ID) GROUPING (JOB) SUM (SALARY)

--

10 Architect 00 1000

10 Coding 00 1000

10 Director 00 1000

10 Programmer 00 1000

10 0 1 4000

20 Architect 00 2000

20 Coding 00 2000

20 Director 00 2000

20 Programmer 00 2000

20 0 1 8000

30 Architect 00 3000

30 Coding 00 3000

30 Director 00 3000

30 Programmer 00 3000

30 0 1 12000

40 Architect 00 4000

40 Coding 00 4000

40 Director 00 4000

40 Programmer 00 4000

40 0 1 16000

Architect 10 10000

Coding 10 10000

Director 10 10000

Programmer 10 10000

1 1 40000

25 rows selected.

Explain the meaning of the values "0" and "1" returned by the GROUPING function in the above result.

If the display of "1" indicates that the column corresponding to the CUBE function (such as the JOB field) is the information corresponding to the null value generated by the CUBE function, that is, the result of the summary calculation for this column.

If "0" is displayed, the column parameter corresponding to this row is not grouped with the ROLLUP function to summarize the activity.

If you still don't understand clearly, see the description in Oracle's official documentation: "Using a single column as its argument,GROUPINGreturns 1 when it encounters aNULLvalue created by aROLLUPorCUBEoperation. That is, if theNULLindicates the row is a subtotal,GROUPINGreturns a 1. Any other type of value, including a storedNULL, returns a 0."

3. Take a closer look at the nuances between CUBE and ROLLUP

The statistical column of rollup (a) includes: (a), (a), ()

The statistical series of rollup (arecine bpencil c) includes: (arecine b), (arecine b), (a), ()

…… And so on, ing...

The statistical column of cube (a) includes: (a), (a), (b), ()

The statistical series of cube (aforce bpencil c) includes: (a), (b), (c), (c), ()

…… And so on, ing...

So, in the above example, the result of CUBE has more statistics than ROLLUP about the first column of GROUP_ID:

Architect 10 10000

Coding 10 10000

Director 10 10000

4. Summary

CUBE gives detailed statistical summary results from various dimensions on the basis of ROLLUP.

The above is how to understand the CUBE in the Oracle grouping function. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.