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

1.限制条件（山东大学）：因为where关键字无法和合计函数一起使用，所以要使用having

having up.university='山东大学'

2.平均答题数目：总题目数量/(distinct)总设备id数量

注：小数点保留4位,round()函数

round(count(qpd.question_id) / count(distinct qpd.device_id), 4) as avg_answer_cnt

3.连表：三张表的桥梁是question_practice_detail，所以选作该表作为主表（from）。

使用inner join或者left join来连接其他两张表。

user_profile与question_practice_detail连接条件：device_id

question_practice_detail与question_detail连接条件：question_id

from

question_practice_detail as qpd

inner join

user_profile as up

on

up.device_id = qpd.device_id

inner join

question_detail as qd

on

qpd.question_id = qd.question_id

4.分组：大学和难度，使用group by，不要写成了order by

group by

up.university,

qd.difficult_level

完整语句如下：

select 
    up.university,
    qd.difficult_level,
    round(count(qpd.question_id) /
    count(distinct qpd.device_id), 4) 
    as avg_answer_cnt
from 
    question_practice_detail as qpd
inner join user_profile as up
on up.device_id = qpd.device_id
inner join question_detail as qd
on qpd.question_id = qd.question_id
group by 
    up.university,
    qd.difficult_level
having up.university='山东大学'