在短视频平台中，完播率是一个重要的业务指标，它衡量了用户观看视频的完成度情况。具体来说，完播率是指视频被完整观看的次数占总播放次数的比例。这个指标能够帮助平台了解用户对视频内容的兴趣和满意度，从而优化推荐算法、提升用户体验，并为创作者提供反馈以改进内容质量。
完播率的定义：
- 完播率 = (完播次数 / 播放次数) × 100%
- 完播次数：视频被完整观看的次数（即用户实际观看时长等于或超过视频总时长）。
- 播放次数：视频被点击播放的总次数。
在短视频业务中的作用：
- 内容优化：高完播率表明用户对视频内容感兴趣并愿意看完，这有助于识别优质内容，指导创作者制作更吸引人的视频。
- 用户参与度评估：通过分析完播率，可以评估用户的参与度和粘性，帮助平台制定更有针对性的运营策略。
- 推荐系统优化：高完播率的视频通常更能吸引用户，平台可以优先推荐这些视频，提高整体用户满意度和平台活跃度。
- 广告投放效果：对于带有广告的视频，完播率可以帮助广告主评估广告的效果，确保广告被充分展示。
任务
请编写一个查询，计算近1个月每个视频的完播率（即视频被完整观看的次数占总播放次数的比例）。
结果应包括视频ID、标题以及完播率（以百分比表示，保留两位小数）。最终输出完播率最高的前5个视频。
ks_video_inf，视频信息表
| 字段名 | 数据类型 | 描述 |
|---|---|---|
video_id |
VARCHAR(20) | 视频的唯一标识符 |
author_id |
VARCHAR(20) | 创建该视频的用户的唯一标识符 |
title |
TEXT | 视频的标题 |
tag |
TEXT | 视频的标签，多个标签之间用空格分隔 |
screen_type |
ENUM('p', 'l') | 屏幕方向：'p' 表示竖屏（portrait），'l' 表示横屏（landscape） |
if_AI_talking |
BOOLEAN | 是否AI配音：1 表示是，0 表示否 |
if_hint |
BOOLEAN | 是否有字幕：1 表示有字幕，0 表示无字幕 |
duration |
INT | 视频时长，单位为秒 |
release_time |
DATETIME | 视频发布的时间 |
release_loc |
VARCHAR(50) | 视频发布的地点 |
示例数据如下：
| video_id | author_id | title | tag | screen_type | if_AI_talking | if_hint | duration | release_time | release_loc |
|---|---|---|---|---|---|---|---|---|---|
| v1001 | a1012 | 是金子迟早会发光，小红升职就是的例子 | 红楼梦 87版红楼梦 王熙凤 | p | 1 | 1 | 187 | 2024-10-01 23:23:00 | 北京 |
| v1002 | a2021 | 制造业为何利润低 | 认知 经济 制造业 | p | 0 | 1 | 324 | 2024-10-08 23:34:00 | 北京 |
ks_video_wat_log，用户视频播放日志表
| 字段名 | 数据类型 | 描述 |
|---|---|---|
uid |
VARCHAR(20) | 用户的唯一标识符 |
video_id |
VARCHAR(20) | 视频的唯一标识符 |
start_time |
DATETIME | 用户开始观看视频的时间 |
end_time |
DATETIME | 用户结束观看视频的时间 |
if_follow |
BOOLEAN | 用户是否关注了视频作者：1 表示是，0 表示否 |
if_like |
BOOLEAN | 用户是否点赞了视频：1 表示是，0 表示否 |
if_retweet |
BOOLEAN | 用户是否转发了视频：1 表示是，0 表示否 |
if_fav |
BOOLEAN | 用户是否收藏了视频：1 表示是，0 表示否 |
if_comment |
BOOLEAN | 用户是否评论了视频：1 表示是，0 表示否 |
click_from |
ENUM('1', '2', '3') | 点击来源：1 - 信息流推荐列表；2 - 朋友转发；3 - 评论区视频链接 |
示例数据如下：
| uid | video_id | start_time | end_time | if_follow | if_like | if_retweet | if_fav | if_comment | click_from |
|---|---|---|---|---|---|---|---|---|---|
| u000157 | v1002 | 2024-11-26 17:54:00 | 2024-11-26 18:04:03 | 1 | 1 | 1 | 1 | 0 | 1 |
| u000157 | v1002 | 2024-11-26 21:04:00 | 2024-11-26 21:07:56 | 1 | 0 | 0 | 1 | 0 | 3 |
| u000157 | v1002 | 2024-11-26 21:44:00 | 2024-11-26 21:47:57 | 1 | 1 | 1 | 1 | 0 | 2 |
| u000224 | v1002 | 2024-11-27 11:17:00 | 2024-11-27 11:22:26 | 1 | 1 | 1 | 1 | 1 | 1 |
成为会员，解锁300道大厂数分SQL真题
MySQL 8.0
×