Extended functions for GROUP by in Oracle Rollup, cube, grouping sets

In addition to the basic usage of Oracle's group BY, there are 3 extension usages, namely rollup, cube, grouping sets, respectively, described below:

1, Rollup

For the database table EMP, suppose that two of the fields are named A,b,c.

If you use GROUP by rollup (A, B), group by IS first performed on (A, a), then group by for a, and the group by operation for the whole table.

The following query results:

Query statements

Select Deptno,job,sum (SAL) from the EMP Group by Rollup (Deptno,job);

Equivalent to

Select Deptno,job,sum (SAL) from the EMP Group by Deptno,job

UNION ALL

Select deptno,null sum (SAL) from the EMP Group by Deptno

UNION ALL

Select Null,null,sum (SAL) from EMP (GROUP by NULL)

2. Cube

If a group by cube (A, B) is used, a group by IS first performed on (A, a), followed by (a), (b.), and finally the group by operation of the whole table, which is 2^2=4 times grouping

The following query results;

Query statements

Select Deptno,job,sum (SAL) from the EMP Group by Cube (Deptno,job);

Equivalent to

Select Deptno,job,sum (SAL) from the EMP Group by Deptno,job

UNION ALL

Select deptno,null sum (SAL) from the EMP Group by Deptno

UNION ALL

Select null,job, sum (SAL) from the 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 in the parameter, if group by grouping sets (A, b) is used, the group by

The following query results:

Query statements

Select Deptno,job,sum (SAL) from the EMP Group by Cube (Deptno,job);

Equivalent to

Select Null,job,sum (SAL) from the EMP group by job

UNION ALL

Select Deptno,null,sum (SAL) from the EMP Group by Deptno

4, grouping

The grouping can be used to determine whether the row is the original row in the database, or there is a statistically generated row, grouping value of 0 indicates that the value is the original value in the database, the 1 description is the result of statistics, only one parameter, and must be a group by the occurrence of a column

The following query results:

Query statements

Select Deptno,job,sum (SAL), grouping (DEPTNO) a,grouping (Job) b from the EMP GROUP by Rollup (Deptno,job);

5, grouping_id

The return value of grouping_id () is actually the binary vector of the Grouping () value of each column in the parameter, and if Grouping (a) =1,grouping (b) = 1, then the return value of grouping_id (A, B) is the binary 11, Turn into 10 and the binary is 3. The parameter can be multiple, but must be a column that appears in group by.

The query results are as follows:

Query statements

Select Deptno,job,sum (SAL), grouping (DEPTNO) a,grouping (Job) b,grouping_id (deptno,job) from the EMP Group by rollup (DEPTNO, Job);

6, group_id

GROUP_ID () uniquely identifies a repeating group that can be removed by group_id

The query results are as follows:

Query statements

Select Deptno,job,sum (SAL), group_id () from the EMP Group by DEPTNO, Rollup (deptno,job) has group_id () = 0;

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Extended functions for GROUP by in Oracle Rollup, cube, grouping sets