ORACLEROLLUP and CUBE Functions

In addition to the basic syntax, Oracle GROUPBY statements also support ROLLUP and CUBE statements. If it is ROLLUP (A, B, C), it first performs GROUPBY on (A, B, C), then performs GROUPBY on (A, B), and then () perform the GROUPBY operation, and then perform the GROUPBY operation on the entire table. For GROUPBYCUBE (A, B, C ),

In addition to the most basic syntax, Oracle group by statements also support ROLLUP and CUBE statements. If ROLLUP (A, B, C) is used, group by is performed on (A, B, C) and group by is performed on (A, B, then perform group by for (A) And then perform group by for the entire table. For group by cube (A, B, C ),

In addition to the most basic syntax, Oracle group by statements also support ROLLUP and CUBE statements. If ROLLUP (A, B, C) is used, group by is performed on (A, B, C) and group by is performed on (A, B, then perform group by for (A) And then perform group by for the entire table. For group by cube (A, B, C), group by (A, B, C) is first performed, followed BY (A, B ), (A, C), (A), (B, C), (B), (C), and finally perform the GROUP BY operation on the entire table. Grouping_id () can beautify the effect.

I. CUBE and ROLLUP

Example:

SELECT
MANAGER_ID, DEPARTMENT_ID, SUM (SALARY) DD
FROM EMPLOYEES EMP
WHERE DEPARTMENT_ID IN (20, 30)
Group by cube (DEPARTMENT_ID, MANAGER_ID)

Returned results:

MANAGER_IDDEPARTMENT_IDDD

43700
10024000
11413700
2016000
2019000
1002013000
201206000
3024700
1003011000
1143013700

Ii. Use GROUPING to beautify data results

SELECT
GROUPING (MANAGER_ID) G_MANAGER_ID,
GROUPING (DEPARTMENT_ID) G_DEPARTMENT_ID,
MANAGER_ID,
DEPARTMENT_ID,
SUM (SALARY) DD
FROM EMPLOYEES EMP
WHERE DEPARTMENT_ID IN (20, 30)
Group by cube (DEPARTMENT_ID, MANAGER_ID)

Returned results:

Enter 1 in the NULL position GROUPING.

3. The GROUPING_ID () function can accept one or more columns and return the decimal value of the GROUPING bit vector.

Example:

SELECT
GROUPING (DEPARTMENT_ID) W,
GROUPING (MANAGER_ID) Q,
GROUPING (DEPARTMENT_ID) | GROUPING (MANAGER_ID) E,
GROUPING_ID (DEPARTMENT_ID, MANAGER_ID) R,
MANAGER_ID,
DEPARTMENT_ID,
SUM (SALARY) DD
FROM EMPLOYEES EMP
WHERE DEPARTMENT_ID IN (20, 30)
Group by cube (DEPARTMENT_ID, MANAGER_ID)

Returned results:

Use the GROUPING_ID () function to filter row data that only has subtotal and total:

SELECT
GROUPING (DEPARTMENT_ID) W,
GROUPING (MANAGER_ID) Q,
GROUPING (DEPARTMENT_ID) | GROUPING (MANAGER_ID) E,
GROUPING_ID (DEPARTMENT_ID, MANAGER_ID) R,
MANAGER_ID,
DEPARTMENT_ID,
SUM (SALARY) DD
FROM EMPLOYEES EMP
WHERE DEPARTMENT_ID IN (20, 30)
Group by cube (DEPARTMENT_ID, MANAGER_ID)
HAVING GROUPING_ID (DEPARTMENT_ID, MANAGER_ID)> 0

Iv. GROUP_ID ()The function can be used to eliminate repeated records returned BY the group by clause. GROUP_ID () does not accept any parameters. If a specific group appears n times, GROUP_ID () returns an integer from 0 to n-1.

First look at the effect when GROUP_ID () is not used

SELECT
DEPARTMENT_ID,
MANAGER_ID,
GROUP_ID () D,
SUM (SALARY) DD
FROM EMPLOYEES EMP
WHERE DEPARTMENT_ID IN (20)
Group by DEPARTMENT_ID, CUBE (DEPARTMENT_ID, MANAGER_ID)
Order by DEPARTMENT_ID, MANAGER_ID

Returned result: the record whose DEPARTMENT_ID is NULL is not in the following set, but there are several more repeated data records (D = 1)

Use GROUP_ID () to filter data, GROUP_ID () = 0

SELECT
DEPARTMENT_ID,
MANAGER_ID,
GROUP_ID (),
SUM (SALARY) DD
FROM EMPLOYEES EMP
WHERE DEPARTMENT_ID IN (20, 30)
Group by DEPARTMENT_ID, CUBE (DEPARTMENT_ID, MANAGER_ID)
HAVING GROUP_ID () = 0
Order by DEPARTMENT_ID, MANAGER_ID

Returned results: