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 …...
Vim 调用外部命令学习笔记
Vim 外部命令集成完全指南 文章目录 Vim 外部命令集成完全指南核心概念理解命令语法解析语法对比 常用外部命令详解文本排序与去重文本筛选与搜索高级 grep 搜索技巧文本替换与编辑字符处理高级文本处理编程语言处理其他实用命令 范围操作示例指定行范围处理复合命令示例 实用技…...
挑战杯推荐项目
“人工智能”创意赛 - 智能艺术创作助手:借助大模型技术,开发能根据用户输入的主题、风格等要求,生成绘画、音乐、文学作品等多种形式艺术创作灵感或初稿的应用,帮助艺术家和创意爱好者激发创意、提高创作效率。 - 个性化梦境…...
Android Wi-Fi 连接失败日志分析
1. Android wifi 关键日志总结 (1) Wi-Fi 断开 (CTRL-EVENT-DISCONNECTED reason3) 日志相关部分: 06-05 10:48:40.987 943 943 I wpa_supplicant: wlan0: CTRL-EVENT-DISCONNECTED bssid44:9b:c1:57:a8:90 reason3 locally_generated1解析: CTR…...
React hook之useRef
React useRef 详解 useRef 是 React 提供的一个 Hook,用于在函数组件中创建可变的引用对象。它在 React 开发中有多种重要用途,下面我将全面详细地介绍它的特性和用法。 基本概念 1. 创建 ref const refContainer useRef(initialValue);initialValu…...

STM32F4基本定时器使用和原理详解
STM32F4基本定时器使用和原理详解 前言如何确定定时器挂载在哪条时钟线上配置及使用方法参数配置PrescalerCounter ModeCounter Periodauto-reload preloadTrigger Event Selection 中断配置生成的代码及使用方法初始化代码基本定时器触发DCA或者ADC的代码讲解中断代码定时启动…...
C++ 基础特性深度解析
目录 引言 一、命名空间(namespace) C 中的命名空间 与 C 语言的对比 二、缺省参数 C 中的缺省参数 与 C 语言的对比 三、引用(reference) C 中的引用 与 C 语言的对比 四、inline(内联函数…...

【VLNs篇】07:NavRL—在动态环境中学习安全飞行
项目内容论文标题NavRL: 在动态环境中学习安全飞行 (NavRL: Learning Safe Flight in Dynamic Environments)核心问题解决无人机在包含静态和动态障碍物的复杂环境中进行安全、高效自主导航的挑战,克服传统方法和现有强化学习方法的局限性。核心算法基于近端策略优化…...
Java数值运算常见陷阱与规避方法
整数除法中的舍入问题 问题现象 当开发者预期进行浮点除法却误用整数除法时,会出现小数部分被截断的情况。典型错误模式如下: void process(int value) {double half = value / 2; // 整数除法导致截断// 使用half变量 }此时...
WebRTC从入门到实践 - 零基础教程
WebRTC从入门到实践 - 零基础教程 目录 WebRTC简介 基础概念 工作原理 开发环境搭建 基础实践 三个实战案例 常见问题解答 1. WebRTC简介 1.1 什么是WebRTC? WebRTC(Web Real-Time Communication)是一个支持网页浏览器进行实时语音…...

Java数组Arrays操作全攻略
Arrays类的概述 Java中的Arrays类位于java.util包中,提供了一系列静态方法用于操作数组(如排序、搜索、填充、比较等)。这些方法适用于基本类型数组和对象数组。 常用成员方法及代码示例 排序(sort) 对数组进行升序…...