Oracle专题6之Oracle的分组函数与数组分组

• 在关系数据库中，使用数据分组可以取得表数据的汇总信息。数据分组是通过分组函数、Group by以及having等子句共同实现的。

  1、分组函数

• sql函数包含单行函数和多行函数。分组函数属于多行函数。作用于一组数据，并对一组数据返回一个值。
• 分组函数：SELECT [column,] group function(column) ... FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column];。
• 常用的分组函数：AVG、SUM、MIN、MAX、COUNT、WM_CONCAT.。

  a、AVG()

• 求平均值。举例：SELECT avg(sal) FROM emp;

  SQL> SELECT avg(sal) FROM emp;
      AVG(SAL)
              2135

  b、SUM()

• 求和。举例：SELECT SUM(sal) FROM emp;

  SQL> SELECT MIN(sal) FROM emp;
      MIN(SAL)
               800

  c、MIN

• 求最小值。举例：SELECT MIN(sal) FROM emp;

  SQL> SELECT MIN(sal) FROM emp;
  MIN(SAL)
     800

d、COUNT()

• 计数（统计）。举例：求员工的总人数，SELECT COUNT(*) FROM emp;

  SQL> SELECT COUNT(*) FROM emp;
  COUNT(*)-
      15

• 在使用分组时可以使用DISTINCT关键字来去重。

  SQL> select count(distinct deptno) from emp;
  COUNT(DISTINCTDEPTNO)
                  3

  e、WM_CONCAT行转列（让查询结果行转列）

• 该函数可以把列值以‘,’号分隔起来，并显示成一行。

  SQL> select wm_concat(ename) from emp;
  WM_CONCAT(ENAME)
  EASON,G_EASON,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,
  FORD,MILLER

  2、Group by子句

• 作用：可以将表中的数据分成若干组。
• Group by子句的语法：SELECT [column,] group function(column), ... FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column];
• 例如：求出每个部分的平均工资，要求显示：部门号，部分的平均工资。
• SELECT deptno, avg(sal) from emp group by deptno;

  SQL> SELECT deptno, avg(sal) from emp group by deptno;
  DEPTNO   AVG(SAL)
      30 1566.66667
      20     2312.5
      10 2916.66667

• 在SELECT列表中所有未包含在组函数中的列都应包含在GROUP BY子句中。如按照部门和职位进行分组：

  SQL> SELECT deptno, job, avg(sal) from emp group by deptno, job;
          DEPTNO JOB         AVG(SAL)
                  20 CLERK            950
                  30 SALESMAN        1400
                  20 MANAGER         2975
                  30 CLERK            950
                  10 PRESIDENT       5000
                  30 MANAGER         2850
                  10 CLERK           1300
                  20 ANALYST         3000
                  10 MANAGER         2450
  已选择9行。

• 包含在group by 子句中的列不必包含在SELECT列表当中：

  SQL> select avg(sal) from emp group by deptno;
      AVG(SAL)
  1566.66667
          2312.5
  2916.66667

• 再如示例：求出每个部分的员工姓名，要求显示：部分标号、员工姓名。

  SQL> select deptno, WM_concat(ename) from emp group by deptno;
  DEPTNO      WM_CONCAT(ENAME)
      10            CLARK,MILLER,KING
      20            EASON,FORD,ADAMS,SCOTT,JONES,G_EASON
      30            ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD

  3、Having子句

• 作用：用于对分组后的结果进行过滤。
• 例子：请平均工资大于2500的部分，要求显示：部门号，平均工资]
• 不能再WHERE子句中使用组函数，可以在having子句中使用组函数。

  SQL> select deptno,avg(sal) from emp group by deptno having avg(sal) >2500;
  DEPTNO   AVG(SAL)
      10 2916.66667

• having子句和group by子句通常一起使用。

• 如果条件中没有组函数，正常使用where语句就可以。比如说：select deptno, avg(sal) from emp where deptno=10 group by deptno;

  SQL> select deptno, avg(sal) from emp where deptno=10 group by deptno;
  DEPTNO   AVG(SAL)
      10 2916.66667

• where使得分组记录数大大降低，从而提高效率。从SQL优化角度来看，尽量使用where子句。先过滤然后分组。

• 分组函数的嵌套：求部分平均工资的最大值。
• select max(avg(sal)) from emp group by deptno;

  SQL> select max(avg(sal)) from emp group by deptno;
  MAX(AVG(SAL))
       2916.66667