2024-12 快手 中等
 需求  表结构  输出示例  参考答案  讨论  举一反三

请编写一个查询，统计横屏（screen_type='l'）和竖屏（screen_type='p'）视频在不同配音和字幕组合下的完播率。具体分为以下四类：

  1. AI配音且有字幕 (if_AI_talking=1 和 if_hint=1)
  2. AI配音无字幕 (if_AI_talking=1 和 if_hint=0)
  3. 不是AI配音有字幕 (if_AI_talking=0 和 if_hint=1)
  4. 不是AI配音无字幕 (if_AI_talking=0 和 if_hint=0)

结果应包括两个行（横屏和竖屏），每行四个列，表示上述四种类型的完播率。

完播率定义为观看至视频结束的用户数占总观看用户数的比例。

输出字段

  • screen_type：屏幕方向（'p' 表示竖屏，'l' 表示横屏）
  • AI_with_hint：AI配音且有字幕的完播率（百分比，保留两位小数）
  • AI_no_hint：AI配音无字幕的完播率（百分比，保留两位小数）
  • no_AI_with_hint：不是AI配音有字幕的完播率（百分比，保留两位小数）
  • no_AI_no_hint：不是AI配音无字幕的完播率（百分比，保留两位小数）

短视频指标体系 行列转化 多表连接 inner join case when sum

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真题

ID126 每月新增用户占比  
中等 inner join case when count 美团
ID162 各金额区间红包拒收率  
简单 case when count sum 百分比计算 支付宝
ID163 最受新一线城市欢迎的红包金额  
简单 inner join case when count sum 百分比计算 支付宝
ID166 比较男女收发红包的差异  
简单 inner join case when sum 支付宝
ID179 数学成绩分段统计（1）
中等 inner join and case when count 字符串处理 多表连接 经典-学生表课程表 作业帮

MySQL 8.0

00:00