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

select
    t1.video_id,
	## 内容有点多（1、sum函数统计该视频组算播放完的次数，2、count(*)函数统计该视频组的被观看的次数、3、消除、4、转换结果的格式）
    cast(sum(if (t1.duration <= t1.subtime, 1, 0))/ count(*) as Decimal(16,3))  `avg_comp_play_rate`
from
    (
        select
		##选出后面需要的字段
            tuvl.id,
            tuvl.uid,
            tuvl.video_id,
			## 以下两个字段推荐提交不用，但是测试最后带一下，好检查问题
            tuvl.start_time,
            tuvl.end_time,
			## 算出该用户这次看某个视频的连续时间（为了和后面视频时间做比较）
            unix_timestamp (end_time) - unix_timestamp (start_time) `subtime`,
            tvi.duration
        from
            tb_user_video_log tuvl
			## 直接内连接，减少数据量
            inner join tb_video_info tvi on tuvl.video_id = tvi.video_id 
			## 过滤条件，挑出是2021年的记录
            where year(tuvl.start_time)='2021' and year(tuvl.end_time)='2021' 
    ) t1
	## 按照视频分组
group by
    t1.video_id
	## 按照视频播放率降序排序
    order by avg_comp_play_rate desc