一、聚合函數(shù)
對一組值進行計算，并返回單個值变汪，也被稱為組函數(shù)；
聚合計算過程：選定分組字段 – 分組 – 計算：
1.選定分組字段：在 select 里蚁趁；
2.分組：用 group by裙盾；
3.計算：根據(jù)實際情況，選定聚合函數(shù)他嫡；聚合函數(shù)
1.計數(shù)：count()
2.求和：sum()
3.最大值/最小值：max/min()
4.平均值：avg()聚合值過濾
聚合值過濾：having番官，不是where！目前SQL執(zhí)行順序：from – where – group by – having – select
eg:按省份和高考分數(shù)分段钢属，統(tǒng)計高分段的人數(shù)：
安徽學生鲤拿，高考分數(shù)>620；
江蘇學生署咽，高考分數(shù)>610;
其他省份學生近顷，高考分數(shù)>600;
二、舉例練習
--常規(guī)聚合函數(shù)使用：
--注意事項：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、統(tǒng)計每個學生考了幾科阶冈？字段：學號闷尿、姓名、考試科數(shù)女坑；
select
stu_id as 學號
,name as 姓名
,count(distinct subject_id) as 考試科數(shù)
from score_info
group by stu_id,name;
--5填具、統(tǒng)計每個學生考試總分是多少？字段：學號匆骗、姓名灌旧、考試總分；
select
stu_id as 學號
,name as 姓名
,sum(score) as 考試總分
from score_info
group by stu_id,name;
--6绰筛、查詢每個省份，不同學院的人數(shù)有多少描融？字段：省份铝噩、學院、人數(shù)窿克；
select
from_where as 省份
,college as 學院
,count(distinct stu_id) as 人數(shù)
from student_info
group by from_where,college
order by from_where,college;
--7骏庸、查詢每個班級人數(shù)有多少？字段：學院年叮、專業(yè)具被、班級、人數(shù)只损；
select
college as 學院
,major as 專業(yè)
,class as 班級
,count(stu_id) as 人數(shù)
from class_info
group by college,major,class
order by college,major;
--與其他函數(shù)結(jié)合使用：
--1一姿、查詢高數(shù)=100分 和 物理=100分的人數(shù)有多少？字段：高數(shù)滿分人數(shù)跃惫，物理滿分人數(shù)叮叹；
--if
select
count(if(subject='高數(shù)' and score=100,stu_id,null)) as 高數(shù)滿分人數(shù)
,count(if(subject='物理' and score=100,stu_id,null)) as 物理滿分人數(shù)
from score_info;
--case when
count(distinct (case when subject='高數(shù)' and score=100 then stu_id else null end)) as 高數(shù)滿分人數(shù)
,count(distinct (case when subject='物理' and score=100 then stu_id else null end)) as 物理滿分人數(shù)
from score_info;
--2、查詢每個班級爆存，高數(shù)和物理不及格的人數(shù)有多少（<60分）蛉顽？字段：學院、專業(yè)先较、班級携冤、高數(shù)不及格人數(shù)悼粮、物理不及格人數(shù)；
select
college as 學院
,major as 專業(yè)
,class as 班級
,count(if(subject='高數(shù)' and score<60,subject,null)) as 高數(shù)不及格人數(shù)
,count(if(subject='物理' and score<60,subject,null)) as 物理不及格人數(shù)
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 人數(shù)
from score_info
group by subject,diff_part;
--hive中曾棕，group by不能用別名（diff_part）扣猫，需要全部復制過來
