用户-视频互动表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%。
- 结果保留两位小数,并按播放进度倒序排序。
输出示例:
示例数据的输出结果如下:
tag | avg_play_progress |
影视 | 90.00% |
美食 | 75.00% |
解:
SELECT tag, CONCAT(avg_play_progress, "%") as avg_play_progress
FROM (
SELECT tag,
ROUND(
AVG(
IF(TIMESTAMPDIFF(SECOND, start_time, end_time) >= duration, 1,
TIMESTAMPDIFF(SECOND, start_time, end_time) / duration)
) * 100, 2
) as avg_play_progress
FROM tb_user_video_log
JOIN tb_video_info USING(video_id)
GROUP BY tag
HAVING avg_play_progress > 60
ORDER BY avg_play_progress DESC
) as t_progress;
-
在内部查询中,我们首先将
tb_user_video_log
表和tb_video_info
表联接,通过video_id
列关联它们。这样,我们可以访问视频播放日志和视频信息。 -
我们使用
TIMESTAMPDIFF
函数来计算每个视频的实际播放时长与预期播放时长之间的差异(以秒为单位)。然后,使用IF
函数,如果实际播放时长大于或等于预期播放时长,则返回1,否则返回实际播放时长与预期播放时长之间的比率。 -
在内部查询中,我们将播放进度数据按标签 (
tag
) 分组,然后计算每个标签下的平均播放进度,并使用ROUND
函数将其四舍五入为两位小数。 -
使用
HAVING
子句筛选出平均播放进度大于60%的标签。 -
最后,我们在外部查询中将平均播放进度的百分比形式 (
avg_play_progress
) 和标签 (tag
) 选择出来,使用CONCAT
函数将百分比形式的播放进度和百分号连接在一起,并对结果的列进行命名。
知识点:
CONCAT
是 SQL 中用于连接字符串的函数,它将一个或多个字符串连接在一起,生成一个新的字符串。具体的语法如下:
CONCAT(string1, string2, ...)