题解 | 统计每个用户的平均刷题数

题意: 求山东大学每个难度题目的平均答题数

第一步, 求出不同大学的question_id, u,d_i,q_i

SELECT up.university, up.device_id, qpd.question_id
FROM user_profile AS up 
JOIN question_practice_detail AS qpd 
ON up.device_id=qpd.device_id

第二步, 将上面的表为设t, 和qd表join, 得出 u,di, dl

SELECT t.university, t.device_id, qd.difficult_level
FROM
(SELECT up.university, up.device_id, qpd.question_id
FROM user_profile AS up 
JOIN question_practice_detail AS qpd 
ON up.device_id=qpd.device_id) t
JOIN question_detail AS qd
ON qd.question_id=t.question_id

第三步, 求出平均值

SELECT t.university, t.device_id, qd.difficult_level, 
COUNT(*)/COUNT(DISTINCT t.device_id) AS avg_answer_cnt
FROM
(SELECT up.university, up.device_id, qpd.question_id
FROM user_profile AS up 
JOIN question_practice_detail AS qpd 
ON up.device_id=qpd.device_id) t
JOIN question_detail AS qd
ON qd.question_id=t.question_id
WHERE t.university="山东大学"
GROUP BY t.university, qd.difficult_level;