VQ29 验证刷题效果,输出题目真实通过率
select
user_id,
count(distinct if(result_info = 1, question_id, null)) / count(distinct question_id) as question_pass_rate,
sum(result_info) / count(result_info) as pass_rate,
count(question_id) / count(distinct question_id) as question_per_cnt
from
done_questions_record
group by
user_id
having
question_pass_rate > 0.6;
这道题有三个要求:
1. 题目通过率question_pass_rate(通过的题目数/总题目数)
通过的题目数:就是一共通过了几道题,同一道题会出现多次重复提交,这时我们需要将重复提交的题目排除,这样得到的才是通过的题目,语句如下:
1 |
|
通过if函数获取所有通过的题目ID,将通过的题目ID进行去重,这样我们就得到了通过的题目数,
总题目数:就是一共刷了几道题,和通过的题目数distinct关键字所有题目id去重
distinct question_id
question_pass_rate:
count(distinct if(result_info = 1, question_id, null)) / count(distinct question_id) as question_pass_rate
2. 正确率(通过的次数/总答题次数)
通过的次数:就是通过了多少次,对result_info进行求和得到的就是通过的次数
总答题次数:就是一共刷了多少次题,对result_info进行计数得到的就是总的答题次数
正确率:
sum(result_info) / count(result_info)
3. 每题目平均提交次数(总提交次数/总题目数)
总提交次数:就是一共刷了多少次题,对question_id进行计数即可
总题目数:就是刷了几道题,这个和第一个要求一样,需要对题目question_id去重
每题目平均提交次数:
count(question_id) / count(distinct question_id)
技能专项-一网打尽时间函数
VQ30 广告点击的高峰期
select
hour(click_time) as click_hour,
count(hour(click_time)) as click_cnt
from user_ad_click_time
group by click_hour
order by click_cnt desc
limit 1
click_hour用hour函数来调取时间date中的hour值,数click_time的数,用group by 对它进行划分。
VQ31 输出在5min内完成点击购买的用户ID
select distinct ud.user_id as uid
from user_ad_click_time as ud
join user_payment_time as up
on ud.trace_id = up.trace_id
where substr(ud.click_time,1,14) = substr(up.pay_time,1,14)
and substr(ud.click_time,15,2)-substr(up.pay_time,15,2)<5
and substr(ud.click_time,15,2)-substr(up.pay_time,15,2)>-5
user_id,并不是唯一的,用trace_id来连接两个表,用substr来截取时间的前一段,再截取后半部分来分析相差的时间是在五分钟内的。
技能专项-一网打尽字符函数
VQ32 字符函数正则匹配1
select id,comment
from comment_detail
where comment like "%是%" or comment like "%求" or comment like "%试%"
%占位符表示0个或多个字符,可放在查询条件的任意位置
实例:
select * from table1 where code like ‘%abc’; 查询列值以abc结尾的
Select * from table1 where code like ‘abc%’ 查询列值以abc开头的
Select * from table1 where code like ‘%abc%’ 查询列值包含abc的;
Select * from table1 where code like ‘ab%c’ 查询列值以ab开头,c结尾的
VQ33 字符函数正则匹配2
select id,comment
from comment_detail
where comment like "是%" or comment like "求%"
还是考察%位置的意义。
VQ34 话题的分布情况
select substring_index(subject_set,',',1) as subject_id1,
count(distinct id ) as cnt
from comment_detail
where substring_index(substring_index(subject_set,',',2),',',-1) = 1002
group by subject_id1
用两个substring_index来截取第二评论为1002的id,然后再用group by 进行划分
VQ35 评论替换和去除
select id,replace(comment,',','') as comment
from comment_detail
where char_length(comment)>3
repalce(comment,‘,’,‘’)意思是把,替换成空值,再用char_length来进行数数
这一部分就结束了 ,告一段落