5.13刷题笔记

SQL31 提取博客URL中的用户名

题目：对于申请参与比赛的用户，blog_url字段中url字符后的字符串为用户个人博客的用户名，现在运营想要把用户的个人博客用户字段提取出单独记录为一个新的字段，请取出所需数据。

我的代码：正确

select 
device_id, 
substring_index(blog_url,'/',-1) as user_name 
from user_submit

我的思路：直接用substring_index截取最后一部分字符串。

改进：

select 
-- 替换法 replace(string, '被替换部分','替换后的结果')
-- device_id, replace(blog_url,'http:/url/','') as user_name

-- 截取法 substr(string, start_point, length*可选参数*)
-- device_id, substr(blog_url,11,length(blog_url)-10) as user_nam

-- 删除法 trim('被删除字段' from 列名)
-- device_id, trim('http:/url/' from blog_url) as user_name

-- 字段切割法 substring_index(string, '切割标志', 位置数（负号：从后面开始）)
device_id, substring_index(blog_url,'/',-1) as user_name

from user_submit;

SQL33 找出每个学校GPA最低的同学

题目：现在运营想要找到每个学校gpa最低的同学来做调研，请你取出每个学校的最低gpa。

我的代码：正确

select device_id, university, gpa

from(
    select 
    device_id, university, gpa,
    row_number()over(partition by university order by gpa) as posn
    from user_profile
) a

where posn = 1

我的思路：使用窗口函数对univeristy分组，然后对不同组依据gpa进行内部排序，并且升序生成对应的内部排名列，最后只需要posn=1直接筛选分最低的。最开始给窗口函数生成的列命名为rank，一直运行错误，rank应该是特殊字段，不能用来直接命名，后面改成posn就可以了。

SQL34 统计复旦用户8月练题情况

题目： 现在运营想要了解复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况，请取出相应明细数据，对于在8月份没有练习过的用户，答题数结果返回0.

我的代码：错误

select
up.device_id,
university,
count(question_id) as question_cnt,
sum(if(result='right',1,0)) as right_question_cnt
from user_profile up 

left join question_practice_detail qpd on up.device_id = qpd.device_id

where university = '复旦大学' and month(qpd.date) = 8
group by up.device_id

我的思路问题：以device_id为连接键进行连接，但是where筛选时，month(qpd.date) = 8把user_profile表中没有出现在question_practice_detail表中的device_id去掉了，导致我怎么连接和筛选都差数据。

正确处理方式：在连接的时候，同时加上and month(qpd.date) = 8进行连接，这是我之前不知道的，这样可以避免where筛选时筛掉需要的数据。

正确代码：

select up.device_id,  university,
    count(question_id) as question_cnt,
    sum(if(qpd.result='right', 1, 0)) as right_question_cnt
from user_profile as up

left join question_practice_detail as qpd
  on qpd.device_id = up.device_id and month(qpd.date) = 8

where up.university = '复旦大学'
group by up.device_id

SQL35 浙大不同难度题目的正确率

题目：现在运营想要了解浙江大学的用户在不同难度题目下答题的正确率情况，请取出相应数据，并按照准确率升序输出。

我的代码：正确

select 
difficult_level,
round(sum(if(qpd.result='right',1,0))/count(qpd.question_id),4) as correct_rate
from question_practice_detail qpd 

left join user_profile up on qpd.device_id = up.device_id
left join question_detail qd on qpd.question_id = qd.question_id

where university = '浙江大学'
group by difficult_level
order by correct_rate asc

我的：思路大致正确，left join以question_practice_detail主表进行连接，这个表没有缺失值，但连接后，没有考虑到question_detail表中difficult_level是否有缺失值，这个题是因为difficult_level没有缺失值，所以正确了。如果直接用inner join，则会排出difficult_level和其他项的缺失值，会加快后面的运行效率？

改进：

• 正确率的计算方式改进：判断result是否为right，是的话赋值为1，对于正确的数目，可以用count，也可以用sum，正确率还可以直接用avg计算。
• join方式选择：如果前面inner join改成left join，为了防止结果中有难度为None的结果，需要在order by前加一句 having qd.difficult_level != 'None'

改进代码：

select difficult_level,
    avg(if(qpd.result='right', 1, 0)) as correct_rate
#    sum(if(qpd.result='right', 1, 0)) / count(qpd.question_id) as correct_rate
#    count(if(qpd.result='right', 1, null)) / count(qpd.question_id) as correct_rate
from user_profile as up

inner join question_practice_detail as qpd
    on up.device_id = qpd.device_id
inner join question_detail as qd
    on qd.question_id = qpd.question_id

where up.university = '浙江大学'
group by qd.difficult_level
order by correct_rate asc;