聚合分组查询--聚合函数
题目:
查看大家在SQL类别中高难度试卷的得分情况。从exam_record数据表中计算所有用户完成SQL类别高难度试卷得分的截断平均值(去掉一个最大值和一个最小值后的平均值)
试卷信息表examination_info(exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间)
exam_id
|
tag
|
difficulty
|
9001
|
SQL
|
hard
|
9002
|
算法
|
medium
|
试卷作答记录表exam_record(uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分)
uid
|
exam_id
|
score
|
1001
|
9001
|
80
|
1001
|
9001
|
81
|
1001
|
9001
|
84
|
1001
|
9002
|
89
|
1001
|
9001
|
(NULL)
|
1001
|
9002
|
(NULL)
|
1002
|
9002
|
87
|
1002
|
9001
|
90
|
1003
|
9001
|
50
|
1004
|
9001
|
(NULL)
|
#方法一:
使用sum()函数求出所有的总和,再分别减去最大值max,最小值min,使用count()函数统计总个数,再减去一个最大值,一个最小值,所以减去2
使用round(X,D)函数保留小数位。X:为小数的值,D:为要保留的个数
select A.tag,A.difficulty,ROUND((SUM(B.score)-MIN(B.score)-MAX(B.score)) / (COUNT(B.score)-2),1) from examination_info A left join exam_record B on A.exam_id=B.exam_id where A.tag='SQL' and A.difficulty='hard'
# 方法二:窗口函数
①先根据条件过滤、获得score升降序排名,
②然后根据排名可过滤score最大和最小,最后求平均
注意:条件过滤时要加AND score is not null !(不然空值也会被排序)
SELECT tag, difficulty, ROUND(AVG(score), 1) as clip_avg_score FROM( SELECT exam_id, tag, difficulty, score, ROW_NUMBER() OVER (ORDER BY score) AS rank_a, ROW_NUMBER() OVER (ORDER BY score DESC) AS rank_b FROM exam_record er LEFT JOIN examination_info ei USING(exam_id) WHERE tag='SQL' AND difficulty='hard' AND score is not null ) a WHERE rank_a != 1 AND rank_b !=1 GROUP BY tag, difficulty;