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

In addition to the basic use of Oracle's group BY, there are 3 ways to use extensions, each of which is rollup, cube, grouping sets. Each of the following is described as:

1, Rollup

To the database table EMP. If two of the fields are named A,b,c.

Assuming that group by rollup (A, b) is used, group by IS first performed on (A, a) and then group by for A. Finally, a group by operation is performed on the whole table.

For example, 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

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

For example, 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 of the participants in the number of references. Suppose you use GROUP by grouping sets (A, B). (a), (b) the group by

For example, 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

You can use grouping to infer whether the row is the original row in the database or the row that the statistic produces. An grouping value of 0 indicates that this value is the original value in the database. The 1 description is the result of the statistic, the number of the parameters is only one and must be a column in group by

For example, 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 a binary vector of the Grouping () value of each column in the parameter. Assuming grouping (a) =1,grouping (b) = 1, the return value of grouping_id (A, a) is the binary 11. Turn into 10 and the binary is 3.

The number of parameters can be multiple, but must be a column that appears in group by.

Query results such as the following:

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

Query results such as the following:

Query statements

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

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