Extended rollup, cube, and groupingsets of groupby in Oracle

Extended rollup, cube, and groupingsets of groupby in Oracle

In addition to the basic usage, Oracle group by has three extended usage types: rollup, cube, and grouping sets, which are described as follows:

1. rollup

For the database table emp, assume that the two fields are a, B, and c.

If group by rollup (a, B) is used, group by is performed on (a, B), group by is performed on a, and group by is performed on the entire table.

The following query results:

 

Query statement

Select deptno, job, sum (sal) from emp group by rollup (deptno, job );

Equivalent

Select deptno, job, sum (sal) from emp group by deptno, job

Union all

Select deptno, null sum (sal) from emp group by deptno

Union all

Select null, null, sum (sal) from emp (group by null)

 

2. cube

If you use group by cube (a, B), group by (a, B) is first performed, followed by (a), (B ), finally, perform the group by operation on the entire table. The total number of grouping operations is 2 ^ 2 = 4.

The following query results;

 

Query statement

Select deptno, job, sum (sal) from emp group by cube (deptno, job );

Equivalent

Select deptno, job, sum (sal) from emp group by deptno, job

Union all

Select deptno, null sum (sal) from emp group by deptno

Union all

Select null, job, sum (sal) from emp group by job

Union all

Select null, null, sum (sal) from emp (group by null)

 

3. grouping sets

Grouping sets is the grouping of each parameter. If group by grouping sets (a, B) is used, group by (a) and (B) are performed.

The following query results:

 

Query statement

Select deptno, job, sum (sal) from emp group by cube (deptno, job );

Equivalent

Select null, job, sum (sal) from emp group by job

Union all

Select deptno, null, sum (sal) from emp group by deptno

 

4. grouping

Grouping can be used to determine whether the row is the original row in the database or the row generated by statistics. If the grouping value is 0, it indicates that the value is the original value in the database, 1 indicates the statistical result. There is only one parameter and it must be a column in group.

The following query results:

Query statement

Select deptno, job, sum (sal), grouping (deptno) a, grouping (job) B from emp group by rollup (deptno, job );

 

5. grouping_id

The return value of Grouping_id () is the binary vector of the grouping () value of each column in the parameter. If grouping (a) = 1, grouping (B) = 1, then grouping_id (, b) the return value is the binary 11, and the hexadecimal value is 3. The parameter can be multiple, but must be a column in group.

The query result is as follows:

 

Query statement

Select deptno, job, sum (sal), grouping (deptno) a, grouping (job) B, grouping_id (deptno, job) from emp group by rollup (deptno, job );

 

6. group_id

GROUP_ID () uniquely identifies a duplicate group. You can use group_id to remove duplicate groups.

The query result is as follows:

 

Query statement

Select deptno, job, sum (sal), group_id () from emp group by deptno, rollup (deptno, job) having group_id () = 0;