聚合函数 [MySQL][数据库]

五大常用的聚合函数

聚合(分组)函数 :

是对一组数据进行汇总的函数,输入的是一组数据的集合,而输出的是单个值

• 之前我们讲过的单行函数是对一行数据进行变换,每行返回一个结果

这里我们将对五个常用的聚合函数进行讲解,我们分为三个部分进行讲解

1. AVG() 和 SUM()
   • AVG就是求平均值,SUM就是求和

这里我们通过举例的方式理解AVG()和SUM()的使用

eg1:

SELECT AVG(salary),SUM(salary)
FROM employees;

• 这里就是计算员工表中的平均工资和总工资
• 这个使用我们没有使用GROUP BY操作分组,那么这个时候默认将表中的所有数据分为了一个大组

eg2:

SELECT SUM(last_name),AVG(last_name),SUM(hire_date)
FROM employees;

• 这里的这几个sum操作和avg操作都是无效的,因为sum和avg都不能对非数值的字段进行操作,而这里的last_name就是字符串类型的,hire_date是日期类型的
• 如果是在Oracle中执行这个操作我们会发现会报错,也就是执行不了,因为在Oracle中就是直接不能使用sum和* avg操作非数值类型的字段,但是在MySQL中对于这种非数值的字段其实还是执行不了,比如对于字符串类型的字段我们执行了sum或者avg操作之后永远都会得到一个0,如果是日期类型的数值,我们会得到一串数值(可能是时间戳)

总结:AVG()和SUM()只使用与数值类型的字段(或者变量)

2. MAX() 和 MIN()
   • MAX()就是求最大值,MIN是求最小值

这里我们通过举例来理解如何使用MAX和MIN

eg1:

SELECT MAX(salary),MIN(salary)
FROM employees;

• 这个时候是求员工中的最大工资和最小工资

eg2:

SELECT MAX(last_name),MIN(last_name),MAX(hire_date)
FROM employees;

• 前面我们说过了使用avg和sum是无法操作非数值类型的字段的,那么对于max和min可以操作非数值类型数据吗? — 我们执行之后可以发现我们可以得到返回的字符串类型的结果和date类型的结果,也就是说明: max和min都是可以对非数值类型的数据进行操作的
  • 其实我们可以想到,字符串类型和日期类型的数据都是可以比较大小的,只是不可以做求和运算,因为我们之前将ORDER BY(排序)的时候我们就讲过了,我们可以使用字符串类型的字段和日期类型的字段作为排序的依据,那么可以排序也即是说明字符串类型和日期类型的数据是可以比较大小的

总结:MAX()和MIN()使用于数值类型,字符串类型,日期时间类型的字段(或变量)

字符串和日期时间类型的数据不可以进行加法运算(求和运算),但可以比较大小

3. COUNT()
   • 计算指定字段在查询结构中出现的个数

这里我们通过举例来说明COUNT()的使用

eg:

SELECT COUNT(employee_id),COUNT(commission_pct),COUNT(1),COUNT(*)
FROM employees;

• 这个时候查询的COUNT(employee_id)为107,因为employees表中有107条记录,并且这107条记录的employee_id字段值都不为null
• 而这个时候查询的COUNT(commission_pct)为100,因为这个时候表中有7条记录的commission_pct字段值为null,而我们这里讲述的这五个聚合函数执行中都是忽略null值的
• 这里的COUNT(1)和COUNT(*)都为107
  • 我们查询表中记录数的时候通常就是查询COUNT(*)

这里我们引出一个问题: 如果要计算表中有多少条记录,我们如何实现?

方式一: COUNT(*)

方式二: COUNT(常数)

• 前面两种方式都是一定可以求得表中的记录数

方式三: COUNT(具体字段)

• 这个时候要求我们具体的字段在表中不能有null值

易错易混淆问题分析:

下面我们通过几个例子来分析:

SELECT AVG(commission_pct),SUM(commission_pct)/COUNT(commission_pct),SUM(commission_pct)/107
FROM employees;

• 这个时候我们执行之后可以发现: avg(commission_pct)的值和sum(commission_pct)/count(commission_pct)的值是相同的,而avg(commission_pct)和sum(commission_pct)/107的值不同

  • 为什么会出现上面的这种情况?
  • 因为我们在执行这五种聚合函数操作的时候都是会忽略null值的,所以我们avg(commission_pct)求得的就是表中commission_pct字段值不为空的100条字段的commission_pct的平均值,对应的sum(commission_pct)/count(commission_pct)其实也就是等同于求得表中的commission_pct值为不为空的数据的平均值,而如果是sum(commission_pct)/107,那么就是求得的表中的所有的commission的平均值,包括commission_pct值为空的数据

• 结论1:AVG(x) = SUM(x)/COUNT(x)

• 结论2:这五大常用的聚合函数在执行时都会忽略null

通过上面的易错易混淆问题分析我们引出一个例题: 查询公司中的平均奖金率

SELECT AVG(commission_pct)
FROM employees;

• 这种写法是错误的,这里查询出的其实是公司中有奖金率的员工的平均奖金率,而不是查询出了公司中所有员工的平均奖金率,因为公司中有的人的奖金率为null,也就是没有奖金率

后面这三种写法都是正确的,都可以计算出公司中的所有员工的平均奖金率

SELECT AVG(IFNULL(commission_pct,0))
FROM employees;

SELECT SUM(commission_pct)/COUNT(IFNULL(commission_pct,0)
FROM employees;

SELECT SUM(commission_pct)/COUNT(1)
FROM employees;