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

题目:

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

 

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
posted @ 2023-11-07 17:36  你还怕大雨吗  阅读(18)  评论(0)    收藏  举报