SQL堂上作业五
组函数
组函数用于对一个组进行操作
常见的组函数有以下几种
| 函数名 | 用途 |
|---|---|
| AVG | 求组平均值 |
| COUNT | 求符合条件的值的个数 |
| MAX | 求组最大值 |
| MIN | 求组最小值 |
| STDDEV | 求标准差 |
| SUM | 求和 |
| VARIANCE | 求方差 |
AVG，SUM，MAX，MIN
我们输入以下的指令，就可以看到这一组的基本情况
SELECT AVG(salary), MAX(salary),
MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';
输出如下：
AVG(SALARY) MAX(SALARY) MIN(SALARY) SUM(SALARY)
----------- ----------- ----------- -----------
8272.72727 11500 6000 273000
MIN和MAX
我们可以使用min和max来求最小值和最大值
SELECT MIN(hire_date), MAX(hire_date)
FROM employees;
输出如下：
MIN(HIRE_DAT MAX(HIRE_DAT
------------ ------------
17-JUN-87 21-APR-00
COUNT
COUNT(*)
我们可以用COUNT(*)统计有多少行
SELECT COUNT(*)
FROM employees
WHERE department_id = 50;
输出如下：
COUNT(*)
----------
45
COUNT(expr)
我们可以在COUNT()中添加一个列名expr，这样就可以统计有多少行中，expr的值是飞空的
SELECT COUNT(commission_pct)
FROM employees
WHERE department_id = 80;
输出如下：
COUNT(COMMISSION_PCT)
---------------------
34
COUNT(DISTINCT expr)
用于统计有在这些行中，有多少种不同的，非空的值
SELECT COUNT(DISTINCT department_id)
FROM employees;
输出如下：
COUNT(DISTINCTDEPARTMENT_ID)
----------------------------
11
组函数与空值
一般情况
注意：组函数中的运算，将全部忽略空值，不会收到空值的影响
SELECT AVG(commission_pct)
FROM employees;
输出如下：
AVG(COMMISSION_PCT)
-------------------
.222857143
需要统计的情况
在部分情况下，我们需要将空值一并进行统计
此时我们可以用上节课讲解过的NVL等函数对空值进行转换
SELECT AVG(NVL(commission_pct, 0))
FROM employees;
输出如下：
AVG(NVL(COMMISSION_PCT,0))
--------------------------
.072897196
创建组数据
什么是“创建组数据”，我们可以用一张图来表示：
在该节中，GROUP BY将会是一个重要的函数
GROUP BY语句
常规用法
我们可以通过GROUP BY语句，打印出每一组所对应的数据出来
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id ;
输出如下：
DEPARTMENT_ID AVG(SALARY)
------------- -----------
100 8600
30 4150
7000
90 19333.3333
20 9500
70 10000
110 10150
50 3475.55556
80 8955.88235
40 6500
60 5760
10 4400
已选择12行。
组别输出
注意：分组的依据department_id不一定需要出现在输出中
SELECT AVG(salary)
FROM employees
GROUP BY department_id ;
输出如下
AVG(SALARY)
-----------
8600
4150
7000
19333.3333
9500
10000
10150
3475.55556
8955.88235
6500
5760
4400
已选择12行。
基于更多的数据进行分组
我们可以基于两个甚至更多的组进行分组
SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id ;
输出如下：
DEPT_ID JOB_ID SUM(SALARY)
---------- -------------------- -----------
110 AC_ACCOUNT 8300
90 AD_VP 34000
50 ST_CLERK 55700
80 SA_REP 243500
50 ST_MAN 36400
80 SA_MAN 61000
110 AC_MGR 12000
90 AD_PRES 24000
60 IT_PROG 28800
100 FI_MGR 12000
30 PU_CLERK 13900
50 SH_CLERK 64300
20 MK_MAN 13000
100 FI_ACCOUNT 39600
SA_REP 7000
70 PR_REP 10000
30 PU_MAN 11000
10 AD_ASST 4400
20 MK_REP 6000
40 HR_REP 6500
已选择20行。
语句错误分析
分组函数和列混合输出分析
我们SELECT出的东西中，要么全是列，要么全是分组函数
下面这个语句中，既有列又有分组函数，则会出现报错
SELECT department_id, COUNT(last_name)
FROM employees;
在这个语句中，将会出现报错，因为department_id不是分组函数
SELECT department_id, COUNT(last_name)
*
第 1 行出现错误:
ORA-00937: 不是单组分组函数
WHERE和GROUP同时使用分析
注意：当一个语句中包含有GROUP BY和分组函数（如AVG）时，不可以通过WHERE语句对分组函数的值进行限制
下面这个语句中，同时有GROUP BY和WHERE限制的AVG，就会报错
SELECT department_id, AVG(salary)
FROM employees
WHERE AVG(salary) > 8000
GROUP BY department_id;
输出如下
WHERE AVG(salary) > 8000
*
第 3 行出现错误:
ORA-00934: 此处不允许使用分组函数
我们可以将WHERE改为HAVING，即可获得正确的输出
SELECT department_id, AVG(salary)
FROM employees
HAVING AVG(salary) > 8000
GROUP BY department_id;
输出如下
DEPARTMENT_ID AVG(SALARY)
------------- -----------
100 8600
90 19333.3333
20 9500
70 10000
110 10150
80 8955.88235
已选择6行。
我们仍可以用WHERE语句限制department_id
SELECT department_id, AVG(salary)
FROM employees
WHERE department_id > 70
GROUP BY department_id;
输出如下
DEPARTMENT_ID AVG(SALARY)
------------- -----------
100 8600
90 19333.3333
110 10150
80 8955.88235
进行一系列的缝合后也是可以的
SELECT department_id, AVG(salary)
FROM employees
WHERE department_id > 70
HAVING AVG(salary) > 10000
GROUP BY department_id;
输出如下
DEPARTMENT_ID AVG(SALARY)
------------- -----------
90 19333.3333
110 10150
HAVING语句
HAVING语句通常在以下情况下对组进行限制
1，每一组的输出与组有关
2，有组函数被使用
3，有和HAVING匹配的组
HAVING使用案例一
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;
输出如下：
DEPARTMENT_ID MAX(SALARY)
------------- -----------
100 12000
30 11000
90 24000
20 13000
110 12000
80 14000
已选择6行。
HAVING使用案例二
SELECT job_id, SUM(salary) PAYROLL
FROM employees
WHERE job_id NOT LIKE '%REP%'
GROUP BY job_id
HAVING SUM(salary) > 13000
ORDER BY SUM(salary);
输出如下：
JOB_ID PAYROLL
-------------------- ----------
PU_CLERK 13900
AD_PRES 24000
IT_PROG 28800
AD_VP 34000
ST_MAN 36400
FI_ACCOUNT 39600
ST_CLERK 55700
SA_MAN 61000
SH_CLERK 64300
已选择9行。
组函数复合
SELECT MAX(AVG(salary))
FROM employees
GROUP BY department_id;
输出如下：
MAX(AVG(SALARY))
----------------
19333.3333
