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

select I.video_id as `video_id`,round(avg(timestampdiff(SECOND,start_time,end_time)>=duration),3) as `avg_comp_play_rate`
from tb_user_video_log as U
left join tb_video_info as I
on I.video_id=U.video_id
where YEAR(start_time)='2021'
group by I.video_id
order by avg_comp_play_rate desc;

# unit: 日期比较返回的时间差单位，常用可选值如下:
# SECOND：秒
# MINUTE：分钟
# HOUR：小时
# DAY：天
# WEEK：星期
# MONTH：月
# QUARTER：季度
# YEAR：年
SELECT TIMESTAMPDIFF(SECOND,'2023-07-31 09:04:08','2023-07-31 09:02:08');  # -120 
# TIMESTAMPDIFF(unit,time1,time2)
# unit(time2-time1)