Oracle enhanced grouping Functions

Oracle enhanced grouping Functions

The ROLLUP () function is an extension of group by grouping statistics, which can achieve the sum of GROUP statistics.

Now let's start preparing our testing environment.

-- Create a new table employee_salary to store data from the user hr. employees

SQL> CREATE TABLE employee_salary ASSELECT E. FIRST_NAME, E. JOB_ID, E. MANAGER_ID, E. SALARY FROM HR. EMPLOYEES E WHERE E. JOB_ID = 'it _ prog ';
-- 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

 

-- Group 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

-- Group 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 result 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. ROLLUP (a, B) is used as an example.

ROLLUP (a, B) = GROUP (a, B) UNION ALL GROUP (a) UNIONALL GROUP ()

That is, the resolution sequence is from right to left, which is displayed as group a and group B, followed by group a and finally group the entire table;

The preceding ROLLUP (sa. job_id, sa. manager_id) is equivalent to the union all set operation.

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 ()

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 display results are the same, the execution efficiency of the ROLLUP () function is higher and faster than that of the union all function.

ROLLUP (A, B, C) is A combination operation, no order, the combination formula is (n + 1), when n = 3, the combination result is 4.

The parameter positions in ROLLUP () are different, and the results may be different!

It is easier to understand CUBE () based on understanding ROLLUP. ROLLUP () is executing a combination operation, and CUBE () is executing a sort action, from left to right, the sorting formula is 2N power.

CUBE (A, B, C) = group by (A, B, C) union all group by (A, B) UNION ALL GROUP BY (A, C) union all groupby (A) union all group by (B) UNION ALL GROUP BY (C) UNION ALL GROUP ()

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 preceding 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 Column After GROUPING is NULL. The returned values include 0 and 1. 1 indicates that the column is empty -- NULL. This NULL value is generated when GROUPING, otherwise, the value is 0;

Migration from 32-bit to 64-bit for a single Oracle instance

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian