本章要点 ROLLUP CUBE GROUPING GROUPING SETS
ROLLUP:在GROUP BY 子句中按组小计,通常是基于多个字段的。分门合计,最后还汇总。
示例:
SQL> select department_id,job_id,sum(salary) from employees
2 where department_id<60
3 group by rollup(department_id,job_id);
DEPARTMENT_ID JOB_ID SUM(SALARY)
------------- ---------- -----------
10 AD_ASST 4400
10 4400
20 MK_MAN 13000
20 MK_REP 6000
20 19000
30 PU_MAN 11000
30 PU_CLERK 13900
30 24900
40 HR_REP 6500
40 6500
50 ST_MAN 36400
DEPARTMENT_ID JOB_ID SUM(SALARY)
------------- ---------- -----------
50 SH_CLERK 64300
50 ST_CLERK 55700
50 156400
211200
15 rows selected.
分析:ROLLUP语句执行是从GROUP BY括号里面的字段中,从右到左逐渐挨个去除字段进行汇总,最后括号里面的字段都去除完就来个总的汇总。
CUBE:立方体的意思,用来解决交叉表的问题
示例:
SQL> select department_id,job_id,sum(salary) from employees
2 where department_id<60
3 group by cube(department_id,job_id);
DEPARTMENT_ID JOB_ID SUM(SALARY)
------------- ---------- -----------
211200
HR_REP 6500
MK_MAN 13000
MK_REP 6000
PU_MAN 11000
ST_MAN 36400
AD_ASST 4400
PU_CLERK 13900
SH_CLERK 64300
ST_CLERK 55700
10 4400
DEPARTMENT_ID JOB_ID SUM(SALARY)
------------- ---------- -----------
10 AD_ASST 4400
20 19000
20 MK_MAN 13000
20 MK_REP 6000
30 24900
30 PU_MAN 11000
30 PU_CLERK 13900
40 6500
40 HR_REP 6500
50 156400
50 ST_MAN 36400
DEPARTMENT_ID JOB_ID SUM(SALARY)
------------- ---------- -----------
50 SH_CLERK 64300
50 ST_CLERK 55700
24 rows selected.
分析:BUBE语句从右边开始把group by对象的其他字段视为空,只留一个字段依次汇总。这,让我想起了概率论与数理统计上的边际分布,道理是一样的。
简单的说,ROLLUP就是解决树状的统计,CUBE就是解决二维表(矩阵)的统计。
续:
用ROLLUP和CUBE的GROUP BY语句中会出现某些字段的值为空,那么如何来区分哪些空是该字段的值自然为空还是由于ROLLUP和CUBE产生的空呢?这就用到GROUPING
示例:
SQL> select department_id,job_id,sum(salary),
2 grouping(department_id) as "GRP_DEPT",grouping(job_id) as "GRP_JOB"
3 from employees
4 where department_id<50 group by rollup(department_id,job_id);
DEPARTMENT_ID JOB_ID SUM(SALARY) GRP_DEPT GRP_JOB
------------- ---------- ----------- ---------- ----------
10 AD_ASST 4400 0 0
10 4400 0 1
20 MK_MAN 13000 0 0
20 MK_REP 6000 0 0
20 19000 0 1
30 PU_MAN 11000 0 0
30 PU_CLERK 13900 0 0
30 24900 0 1
40 HR_REP 6500 0 0
40 6500 0 1
54800 1 1
11 rows selected.
分析:1表示因ROLLUP和CUBE产生的空,0表示自然的空。
GROUPING SETS:用小括号把几个子字段组合起来,视为一个字段的情况处理
示例:
SQL> select department_id,job_id,manager_id,avg(salary) from employees group by grouping sets((department_id,job_id),(job_id,manager_id));
DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY)
------------- ---------- ---------- -----------
AC_MGR 101 12008
SH_CLERK 122 3200
SH_CLERK 124 2825
MK_MAN 100 13000
ST_MAN 100 7280
ST_CLERK 121 2675
SA_REP 148 8650
SH_CLERK 120 2900
AD_ASST 101 4400
AD_PRES 24000
FI_MGR 101 12008
DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY)
------------- ---------- ---------- -----------
SA_REP 146 8500
SH_CLERK 123 3475
IT_PROG 102 9000
IT_PROG 103 4950
FI_ACCOUNT 108 7920
PU_MAN 100 11000
AC_ACCOUNT 205 8300
ST_CLERK 122 2700
SA_REP 145 8500
HR_REP 101 6500
PR_REP 101 10000
DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY)
------------- ---------- ---------- -----------
AD_VP 100 17000
ST_CLERK 120 2625
ST_CLERK 124 2925
SA_REP 147 7766.66667
SA_REP 149 8333.33333
ST_CLERK 123 3000
SH_CLERK 121 3675
MK_REP 201 6000
PU_CLERK 114 2780
SA_MAN 100 12200
110 AC_ACCOUNT 8300
DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY)
------------- ---------- ---------- -----------
90 AD_VP 17000
50 ST_CLERK 2785
80 SA_REP 8396.55172
110 AC_MGR 12008
50 ST_MAN 7280
80 SA_MAN 12200
50 SH_CLERK 3215
20 MK_MAN 13000
90 AD_PRES 24000
60 IT_PROG 5760
100 FI_MGR 12008
DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY)
------------- ---------- ---------- -----------
30 PU_CLERK 2780
100 FI_ACCOUNT 7920
70 PR_REP 10000
SA_REP 7000
10 AD_ASST 4400
20 MK_REP 6000
40 HR_REP 6500
30 PU_MAN 11000
52 rows selected.
欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/) | Powered by Discuz! X3.2 |