ORACLE command CUBE

Basic command: group by cube (Field List );

The CUBE command generates different permutation and Combination Based on the field list, and generates statistical summary based on each combination result.

For example: group by cube (city, job, age); shows the result of the permutation and combination:

1) city, job, age

2) city, job

3) city

4) job, age

5) age

6) city, age

7) job

So there are seven types of permutation and combination.

2) generate different statistical summaries based on each sort and combination, as shown below:

1) group by cube (city, job, age );

2) group by cube (city, job );

3) group by cube (city );

4) group by cube (job, age );

5) group by cube (age );

6) group by cube (city, age );

7) group by cube (job );

 

Example:

1) First, let's look at the tables we will use ):

SQL> SELECT * FROM EMP ;      EMPNO ENAME      JOB         MGR HIREDATE          SAL DEPTNO---------- ---------- --------- ----- ----------- --------- ------      7369 SMITH      CLERK      7902 1980/12/17     800.00     20      7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00     30      7521 WARD       SALESMAN   7698 1981/2/22     1250.00     30      7566 JONES      MANAGER    7839 1981/4/2      2975.00     20      7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00     30      7698 BLAKE      MANAGER    7839 1981/5/1      2850.00     30      7782 CLARK      MANAGER    7839 1981/6/9      2450.00     10      7788 SCOTT      ANALYST    7566 1987/4/19     3000.00     20      7839 KING       PRESIDENT       1981/11/17    5000.00     10      7844 TURNER     SALESMAN   7698 1981/9/8      1500.00     30      7876 ADAMS      CLERK      7788 1987/5/23     1100.00     20      7900 JAMES      CLERK      7698 1981/12/3      950.00     30      7902 FORD       ANALYST    7566 1981/12/3     3000.00     20      7934 MILLER     CLERK      7782 1982/1/23     1300.00     10

2) calculate the average salary (SAL) of different occupations based on the JOB ):

SQL> SELECT JOB, AVG(SAL) FROM EMP GROUP BY JOB ORDER BY JOB ASC ; JOB         AVG(SAL)--------- ----------ANALYST         3000CLERK           1037.5MANAGER         2758.33333PRESIDENT       5000SALESMAN        1400

3) calculate the average salary (SAL) based on different departments (DEPTNO) and jobs ):

SQL> SELECT JOB, DEPTNO , AVG(SAL) FROM EMP GROUP BY JOB , DEPTNO ORDER BY JOB; JOB       DEPTNO   AVG(SAL)--------- ------ ----------ANALYST       20       3000CLERK         10       1300CLERK         20        950CLERK         30        950MANAGER       10       2450MANAGER       20       2975MANAGER       30       2850PRESIDENT     10       5000SALESMAN      30       1400

-- We can see that the average annual salary AVG (SAL) of different departments (deptno: 10, 20, 30) is different.

4) Statistical summary:

 SQL> SELECT JOB, DEPTNO, AVG(SAL) FROM EMP GROUP BY CUBE(JOB, DEPTNO) ORDER BY JOB DESC ; JOB       DEPTNO   AVG(SAL)--------- ------ ----------              10 2916.66666              20       2175              30 1566.66666                 2073.21428SALESMAN      30       1400SALESMAN               1400PRESIDENT     10       5000PRESIDENT              5000MANAGER       10       2450MANAGER       20       2975MANAGER       30       2850MANAGER          2758.33333CLERK         10       1300CLERK         20        950CLERK         30        950CLERK                1037.5ANALYST       20       3000ANALYST                3000

--- 18 rows selected .. /// A total of 18 rows of results

Let's take a look: group by cube (job, deptno) order by job DESC; according to the job and deptno import statistical summary, the arrangement and combination are as follows:

1) Job, deptno

2) Job

3) deptno

4) Remove the job and deptno.

The statistics are summarized as follows:

1) collect statistics based on job and deptno. The results are as follows:

SALESMAN      30       1400PRESIDENT     10       5000MANAGER       10       2450MANAGER       20       2975MANAGER       30       2850CLERK         10       1300CLERK         20        950CLERK         30        950ANALYST       20       3000

-- 9 rows selected .. // Nine rows of results.

2) According to the job statistical summary, the results are as follows:

SALESMAN               1400PRESIDENT              5000MANAGER                 2758.33333CLERK                       1037.5ANALYST                    3000

.. // Five rows are selected.

3) The Summary Result Based on deptno is as follows:

              10       2916.66666              20       2175              30       1566.66666

.. // Three rows are selected.
4) The last line is left:

                 2073.21428

.. // The last line is left,

To verify whether the result is accurate, test:

SQL> SELECT AVG(SAL) FROM EMP;   AVG(SAL)----------2073.21428
-- // Accumulate 9 + 5 + 3 + 1 = 18 and select job, deptno, AVG (SAL) from EMP group by cube (job, deptno) order by job DESC; the query results are the same.
Group by cube (); it is easy to understand through examples.