均值的计算（1）

Mysql插入中文数据报错，修改对应utf8编码：https://www.cnblogs.com/Amine/p/15463871.html

1.题目：运营想要了解每个学校答过题的用户平均答题数量情况，请你取出数据。

示例：用户信息表 user_profile，其中device_id指终端编号（认为每个用户有唯一的一个终端），gender指性别，age指年龄，university指用户所在的学校，gpa是该用户平均学分绩点，active_days_within_30是30天内的活跃天数。

第一行表示:用户的常用信息为使用的设备id为2138，性别为男，年龄21岁，北京大学，gpa为3.4，在过去的30天里面活跃了7天

最后一行表示:用户的常用信息为使用的设备id为4321，性别为男，年龄28岁，复旦大学，gpa为3.6，在过去的30天里面活跃了9天

 

 

 

 

请你写SQL查找每个学校用户的平均答题数目(说明：某学校用户平均答题数量计算方式为该学校用户答题总次数除以答过题的不同用户个数)根据示例，你的查询应返回以下结果（结果保留4位小数），结果按照university升序排序：

 

解释:

第一行：北京大学总共有2个用户，2138和6543，2个用户在question_practice_detail里面答了2题，平均答题数目为2/2=1.0000

最后一行:浙江大学总共有1个用户，2315，这个用户在question_practice_detail里面答了3题，平均答题数目为3/1=3.0000

问题分解：

限定条件：无；

每个学校：按学校分组，group by university

平均答题数量：在每个学校的分组内，用总答题数量除以总人数即可得到平均答题数量count(question_id) / count(distinct device_id)。

表连接：学校和答题信息在不同的表，需要做连接

 

#方法一，每一列所属的表都很清楚

#select

#u.university,count(q.question_id) / count(distinct q.device_id) as avg_answer_cnt

#from user_profile as u

#join question_practice_detail as q on u.device_id=q.device_id

#group by u.university

 

#方法二，只有交集列才进行说明

select

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

from user_profile as u

join question_practice_detail as q on u.device_id=q.device_id

group by university

 

2.题目：运营想要计算一些参加了答题的不同学校、不同难度的用户平均答题量，请你写SQL取出相应数据

用户信息表：user_profile

题库练习明细表：question_practice_detail

第一行表示:id为1的用户的常用信息为使用的设备id为2138，在question_id

表：question_detail

第一行表示: 题目id为111的难度为hard

最后一行表示: 题目id为117的难度为easy

请你写一个SQL查询，计算不同学校、不同难度的用户平均答题量，根据示例，你的查询应返回以下结果(结果在小数点位数保留4位，4位之后四舍五入)：

 

问题分解：

限定条件：无；

每个学校：按学校分组group by university

不同难度：按难度分组group by difficult_level

平均答题数：总答题数除以总人数count(qpd.question_id) / count(distinct qpd.device_id)

来自上面信息三个表，需要联表，up与qpd用device_id连接，qd与qpd用question_id连接。

思路：

#select university,difficult_level,count(a.question_id)/count(distinct a.device_id)

#from user_profile b

#join question_practice_detail a

#on a.device_id = b.device_id

#join question_detail c

#on a.question_id = c.question_id

#group by university,difficult_level

 

#方法一，打出列的全称

select t1.university,t3.difficult_level,count(t2.question_id)/count(distinct t2.device_id) as avg_answer_cnt

from user_profile as t1

join question_practice_detail t2 on t1.device_id=t2.device_id

join question_detail t3 on t3.question_id=t2.question_id

group by t1.university,t3.difficult_level

 

#方法二，不打出列的全称

#select university,difficult_level,(count(q.question_id)/count(distinct(q.device_id)))

#from user_profile u join question_practice_detail q

#on u.device_id = q.device_id

#join question_detail qd on qd.question_id = q.question_id

#group by university,qd.difficult_level

 

#方法三，不使用表连接方法

#SELECT u.university,d.difficult_level,

#COUNT(q.question_id)/COUNT(DISTINCT(q.device_id)) as avg_answer_cnt

#FROM user_profile AS u,question_practice_detail AS q,question_detail AS d

#WHERE u.device_id = q.device_id and d.question_id = q.question_id

#GROUP BY university,difficult_level;

 

#方法四 格式简洁易懂，学习模仿！

#SELECT

#    t1.university,

#    t3.difficult_level,

#    COUNT(t2.question_id)/COUNT(distinct(t2.device_id)) as avg_answer_cnt

#FROM

#    user_profile t1,

#    question_practice_detail t2,

#    question_detail t3

#WHERE

#    t1.device_id = t2.device_id

#    and

#    t2.question_id = t3.question_id

#GROUP BY t1.university,t3.difficult_level;

 

3.题目：运营想要查看参加了答题的山东大学的用户在不同难度下的平均答题题目数，请取出相应数据

用户信息表：user_profile

题库练习明细表：question_practice_detail

表：question_detail

请你写一个SQL查询，计算山东、不同难度的用户平均答题量，根据示例，你的查询应返回以下结果(结果在小数点位数保留4位，4位之后四舍五入)：

 

问题分解：

限定条件：山东大学的用户 up.university="山东大学"；

不同难度：按难度分组group by difficult_level

平均答题数：总答题数除以总人数count(qpd.question_id) / count(distinct qpd.device_id) 来自上面信息三个表，需要联表，up与qpd用device_id连接并限定大学，qd与qpd用question_id连接。

思路：

#select university,difficult_level,(count(q.question_id)/count(distinct(q.device_id)))

#from user_profile u join question_practice_detail q

#on u.device_id = q.device_id

#join question_detail qd on qd.question_id = q.question_id

#where university = '山东大学'

#group by university,qd.difficult_level

 

##方法一，注意where里面的条件用and连接，distinct别忘了。

#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.device_id=t2.device_id and

#    t2.question_id=t3.question_id and

#    t1.university='山东大学'

#GROUP BY

#    t3.difficult_level

    

#方法二,使用join连接

SELECT

    t1.university,

    t3.difficult_level,

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

FROM

    user_profile as t1

inner join question_practice_detail as t2

    on t1.device_id=t2.device_id

inner join question_detail as t3

    on t2.question_id=t3.question_id

WHERE

    t1.university='山东大学'

GROUP BY

    t3.difficult_level;