牛客网SQL进阶挑战知识点（三）聚合分组查询

最新推荐文章于 2022-08-28 15:40:10 发布

是Yvonne一碗呀

最新推荐文章于 2022-08-28 15:40:10 发布

阅读量224

点赞数

文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/yvonneshizhixi/article/details/125430755

版权

SQL14 SQL类别高难度试卷得分的截断平均值

SQL14 SQL类别高难度试卷得分的截断平均值

select tag,difficulty,round(avg_score,1) as clip_avg_score
from
(select tag,difficulty,(sum(score)-max(score)-min(score))/(count(score)-2) as avg_score
 from
 examination_info join exam_record using (exam_id)
where tag='SQL' and difficulty='hard')x

这道题难在如何处理去掉最高分和最低分后在求平均：分子加总起来后减去最大值和最小值即可；注意分母同步也要减小2.

SQL15 统计作答次数

select count(id) as total_pv,count(score) as complete_pv,
count(distinct if(score is not null,exam_id,null)) as complete_exam_cnt
from exam_record

这道题很简单，对分数是否为空值进行筛选去重后计数即可；

SQL16 得分不小于平均分的最低分

select min(score) as min_score_over_avg
from
(select score
from
exam_record join examination_info using(exam_id)
where tag='SQL'
AND score >= (select avg(score) from exam_record join examination_info using(exam_id)
where tag='SQL')
) x

这道题重要在如何表示出平均分：建立子查询，表示出均值

SQL17 平均活跃天数和月活人数

select date_format(start_time,'%Y%m') as month,
round(count(distinct uid,date(submit_time))/count(distinct uid),2) as avg_active_days,
count(distinct uid) as mau
from
exam_record
where submit_time is not null and year(submit_time)='2021'
group by month

这道题我一开始掉进坑里了，我并没有很好的理解【平均活跃天数】，所以分子表示为count(distinct date())，想着说去重后的天数就是一个月活跃的天数，但是这里是根据人数求平均活跃天数，所以是把每个用户活跃的天数相加！！

那么去重的条件就是两个uid\date

count(distinct uid,date(submit_time))

得到的是所有用户活跃的天数加总（可以有重复的天数对应不同的用户）

SQL18 月总刷题数和日均刷题数

select coalesce(year_mon,'2021汇总') as submit_month,
count(question_id) as month_q_cnt,
round(count(question_id)/max(t.days_month),3) as avg_day_cnt
from
(select question_id,
dayofmonth(last_day(submit_time)) as days_month,
date_format(submit_time,'%Y%m') as year_mon
from practice_record
where year(submit_time)=2021) as t
group by t.year_mon
with rollup;

这道题卡住我了，主要在两个方面：

一个是如何表示汇总行的第一个字段：使用 coalesce函数

COALESCE是一个函数，coalesce (expression_1, expression_2, ...,expression_n)依次参考各参数表达式，遇到非null值即停止并返回该值。

二是如何把每个月的天数表达出来（有的月30天有的月31天）

dayofmonth(last_day(submit_time))

这里采用的dayofmonth()是用1-31数字表示日期是该月份的第几天；

last_day()则表示函数返回指定日期对应月份的最后一天；

因此最后一天对应的第几天就是这个月一共有多少天！

但是！！由于第一个select使用了group by ，因此给天数加一个max()函数；

with rollup用在group by之后做总体的汇总！

SQL19 未完成试卷数大于1的有效用户

select uid,sum(if(score is null,1,0)) as incomplete_cnt,
sum(if(score is null,0,1)) as complete_cnt,
group_concat(distinct CONCAT(DATE_FORMAT(start_time, '%Y-%m-%d'),':',tag) separator ';') as detail
from exam_record left join examination_info using(exam_id)
where year(start_time)='2021'
group by uid
having incomplete_cnt>1
and incomplete_cnt<5
and complete_cnt >= 1
order by incomplete_cnt desc

这道题主要难在如何表示{}里面的内容；

【detail中是作答过的试卷{日期:tag}集合，日期和tag间用:连接，多元素间用;连接。】