第二十二例：链接查询，统计每个学校的答过题的用户的平均答题数
- 题目链接：统计每个学校的答过题的用户的平均答题数牛客题霸牛客网 (nowcoder.com)
- 初始化数据：
droptable if exists `user_profile`;
droptable if exists `question_practice_detail`;
CREATETABLE `user_profile` (
`device_id` intNOTNULL,
`gender` varchar(14) NOTNULL,
`age` int ,
`university` varchar(32) NOTNULL,
`gpa` float,
`active_days_within_30` int
);
CREATETABLE `question_practice_detail` (
`device_id` intNOTNULL,
`question_id`int NOTNULL,
`result` varchar(32) NOTNULL
);
INSERTINTO user_profile VALUES(2138,'male',21,'北京大学',3.4,7);
INSERTINTO user_profile VALUES(3214,'male',null,'复旦大学',4.0,15);
INSERTINTO user_profile VALUES(6543,'female',20,'北京大学',3.2,12);
INSERTINTO user_profile VALUES(2315,'female',23,'浙江大学',3.6,5);
INSERTINTO user_profile VALUES(5432,'male',25,'山东大学',3.8,20);
INSERTINTO user_profile VALUES(2131,'male',28,'山东大学',3.3,15);
INSERTINTO user_profile VALUES(4321,'male',28,'复旦大学',3.6,9);
INSERTINTO question_practice_detail VALUES(2138,111,'wrong');
INSERTINTO question_practice_detail VALUES(3214,112,'wrong');
INSERTINTO question_practice_detail VALUES(3214,113,'wrong');
INSERTINTO question_practice_detail VALUES(6543,111,'right');
INSERTINTO question_practice_detail VALUES(2315,115,'right');
INSERTINTO question_practice_detail VALUES(2315,116,'right');
INSERTINTO question_practice_detail VALUES(2315,117,'wrong');
INSERTINTO question_practice_detail VALUES(5432,118,'wrong');
INSERTINTO question_practice_detail VALUES(5432,112,'wrong');
INSERTINTO question_practice_detail VALUES(2131,114,'right');
INSERTINTO question_practice_detail VALUES(5432,113,'wrong');
- 题目：请你写sql查找每个学校用户的平均答题数目你的查询应返回以下结果（结果保留4位小数），注意：结果按照university升序排序！！！
- 分析1：先查找每个学校用户：select u.university from user_profile u group by u.university;
- 分析2：求学校用户平均答题数量，注意这里需要将user_profile表和quesiton_practice_detail进行链接使用inner join on：select u.university count(q.question_id)/count(DISTINC u.device_id) as avg_answer_cnt from user_ profile u inner join question_practice_detail q on u.device_id=q.device_id group by u.university;
- 示例代码
select
u.university,
round(count(q.question_id)/count(DISTINCT u.device_id),4) as avg_anser_cnt
from
user_profile u
inner join
question_practice_detail q
on
q.device_id=u.device_id
groupby
u.university
orderby
u.university
第二十三例：链接查询，统计每个学校各难度的用户平均刷题数
- 题目链接：统计每个学校各难度的用户平均刷题数牛客题霸牛客网 (nowcoder.com)
- 初始化数据：
droptable if exists `user_profile`;
droptable if exists `question_practice_detail`;
droptable if exists `question_detail`;
CREATETABLE `user_profile` (
`id` intNOTNULL,
`device_id` intNOTNULL,
`gender` varchar(14) NOTNULL,
`age` int ,
`university` varchar(32) NOTNULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int
);
CREATETABLE `question_practice_detail` (
`id` intNOTNULL,
`device_id` intNOTNULL,
`question_id`int NOTNULL,
`result` varchar(32) NOTNULL
);
CREATETABLE `question_detail` (
`id` intNOTNULL,
`question_id`int NOTNULL,
`difficult_level` varchar(32) NOTNULL
);
INSERTINTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERTINTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERTINTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERTINTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERTINTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERTINTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERTINTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);
INSERTINTO question_practice_detail VALUES(1,2138,111,'wrong');
INSERTINTO question_practice_detail VALUES(2,3214,112,'wrong');
INSERTINTO question_practice_detail VALUES(3,3214,113,'wrong');
INSERTINTO question_practice_detail VALUES(4,6543,111,'right');
INSERTINTO question_practice_detail VALUES(5,2315,115,'right');
INSERTINTO question_practice_detail VALUES(6,2315,116,'right');
INSERTINTO question_practice_detail VALUES(7,2315,117,'wrong');
INSERTINTO question_practice_detail VALUES(8,5432,117,'wrong');
INSERTINTO question_practice_detail VALUES(9,5432,112,'wrong');
INSERTINTO question_practice_detail VALUES(10,2131,113,'right');
INSERTINTO question_practice_detail VALUES(11,5432,113,'wrong');
INSERTINTO question_practice_detail VALUES(12,2315,115,'right');
INSERTINTO question_practice_detail VALUES(13,2315,116,'right');
INSERTINTO question_practice_detail VALUES(14,2315,117,'wrong');
INSERTINTO question_practice_detail VALUES(15,5432,117,'wrong');
INSERTINTO question_practice_detail VALUES(16,5432,112,'wrong');
INSERTINTO question_practice_detail VALUES(17,2131,113,'right');
INSERTINTO question_practice_detail VALUES(18,5432,113,'wrong');
INSERTINTO question_practice_detail VALUES(19,2315,117,'wrong');
INSERTINTO question_practice_detail VALUES(20,5432,117,'wrong');
INSERTINTO question_practice_detail VALUES(21,5432,112,'wrong');
INSERTINTO question_practice_detail VALUES(22,2131,113,'right');
INSERTINTO question_practice_detail VALUES(23,5432,113,'wrong');
INSERTINTO question_detail VALUES(1,111,'hard');
INSERTINTO question_detail VALUES(2,112,'medium');
INSERTINTO question_detail VALUES(3,113,'easy');
INSERTINTO question_detail VALUES(4,115,'easy');
INSERTINTO question_detail VALUES(5,116,'medium');
INSERTINTO question_detail VALUES(6,117,'easy');
- 题目：请你写一个SQL查询，计算不同学校、不同难度的用户平均答题量，根据示例，你的查询应返回以下结果(结果在小数点位数保留4位，4位之后四舍五入)
- 分析1：计算不同学校，不同难度的用户平均答题量：select u.university qd.difficult_level, avg_answer_cnt from question_practive_detail qp left join user_profile u on qp.device_id = u.device_id left join question_detail qd on qp.question_id = qd.question_id;
- 示例代码
select
u.university,
qd.difficult_level,
round(count(qpd.question_id)/count(distinct qpd.device_id),4) as avg_answer_cnt
from
question_practice_detail qpd
left join
user_profile u
on
qpd.device_id=u.device_id
left join
question_detail qd
on
qpd.question_id=qd.question_id
groupby
u.university,
qd.difficult_level;
