select difficult_level, avg(case when qpd.result='right' then 1 else 0 end) as correct_rate # 对result进行统计平均数，如果result为right则记作1，否则记为0，这样就可以用AVG函数来统计正确率 # 为了防止结果中有难度为None的结果，需要在order by前加一句 having qd.difficult_level != 'None' from user_profile up left join question_practice_detail qpd on up.devic...

select u.device_id, u.university, count(q.question_id) as question_cnt, sum(case when q.result='right' then 1 else 0 end) as right_question_cnt # 如果result为right则记为1，其余的无论是wrong还是null全部记为0，然后求和得到的就是正确答题数了 from user_profile u left join question_practice_detail q on u.device_id=q.device_id and mo...

select device_id, university, gpa from (select device_id, university, gpa, rank() over (partition by university order by gpa) as rk # 将user_profile里的数据先按照学校分组，再在各分组里面按照gpa降序排列，命名为a表 from user_profile) a where a.rk=1; # 条件：a表中的rank=1,即选择各学校里面gpa倒数第一的同学数据

select device_id, substring_index(blog_url,'/',-1) as user_name # 要用到substring_index()这个函数的用法 # substring_index(str,delim,count) # str:要处理的字符串 # delim:分隔符 # count:计数 # 例子：str=www.wikibt.com # substring_index(str,'.',1) # 结果是：www # 如果count是正数，那么就是从左往右数，第N个分隔符...

select substring_index(profile,',',-1) as gender, # 要用到substring_index()这个函数的用法 # substring_index(str,delim,count) # str:要处理的字符串 # delim:分隔符 # count:计数 # 例子：str=www.wikibt.com # substring_index(str,'.',1) # 结果是：www # 如果count是正数，那么就是从左往右数，第N个分隔符的左边的所有内容 # 如果co...

select day(date) as day, count(question_id) as question_cnt from question_practice_detail where date like ('2021-08%') #重点是where句，他有三种改法 #1.like写法：where date like ('2021-08%) #2.正则表达式写法：where date regexp '2021-08' #3.substring写法：where substring(date,1,7) = '2021-08' 即date字符串第一个到第七个截取的子串为‘2021-08’ gr...

select university, difficult_level, round(count(qpd.question_id)/count(distinct qpd.device_id),4) as avg_answer_cnt # 平均答题数=总问题数/回答的总设备数 这里的设备数通过统计所有设备使用情况去重求得 from user_profile u,question_practice_detail qpd,question_detail qd where u.device_id=qpd.device_id and qpd.question_id=qd.question_id # 用户表...

select university,avg(question_cnt) as avg_question_cnt from user_profile group by university # 按学校分组 order by avg_question_cnt asc; # 按平均发贴请况升序排列

select university, round(avg(question_cnt),3) as avg_question_cnt, round(avg(answer_cnt),3) as avg_answer_cnt # 保留三位小数 from user_profile group by university # 按学校分组 having avg(question_cnt)<5 or avg(answer_cnt)<20; # 注:where 从记录中法过滤出某一条记录 # having 可以从一组组记录中过滤掉其哪几组

select gender,university, round(count(device_id),1) as user_num, round(avg(active_days_within_30),1) as avg_active_day, round(avg(question_cnt),1) as avg_question_cnt from user_profile group by gender,university;

select round(count(gender),1) as male_num , round(avg(gpa),1) as avg_gpa # 注意题目要求保留一位小数 from user_profile where gender = 'male';

select round(max(gpa),1) as gpa //取gpa最大值，保留一位小数 from user_profile where university = '复旦大学'; //注意是复旦大学的最高gpa

select device_id,age,university from user_profile where university like "北京%";

select device_id,gender,age,university,gpa from user_profile where (gpa > 3.5 and university = '山东大学' ) or (gpa > 3.8 and university = '复旦大学') ; # 这里大学名字是汉字要用单引号