题目:运营想要计算一些参加了答题的不同学校、不同难度的用户平均答题量,请你写SQL取出相应数据
用户信息表:user_profile
题库练习明细表:question_practice_detail
表:question_detail
请你写一个SQL查询,计算不同学校、不同难度的用户平均答题量,根据示例,你的查询应返回以下结果(结果在小数点位数保留4位,4位之后四舍五入):
两种解法:
第一种:两次join on
select university,difficult_level,(count(q.question_id)/count(distinct(q.device_id)))
from user_profile u join question_practice_detail q
on u.device_id = q.device_id
join question_detail qd on qd.question_id = q.question_id
group by university,qd.difficult_level
第二种:where语句
SELECT u.university,d.difficult_level,
COUNT(q.question_id)/COUNT(DISTINCT(q.device_id)) as avg_answer_cnt
FROM user_profile AS u,question_practice_detail AS q,question_detail AS d
WHERE u.device_id = q.device_id and d.question_id = q.question_id
GROUP BY university,difficult_level;
同时测试,第一种处理起来效率更高,第二种优化效果不好