博客摘录「 sql 笔试题——各个视频的平均完播率」2023年6月13日

问题

计算2021年里有播放记录的每个视频的完播率(结果保留三位小数)，并按完播率降序排序

题目理解

代码：

select t1.video_id, round(sum(if(t1.end_time-t1.start_time >= t2.duration,1,0)) / count(t1.end_time),3) as avg_comp_play_rate
from tb_user_video_log t1 left join tb_video_info t2 on t1.video_id = t2.video_id
where year(t1.start_time) = 2021
group by t1.video_id
order by avg_comp_play_rate DESC

知识点

联结

• 内联结：

将左表和右表根据指定联结列的相等数据进行联结

• 左联结：

将左表作为主表，例如：

select t1.video_id, round(sum(if(t1.end_time-t1.start_time >= t2.duration,1,0)) / count(t1.end_time),3) as avg_comp_play_rate
from tb_user_video_log t1 left join tb_video_info t2 on t1.video_id = t2.video_id

就是将tb_user_video_log作为左主表同tb_video_info联结，tb_user_video_log中的所有内容都会保存，tb_user_video_log会位于结果表的左侧

• 右联结：

将右表作为主表，比如：select * from a_table a  right outer join b_table b  on a.a_id = b.b_id;

就是将a_table作为右主表同b_table联结，a_table会位于结果表的右侧

tip

• 统计满足某一条见的行数：

        SUM(IF(条件，1，0）

• 两个datetime类型数据相减

        结果值：秒

• 提取时间中的年份：YEAR(date)

        如果要获取日期时间值的月份部分、日份部分等，可以使用类似的函数，如 MONTH、DAY 等等。