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

# 问题：计算2021年里有播放记录的每个视频的完播率(结果保留三位小数)，并按完播率降序排序
# 注：视频完播率是指完成播放次数占总播放次数的比例。简单起见，结束观看时间与开始播放时间的差>=视频时长时，视为完成播放。

# 1、时间差函数：datediff
#  　　语法：传入两个日期参数，比较DAY天数，第一个参数减去第二个参数的天数值
# SELECT DATEDIFF('2013-01-13','2012-10-01'); # 104

# 2、时间差函数：timediff
# 　　语法：timediff(time1,time2)
# 　　结果：返回两个时间相减得到的差值，time1-time2

# 3、时间差函数：timestampdiff
# 　　语法：timestampdiff(interval, datetime1,datetime2)
# 　　结果：返回（时间2-时间1）的时间差，结果单位由interval参数给出。



# 完成播放 (timestampdiff(second,end_time,strat_time) >= duration )
# 要记录 如果是完成播放 要加一 使用 sum 和 if
# sum(if(timestampdiff(second,end_time,strat_time) >= duration ),1,0)
# 视频完播率= 完成播放的量/ count(a.device_id) 
# 取3位数 使用round

# 表的连接 到底是where innerjoin 还是左连接又连接？
# 以什么键来连接？

# 应该以video_id来连接   左连接和where 应该都没关系



select a.video_id	,
# round(sum(if(timediff(end_time,start_time) >= duration,1,0) ) / count(a.video_id)  ,3)
round(sum(if((end_time-start_time) >= duration,1,0))  / count(a.video_id)  ,3)
As  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
# 判断是2021 Ian看的 year(start_time)=2021
group by a.video_id
order by avg_comp_play_rate desc;



select a.video_id	,
# round(sum(if(timediff(end_time,start_time) >= duration,1,0) ) / count(a.video_id)  ,3)
round(sum(if((end_time-start_time) >= duration,1,0))  / count(a.video_id)  ,3)
As  avg_comp_play_rate
from tb_user_video_log a, tb_video_info b
where a.video_id= b.video_id 
and  year(start_time)=2021
# 判断是2021 Ian看的 year(start_time)=2021
group by a.video_id
order by avg_comp_play_rate desc;


select a.video_id	,
# round(sum(if(timediff(end_time,start_time) >= duration,1,0) ) / count(a.video_id)  ,3)  自己之前写的 是错的
# round(sum(if((end_time-start_time) >= duration,1,0))  / count(a.video_id)  ,3)
 round(sum(if(timestampdiff(second,start_time,end_time) >= duration,1,0) ) / count(a.video_id)  ,3) 
As  avg_comp_play_rate
from tb_user_video_log a, tb_video_info b
where a.video_id= b.video_id 
and  year(start_time)=2021
# 判断是2021 Ian看的 year(start_time)=2021
group by a.video_id
order by avg_comp_play_rate desc;