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

The ROLLUP () function is an extension of GROUP BY grouping statistics, which can achieve the effect of summation of grouping statistics.

Let's begin to prepare the environment for our experiment.

-- create a new table employee_salary, which stores data from the user hr.employees

SQL > CREATE TABLE employee_salary ASSELECT E.FIRSTRANAMEPROGRAPHY E.JOBREID, E.MANAGERING IDD, E.SALARY FROM HR.EMPLOYEES E WHERE E.JOBING IDC

-- View the newly created table

SQL > SELECT * FROM employee_salary

-- the display effect is as follows

FIRST_NAME JOB_ID MANAGER_ID SALARY

Alexander IT_PROG 102 9000.00

Bruce IT_PROG 103 6000.00

David IT_PROG 103 4800.00

Valli IT_PROG 103 4800.00

Diana IT_PROG 103 4200.00

-- first grouped by JOB_ID to view salary and

SQL > SELECT sa.job_id,SUM (sa.salary) FROM employee_salary sa GROUP BY sa.job_id

-- the display effect is as follows

JOB_ID SUM (SA.SALARY)

IT_PROG 28800

-- grouped by MANAGER_ID to view salary and

SELECT sa.manager_id,SUM (sa.salary) FROM employee_salary sa GROUP BY sa.manager_id

-- the display effect is as follows

MANAGER_ID SUM (SA.SALARY)

102 9000

103 19800

Let's use the ROLLUP function to see what the effect is.

SELECT sa.job_id,sa.manager_id,SUM (sa.salary) FROM employee_salary sa GROUP BY ROLLUP (sa.job_id,sa.manager_id)

-- the display effect is as follows

JOB_ID MANAGER_ID SUM (SA.SALARY)

IT_PROG 102 9000

IT_PROG 103 19800

IT_PROG 28800

28800

Description: ROLLUP parsing process, take ROLLUP (aPermie b) as an example

ROLLUP (a) UNIONALL GROUP () = GROUP (a) UNIONALL GROUP ()

That is, the parsing order is from right to left, showing that it is grouped according to a, followed by a, and finally by the whole table.

The above ROLLUP (sa.job_id,sa.manager_id) is equivalent to the collection operation of the following UNION ALL

SELECT sa.job_id,sa.manager_id,SUM (sa.salary) FROM employee_salary sa

GROUP BY sa.job_id,sa.manager_id

UNION ALL

SELECT sa.job_id,NULL,SUM (sa.salary) FROM employee_salary sa

GROUP BY sa.job_id

UNION ALL

SELECT NULL,NULL,SUM (sa.salary) FROM employee_salary sa GROUP BY ()

ORDER BY 1,2

-- the display effect is as follows

JOB_ID MANAGER_ID SUM (SA.SALARY)

IT_PROG 102 9000

IT_PROG 103 19800

IT_PROG 28800

28800

Note: although the final effect shown is the same, the execution efficiency of the ROLLUP () function is higher and faster than that of UNION ALL.

ROLLUP is performing a combination operation without sequence, and the combination formula is (n = 1). When n = 3, there are four combination results.

If the position of the parameter in ROLLUP () is different, the result may be different!

It is easier to understand CUBE () on the basis of understanding ROLLUP. ROLLUP () is performing a combination operation, and CUBE () is performing a sorting action. From left to right, the sorting formula is to the 2N power.

CUBE (A Magi BMague C) = = GROUPBY (A Magi B Magi C) UNION ALL GROUPBY (A Magi B) UNION ALL GROUPBY (A Magi C) UNION ALL GROUPBY (A) UNION ALL GROUPBY (B) UNION ALL GROUPBY (C) UNION ALL GROUPBY ()

For example: execute the following statement

SQL > SELECT sa.job_id,sa.manager_id,SUM (sa.salary) FROM employee_salary sa GROUP BY CUBE (sa.job_id,sa.manager_id)

-- the display effect is as follows

JOB_ID MANAGER_ID SUM (SA.SALARY)

28800

102 9000

103 19800

IT_PROG 28800

IT_PROG 102 9000

IT_PROG 103 19800

6 rows selected

The above statement is equivalent to executing the following statement

SQL > SELECT sa.job_id,sa.manager_id,SUM (sa.salary) FROM employee_salary sa GROUP BY (sa.job_id,sa.manager_id)

UNION ALL

SELECT sa.job_id,NULL,SUM (sa.salary) FROM employee_salary sa

GROUP BY (sa.job_id)

UNION ALL

SELECT NULL,sa.manager_id,SUM (sa.salary) FROM employee_salary sa

GROUP BY (sa.manager_id)

UNION ALL

SELECT NULL,NULL,SUM (sa.salary) FROM employee_salary sa

GROUPBY ()

About GROUPING

GROUPING (A) is used to determine whether the grouped column is a null NULL. The return value has two values: 0 and 1. 1 indicates that the column is empty-- NULL. This null value is generated because it is grouped, otherwise it is 0.

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.