高级分组rollup,cube操作

目的是掌握高级分组的语法.理解高级分组的工作原理.

组函数中的集合操作

Rollup分组

按部门分组SQL> select department_id,sum(salary) from emp group by department_id;

DEPARTMENT_ID SUM(SALARY)
------------- -----------
          100       51600
           30       27390
                     7000
           20       20900
           70       11000
           90       58000
          110       20300
           50      172040
           40        7150
           80      304500
           10        4840

DEPARTMENT_ID SUM(SALARY)
------------- -----------
           60       31680

12 rows selected.

按部门分组,并求总计

 SQL> select department_id,sum(salary) from emp group by rollup(department_id);

DEPARTMENT_ID SUM(SALARY)
------------- -----------
           10        4840
           20       20900
           30       27390
           40        7150
           50      172040
           60       31680
           70       11000
           80      304500
           90       58000
          100       51600
          110       20300

DEPARTMENT_ID SUM(SALARY)
------------- -----------
                     7000
                   716400

13 rows selected.

Rollup分组，一次全表扫描   

 

select department_id,sum(salary) from emp group by rollup(department_id);

-----------------------------------------------------------

分解为下列语句

select department_id,sum(salary) from emp group by department_id

union all

select null,sum(salary) from emp

order by 1;

两次扫描表，效率低

 

Group by Rollup(a,b,c,d)

的结果集为,共n+1个集

Group by a,b,c,d

Union all

Group by a,b,c

Union all

Group by a,b

Union all

Group by a

Union all

Group by null

 SQL> select department_id,sum(salary) from emp group by rollup(department_id,job_id);

DEPARTMENT_ID SUM(SALARY)
------------- -----------
                     7000
                     7000
           10        4840
           10        4840
           20       14300
           20        6600
           20       20900
           30       12100
           30       15290
           30       27390
           40        7150

DEPARTMENT_ID SUM(SALARY)
------------- -----------
           40        7150
           50       40040
           50       70730
           50       61270
           50      172040
           60       31680
           60       31680
           70       11000
           70       11000
           80       61000
           80      243500

DEPARTMENT_ID SUM(SALARY)
------------- -----------
           80      304500
           90       34000
           90       24000
           90       58000
          100       12000
          100       39600
          100       51600
          110       12000
          110        8300
          110       20300
                   716400

33 rows selected.

结果为

select DEPARTMENT_ID,job_id,sum(salary) from emp group by DEPARTMENT_ID,job_id

union all

select DEPARTMENT_ID,null,sum(salary) from emp group by DEPARTMENT_ID

union all

select null,null,sum(salary) from emp;

 

Grouping(列名称）的使用，为了表达该列是否参加了分组活动：

0为该列参加了分组，1为该列未参加分组操作

SQL> select department_id,sum(salary),grouping(department_id),grouping(job_id) from emp group by rollup(department_id,job_id);

DEPARTMENT_ID SUM(SALARY) GROUPING(DEPARTMENT_ID) GROUPING(JOB_ID)
------------- ----------- ----------------------- ----------------
                     7000                       0                0
                     7000                       0                1
           10        4840                       0                0
           10        4840                       0                1
           20       14300                       0                0
           20        6600                       0                0
           20       20900                       0                1
           30       12100                       0                0
           30       15290                       0                0
           30       27390                       0                1
           40        7150                       0                0

DEPARTMENT_ID SUM(SALARY) GROUPING(DEPARTMENT_ID) GROUPING(JOB_ID)
------------- ----------- ----------------------- ----------------
           40        7150                       0                1
           50       40040                       0                0
           50       70730                       0                0
           50       61270                       0                0
           50      172040                       0                1
           60       31680                       0                0
           60       31680                       0                1
           70       11000                       0                0
           70       11000                       0                1
           80       61000                       0                0
           80      243500                       0                0

DEPARTMENT_ID SUM(SALARY) GROUPING(DEPARTMENT_ID) GROUPING(JOB_ID)
------------- ----------- ----------------------- ----------------
           80      304500                       0                1
           90       34000                       0                0
           90       24000                       0                0
           90       58000                       0                1
          100       12000                       0                0
          100       39600                       0                0
          100       51600                       0                1
          110       12000                       0                0
          110        8300                       0                0
          110       20300                       0                1
                   716400                       1                1

33 rows selected.

 

Cube分组SQL> select department_id,sum(salary),grouping(department_id),grouping(job_id) from emp group by cube(department_id,job_id);

DEPARTMENT_ID SUM(SALARY) GROUPING(DEPARTMENT_ID) GROUPING(JOB_ID)
------------- ----------- ----------------------- ----------------
                     7000                       0                1
                   716400                       1                1
                    34000                       1                0
                    12000                       1                0
                    12000                       1                0
                     7150                       1                0
                    14300                       1                0
                     6600                       1                0
                    11000                       1                0
                    12100                       1                0
                    61000                       1                0

DEPARTMENT_ID SUM(SALARY) GROUPING(DEPARTMENT_ID) GROUPING(JOB_ID)
------------- ----------- ----------------------- ----------------
                     7000                       0                0
                   250500                       1                0
                    40040                       1                0
                     4840                       1                0
                    24000                       1                0
                    31680                       1                0
                    15290                       1                0
                    70730                       1                0
                    61270                       1                0
                     8300                       1                0
                    39600                       1                0

DEPARTMENT_ID SUM(SALARY) GROUPING(DEPARTMENT_ID) GROUPING(JOB_ID)
------------- ----------- ----------------------- ----------------
           10        4840                       0                1
           10        4840                       0                0
           20       20900                       0                1
           20       14300                       0                0
           20        6600                       0                0
           30       27390                       0                1
           30       12100                       0                0
           30       15290                       0                0
           40        7150                       0                1
           40        7150                       0                0
           50      172040                       0                1

DEPARTMENT_ID SUM(SALARY) GROUPING(DEPARTMENT_ID) GROUPING(JOB_ID)
------------- ----------- ----------------------- ----------------
           50       40040                       0                0
           50       70730                       0                0
           50       61270                       0                0
           60       31680                       0                1
           60       31680                       0                0
           70       11000                       0                1
           70       11000                       0                0
           80      304500                       0                1
           80       61000                       0                0
           80      243500                       0                0
           90       58000                       0                1

DEPARTMENT_ID SUM(SALARY) GROUPING(DEPARTMENT_ID) GROUPING(JOB_ID)
------------- ----------- ----------------------- ----------------
           90       34000                       0                0
           90       24000                       0                0
          100       51600                       0                1
          100       12000                       0                0
          100       39600                       0                0
          110       20300                       0                1
          110       12000                       0                0
          110        8300                       0                0

52 rows selected.

 

结果集为，2**n(2的N次方)个结果集

select DEPARTMENT_ID,job_id,sum(salary) from emp group by DEPARTMENT_ID,job_id

union all

select DEPARTMENT_ID,null,sum(salary) from emp group by DEPARTMENT_ID

union all

select null,job_id,sum(salary) from emp group by JOB_ID

union all

select null,null,sum(salary) from emp;