Loading web-font TeX/Main/Italic

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

创建组数据

什么是“创建组数据”,我们可以用一张图来表示:

1666408323325

在该节中,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
posted @   AlphaInf  阅读(58)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性
历史上的今天:
2018-10-22 失分统计#3
2017-10-22 【NOIP2015资源+题解】
2017-10-22 【NOIP2015提高组】 Day2 T3 运输计划
点击右上角即可分享
微信分享提示