PLSQL_ Basic Series 2_ grouping function group By/rollup/cube

2014-11-30 Baoxinjian

I. Summary

The result set generated by the ROLLUP operator is similar to the result set generated by the CUBE operator.

1. The following are the specific differences between CUBE and ROLLUP:

The result set generated by cube shows aggregations for all combinations of values in the selected column.

The result set generated by ROLLUP shows the aggregation of a hierarchy of values in the selected column.

2. ROLLUP Advantages:

(1). ROLLUP returns a single result set, while COMPUTE by returns multiple result sets, and multiple result assemblies increase the complexity of the application code.

(2). ROLLUP can be used in server cursors, while COMPUTE by is not possible.

(3). Sometimes, the query optimizer generates more efficient execution plans for ROLLUP than is generated for COMPUTE by.

Second, create test data

1. Create a profile

CREATE TABLEDEPARTMENT (DEPARTMENTCHAR(Ten), EMPLOYEECHAR(6), SALARYINT); BEGIN INSERT  intoDEPARTMENTSELECT 'A','ZHANG', -  fromDUAL;INSERT  intoDEPARTMENTSELECT 'A','LI', $  fromDUAL;INSERT  intoDEPARTMENTSELECT 'A','WANG', -  fromDUAL;INSERT  intoDEPARTMENTSELECT 'A','ZHAO', -  fromDUAL;INSERT  intoDEPARTMENTSELECT 'A','DUAN', -  fromDUAL;INSERT  intoDEPARTMENTSELECT 'B','DUAN', -  fromDUAL;INSERT  intoDEPARTMENTSELECT 'B','DUAN', the  fromDUAL;END;

2. View information

Three, rollup case

The rollup result set has more than three aggregated information: the total of department A, the total of department B, and the total sum. Which totals the Duan in Department b

Iv. Case of Cube

The result set of cube is 5 rows on the basis of the rollup result set, which is equivalent to the result of the GROUP by employees (that is, cube) on the rollup result set on the Union.

Thanks and regards

PLSQL_ Basic Series 2_ grouping function group By/rollup/cube