高级分组

来源：互联网发布：淘宝内衣买家晒图编辑：程序博客网时间：2024/05/22 03:13

高级分组

group by

目的按分组的列找相同的即为一组求其他列的聚集运算

group by rollup 和 group by cube

达到会拆分固定模式分组

group by grouping sets()

可以功能增强分组列自定义

grouping()

conn / as sysdba

@?/rdbms/admin/utlsampl.sql

conn scott/tiger

group by 生成数据结果时,只会生成相关列的数据统计信息,而不是生成小计和总计

group by统计结果如下:

SQL> select deptno,job,avg(sal) from emp group by deptno,job;

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

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

| 10 | 1300 | | 2450 | 5000 | |

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

| 20 | 950 | 3000 | 2975 | | |

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

| 30 | 950 | | 2850 | | 1400 |

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

rollup操作在保留group by统计结果的基础上,还会生成横向小计(部门平均工资)和总计(所有员工的平均工资)

select deptno,job,avg(sal) from emp group by rollup(deptno,job);

select deptno,job,avg(sal) from emp group by deptno,job

union all

select deptno,null,avg(sal) from emp group by deptno

union all

select null,null,avg(sal) from emp

group by rollup(a,b,c)

group by a,b,c

union all

group by a,b

union all

group by a

union all

group by null;

rollup(N) 分解为 N+1个的group by的由右至左N逐个递减,附加group by null的union all的集合

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

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

| 10 | 1300 | | 2450 | 5000 | | 2916.66667 |

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

| 20 | 950 | 3000 | 2975 | | | 2175 |

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

| 30 | 950 | | 2850 | | 1400 | 1566.66667 |

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

| 合计 | | | | | | 2073.21429 |

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

验证数字来源:

SCOTT@ora10g> select (1300+2450+5000)/3 from dual;

(1300+2450+5000)/3

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

2916.66667

SCOTT@ora10g> select avg(sal) from emp;

AVG(SAL)

----------

2073.21429

SCOTT@ora10g>

cube 是在group by rollup 的基础上,再做出纵向小计:

SQL> select deptno,job,avg(sal) from emp group by cube(deptno,job);

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

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

| 10 | 1300 | | 2450 | 5000 | | 2916.66667 |

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

| 20 | 950 | 3000 | 2975 | | | 2175 |

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

| 30 | 950 | | 2850 | | 1400 | 1566.66667 |

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

| 职位小计 |1037.5 | 3000 |2758.333 | 5000 | 1400 | |

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

| 合计 | | | | | | 2073.21429 |

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

group by

group by rollup

group by cube()

SCOTT@orasid> select deptno,job,avg(sal) from emp group by cube(deptno,job);

DEPTNO JOB AVG(SAL)

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

2073.21429

CLERK 1037.5

ANALYST 3000

MANAGER 2758.33333

SALESMAN 1400

PRESIDENT 5000

10 2916.66667

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

20 2175

20 CLERK 950

20 ANALYST 3000

20 MANAGER 2975

30 1566.66667

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 1400

验证数值来源:

SCOTT@ora10g> select (800+1100+950+1300)/4 from dual;

(800+1100+950+1300)/4

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

1037.5

SCOTT@ora10g>

ROLLUP sql写法:

SCOTT@ora10g> select deptno,job,avg(sal) from emp group by rollup (deptno,job);

DEPTNO JOB AVG(SAL)

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

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

10 2916.66667

20 CLERK 950

20 ANALYST 3000

20 MANAGER 2975

20 2175

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 1400

30 1566.66667

2073.21429

13 rows selected.

SCOTT@ora10g>

SCOTT@ora10g> select deptno,job,avg(sal) from emp group by cube (deptno,job) order by 1;

DEPTNO JOB AVG(SAL)

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

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

10 2916.66667

20 ANALYST 3000

20 CLERK 950

20 MANAGER 2975

20 2175

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 1400

30 1566.66667

ANALYST 3000

CLERK 1037.5

MANAGER 2758.33333

PRESIDENT 5000

SALESMAN 1400

2073.21429

18 rows selected.

SCOTT@ora10g>

grouping

在使用rollup和cube时统计结果可能用到一列也可能用到多列,为了确定统计结果是否使用了特定列才使用grouping

返回为0 代表使用了该列

返回为1 代表没使用该列

SCOTT@ora10g> select deptno,job,avg(sal),grouping(deptno),grouping(job) from emp

group by cube (deptno,job) order by 1;

DEPTNO JOB AVG(SAL) GROUPING(DEPTNO) GROUPING(JOB)

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

10 CLERK 1300 0 0

10 MANAGER 2450 0 0

10 PRESIDENT 5000 0 0

10 2916.66667 0 1

20 ANALYST 3000 0 0

20 CLERK 950 0 0

20 MANAGER 2975 0 0

20 2175 0 1

30 CLERK 950 0 0

30 MANAGER 2850 0 0

30 SALESMAN 1400 0 0

30 1566.66667 0 1

ANALYST 3000 1 0

CLERK 1037.5 1 0

MANAGER 2758.33333 1 0

PRESIDENT 5000 1 0

SALESMAN 1400 1 0

2073.21429 1 1

18 rows selected.

SCOTT@ora10g>

解析rollup语句

rollup(a,b,c)

等同于

group by a,b,c

union all

group by a,b

union all

group by a

union all

group by null

为维持输出列的数量一直 select子句中用null来代替

例子一:

SCOTT@ora10g> select deptno,sum(sal) from emp group by rollup(deptno);

DEPTNO SUM(SAL)

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

10 8750

2010875

30 9400

29025

分解为:

SCOTT@ora10g> select deptno,sum(sal) from emp group by deptno

2 union all

3 select null,sum(sal) from emp

4 order by 1;

DEPTNO SUM(SAL)

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

10 8750

2010875

30 9400

29025

SCOTT@ora10g>

例子二:

SCOTT@ora10g> select deptno,job,sum(sal) from emp group by rollup(deptno,job);

DEPTNO JOB SUM(SAL)

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

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

10 8750

20 CLERK 1900

20 ANALYST 6000

20 MANAGER 2975

20 10875

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 5600

30 9400

29025

13 rows selected.

分解为:

SCOTT@ora10g> select deptno,job,sum(sal) from emp group by deptno,job

2 union all

3 select deptno,null,sum(sal) from emp group by deptno

4 union all

5 select null,null,sum(sal) from emp

6 order by 1;

DEPTNO JOB SUM(SAL)

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

10 CLERK 1300

10 8750

10 MANAGER 2450

10 PRESIDENT 5000

20 ANALYST 6000

20 MANAGER 2975

20 CLERK 1900

20 10875

30 SALESMAN 5600

30 CLERK 950

30 MANAGER 2850

30 9400

29025

13 rows selected.

SCOTT@ora10g>

解析cube语句

cube(a,b,c) 分解成2的N次方个union all组合

等同于:

group by a,b,c

union all

group by a,b

union all

group by a

union all

group by a,c

union all

group by b,c

union all

group by b

union all

group by c

union all

group by null

例子一:

SCOTT@ora10g> select deptno,sum(sal) from emp group by cube(deptno);

DEPTNO SUM(SAL)

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

29025

10 8750

2010875

30 9400

SCOTT@ora10g>

解析为:

SCOTT@ora10g> select deptno,sum(sal) from emp group by deptno

2 union all

3 select null,sum(sal) from emp

4* order by 1

SCOTT@ora10g> /

DEPTNO SUM(SAL)

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

10 8750

2010875

30 9400

29025

SCOTT@ora10g>

例子二:

SCOTT@ora10g> select deptno,job,sum(sal) from emp group by cube(deptno,job);

DEPTNO JOB SUM(SAL)

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

29025

CLERK 4150

ANALYST 6000

MANAGER 8275

SALESMAN 5600

PRESIDENT 5000

10 8750

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

20 10875

20 CLERK 1900

20 ANALYST 6000

20 MANAGER 2975

30 9400

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 5600

18 rows selected.

SCOTT@ora10g>

解析为:

SCOTT@ora10g> select deptno,job,sum(sal) from emp group by deptno,job

union all

select deptno,null,sum(sal) from emp group by deptno

union all

select null,job,sum(sal) from emp group by job

union all

select null,null,sum(sal) from emp

order by 1

SCOTT@ora10g> /

DEPTNO JOB SUM(SAL)

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

10 PRESIDENT 5000

10 8750

10 CLERK 1300

10 MANAGER 2450

20 10875

20 MANAGER 2975

20 ANALYST 6000

20 CLERK 1900

30 9400

30 MANAGER 2850

30 CLERK 950

30 SALESMAN 5600

CLERK 4150

SALESMAN 5600

PRESIDENT 5000

MANAGER 8275

ANALYST 6000

29025

18 rows selected.

SCOTT@ora10g>

grouping sets

GROUPING SETS是一个GROUP BY的增强语句

GROUPING SETS能让你随心所欲的指定多个分组列小计或合计

ORACLE会用UNION ALL将GROUPING SETS中制定的每组合计组合在一起

GROUPING SETS相对与多个GROUP BY的优势：

只要一次扫描基表能得到更好的性能

不需要写复杂的UNION语句

CUBE，ROLLUP和GROUPING SETS的区别

SQL> select deptno,job,avg(sal) from emp group by grouping sets(deptno,job);

DEPTNO JOB AVG(SAL)

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

30 1566.66667

20 2175

10 2916.66667

CLERK 1037.5

SALESMAN 1400

PRESIDENT 5000

MANAGER 2758.33333

ANALYST 3000

8 rows selected.

SQL>

实际就是两个group by的union all

SQL> select deptno,null,avg(sal) from emp group by deptno;

DEPTNO N AVG(SAL)

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

30 1566.66667

20 2175

10 2916.66667

SQL> select null,job,avg(sal) from emp group by job;

N JOB AVG(SAL)

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

CLERK 1037.5

SALESMAN 1400

PRESIDENT 5000

MANAGER 2758.33333

ANALYST 3000

SQL>

SQL> select deptno,null,avg(sal) from emp group by deptno

union all

select null,job,avg(sal) from emp group by job;

DEPTNO NULL AVG(SAL)

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

30 1566.66667

20 2175

10 2916.66667

CLERK 1037.5

SALESMAN 1400

PRESIDENT 5000

MANAGER 2758.33333

ANALYST 3000

8 rows selected.

SQL>

部门和岗位分组薪水小计:

为了方便描述使用子查询添加行号

SQL> select rownum,a.* from (

select deptno,job,mgr,avg(sal) from emp

where ename <> 'KING'

group by grouping sets((deptno,job),(job,mgr)) order by 1

) a;

ROWNUM DEPTNO JOB MGR AVG(SAL)

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

1 10 MANAGER 2450

2 10 CLERK 1300

3 20 MANAGER 2975

4 20 CLERK 950

5 20 ANALYST 3000

6 30 MANAGER 2850

7 30 CLERK 950

8 30 SALESMAN 1400

9 CLERK 7902 800

10 CLERK 7698 950

11 CLERK 7788 1100

12 CLERK 7782 1300

13 SALESMAN 7698 1400

14 MANAGER 7839 2758.33333

15 ANALYST 7566 3000

15 rows selected.

SQL>

验证数据来源 10号部门只有一个管理人员 2450/1 所以是2450

第一行

SQL> select ename,job,mgr,sal from emp where deptno=10 and job='MANAGER';

ENAME JOB MGR SAL

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

CLARK MANAGER 7839 2450

SQL>

第二行

SQL> select ename,job,mgr,sal from emp where deptno=10 and job='CLERK';

ENAME JOB MGR SAL

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

MILLER CLERK 7782 1300

SQL>

第三行

SQL> select ename,job,mgr,sal from emp where deptno=20 and job='MANAGER';

ENAME JOB MGR SAL

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

JONES MANAGER 7839 2975

SQL>

前8行依次类推都属于 (deptno,job) 得来的数据

直到第9行是(job,mgr)得来的数据

SQL> select ename,job,mgr,sal from emp where job='CLERK';

ENAME JOB MGR SAL

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

SMITH CLERK 7902 800

ADAMS CLERK 7788 1100

JAMES CLERK 7698 950

MILLER CLERK 7782 1300

SQL>

他们job相同但MGR不同所以上面4个结果都出现了

分别对应9行 10行 11行 12行

9 CLERK 7902 800

10 CLERK 7698 950

11 CLERK 7788 1100

12 CLERK 7782 1300

13 SALESMAN 7698 1400

14 MANAGER 7839 2758.33333

15 ANALYST 7566 3000

第13行的数据来源

SQL> select ename,job,mgr,sal from emp where job='SALESMAN';

ENAME JOB MGR SAL

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

ALLEN SALESMAN 7698 1600

WARD SALESMAN 7698 1250

MARTIN SALESMAN 7698 1250

TURNER SALESMAN 7698 1500

SQL> select (1600+1250+1250+1500)/4 from dual

2 ;

(1600+1250+1250+1500)/4

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

1400

SQL>

第14行的数据来源

SQL> select ename,job,mgr,sal from emp where job='MANAGER';

ENAME JOB MGR SAL

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

JONES MANAGER 7839 2975

BLAKE MANAGER 7839 2850

CLARK MANAGER 7839 2450

SQL>

第15行的数据来源

SQL> select ename,job,mgr,sal from emp where job='ANALYST';

ENAME JOB MGR SAL

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

SCOTT ANALYST 7566 3000

FORD ANALYST 7566 3000

SQL>

实际上前8行就是

select deptno,job,null,avg(sal) from emp where ename <> 'KING' group by deptno,job;

后7行就是

select null,job,mgr,avg(sal) from emp where ename <> 'KING' group by job,mgr

所以整体语句分解为:

select deptno,job,null,avg(sal) from emp where ename <> 'KING' group by deptno,job

union all

select null,job,mgr,avg(sal) from emp where ename <> 'KING' group by job,mgr

SQL> /

DEPTNO JOB NULL AVG(SAL)

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

20 CLERK 950

30 SALESMAN 1400

20 MANAGER 2975

30 CLERK 950

30 MANAGER 2850

10 CLERK 1300

10 MANAGER 2450

20 ANALYST 3000

CLERK 7902 800

CLERK 7698 950

CLERK 7788 1100

CLERK 7782 1300

SALESMAN 7698 1400

MANAGER 7839 2758.33333

ANALYST 7566 3000

15 rows selected.

SQL>

练习求下面两个的分解情况

group by grouping sets (a,b,(b,c))

group by grouping sets (a,(b),())