此题共包含两张表：

表1：用户-视频互动表tb_user_video_log

表2：短视频信息表tb_video_info

要解决的问题：

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

解题思路：

  1. 2021年有播放记录的视频，where条件语句

  2. 每个视频的完播率（满足条件的视频个数/全部视频个数），需要将表1和表2关联，关联条件为video_id

    a. 判断是否满足条件，TIMESTAMPDIFF(second,start_time,end_time) >= duration

    b. 满足条件的视频个数，sum(if(a, 1, 0))

    c. 全部视频个数，只要播放过就算，count(start_time)

  3. 将数据按video_id分组

  4. 按完播率排序

易混易错：

  1. sum VS count

  • sum是对符合条件的记录的数值列求和

  • count是行数汇总，count(列名)不包含null值的行数，而count(*)包含所有行

  • 因此如果在与if条件搭配使用时，一般会选择sum

  1. timestamp VS timestampdiff

  • TIMESTAMPDIFF是用来获取两个时间间隔的函数，语法为timestampdiff(interval,datetime_expr1,datetime_expr2) interval可以是时、分、秒、月、日、年、星期等等

  • timestamp是日期型数据，通常用来插入或更新记录


select a.video_id, 
			 ROUND(sum(if (TIMESTAMPDIFF(second,start_time,end_time) >= duration, 1, 0)) / count(start_time), 3) 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