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 …...

树莓派超全系列教程文档--(61)树莓派摄像头高级使用方法
树莓派摄像头高级使用方法 配置通过调谐文件来调整相机行为 使用多个摄像头安装 libcam 和 rpicam-apps依赖关系开发包 文章来源: http://raspberry.dns8844.cn/documentation 原文网址 配置 大多数用例自动工作,无需更改相机配置。但是,一…...

新能源汽车智慧充电桩管理方案:新能源充电桩散热问题及消防安全监管方案
随着新能源汽车的快速普及,充电桩作为核心配套设施,其安全性与可靠性备受关注。然而,在高温、高负荷运行环境下,充电桩的散热问题与消防安全隐患日益凸显,成为制约行业发展的关键瓶颈。 如何通过智慧化管理手段优化散…...
MySQL中【正则表达式】用法
MySQL 中正则表达式通过 REGEXP 或 RLIKE 操作符实现(两者等价),用于在 WHERE 子句中进行复杂的字符串模式匹配。以下是核心用法和示例: 一、基础语法 SELECT column_name FROM table_name WHERE column_name REGEXP pattern; …...
laravel8+vue3.0+element-plus搭建方法
创建 laravel8 项目 composer create-project --prefer-dist laravel/laravel laravel8 8.* 安装 laravel/ui composer require laravel/ui 修改 package.json 文件 "devDependencies": {"vue/compiler-sfc": "^3.0.7","axios": …...

Golang——9、反射和文件操作
反射和文件操作 1、反射1.1、reflect.TypeOf()获取任意值的类型对象1.2、reflect.ValueOf()1.3、结构体反射 2、文件操作2.1、os.Open()打开文件2.2、方式一:使用Read()读取文件2.3、方式二:bufio读取文件2.4、方式三:os.ReadFile读取2.5、写…...

Linux部署私有文件管理系统MinIO
最近需要用到一个文件管理服务,但是又不想花钱,所以就想着自己搭建一个,刚好我们用的一个开源框架已经集成了MinIO,所以就选了这个 我这边对文件服务性能要求不是太高,单机版就可以 安装非常简单,几个命令就…...
智能职业发展系统:AI驱动的职业规划平台技术解析
智能职业发展系统:AI驱动的职业规划平台技术解析 引言:数字时代的职业革命 在当今瞬息万变的就业市场中,传统的职业规划方法已无法满足个人和企业的需求。据统计,全球每年有超过2亿人面临职业转型困境,而企业也因此遭…...

2.3 物理层设备
在这个视频中,我们要学习工作在物理层的两种网络设备,分别是中继器和集线器。首先来看中继器。在计算机网络中两个节点之间,需要通过物理传输媒体或者说物理传输介质进行连接。像同轴电缆、双绞线就是典型的传输介质,假设A节点要给…...

pgsql:还原数据库后出现重复序列导致“more than one owned sequence found“报错问题的解决
问题: pgsql数据库通过备份数据库文件进行还原时,如果表中有自增序列,还原后可能会出现重复的序列,此时若向表中插入新行时会出现“more than one owned sequence found”的报错提示。 点击菜单“其它”-》“序列”,…...
【java】【服务器】线程上下文丢失 是指什么
目录 ■前言 ■正文开始 线程上下文的核心组成部分 为什么会出现上下文丢失? 直观示例说明 为什么上下文如此重要? 解决上下文丢失的关键 总结 ■如果我想在servlet中使用线程,代码应该如何实现 推荐方案:使用 ManagedE…...