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

SELECT vi.tag, AVG((uv.end_time-uv.start_time)/vi.duration) AS avg_progress FROM tb_user_video_log uv JOIN tb_video_info vi ON uv.video_id = vi.video_id WHERE (uv.end_time-uv.start_time)/vi.duration > 0.6 GROUP BY vi.tag;