问题
计算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
等等。