oracle9i学习笔记之十五 增强GROUP BY子句

 1.组及组函数回顾

  SELECT    [column,]group_function(column)...
  FROM      table
  [WHERE    condition]
  [GROUP BY group_by_expression]
  [ORDER BY column];

例1：计算工作岗位以CL开头的各部门雇员的平均薪水、有佣金收入的雇员数和最晚受雇日期
   
    SELECT  AVG(sal),COUNT(comm),MAX(hiredate)
    FROM    emp
    WHERE   job LIKE 'CL%';

   
例2：查询emp表，按照部门编号和工作岗位分组，显示部门编号、工作岗位、薪水合计、有佣金收入的雇员数
    SELECT   deptno,job,SUM(sal),COUNT(comm)
    FROM     emp
    GROUP BY deptno,job;

2.HAVING子句的回顾
 
  SELECT    [column,]group_function(column)...
  FROM      table
  [WHERE    condition]
  [GROUP BY group_by_expression]
  [HAVING   having_expression]
  [ORDER BY column] ;
 
例：查询emp表，按照部门编号进行分组，显示平均薪水高于$2000的部门编号和平均薪水

    SELECT   deptno,AVG(sal)
    FROM     emp
    GROUP BY deptno
    HAVING   AVG(sal)>2000;

3.ROLLUP操作

  SELECT    [column,]group_function(column)...
  FROM      table
  [WHERE    condition]
  [GROUP BY [ROLLUP]group_by_expression]
  [HAVING   having_expression]
  [ORDER BY column] ;

1)ROLLUP分组产生一个包含常规分组行和小计值的结果集
2)ROLLUP是一个GROUP BY 子句的扩展
3)用ROLLUP操作产生小计和累计
 
例：
   SELECT   deptno department_id,job job_id,SUM(sal) salary
   FROM     emp
   WHERE    deptno<60
   GROUP BY ROLLUP(deptno,job);
结果：
        DEPARTMENT_ID  JOB_ID     SALARY 
             10                   3000 
             10        CLERK      1300 
             10        MANAGER    2450 
             10        PRESIDENT  5000 
             10                   11750  //dept10薪水小计
             20        CLERK      3100 
             20        ANALYST    6000 
             20        MANAGER    2975 
             20                   12075  //dept20薪水小计 
             30        CLERK      950 
             30        MANAGER    2850 
             30        SALESMAN   5600 
             30                   9400  //dept30薪水小计
                                  33225 //所有salary之和

4.CUBE操作

  SELECT    [column,]group_function(column)...
  FROM      table
  [WHERE    condition]
  [GROUP BY [CUBE]group_by_expression]
  [HAVING   having_expression]
  [ORDER BY column] ;

1)CUBE是GROUP BY子句的扩展
2)CUBE分组是产生一个包含ROLLUP行和交叉表行的结果集
 
例：
    SELECT    deptno department_id,job job_id,SUM(sal) salary
    FROM      emp
    WHERE     deptno<60
    GROUP BY  CUBE(deptno,job)
    ORDER BY  1;
结果：
        DEPARTMENT_ID  JOB_ID    SALARY 
             10        CLERK     1300 
             10        MANAGER   2450 
             10        PRESIDENT 5000 
             10                  3000 
             10                  11750 
             20        ANALYST   6000 
             20        CLERK     3100 
             20        MANAGER   2975 
             20                  12075 
             30        CLERK     950 
             30        MANAGER   2850 
             30        SALESMAN  5600 
             30                  9400 
                       ANALYST   6000 
                       CLERK     5350 
                       MANAGER   8275 
                       PRESIDENT 5000 
                       SALESMAN  5600 
                                 3000 
                                 33225 

5.GROUPING函数

  SELECT    [column,]group_function(column),GROUPING(expr)
  FROM      table
  [WHERE    condition]
  [GROUP BY [ROLLUP][CUBE]group_by_expression]
  [HAVING   having_expression]
  [ORDER BY column] ;

1)GROUPING函数既可以用于CUBE操作，也可以用于ROLLUP操作
2)用GROUPING函数模拟能够发现在一行中的构成小计的分组
3)GROUPING函数返回0或1
 
例：
   SELECT   deptno deptid,job,SUM(sal),
            GROUPING(deptno) GRP_DEPT,
            GROUPING(job) GRP_JOB
   FROM     emp
   WHERE    deptno<50
   GROUP BY ROLLUP(deptno,job);

结果：
     DEPTID  JOB    SUM(SAL)  GRP_DEPT  GRP_JOB 
       10             3000       0         0 
       10   CLERK     1300       0         0 
       10   MANAGER   2450       0         0 
       10   PRESIDENT 5000       0         0 
       10             11750      0         1 
       20   CLERK     3100       0         0 
       20   ANALYST   6000       0         0 
       20   MANAGER   2975       0         0 
       20             12075      0         1 
       30   CLERK     950        0         0 
       30   MANAGER   2850       0         0 
       30   SALESMAN  5600       0         0 
       30             9400       0         1 
                      33225      1         1 

已选择14行。

6.分组集
1)GROUPING SETS是GROUP BY子句更进一步的扩展
2)能够用GROUPING SETS在同一查询中定义多个分组
3)Oracle服务器计算在GROUPING SETS子句中指定的所有分组
4)分组集合的效率：
  -对基表仅进行一次查询
  -不需要写复杂的UNION语句
  -GROUPING SETS有更多的元素，更好的执行性能 

例：
    SELECT    deptno department_id,job job_id，
              mgr manager_id,AVG(sal) salary
    FROM      emp
    GROUP BY  GROUPING SETS((deptno,job),(job,mgr));
结果：
      DEPARTMENT_ID  JOB_ID  MANAGER_ID  SALARY 
          10                              3000 
          10         CLERK                1300 
          10         MANAGER              2450 
          10         PRESIDENT            5000 
          20         CLERK                1550 
          20         ANALYST              3000 
          20         MANAGER              2975 
          30         CLERK                950 
          30         MANAGER              2850 
          30         SALESMAN             1400 
                                          3000 
                     CLERK      7698      950 
                     CLERK      7782      1300 
                     CLERK      7788      1100   
                     CLERK      7902      2000 
                     ANALYST    7566      3000 
                     MANAGER    7839      2758.33333 
                     SALESMAN   7698      1400 
                     PRESIDENT            5000 

已选择19行。

7.复合列
1)复合列是一个作为整体被处理的列集合
  -ROLLUP(a,(b,c),d)
2)为了指定复合列，用GROUP BY子句来分组在圆括号内的列，因此Oracle服务器在进行ROLLUP
或CUBE操作时将它们作为一个整体来处理
3)当使用ROLLUP或CUBE时，复合列将跳过在确定级别上的聚合

例：
   SELECT   deptno,job,mgr,SUM(sal)
   FROM     emp
   GROUP BY ROLLUP(deptno,(job,mgr));
结果：
      DEPTNO  JOB     MGR    SUM(SAL) 
        10                    3000 
        10   CLERK    7782    1300 
        10   MANAGER  7839    2450 
        10   PRESIDENT        5000 
        10                    11750 
        20   CLERK    7788    1100 
        20   CLERK    7902    2000 
        20   ANALYST  7566    6000 
        20   MANAGER  7839    2975 
        20                    12075 
        30   CLERK    7698    950 
        30   MANAGER  7839    2850 
        30   SALESMAN 7698    5600 
        30                    9400 
                              33225 

练习
1.查询emp表，根据部门编号、工作岗位、所属的经理编号进行分组，使用ROLLUP查询薪水的合计

   SELECT   deptno,job,mgr,SUM(sal)
   FROM     emp
   GROUP BY ROLLUP(deptno,job,mgr);

2.查询emp表，根据部门编号、工作岗位、所属的经理编号进行分组，使用CUBE查询薪水的合计

   SELECT   deptno,job,mgr,SUM(sal)
   FROM     emp
   GROUP BY CUBE(deptno,job,mgr);

3.用GROUPING SETS写一个查询，显示下面的分组：
    deptno,mgr,job
    deptno,job
    mgr,job
  查询计算每个分组的工资总数

   SELECT   deptno,job,mgr,SUM(sal)
   FROM     emp
   GROUP BY GROUPING SETS((deptno,job,mgr),(deptno,job),(mgr,job));