数据库知识点积累day03(牛客网)

SQL19分组过滤练习题

取出平均发贴数低于5的学校或平均回帖数小于20的学校

问题分解

限定条件：平均发贴数低于5或平均回帖数小于20的学校，avg(question_cnt)<5 or
avg(answer_cnt)<20，聚合函数结果作为筛选条件时，不能用where，而是用having语法，配合重命名即可；
按学校输出：需要对每个学校统计其平均发贴数和平均回帖数，因此group by university

select university, 
avg(question_cnt) avg_question_cnt ,
avg(answer_cnt) avg_answer_cnt from  user_profile
group by university
having avg_question_cnt<5 or avg_answer_cnt<20

SQL20不同大学的用户平均发帖情况，并按照平均发帖情况进行升序排列

问题分解：

限定条件：无；
不同大学：按学校分组group by university
平均发帖数：avg(question_cnt)
升序排序：order by avg_question_cnt

select university ,avg(question_cnt)  avg_question_cnt from user_profile 
group by university
order by  avg_question_cnt

SQL21 浙江大学用户题目回答情况

根据题目的数据只有1个浙江大学的用户，那么把浙江大学这个用户所有答题数据查询出来就行

第一种：创建一张临时表用，获取浙江大学device_id对用户题目回答明细进行过滤。

select device_id,question_id,result from question_practice_detail
where device_id=(select device_id from	user_profile where university ="浙江大学")

第二种：先将两张表关联在一起，然后再筛选出浙江大学的明细数据

select t1.device_id,question_id,result
from question_practice_detail t1
left JOIN user_profile t2
on t1.device_id = t2.device_id
where university = '浙江大学'

SQL22 统计每个学校的答过题的用户的平均答题数

思路：
第一，每个学校，涉及到利用GROUP BY分组实现；
第二，用户平均答题数=所有用户答题总数/所有用户数，分母所有用户涉及到COUNT(DISTINCT …)对登录设备账号进行剔重【由于存在一个设备账号多次登录的情况】，分子所有用户答题总数涉及COUNT()对question_id进行计数，不需要剔重；
第三，需要对两个表进行联结FROM …JOIN…ON…。

select university ,
count(question_id) / count(distinct qpd.device_id) as avg_answer_cnt 
from question_practice_detail as qpd
inner join user_profile as up
on qpd.device_id =up.device_id
group by university