题解 | #统计每个学校各难度的用户平均刷题数#

select university, difficult_level, count(up.device_id)/count(distinct up.device_id) as avg_answer_cnt
from (select qpd.device_id, qpd.question_id, qpd.result, qd.difficult_level from question_practice_detail qpd left join question_detail qd on qpd.question_id=qd.question_id) qpdd left join user_profile up on up.device_id=qpdd.device_id
group by university,difficult_level

三个表连接，左结合，左外连接（避免结果中出现未答过题的用户记录）答题记录和用户记录连接

避免连接消耗，先投影再连接（半连接思想）