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

How to understand the ROLLUP of Oracle grouping function? in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

By demonstrating the usage of Oracle ROLLUP grouping function, we can experience the functional charm of Oracle in the field of statistical query. The ROLLUP grouping function can be understood as the simplified usage of the encapsulated Group By grouping function. At the same time, the rewriting idea of ROLLUP's Group By is given.

1. Initialize the experimental environment

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.

two。 First take a look at the effect of ordinary grouping: perform ordinary group by operations on group_id-grouping according to groups

SECOOLER@ora11g > select group_id,sum (salary) from group_test group by group_id

GROUP_ID SUM (SALARY)

--

30 12000

20 8000

40 16000

10 4000

3. Perform ordinary roolup operations on group_id-grouping according to groups, while calculating the total

SECOOLER@ora11g > select group_id,sum (salary) from group_test group by rollup (group_id)

GROUP_ID SUM (SALARY)

--

10 4000

20 8000

30 12000

40 16000

40000

Use the Group By statement to translate the above SQL statement as follows (union all a row that counts all the data):

SECOOLER@ora11g > select group_id,sum (salary) from group_test group by group_id

2 union all

3 select null, sum (salary) from group_test

4 order by 1

GROUP_ID SUM (SALARY)

--

10 4000

20 8000

30 12000

40 16000

40000

4. Take another look at the case of two columns of rollup.

SECOOLER@ora11g > select group_id,job,sum (salary) from group_test group by rollup (group_id,job)

GROUP_ID JOB SUM (SALARY)

10 Coding 1000

10 Director 1000

10 Architect 1000

10 Programmer 1000

10 4000

20 Coding 2000

20 Director 2000

20 Architect 2000

20 Programmer 2000

20 8000

30 Coding 3000

30 Director 3000

30 Architect 3000

30 Programmer 3000

30 12000

40 Coding 4000

40 Director 4000

40 Architect 4000

40 Programmer 4000

40 16000

40000

21 rows selected.

How can the above SQL statement be translated using Group By?

The answers are as follows:

SECOOLER@ora11g > select group_id,job,sum (salary) from group_test group by group_id,job

2 union all

3 select group_id,null,sum (salary) from group_test group by group_id

4 union all

5 select null,null,sum (salary) from group_test

6 order by 1,2

GROUP_ID JOB SUM (SALARY)

10 Architect 1000

10 Coding 1000

10 Director 1000

10 Programmer 1000

10 4000

20 Architect 2000

20 Coding 2000

20 Director 2000

20 Programmer 2000

20 8000

30 Architect 3000

30 Coding 3000

30 Director 3000

30 Programmer 3000

30 12000

40 Architect 4000

40 Coding 4000

40 Director 4000

40 Programmer 4000

40 16000

40000

21 rows selected.

5. As an additional step, experience the effect of the GROUPING function.

Just look at the effect and OK:

SECOOLER@ora11g > 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.

Do you see any effect?

Some students still do not see it, a little explanation:

If the display of "1" indicates that the column corresponding to the GROUPING function (such as the JOB field) is the information corresponding to the null value generated by the ROLLUP 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, GROUPING returns 1 when it encounters a NULL value created by a ROLLUP or CUBE operation. That is, if the NULL indicates the row is a subtotal, GROUPING returns a 1. Any other type of value, including a stored NULL, returns a 0."

6. Summary

ROLLUP brings great convenience in the process of data statistics and report generation, and the efficiency is much higher than that of Group By + Union combination method. This also reflects the characteristics of humanization, automation and high efficiency of Oracle in SQL statistical analysis.

The answer to the ROLLUP question on how to understand the Oracle grouping function is shared here. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

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.