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