【SQL】聚合函数

最新推荐文章于 2024-12-31 13:37:09 发布

原创最新推荐文章于 2024-12-31 13:37:09 发布 · 2.4k 阅读

CC 4.0 BY-SA版权

9 篇文章

订阅专栏

如果我们要统计一张表的数据量，例如，想查询students表一共有多少条记录，难道必须用

SELECT * FROM students

查出来然后再数一数有多少行吗？

这个方法当然可以，但是比较弱智。对于统计总数、平均数这类计算，SQL提供了专门的聚合函数，使用聚合函数进行查询，就是聚合查询，它可以快速获得结果。

仍然以查询students表一共有多少条记录为例，我们可以使用SQL内置的COUNT()函数查询：

-- 使用聚合查询:
SELECT COUNT(*) FROM students;

COUNT(*) 表示查询所有列的行数，要注意聚合的计算结果虽然是一个数字，但查询的结果仍然是一个二维表，只是这个二维表只有一行一列，并且列名是COUNT(*)。

通常，使用聚合查询时，我们应该给列名设置一个别名，便于处理结果：

-- 使用聚合查询并设置结果集的列名为num:
SELECT COUNT(*) num FROM students;

COUNT(*)和COUNT(id)实际上是一样的效果。另外注意，聚合查询同样可以使用WHERE条件，因此我们可以方便地统计出有多少男生、多少女生、多少80分以上的学生等：

-- 使用聚合查询并设置WHERE条件:
SELECT COUNT(*) boys FROM students WHERE gender = 'M';

除了COUNT()函数外，SQL还提供了如下聚合函数：

注意，MAX()和MIN()函数并不限于数值类型。如果是字符类型，MAX()和MIN()会返回排序最后和排序最前的字符。

要统计男生的平均成绩，我们用下面的聚合查询：

-- 使用聚合查询计算男生平均成绩:
SELECT AVG(score) average FROM students WHERE gender = 'M';

要特别注意：如果聚合查询的WHERE条件没有匹配到任何行，COUNT()会返回0，而SUM()、AVG()、MAX()和MIN()会返回NULL：

-- WHERE条件gender = 'X'匹配不到任何行:
SELECT AVG(score) average FROM students WHERE gender = 'X';

 -- 每页3条记录，通过聚合查询获得总页数
 SELECT CEILING(COUNT(*) / 3) FROM students;

Submit

如果我们要统计一班的学生数量，我们知道，可以用SELECT COUNT(*) num FROM students WHERE class_id = 1;。如果要继续统计二班、三班的学生数量，难道必须不断修改WHERE条件来执行SELECT语句吗？

对于聚合查询，SQL还提供了“分组聚合”的功能。我们观察下面的聚合查询：

-- 按class_id分组:
SELECT COUNT(*) num FROM students GROUP BY class_id;

执行这个查询，COUNT()的结果不再是一个，而是3个，这是因为，GROUP BY子句指定了按class_id分组，因此，执行该SELECT语句时，会把class_id相同的列先分组，再分别计算，因此，得到了3行结果。

但是这3行结果分别是哪三个班级的，不好看出来，所以我们可以把class_id列也放入结果集中：

-- 按class_id分组:
SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;

这下结果集就可以一目了然地看出各个班级的学生人数。我们再试试把name放入结果集：

-- 按class_id分组:
SELECT name, class_id, COUNT(*) num FROM students GROUP BY class_id;

不出意外，执行这条查询我们会得到一个语法错误，因为在任意一个分组中，只有class_id都相同，name是不同的，SQL引擎不能把多个name的值放入一行记录中。因此，聚合查询的列中，只能放入分组的列。

注意：AlaSQL并没有严格执行SQL标准，上述SQL在浏览器可以正常执行，但是在MySQL、Oracle等环境下将报错，请自行在MySQL中测试。

也可以使用多个列进行分组。例如，我们想统计各班的男生和女生人数：

-- 按class_id, gender分组:
SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;

上述查询结果集一共有6条记录，分别对应各班级的男生和女生人数。

-- 统计学生的人数
select count(*) as '学生数' from students;

-- 统计班级表中的班级数目（行数）
select count(*) as '班级数' from class;

-- 统计成绩表中的最大成绩，最小成绩，平均成绩,总成绩

select max(score) as '最大成绩',min(score) as '最小成绩',
avg(score) as '平均成绩',sum(score) as '成绩总和' from grade;

-- 统计所有学生的平均年龄

select avg(datediff(year,birthday,getdate())) as '平均年龄'
from students;

-- 统计grade表中每个学生的平均成绩（需要按学号分组）

select studentid,avg(score) as 平均成绩
from grade
group by studentid;

-- 统计grade表中每个学生的最大成绩（需要按学号分组）

select studentid,max(score) as 最大成绩
from grade
group by studentid;

-- 统计grade表中每个学生的总成绩（需要按学号分组）

select studentid,sum(score) as 总成绩
from grade
group by studentid;

-- 统计grade表中每个学生的平均成绩（需要按学号分组）,只显示90分以上的

select studentid,avg(score) as 平均成绩
from grade
group by studentid
having avg(score)>90;

-- 统计‘202001123’这位同学的平均成绩

select studentid,avg(score) 
from grade
where studentid='202001123'
group by studentid;

-- 统计男、女生的平均年龄（按性别分组）

select sex,avg(datediff(year,birthday,getdate())) as 平均年龄
from student
group by sex;

使用SQL提供的聚合查询，我们可以方便地计算总数、合计值、平均值、最大值和最小值；

聚合查询也可以添加WHERE条件。