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

第一种方法 用时42ms 查询方法

SELECT

    t1.university,

    t3.difficult_level,

    COUNT(t2.question_id) / COUNT(DISTINCT(t2.device_id)) as avg_answer_cnt

from

    user_profile as t1,

    question_practice_detail as t2,

    question_detail as t3

WHERE

    t1.university = '山东大学'

    and t1.device_id = t2.device_id

    and t2.question_id = t3.question_id

GROUP BY

    t3.difficult_level;

第二种方法 用时43ms，左连接两个表

select

  u.university,

  d.difficult_level,

  count(q.question_id)/count(distinct q.device_id) as avg_answer_cnt

from

  question_practice_detail q

left join question_detail d on q.question_id=d.question_id

left join user_profile u  on q.device_id=u.device_id

group by u.university,d.difficult_level

having u.university = '山东大学'