题解 | #各个视频的平均完播率#

思路简单，先算出每个视频所观看的长度

select tu.video_id,TIMESTAMPDIFF(second,tu.start_time,tu.end_time) from tb_user_video_log tu;

再关联另外一个表，过滤出2021的数据，并同时取到另外一个表的时长字段(duration)信息

select tu.video_id, TIMESTAMPDIFF(second,tu.start_time,tu.end_time) space, tv.duration 
from tb_user_video_log tu
join tb_video_info tv
on tu.video_id = tv.video_id
where DATE_FORMAT(tu.start_time,"%Y") = '2021';

将上面嵌套称为子查询，通过case when的方式来计算出对应的完播率

select 
    t.video_id
    ,round(ifnull(sum(case when t.space >= t.duration then 1 end),0) / count(1), 3) avg_comp_play_rate
from 
(select tu.video_id, TIMESTAMPDIFF(second,tu.start_time,tu.end_time) space, tv.duration 
from tb_user_video_log tu
join tb_video_info tv
on tu.video_id = tv.video_id
where DATE_FORMAT(tu.start_time,"%Y") = '2021') t
group by t.video_id
order by avg_comp_play_rate desc