题解 | #浙大不同难度题目的正确率#

select difficult_level,
    avg(if(qpd.result='right', 1, 0)) as correct_rate
#    sum(if(qpd.result='right', 1, 0)) / count(qpd.question_id) as correct_rate
#    count(if(qpd.result='right', 1, null)) / count(qpd.question_id) as correct_rate
from user_profile as up
inner join question_practice_detail as qpd
    on up.device_id = qpd.device_id

inner join question_detail as qd
    on qd.question_id = qpd.question_id

where up.university = '浙江大学'
group by qd.difficult_level
order by correct_rate asc;

• correct_rate的几种写法：
• avg(if(qpd.result='right', 1, 0)) ：直接取正确结果的概率，有为1，无为0，平均即正确率
• sum(if(qpd.result='right', 1, 0)) / count(qpd.question_id) ：正确的个数/总的答题个数
• count(if(qpd.result='right', 1, null)) / count(qpd.question_id) ：正确的个数/总的答题个数