SQL学习,大厂面试真题(1):观看各个视频的平均完播率
各个视频的平均完播率
1、视频信息表
ID | AuthorName | Category | Age | Start Time |
---|---|---|---|---|
1 | 张三 | 影视 | 30 | 2024-01-01 7:00:00 |
2 | 李四 | 美食 | 60 | 2024-01-01 7:00:00 |
3 | 王麻子 | 旅游 | 90 | 2024-01-01 7:00:00 |
(video_id-视频ID, AuthorName-创作者, tag-类别标签, duration-视频时长(秒), release_time-发布时间)
2、视频互动表
ID | Group | Start Time | End Time | Status | Flag1 | Flag2 | Value |
---|---|---|---|---|---|---|---|
1 | 1 | 2024-06-01 10:00:00 | 2024-06-01 10:00:30 | 0 | 1 | 1 | null |
2 | 1 | 2024-06-01 10:00:00 | 2024-06-01 10:00:24 | 0 | 0 | 1 | null |
3 | 1 | 2024-06-01 11:00:00 | 2024-06-01 11:00:34 | 0 | 1 | 0 | 1 |
1 | 2 | 2024-09-01 10:00:00 | 2024-09-01 10:00:42 | 1 | 0 | 1 | null |
2 | 2 | 2024-06-01 11:00:00 | 2024-06-01 11:00:30 | 1 | 0 | 1 | null |
3 | 1 | 2024-06-01 12:00:00 | 2024-06-01 11:00:34 | 0 | 1 | 0 | 1 |
(uid-用户ID, video_id-视频ID, start_time-开始观看时间, end_time-结束观看时间, if_follow-是否关注, if_like-是否点赞, if_retweet-是否转发, comment_id-评论ID)
问题:计算2024年里有播放记录的每个视频的完播率(结果保留三位小数),并按完播率降序排序
注:视频完播率是指完成播放次数占总播放次数的比例。简单起见,结束观看时间与开始播放时间的差>=视频时长时,视为完成播放。
SQL实现过程:
1、创建表和插入数据
CREATE TABLE dy_video_info (id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',video_id INT UNIQUE NOT NULL COMMENT '视频ID',authorname VARCHAR(16) NOT NULL COMMENT '创作者ID',tag VARCHAR(16) NOT NULL COMMENT '类别标签',duration INT NOT NULL COMMENT '视频时长(秒数)',release_time datetime NOT NULL COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8mb3_general_ci;INSERT INTO dy_video_info(video_id, authorname, tag, duration, release_time) VALUES(1, '张三', '影视', 31, '2024-01-01 7:00:00'),(2, '李四', '美食', 65, '2024-01-01 7:00:00'),(3, '王麻子', '搞笑', 90, '2024-01-01 7:00:00');CREATE TABLE dy_user_video_log (id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',uid INT NOT NULL COMMENT '用户ID',video_id INT NOT NULL COMMENT '视频ID',start_time datetime COMMENT '开始观看时间',end_time datetime COMMENT '结束观看时间',if_follow TINYINT COMMENT '是否关注',if_like TINYINT COMMENT '是否点赞',if_retweet TINYINT COMMENT '是否转发',comment_id INT COMMENT '评论ID'
) CHARACTER SET utf8 COLLATE utf8mb3_general_ci;INSERT INTO dy_user_video_log(uid, video_id, start_time, end_time, if_follow, if_like, if_retweet, comment_id) VALUES(1, 1, '2024-06-01 10:00:00', '2024-06-01 10:00:30', 0, 1, 1, null),(2, 1, '2024-06-01 10:00:00', '2024-06-01 10:00:24', 0, 0, 1, null),(3, 3, '2024-06-01 11:00:00', '2024-06-01 11:00:34', 0, 1, 0, 1),(1, 2, '2024-09-01 10:00:00', '2024-09-01 10:00:42', 1, 0, 1, null),(2, 2, '2024-06-01 11:00:00', '2024-06-01 11:00:30', 1, 0, 1, null),(3, 3, '2024-06-01 11:00:00', '2024-06-01 11:00:34', 0, 1, 0, 1);
a、先分析:
b、计算结束时间和开始时间的差值:
SELECT video_id ,end_time - start_time as avg_comp_play_rate
FROM dy_user_video_log ORDER BY video_id
c、加入结束时间减开始时间大于30的记为1,其他的记为0
SELECT video_id,avg_comp_play_rate,IF(avg_comp_play_rate > 30, 1, 0) AS play_rate_result
FROM
(SELECT video_id,(end_time - start_time) as avg_comp_play_rateFROM dy_user_video_log
) AS derived_table_name;
2、SQL实现效果
-- 选择视频ID和计算平均完成播放率
SELECT a.video_id, -- 选择视频的ID-- 计算平均完成播放率,四舍五入到小数点后三位round(-- 使用条件求和和计数函数计算完成播放率sum(if(-- 如果视频的结束时间减去开始时间大于等于视频的时长,则认为是完成播放end_time - start_time >= duration, 1, -- 完成播放记为10 -- 否则记为0)) / -- 将完成播放的个数除以总播放次数count(start_time), -- 计算总播放次数3 -- 四舍五入到小数点后三位) as avg_comp_play_rate -- 将计算结果命名为avg_comp_play_rate
FROM dy_user_video_log a -- 从dy_user_video_log表中选择数据,别名为a
-- 左连接dy_video_info表,别名为b,根据视频ID匹配
LEFT JOIN dy_video_info bon a.video_id = b.video_id
WHERE year(start_time) = 2024 -- 筛选出开始时间年份为2024的记录
GROUP BY a.video_id -- 根据视频ID分组
ORDER BY avg_comp_play_rate DESC; -- 按平均完成播放率降序排列
相关文章:

SQL学习,大厂面试真题(1):观看各个视频的平均完播率
各个视频的平均完播率 1、视频信息表 IDAuthorNameCategoryAgeStart Time1张三影视302024-01-01 7:00:002李四美食602024-01-01 7:00:003王麻子旅游902024-01-01 7:00:00 (video_id-视频ID, AuthorName-创作者, tag-类别标签, duration-视频时长(秒&…...
2023年全国大学生数学建模竞赛C题蔬菜类商品的自动定价与补货决策(含word论文和源代码资源)
文章目录 一、题目二、word版实验报告和源代码(两种获取方式) 一、题目 2023高教社杯全国大学生数学建模竞赛题目 C题 蔬菜类商品的自动定价与补货决策 在生鲜商超中,一般蔬菜类商品的保鲜期都比较短,且品相随销售时间的增加而…...

inpaint下载安装2024-inpaint软件安装包下载v5.0.6官网最新版附加详细安装步骤
Inpaint软件最新版是一款功能强大的图片去水印软件,这款软件拥有强大的智能算法,能够根据照片的背景为用户去除照片中的各种水印,并修补好去除水印后的图片。并且软件操作简单、界面清爽,即使是修图新手也能够轻松上手,…...

分享三个仓库
Hello , 我是恒。大概有半个月没有发文章了,都写在文档里了 今天分享三个我开源的项目,比较小巧但是有用 主页 文档导航 Github地址: https://github.com/lmliheng/document 在线访问:http://document.liheng.work/ 里面有各种作者书写的文档ÿ…...

MacOS - 启动台多了个『卸载 Adobe Photoshop』
问题描述 今天安装好了 Adobe Ps,但是发现启动台多了个『卸载 Adobe Photoshop』强迫症又犯了,想把它干掉! 解决方案 打开访达 - 前往 - 资源库,搜索要卸载的名字就可以看到,然后移除到垃圾筐...
PHP 日期处理完全指南
PHP 日期处理完全指南 引言 在PHP开发中,日期和时间处理是一个常见且重要的任务。PHP提供了丰富的内置函数来处理日期和时间,包括日期的格式化、计算、解析等。本文将详细介绍PHP中日期处理的相关知识,帮助读者全面理解和掌握这一技能。 1. PHP日期函数基础 1.1 date()函…...

KVB:怎么样选择最优交易周期?
摘要 在金融交易中,周期的选择是影响交易成败的重要因素之一。不同的交易周期对应不同的市场环境和交易策略,选择合适的周期可以提高交易的成功率。本文将详细探讨交易中如何选择最优周期,包括短周期、中周期和长周期的特点及适用情况&#…...
前端面试题日常练-day69 【面试题】
题目 希望这些选择题能够帮助您进行前端面试的准备,答案在文末 TypeScript中,以下哪个关键字用于声明一个变量的类型为联合类型? a) union b) any c) all d) | 在TypeScript中,以下哪个符号用于声明一个变量的类型为对象类型&am…...

Java 解析xml文件-工具类
Java 解析xml文件-工具类 简述 Java解析xml文件,对应的Javabean是根据xml中的节点来创建,如SeexmlZbomord、SeexmlIdoc等等 工具类代码 import cn.hutool.core.io.FileUtil; import com.alibaba.cloud.commons.io.IOUtils; import com.seexml.bom.Se…...

PyQt5学习系列之新项目创建并使用widget
PyQt5学习系列之新项目创建并使用widget 前言报错新建项目程序完整程序总结 前言 新建项目,再使用ui转py,无论怎么样都打不开py文件,直接报错。 报错 Connected to pydev debugger (build 233.11799.298)新建项目程序 # Press ShiftF10 to…...

mtk8675 安卓端assert函数的坑
8675 安卓端, assert(pthread_mutex_init(&mutex_data_, &mattr) 0);用这行代码发现pthread_mutex_init函数没有被调用,反汇编发现不光没调用assert,pthread_mutex_init也没调用。直接pthread_mutex_init(&mutex_data_, &ma…...
编程入门笔记:从基础到进阶的探索之旅
编程入门笔记:从基础到进阶的探索之旅 编程,作为现代科技的基石,正日益渗透到我们生活的方方面面。对于初学者来说,掌握编程技能不仅有助于提升解决问题的能力,还能开启通往创新世界的大门。本篇文章将从四个方面、五…...

小规模自建 Elasticsearch 的部署及优化
本文将详细介绍如何在 CentOS 7 操作系统上部署并优化 Elasticsearch 5.3.0,以承载千万级后端服务的数据采集。要使用Elasticsearch至少需要三台独立的服务器,本文所用服务器配置为4核8G的ECS云服务器,其中一台作为 master + data 节点、一台作为 client + data 节点、最后一…...

MySQL 示例数据库大全
前言: 我们练习 SQL 时,总会自己创造一些测试数据或者网上找些案例来学习,其实 MySQL 官方提供了好几个示例数据库,在 MySQL 的学习、开发和实践中具有非常重要的作用,能够帮助初学者更好地理解和应用 MySQL 的各种功…...

VirtualBox、Centos7下安装docker后pull镜像问题、ftp上传文件问题
Docker安装篇(CentOS7安装)_docker 安装 centos7-CSDN博客 首先,安装docker可以根据这篇文章进行安装,安装完之后,我们就需要去通过docker拉取相关的服务镜像,然后安装相应的服务容器,比如我们通过docker来安装mysql,…...
链表 题目汇总
237. 删除链表中的节点...

grafana连接influxdb2.x做数据大盘
连接influxdb 展示数据 新建仪表盘 选择存储库 设置展示...
Java证件识别中的身份证识别接口
现如今,越来越多的互联网应用需要对身份证进行实名认证,但不知道大家有没有发现,从最初的手动录入身份证信息转变到了现在的图片上传自动识别呢?其实,这都是因为集成了身份证识别接口功能,今天,…...

迷你小风扇哪个品牌好?迷你小风扇前十名公开揭晓!
随着夏日的炎热袭来,迷你小风扇成为了许多人随身携带的清凉利器。无论是在办公室、户外活动,还是在旅行途中,迷你小风扇都以其小巧便携、强劲风力和持久续航的优势,迅速俘获了大批用户的喜爱。然而,市面上迷你小风扇品…...

MikroTik RouterOS 授权签名验证分析
MikroTik 软路由 百科https://baike.baidu.com/item/mikrotik/9776775官网https://mikrotik.com/ 授权文件分析 -----BEGIN MIKROTIK SOFTWARE KEY------------ mr3jH5qhn9irtF53ZICFTN7Tk7wIx7ZkxdAxJ19ydASY ShhFteHMntBTyaS8wuNdIJJPidJxbuNPLTvCsv7zLA …...

AI-调查研究-01-正念冥想有用吗?对健康的影响及科学指南
点一下关注吧!!!非常感谢!!持续更新!!! 🚀 AI篇持续更新中!(长期更新) 目前2025年06月05日更新到: AI炼丹日志-28 - Aud…...

微软PowerBI考试 PL300-选择 Power BI 模型框架【附练习数据】
微软PowerBI考试 PL300-选择 Power BI 模型框架 20 多年来,Microsoft 持续对企业商业智能 (BI) 进行大量投资。 Azure Analysis Services (AAS) 和 SQL Server Analysis Services (SSAS) 基于无数企业使用的成熟的 BI 数据建模技术。 同样的技术也是 Power BI 数据…...

高频面试之3Zookeeper
高频面试之3Zookeeper 文章目录 高频面试之3Zookeeper3.1 常用命令3.2 选举机制3.3 Zookeeper符合法则中哪两个?3.4 Zookeeper脑裂3.5 Zookeeper用来干嘛了 3.1 常用命令 ls、get、create、delete、deleteall3.2 选举机制 半数机制(过半机制࿰…...

Cilium动手实验室: 精通之旅---20.Isovalent Enterprise for Cilium: Zero Trust Visibility
Cilium动手实验室: 精通之旅---20.Isovalent Enterprise for Cilium: Zero Trust Visibility 1. 实验室环境1.1 实验室环境1.2 小测试 2. The Endor System2.1 部署应用2.2 检查现有策略 3. Cilium 策略实体3.1 创建 allow-all 网络策略3.2 在 Hubble CLI 中验证网络策略源3.3 …...

el-switch文字内置
el-switch文字内置 效果 vue <div style"color:#ffffff;font-size:14px;float:left;margin-bottom:5px;margin-right:5px;">自动加载</div> <el-switch v-model"value" active-color"#3E99FB" inactive-color"#DCDFE6"…...

如何理解 IP 数据报中的 TTL?
目录 前言理解 前言 面试灵魂一问:说说对 IP 数据报中 TTL 的理解?我们都知道,IP 数据报由首部和数据两部分组成,首部又分为两部分:固定部分和可变部分,共占 20 字节,而即将讨论的 TTL 就位于首…...
大数据学习(132)-HIve数据分析
🍋🍋大数据学习🍋🍋 🔥系列专栏: 👑哲学语录: 用力所能及,改变世界。 💖如果觉得博主的文章还不错的话,请点赞👍收藏⭐️留言Ǵ…...
python报错No module named ‘tensorflow.keras‘
是由于不同版本的tensorflow下的keras所在的路径不同,结合所安装的tensorflow的目录结构修改from语句即可。 原语句: from tensorflow.keras.layers import Conv1D, MaxPooling1D, LSTM, Dense 修改后: from tensorflow.python.keras.lay…...
Linux离线(zip方式)安装docker
目录 基础信息操作系统信息docker信息 安装实例安装步骤示例 遇到的问题问题1:修改默认工作路径启动失败问题2 找不到对应组 基础信息 操作系统信息 OS版本:CentOS 7 64位 内核版本:3.10.0 相关命令: uname -rcat /etc/os-rele…...
JS设计模式(4):观察者模式
JS设计模式(4):观察者模式 一、引入 在开发中,我们经常会遇到这样的场景:一个对象的状态变化需要自动通知其他对象,比如: 电商平台中,商品库存变化时需要通知所有订阅该商品的用户;新闻网站中࿰…...