有用户-视频互动表tb_user_video_log uid-用户ID video_id-视频ID start_time-开始观看时间 end_time-结束观看时间 if_follow-是否关注 if_like-是否点赞 if_retweet-是否转发 comment_id-评论ID、短视频信息表tb_video_info video_id-视频ID author-创作者ID tag-类别标签

SELECT 
    v.video_id, 
    ROUND(COUNT(CASE WHEN v.duration <= TIMESTAMPDIFF(SECOND, l.start_time, l.end_time) THEN 1 END) / COUNT(*), 3) AS completion_rate
FROM 
    tb_video_info v
    JOIN tb_user_video_log l ON v.video_id = l.video_id
WHERE 
    YEAR(l.start_time) = 2021
GROUP BY 
    v.video_id
ORDER BY 
    completion_rate DESC;