Oracle學習（4）：分組函數
分組函數
什麼是分組函數？
分組函數作用於一組數據，並對一組數據返回一個值
組函數的類型
l
AVG
l
COUNT
l
MAX
l
MIN
l
SUM
數值求和
SQL> --求員工工資總額
SQL> select sum(sal) from emp;
SUM(SAL)
----------
29025
求數據條數
SQL> --求員工的人數
SQL> select count(*)from emp;
COUNT(*)
----------
14
求平均
SQL> --平均工資
SQL> select sum(sal)/count(*) 方式一, avg(sal) 方式二 from emp;
方式一 方式二
---------- ----------
2073.21429 2073.21429
SQL> --獎金的平均值
SQL> select sum(comm)/count(*) 方式一,sum(comm)/count(comm) 方式二, avg(comm) 方式三
2 from emp;
方式一 方式二 方式三
---------- ---------- ----------
157.142857 550 550
SQL> select count(*), count(comm) from emp;
COUNT(*) COUNT(COMM)
---------- ----------- （組函數自動濾空）
14 4
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------- --------- ---------- -------------- ----- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 13-7月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 13-7月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已選擇14行。
組函數自動濾空
SQL> --組函數自動濾空
SQL> select count(*), count(nvl(comm,0)) from emp;
COUNT(*) COUNT(NVL(COMM,0))
---------- ------------------
14 14
最大最小值
SQL> --最高工資 最低工資
SQL> select max(sal),min(sal) from emp;
MAX(SAL) MIN(SAL)
---------- ----------
5000 800
SQL> host cls
分組數據
group by分組
SQL> --group by分組：求各個部門的平均工資
SQL> select deptno,avg(sal)
2 from emp
3 group by deptno;
DEPTNO AVG(SAL)
---------- ----------
30 1566.66667
20 2175
10 2916.66667
SQL> ed
已寫入 file afiedt.buf
1 select deptno,job,avg(sal)
2 from emp
3 group by deptno,job
4* order by 1
SQL> /
DEPTNO JOB AVG(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 ANALYST 3000
20 CLERK 950
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 1400
已選擇9行。
**********************************************************************************************
注意：
在SELECT列表中所有未包含在組函數中的列都應該包含
在GROUPBY子句中。
包含在GROUPBY
子句中的列不必包含在SELECT
列表中
**********************************************************************************************
group by作用於多列
SQL> --group by作用於多列：先按照第一列分組；如果相同，再按照第二列分組
SQL> select deptno,avg(sal)
2 from emp
3 group by deptno
4 having avg(sal)>2000;
DEPTNO AVG(SAL)
---------- ----------
20 2175
10 2916.66667
having:在分組的基礎上，再過濾
SQL> --having:在分組的基礎上，再過濾
SQL> select deptno,avg(sal)
2 from emp
3 group by deptno
4 where depno=10;
where depno=10
*
第 4 行出現錯誤:
ORA-00933: SQL 命令未正確結束
**********************************************************************
（where後面不能跟組函數！！！）
不能在 WHERE 子句中使用組函數（注意）。
可以在 HAVING 子句中使用組函數。
**********************************************************
SQL> ed
已寫入 file afiedt.buf
1 select deptno,avg(sal)
2 from emp
3 where deptno=10
4* group by deptno
5 /
DEPTNO AVG(SAL)
---------- ----------
10 2916.66667
SQL> select deptno,avg(sal)
2 from emp
3 group by deptno
4 having deptno=10;
DEPTNO AVG(SAL)
---------- ----------
10 2916.66667
數據庫優化問題
SQL> --
where和having均可使用時，盡量采用where！ 因為where可以先對數據進行過濾然後分組，提高分組效率！
SQL> host cls
group by的增強
SQL> /*
SQL> group by的增強
SQL> group by deptno,job
SQL> +
SQL> group by deptno
SQL> +
SQL> group by null
SQL> =
SQL> group by rollup(deptno,job)
SQL>
SQL>
SQL> group by rollup(a,b)
SQL> =
SQL> group by a,b
SQL> +
SQL> group by a
SQL> +
SQL> group by null
SQL> */
SQL> select deptno,job,sum(sal)
2 from emp
3 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行。
設定格式
SQL> break on deptno skip 2
SQL> /
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
MANAGER 2450
PRESIDENT 5000
8750
20 CLERK 1900
ANALYST 6000
MANAGER 2975
10875
30 CLERK 950
MANAGER 2850
SALESMAN 5600
9400
DEPTNO JOB SUM(SAL)
---------- --------- ----------
29025
已選擇13行。
取消格式
SQL> break on null
SQL> /
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行。
SQL> spool off
