5-MySQL(函数聚合函数)

一、聚合函数

对一组值进行计算，并返回单个值，也被称为组函数；

• 聚合计算过程：选定分组字段 – 分组 – 计算：
  1.选定分组字段：在 select 里；
  2.分组：用 group by；
  3.计算：根据实际情况，选定聚合函数；

• 聚合函数
  1.计数：count()
  2.求和：sum()
  3.最大值/最小值：max/min()
  4.平均值：avg()

• 聚合值过滤
  聚合值过滤：having，不是where！

• 目前SQL执行顺序：from – where – group by – having – select

• eg:按省份和高考分数分段，统计高分段的人数：
  安徽学生，高考分数>620；
  江苏学生，高考分数>610;
  其他省份学生，高考分数>600;

二、举例练习

表例

--常规聚合函数使用：
--注意事项：hive中distinct和group by 不能一起用
--1、查询每个学生考试单科最高分是多少？字段：学号、姓名、单科最高分；

select 
stu_id as 学号
,name as 姓名
,max(score) as 单科最高分

from score_info
group by stu_id,name;

--2、查询每个学生考试单科最低分是多少？字段：学号、姓名、单科最低分；

select 
stu_id as 学号
,name as 姓名
,min(score) as 单科最低分

from score_info
group by stu_id,name;

--3、查询每个学生考试平均分是多少？字段：学号、姓名、考试平均分；

select 
stu_id as 学号
,name as 姓名
,avg(score) as 考试平均分

from score_info
group by stu_id,name;

--4、统计每个学生考了几科？字段：学号、姓名、考试科数；

select 
stu_id as 学号
,name as 姓名
,count(distinct subject_id) as 考试科数

from score_info
group by stu_id,name;

--5、统计每个学生考试总分是多少？字段：学号、姓名、考试总分；

select 
stu_id as 学号
,name as 姓名
,sum(score) as 考试总分

from score_info
group by stu_id,name;

--6、查询每个省份，不同学院的人数有多少？字段：省份、学院、人数；

select 
from_where as 省份
,college as 学院
,count(distinct stu_id) as 人数

from student_info
group by from_where,college
order by from_where,college;

--7、查询每个班级人数有多少？字段：学院、专业、班级、人数；

select 
college as 学院
,major as 专业
,class as 班级
,count(stu_id) as 人数

from class_info
group by college,major,class
order by college,major;

--与其他函数结合使用：
--1、查询高数=100分 和 物理=100分的人数有多少？字段：高数满分人数，物理满分人数；
--if

select 
count(if(subject='高数' and score=100,stu_id,null)) as 高数满分人数
,count(if(subject='物理' and score=100,stu_id,null)) as 物理满分人数

from score_info;

--case when

count(distinct (case when subject='高数' and score=100 then stu_id else null end)) as 高数满分人数
,count(distinct (case when subject='物理' and score=100 then stu_id else null end)) as 物理满分人数

from score_info;

--2、查询每个班级，高数和物理不及格的人数有多少（<60分）？字段：学院、专业、班级、高数不及格人数、物理不及格人数；

select
college as 学院
,major as 专业
,class as 班级
,count(if(subject='高数' and score<60,subject,null)) as 高数不及格人数
,count(if(subject='物理' and score<60,subject,null)) as 物理不及格人数

from score_info join class_info 
on score_info.stu_id=class_info.stu_id
group by college,major,class
order by college,major;

--

select
subject
,case when score>0 and score<60 then '0-60'
    when score>=60 and score <70 then '60-70'
    when score>=70 and score <80 then '70-80'
    when score>=80 and score <80 then '80-90'
    when score>=90 and score <100 then '90-100'
    end as diff_part
,count(distinct stu_id) as 人数

from score_info
group by subject,diff_part;
--hive中，group by不能用别名（diff_part），需要全部复制过来