短视频平台用户观看行为分析 - 播放进度统计

短视频平台用户观看行为分析 - 播放进度统计

本文将通过SQL语句统计不同类型短视频的平均播放进度，并筛选出播放进度大于60%的视频类别，分析用户对不同类型视频的观看偏好。

数据表结构

用户-视频互动表tb_user_video_log

| id | uid | video_id | start_time | end_time | if_follow | if_like | if_retweet | comment_id | |---|---|---|---|---|---|---|---|---| | 1 | 101 | 2001 | 2021-10-01 10:00:00 | 2021-10-01 10:00:30 | 0 | 1 | 1 | NULL | | 2 | 102 | 2001 | 2021-10-01 10:00:00 | 2021-10-01 10:00:21 | 0 | 0 | 1 | NULL | | 3 | 103 | 2001 | 2021-10-01 11:00:50 | 2021-10-01 11:01:20 | 0 | 1 | 0 | 1732526 | | 4 | 102 | 2002 | 2021-10-01 11:00:00 | 2021-10-01 11:00:30 | 1 | 0 | 1 | NULL | | 5 | 103 | 2002 | 2021-10-01 10:59:05 | 2021-10-01 11:00:05 | 1 | 0 | 1 | NULL |

(uid-用户ID, video_id-视频ID, start_time-开始观看时间, end_time-结束观看时间, if_follow-是否关注, if_like-是否点赞, if_retweet-是否转发, comment_id-评论ID)

短视频信息表tb_video_info

| id | video_id | author | tag | duration | release_time | |---|---|---|---|---|---| | 1 | 2001 | 901 | '影视' | 30 | 2021-01-01 07:00:00 | | 2 | 2002 | 901 | '美食' | 60 | 2021-01-01 07:00:00 | | 3 | 2003 | 902 | '旅游' | 90 | 2021-01-01 07:00:00 |

(video_id-视频ID, author-创作者ID, tag-类别标签, duration-视频时长, release_time-发布时间)

问题：

计算各类视频的平均播放进度，将进度大于60%的类别输出。

注：

• 播放进度=播放时长÷视频时长*100%，当播放时长大于视频时长时，播放进度均记为100%。
• 结果保留两位小数，并按播放进度倒序排序。

SQL代码

SELECT tb_video_info.tag, 
    ROUND(AVG(IF(tb_user_video_log.end_time IS NULL OR tb_video_info.duration = 0, 100, 
        IF(tb_user_video_log.end_time-tb_user_video_log.start_time > tb_video_info.duration,
            100, (tb_user_video_log.end_time-tb_user_video_log.start_time)/tb_video_info.duration*100))), 2) AS play_progress
FROM tb_user_video_log 
JOIN tb_video_info ON tb_user_video_log.video_id = tb_video_info.video_id 
GROUP BY tb_video_info.tag 
HAVING play_progress > 60 
ORDER BY play_progress DESC;