牛客网sql自刷题

牛客网sql公司真题

----------------------分割线---------------------------

小笔记

 1. 时长：TIMESTAMPDIFF(SECOND,start_time,end_time)
 timestampdiff(时间格式，开始时间，结束时间)求时间差，按照格式可以返回为以下任意格式（second，minute，hour，day，week，month，year...
 
 2. 连接字符concat :concat(100,'%')#返回100%
 4. SQL无法直接对%格式的数据进行比较（被定义为字符串数据），所以需要提取百分数中间的数字
SUBSTRING_INDEX(avg_play_progress,"%",1)>60

SQL156 简单 各个视频的平均完播率

关键点：avg函数的妙用

SELECT a.video_id, ROUND(AVG(IF(
        TIMESTAMPDIFF(SECOND, start_time, end_time)>=duration, 1, 0
    )), 3) as avg_comp_play_rate
from tb_user_video_log a
left join tb_video_info b
on a.video_id=b.video_id
WHERE YEAR(start_time)=2021
GROUP BY a.video_id
ORDER BY avg_comp_play_rate DESC;

----------------------分割线---------------------------

sql157

select tag,concat(rate,'%') rate

from
(
select tag,round(avg(if(timestampdiff(second,start_time,end_time)<=duration,
timestampdiff(second,start_time,end_time)/duration,1)) * 100,2) as rate
from tb_user_video_log a
left join tb_video_info b
on a.video_id=b.video_id
group by tag
having rate >60

ord