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
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性
2018-10-22 失分统计#3
2017-10-22 【NOIP2015资源+题解】
2017-10-22 【NOIP2015提高组】 Day2 T3 运输计划