聚合分组查询--聚合函数

题目：

查看大家在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;