Rollup and cube of Oralce advanced SQL

Rollup and cube of Oralce advanced SQL
In oracle SQL query, oracle provides some advanced functions to simplify SQL query statements and improve query performance. The following describes the usage and difference of rollup and cube.

1. rollup


Rollup can calculate the subtotal and total of multiple layers of the specified grouping field in the select statement. rollup is very easy to use and efficient.
Rollup scans the grouping field from right to left and gradually creates a high-level subtotal. finally, create a total row. rollup creates a subtotal for n + 1 level, and n refers to the number of fields in rollup.




1.1 when to use rollup
For subtotal of some level dimensions (calculate the subtotal and total of some level fields) for the statistical summary table in the data warehouse, rollup can simplify the statistical summary table and increase the speed of querying the statistical summary table
1.2 rollup example

 

First, we construct two basic tables: emp (employee table) and dept (Department table ):

SQL> create table emp as select * from scott.emp;Table created.SQL> create table dept as select * from scott.dept;Table created.SQL> select * from emp;     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ----------      7369 SMITH      CLERK           7902 17-DEC-80        800                    20      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30      7566 JONES      MANAGER         7839 02-APR-81       2975                    20      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20      7839 KING       PRESIDENT            17-NOV-81       5000                    10      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20      7900 JAMES      CLERK           7698 03-DEC-81        950                    30      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20      7934 MILLER     CLERK           7782 23-JAN-82       1300                    1014 rows selected.SQL> select * from dept;    DEPTNO DNAME          LOC---------- -------------- -------------        10 ACCOUNTING     NEW YORK        20 RESEARCH       DALLAS        30 SALES          CHICAGO        40 OPERATIONS     BOSTON

To calculate the total salary of each job in each department, the SQL statement is as follows:

SQL> select b.dname, a.job, sum(a.sal)  2  from emp a, dept b  3  where a.deptno = b.deptno  4  group by b.dname, a.job;DNAME          JOB       SUM(A.SAL)-------------- --------- ----------SALES          MANAGER         2850SALES          CLERK            950ACCOUNTING     MANAGER         2450ACCOUNTING     PRESIDENT       5000ACCOUNTING     CLERK           1300SALES          SALESMAN        5600RESEARCH       MANAGER         2975RESEARCH       ANALYST         6000RESEARCH       CLERK           19009 rows selected.

If you want to use an SQL statement to count Total salary for each position in each department, Total salary for each departmentAnd Total salaries of all departmentsThen, it is necessary to use a group by statement to calculate the total salary of each department and then combine it with the above results to get the final result. However, in this case, the writing will be more complex than the writing, in addition, the emp and dept tables must be scanned several times. Now oracle provides the rollup clause. Let's take a look at the results of the rollup clause:

SQL> select b.dname, a.job, sum(a.sal)  2  from emp a, dept b  3  where a.deptno = b.deptno  4  group by rollup(b.dname, a.job);DNAME          JOB       SUM(A.SAL)-------------- --------- ----------SALES          CLERK            950SALES          MANAGER         2850SALES          SALESMAN        5600SALES                          9400RESEARCH       CLERK           1900RESEARCH       ANALYST         6000RESEARCH       MANAGER         2975RESEARCH                      10875ACCOUNTING     CLERK           1300ACCOUNTING     MANAGER         2450ACCOUNTING     PRESIDENT       5000ACCOUNTING                     8750                              29025

From the above results, we can see that the rollup clause adds a subtotal for each department and a total line for all departments, that is, the total salary of each department and the total salary of all departments are counted.

Generally, rollup is used together with the group by statement. It is an extension of the group by statement.

 

If the statement is group by rollup (a, B), oracle first performs group by on fields a and B from the right to the left, and then performs group by on field, finally, perform group by for the entire table. If the statement is group by rollup (a, B, c), oracle first performs group by on fields a, B, and c from right to left, then, group by is performed on fields a and B, group by is performed on field a, and group by is performed on the entire table.

 

The following is an example of a rollup field:

 

SQL> select b.dname, a.job, to_char(hiredate, 'yyyy'), sum(sal)  2  from emp a, dept b  3  where a.deptno = b.deptno  4  group by b.dname, a.job, to_char(hiredate, 'yyyy')  5  order by 1, 2, 3;DNAME          JOB       TO_C   SUM(SAL)-------------- --------- ---- ----------ACCOUNTING     CLERK     1982       1300ACCOUNTING     MANAGER   1981       2450ACCOUNTING     PRESIDENT 1981       5000RESEARCH       ANALYST   1981       3000RESEARCH       ANALYST   1987       3000RESEARCH       CLERK     1980        800RESEARCH       CLERK     1987       1100RESEARCH       MANAGER   1981       2975SALES          CLERK     1981        950SALES          MANAGER   1981       2850SALES          SALESMAN  1981       560011 rows selected.SQL> select b.dname, a.job, to_char(hiredate, 'yyyy'), sum(sal)  2  from emp a, dept b  3  where a.deptno = b.deptno  4  group by rollup(b.dname, a.job, to_char(hiredate, 'yyyy'));DNAME          JOB       TO_C   SUM(SAL)-------------- --------- ---- ----------SALES          CLERK     1981        950SALES          CLERK                 950SALES          MANAGER   1981       2850SALES          MANAGER              2850SALES          SALESMAN  1981       5600SALES          SALESMAN             5600SALES                               9400RESEARCH       CLERK     1980        800RESEARCH       CLERK     1987       1100RESEARCH       CLERK                1900RESEARCH       ANALYST   1981       3000RESEARCH       ANALYST   1987       3000RESEARCH       ANALYST              6000RESEARCH       MANAGER   1981       2975RESEARCH       MANAGER              2975RESEARCH                           10875ACCOUNTING     CLERK     1982       1300ACCOUNTING     CLERK                1300ACCOUNTING     MANAGER   1981       2450ACCOUNTING     MANAGER              2450ACCOUNTING     PRESIDENT 1981       5000ACCOUNTING     PRESIDENT            5000ACCOUNTING                          8750                                   2902524 rows selected.

 

Part 1 rollup (Partial rollup)

When you only want to count some fields, you can use some rollup. for example, group by a, rollup (B, c), this statement creates three (2 + 1) level subtotal. they are level (a, B, c), level (a, B), and level ().

 

SQL> select b.dname, a.job, to_char(hiredate, 'yyyy'), sum(sal)  2  from emp a, dept b  3  where a.deptno = b.deptno  4  group by b.dname, rollup(a.job, to_char(hiredate, 'yyyy'));DNAME          JOB       TO_C   SUM(SAL)-------------- --------- ---- ----------SALES          CLERK     1981        950SALES          CLERK                 950SALES          MANAGER   1981       2850SALES          MANAGER              2850SALES          SALESMAN  1981       5600SALES          SALESMAN             5600SALES                               9400RESEARCH       CLERK     1980        800RESEARCH       CLERK     1987       1100RESEARCH       CLERK                1900RESEARCH       ANALYST   1981       3000RESEARCH       ANALYST   1987       3000RESEARCH       ANALYST              6000RESEARCH       MANAGER   1981       2975RESEARCH       MANAGER              2975RESEARCH                           10875ACCOUNTING     CLERK     1982       1300ACCOUNTING     CLERK                1300ACCOUNTING     MANAGER   1981       2450ACCOUNTING     MANAGER              2450ACCOUNTING     PRESIDENT 1981       5000ACCOUNTING     PRESIDENT            5000ACCOUNTING                          875023 rows selected.

From the above results, we can see that some rollup results:

 

Normal summary rows are generated by group by instead of rollup.

 

 

2. cube

Cube allows you to create subtotal for different combinations of specified columns. if the number of specified columns is n, group by cube creates two or more subtotal columns. cube is a grouping statistics statement that is more fine-grained than rollup. First, let's look at the results of the cube statement:

2.1 When to use cube

 

When cross-tabular report (cross report) is required, rollup can simplify the statistical summary table and improve the speed of querying the statistical summary table.

 

2.2 cube example

 

SQL> select b.dname, a.job, sum(a.sal)  2  from emp a, dept b  3  where a.deptno = b.deptno  4  group by cube(b.dname, a.job);DNAME          JOB       SUM(A.SAL)-------------- --------- ----------                              29025               CLERK           4150               ANALYST         6000               MANAGER         8275               SALESMAN        5600               PRESIDENT       5000SALES                          9400SALES          CLERK            950SALES          MANAGER         2850SALES          SALESMAN        5600RESEARCH                      10875RESEARCH       CLERK           1900RESEARCH       ANALYST         6000RESEARCH       MANAGER         2975ACCOUNTING                     8750ACCOUNTING     CLERK           1300ACCOUNTING     MANAGER         2450ACCOUNTING     PRESIDENT       500018 rows selected.

From the above results, we can see that group by cube (B. dname,. the statement first counts the total salary of all departments, and then counts the total salary of each position (. job), and then calculate the total salary of each department (B. dname. dname,. job.

 

 

If the statement is group by cube (a, B), oracle first performs group by on fields a and B, then performs group by on field a, and then performs group by on field B, finally, perform group by for the entire table. If the statement is group by cube (a, B, c), the oracle grouping fields are (a, B, c), (a, B), (a, B), (, c), (B, c), (a), (B), (c), and finally the total for the entire table
The following is an example of three fields in cube:

SQL> select b.dname, a.job, to_char(hiredate, 'yyyy'), sum(sal)  2  from emp a, dept b  3  where a.deptno = b.deptno  4  group by cube(b.dname, a.job, to_char(hiredate, 'yyyy'));DNAME          JOB       TO_C   SUM(SAL)-------------- --------- ---- ----------                                   29025                         1980        800                         1981      22825                         1982       1300                         1987       4100               CLERK                4150               CLERK     1980        800               CLERK     1981        950               CLERK     1982       1300               CLERK     1987       1100               ANALYST              6000               ANALYST   1981       3000               ANALYST   1987       3000               MANAGER              8275               MANAGER   1981       8275               SALESMAN             5600               SALESMAN  1981       5600               PRESIDENT            5000               PRESIDENT 1981       5000SALES                               9400SALES                    1981       9400SALES          CLERK                 950SALES          CLERK     1981        950SALES          MANAGER              2850SALES          MANAGER   1981       2850SALES          SALESMAN             5600SALES          SALESMAN  1981       5600RESEARCH                           10875RESEARCH                 1980        800RESEARCH                 1981       5975RESEARCH                 1987       4100RESEARCH       CLERK                1900RESEARCH       CLERK     1980        800RESEARCH       CLERK     1987       1100RESEARCH       ANALYST              6000RESEARCH       ANALYST   1981       3000RESEARCH       ANALYST   1987       3000RESEARCH       MANAGER              2975RESEARCH       MANAGER   1981       2975ACCOUNTING                          8750ACCOUNTING               1981       7450ACCOUNTING               1982       1300ACCOUNTING     CLERK                1300ACCOUNTING     CLERK     1982       1300ACCOUNTING     MANAGER              2450ACCOUNTING     MANAGER   1981       2450ACCOUNTING     PRESIDENT            5000ACCOUNTING     PRESIDENT 1981       500048 rows selected.

Part 1 cube (partial cube)
Some cubes are similar to some rollup. Placing a column outside the cube operator can limit the subtotal of a column combination. for example, group by a, cube (B, c), this statement will generate a subtotal of 4 (2*2) layers, which are layers (a, B, c ), level (a, B), level (a, c), level ().

SQL> select b.dname, a.job, to_char(hiredate, 'yyyy'), sum(sal)  2  from emp a, dept b  3  where a.deptno = b.deptno  4  group by b.dname, cube(a.job, to_char(hiredate, 'yyyy'));DNAME          JOB       TO_C   SUM(SAL)-------------- --------- ---- ----------SALES                               9400SALES                    1981       9400SALES          CLERK                 950SALES          CLERK     1981        950SALES          MANAGER              2850SALES          MANAGER   1981       2850SALES          SALESMAN             5600SALES          SALESMAN  1981       5600RESEARCH                           10875RESEARCH                 1980        800RESEARCH                 1981       5975RESEARCH                 1987       4100RESEARCH       CLERK                1900RESEARCH       CLERK     1980        800RESEARCH       CLERK     1987       1100RESEARCH       ANALYST              6000RESEARCH       ANALYST   1981       3000RESEARCH       ANALYST   1987       3000RESEARCH       MANAGER              2975RESEARCH       MANAGER   1981       2975ACCOUNTING                          8750ACCOUNTING               1981       7450ACCOUNTING               1982       1300ACCOUNTING     CLERK                1300ACCOUNTING     CLERK     1982       1300ACCOUNTING     MANAGER              2450ACCOUNTING     MANAGER   1981       2450ACCOUNTING     PRESIDENT            5000ACCOUNTING     PRESIDENT 1981       500029 rows selected.