題目：運(yùn)營(yíng)想要查看參加了答題的山東大學(xué)的用戶在不同難度下的平均答題題目數(shù)，請(qǐng)取出相應(yīng)數(shù)據(jù) 用戶信息表：user_profile id device_id gender age university gpa active_days_within_30 question_cnt answer_cnt 1 2138 male 21 北京大學(xué) 3.4 7 2 12 2 3214 male NULL 復(fù)旦大學(xué) 4 15 5 25 3 6543 female 20 北京大學(xué) 3.2 12 3 30 4 2315 female 23 浙江大學(xué) 3.6 5 1 2 5 5432 male 25 山東大學(xué) 3.8 20 15 70 6 2131 male 28 山東大學(xué) 3.3 15 7 13 7 4321 male 28 復(fù)旦大學(xué) 3.6 9 6 52 第一行表示:id為1的用戶的常用信息為使用的設(shè)備id為2138，性別為男，年齡21歲，北京大學(xué)，gpa為3.4，在過去的30天里面活躍了7天，發(fā)帖數(shù)量為2，回答數(shù)量為12 最后一行表示:id為7的用戶的常用信息為使用的設(shè)備id為432，性別為男，年齡28歲，復(fù)旦大學(xué)，gpa為3.6，在過去的30天里面活躍了9天，發(fā)帖數(shù)量為6，回答數(shù)量為52 題庫(kù)練習(xí)明細(xì)表：question_practice_detail id device_id question_id result 1 2138 111 wrong 2 3214 112 wrong 3 3214 113 wrong 4 6534 111 right 5 2315 115 right 6 2315 116 right 7 2315 117 wrong 8 5432 117 wrong 9 5432 112 wrong 10 2131 113 right 11 5432 113 wrong 12 2315 115 right 13 2315 116 right 14 2315 117 wrong 15 5432 117 wrong 16 5432 112 wrong 17 2131 113 right 18 5432 113 wrong 19 2315 117 wrong 20 5432 117 wrong 21 5432 112 wrong 22 2131 113 right 23 5432 113 wrong 第一行表示:id為1的用戶的常用信息為使用的設(shè)備id為2138，在question_id為111的題目上，回答錯(cuò)誤 ...... 最后一行表示:id為23的用戶的常用信息為使用的設(shè)備id為5432，在question_id為113的題目上，回答錯(cuò)誤 表：question_detail id question_id difficult_level 1 111 hard 2 112 medium 3 113 easy 4 115 easy 5 116 medium 6 117 easy 第一行表示: 題目id為111的難度為hard .... 最后一行表示: 題目id為117的難度為easy 請(qǐng)你寫一個(gè)SQL查詢，計(jì)算山東、不同難度的用戶平均答題量，根據(jù)示例，你的查詢應(yīng)返回以下結(jié)果(結(jié)果在小數(shù)點(diǎn)位數(shù)保留4位，4位之后四舍五入)： university difficult_level avg_answer_cnt 山東大學(xué) easy 4.5000 山東大學(xué) medium 3.0000 山東大學(xué)有設(shè)備id為5432和2131這2個(gè)用戶，這2個(gè)用戶總共在question_practice_detail表下有12條答題記錄，且答題題目是112，113，117，且數(shù)目分別為3，6，3，從question_detail可以知道題目難度分別為medium,easy,easy，所以，easy共有9個(gè)，故easy的題目平均答題量= 9(easy=9)2 (device_id=3214 or device_id=5432) =4.5000，medium共有3個(gè)，medium的答題只有device_id=5432的用戶，故medium的題目平均答題量= 3(medium=9)1 ( device_id=5432) =3.0000
示例1
輸入
drop table if exists `user_profile`;
drop table if exists `question_practice_detail`;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int
);
CREATE TABLE `question_practice_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL
);
CREATE TABLE `question_detail` (
`id` int NOT NULL,
`question_id`int NOT NULL,
`difficult_level` varchar(32) NOT NULL
);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大學(xué)',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'復(fù)旦大學(xué)',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大學(xué)',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大學(xué)',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山東大學(xué)',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山東大學(xué)',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'復(fù)旦大學(xué)',3.6,9,6,52);
INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(8,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(9,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(10,2131,113,'right');
INSERT INTO question_practice_detail VALUES(11,5432,113,'wrong');
INSERT INTO question_practice_detail VALUES(12,2315,115,'right');
INSERT INTO question_practice_detail VALUES(13,2315,116,'right');
INSERT INTO question_practice_detail VALUES(14,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(15,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(16,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(17,2131,113,'right');
INSERT INTO question_practice_detail VALUES(18,5432,113,'wrong');
INSERT INTO question_practice_detail VALUES(19,2315,117,'wrong');
INSERT INTO question_practice_detail VALUES(20,5432,117,'wrong');
INSERT INTO question_practice_detail VALUES(21,5432,112,'wrong');
INSERT INTO question_practice_detail VALUES(22,2131,113,'right');
INSERT INTO question_practice_detail VALUES(23,5432,113,'wrong');
INSERT INTO question_detail VALUES(1,111,'hard');
INSERT INTO question_detail VALUES(2,112,'medium');
INSERT INTO question_detail VALUES(3,113,'easy');
INSERT INTO question_detail VALUES(4,115,'easy');
INSERT INTO question_detail VALUES(5,116,'medium');
INSERT INTO question_detail VALUES(6,117,'easy');