用户-视频互动表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 tb_user_video_log.video_id, COUNT(CASE WHEN tb_user_video_log.end_time IS NOT NULL THEN 1 END) / COUNT(*) AS complete_rate FROM tb_user_video_log INNER JOIN tb_video_info ON tb_user_video_log.video_id = tb_video_info.video_id WHERE YEAR(tb_user_video_log.start_time) = 2021 GROUP BY tb_user_video_log.video_id ORDER BY complete_rate DESC;