SQL160 国庆期间每类视频点赞量和转发量

2024-06-18 05:52
文章标签 sql160 点赞量 转发 视频 国庆 期间

本文主要是介绍SQL160 国庆期间每类视频点赞量和转发量，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

描述

用户-视频互动表tb_user_video_log

iduidvideo_idstart_timeend_timeif_followif_likeif_retweetcomment_id
110120012021-09-24 10:00:002021-09-24 10:00:20110NULL
210520022021-09-25 11:00:002021-09-25 11:00:30001NULL
310220022021-09-25 11:00:002021-09-25 11:00:30111NULL
410120022021-09-26 11:00:002021-09-26 11:00:30101NULL
510120022021-09-27 11:00:002021-09-27 11:00:30110NULL
610220022021-09-28 11:00:002021-09-28 11:00:30101NULL
710320022021-09-29 11:00:002021-10-02 11:00:30101NULL
810220022021-09-30 11:00:002021-09-30 11:00:30111NULL
910120012021-10-01 10:00:002021-10-01 10:00:20110NULL
1010220012021-10-01 10:00:002021-10-01 10:00:15001NULL
1110320012021-10-01 11:00:502021-10-01 11:01:151101732526
1210620022021-10-02 10:59:052021-10-02 11:00:05201NULL
1310720022021-10-02 10:59:052021-10-02 11:00:05101NULL
1410820022021-10-02 10:59:052021-10-02 11:00:05111NULL
1510920022021-10-03 10:59:052021-10-03 11:00:05010NULL

（uid-用户ID, video_id-视频ID, start_time-开始观看时间, end_time-结束观看时间, if_follow-是否关注, if_like-是否点赞, if_retweet-是否转发, comment_id-评论ID）

短视频信息表tb_video_info

idvideo_idauthortagdurationrelease_time
12001901旅游302020-01-01 07:00:00
22002901旅游602021-01-01 07:00:00
32003902影视902020-01-01 07:00:00
42004902美女902020-01-01 08:00:00

（video_id-视频ID, author-创作者ID, tag-类别标签, duration-视频时长, release_time-发布时间）

问题：统计2021年国庆头3天每类视频每天的近一周总点赞量和一周内最大单天转发量，结果按视频类别降序、日期升序排序。假设数据库中数据足够多，至少每个类别下国庆头3天及之前一周的每天都有播放记录。

输出示例：

示例数据的输出结果如下

tagdtsum_like_cnt_7dmax_retweet_cnt_7d
旅游2021-10-0152
旅游2021-10-0253
旅游2021-10-0363

解释：

由表tb_user_video_log里的数据可得只有旅游类视频的播放，2021年9月25到10月3日每天的点赞量和转发量如下：

tagdtlike_cntretweet_cnt
旅游2021-09-2512
旅游2021-09-2601
旅游2021-09-2710
旅游2021-09-2801
旅游2021-09-2901
旅游2021-09-3011
旅游2021-10-0121
旅游2021-10-0213
旅游2021-10-0310

因此国庆头3天（10.0110.03）里10.01的近7天（9.2510.01）总点赞量为5次，单天最大转发量为2次（9月25那天最大）；同理可得10.02和10.03的两个指标。

示例1

输入：

DROP TABLE IF EXISTS tb_user_video_log, tb_video_info;
CREATE TABLE tb_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 utf8_bin;CREATE TABLE tb_video_info (id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',video_id INT UNIQUE NOT NULL COMMENT '视频ID',author INT 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 utf8_bin;INSERT INTO tb_user_video_log(uid, video_id, start_time, end_time, if_follow, if_like, if_retweet, comment_id) VALUES(101, 2001, '2021-09-24 10:00:00', '2021-09-24 10:00:20', 1, 1, 0, null),(105, 2002, '2021-09-25 11:00:00', '2021-09-25 11:00:30', 0, 0, 1, null),(102, 2002, '2021-09-25 11:00:00', '2021-09-25 11:00:30', 1, 1, 1, null),(101, 2002, '2021-09-26 11:00:00', '2021-09-26 11:00:30', 1, 0, 1, null),(101, 2002, '2021-09-27 11:00:00', '2021-09-27 11:00:30', 1, 1, 0, null),(102, 2002, '2021-09-28 11:00:00', '2021-09-28 11:00:30', 1, 0, 1, null),(103, 2002, '2021-09-29 11:00:00', '2021-09-29 11:00:30', 1, 0, 1, null),(102, 2002, '2021-09-30 11:00:00', '2021-09-30 11:00:30', 1, 1, 1, null),(101, 2001, '2021-10-01 10:00:00', '2021-10-01 10:00:20', 1, 1, 0, null),(102, 2001, '2021-10-01 10:00:00', '2021-10-01 10:00:15', 0, 0, 1, null),(103, 2001, '2021-10-01 11:00:50', '2021-10-01 11:01:15', 1, 1, 0, 1732526),(106, 2002, '2021-10-02 10:59:05', '2021-10-02 11:00:05', 2, 0, 1, null),(107, 2002, '2021-10-02 10:59:05', '2021-10-02 11:00:05', 1, 0, 1, null),(108, 2002, '2021-10-02 10:59:05', '2021-10-02 11:00:05', 1, 1, 1, null),(109, 2002, '2021-10-03 10:59:05', '2021-10-03 11:00:05', 0, 1, 0, null);INSERT INTO tb_video_info(video_id, author, tag, duration, release_time) VALUES(2001, 901, '旅游', 30, '2020-01-01 7:00:00'),(2002, 901, '旅游', 60, '2021-01-01 7:00:00'),(2003, 902, '影视', 90, '2020-01-01 7:00:00'),(2004, 902, '美女', 90, '2020-01-01 8:00:00');

输出：

旅游|2021-10-01|5|2
旅游|2021-10-02|5|3
旅游|2021-10-03|6|3

解答

  1. 内层子查询：首先，我们从tb_video_info和tb_user_video_log两个表中获取视频的标签（tag）、结束时间（end_time），并对每类视频的每天点赞量（if_like）和转发量（if_retweet）进行求和。

  2. 日期处理：使用substr(b.end_time,1,10)函数提取日期的年月日部分，以便后续的日期范围筛选和排序。

  3. 中间子查询：在内层子查询的基础上，使用窗口函数计算每类视频在指定日期前6天（即近一周）的总点赞量和最大单天转发量。

  4. 窗口函数：

    • SUM(if_like) over(PARTITION BY tag ORDER BY dt ROWS 6 PRECEDING)：计算每类视频在近一周内的总点赞量。
    • MAX(if_retweet) over(PARTITION BY tag ORDER BY dt ROWS 6 PRECEDING)：计算每类视频在近一周内的最大单天转发量。

  5. 外层查询：在中间子查询的基础上，通过WHERE子句筛选出2021年国庆头3天的数据。

  6. 结果排序：最后，使用ORDER BY子句按视频类别降序和日期升序对结果进行排序。

SELECT  *
FROM
(SELECT  tag,dt-- 计算每类视频近一周的总点赞量,SUM(if_like) over( PARTITION BY tag ORDER BY  dt  rows  6 preceding  )   AS sum_like_cnt_7d-- 计算每类视频近一周的最大单天转发量,MAX(if_retweet) over( PARTITION BY tag ORDER BY dt  rows  6 preceding  ) AS max_retweet_cnt_7dFROM(SELECT  a.tag,substr(b.end_time,1,10) dt -- 提取日期的年月日部分,SUM(if_retweet) if_retweet -- 计算每天的转发量,SUM(if_like) if_like -- 计算每天的点赞量FROM tb_video_info aLEFT JOIN tb_user_video_log bON a.video_id = b.video_id -- 通过视频ID关联两个表GROUP BY  tag,substr(b.end_time,1,10) -- 按视频标签和日期分组) t
) t
WHERE dt BETWEEN '2021-10-01' AND '2021-10-03' -- 筛选国庆头3天的数据order by tag desc, dt -- 按视频类别降序和日期升序排序

这篇关于SQL160 国庆期间每类视频点赞量和转发量的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1071479。

相关文章

百度OCR识别结构结构化处理视频

百度OCR识别结构结构化处理视频

https://edu.csdn.net/course/detail/10506
阅读更多...
RISC-V教学内容及短视频吸引因素

RISC-V教学内容及短视频吸引因素

RISC-V 教学内容 在我的认知中，大多数人对RV仍然了解甚少，我想本实习岗位主要面向对 RV 不了解或了解很少的同学，帮助大家更好入门。 因此教学内容为： RISC-V 简要介绍；RISC-V 指令集知识简要讲解，指令集介绍，各个指令集的指令规则及指令运行演示，可以结合模拟器运行结果讲解更为直观；常见 RV 模拟器介绍，分别适用的场景。对于一些简单的 RV 模拟器可以展开讲解安装部署使用
阅读更多...
YOLOv9摄像头或视频实时检测

YOLOv9摄像头或视频实时检测

1、下载yolov9的项目 地址：YOLOv9 2、使用下面代码进行检测 import torchimport cv2from models.experimental import attempt_loadfrom utils.general import non_max_suppression, scale_boxesfrom utils.plots import plot_o
阅读更多...
google gemini1.5 flash视频图文理解能力初探（一）

google gemini1.5 flash视频图文理解能力初探（一）

市面能够对视频直接进行分析的大模型着实不多，而且很多支持多模态的大模型那效果着实也不好。 从这篇公众号不只是100万上下文，谷歌Gemini 1.5超强功能展示得知，Gemini 1.5可以一次性处理1小时的视频、11小时的音频或100,000行代码，并衍生出更多的数据分析玩法。能力覆盖： 跨模式理解和推理，当给出一部 44 分钟的巴斯特-基顿（Buster Keaton）无声电影时，该模型能准
阅读更多...
警惕！推广文章与视频的兼职骗局大揭秘

警惕！推广文章与视频的兼职骗局大揭秘

在互联网时代，我们常常会看到各种各样的推广兼职信息，承诺可以轻松赚钱，特别是在推广文章和视频方面。然而，在这些看似诱人的机会背后，往往隐藏着一些骗局。本文将为大家揭示这些骗局，帮助大家避免上当受骗。 一、骗局特征 1. **高额回报承诺**：骗子常常以高额的回报作为诱饵，声称只要转发文章、分享视频，就能获得丰厚的佣金或收益。然而，这种不切实际的高额回报往往是骗局的开始。 2. **简单操作
阅读更多...
视频监控平台：支持交通部行业标准JT/T905协议（即:出租汽车服务管理信息系统）的源代码的函数和功能介绍及分享

视频监控平台：支持交通部行业标准JT/T905协议（即:出租汽车服务管理信息系统）的源代码的函数和功能介绍及分享

目录 一、视频监控平台介绍 （一）概述 （二）视频接入能力介绍 （三）功能介绍 二、JT/T905协议介绍 （一）概述 （二）主要内容 1、设备要求 2、业务功能要求 3、技术功能要求 4、性能要求 5、接口要求 6、设备通信协议与数据格式 三、代码和解释 （一）代码和注释 （二）函数功能说明 1. 头文件保护 2. 包含必要的头文件 3. 引入命名空间std
阅读更多...
AIGC-Animate Anyone阿里的图像到视频 角色合成的框架-论文解读

AIGC-Animate Anyone阿里的图像到视频 角色合成的框架-论文解读

Animate Anyone: Consistent and Controllable Image-to-Video Synthesis for Character Animation 论文:https://arxiv.org/pdf/2311.17117 网页:https://humanaigc.github.io/animate-anyone/ MOTIVATION 角色动画的
阅读更多...
WordPress视频主题Qinmei 2.0

WordPress视频主题Qinmei 2.0

WordPress视频主题Qinmei 2.0，简单漂亮的WP视频站源码 主题功能 可以根据豆瓣ID直接获取到其他详细信息，省去慢慢填写的痛苦；播放器支持直链，解析，m3u8格式，同时解析可匹配正则自动更改；新增动态页面，将评论样式改变作为信息流展示；分类展示，可根据评分排序等等； WordPress视频主题Qinmei 2.0
阅读更多...
视频批量剪辑新境界：一键转码MP4至MP3并自动删除原文件，轻松优化存储空间与播放体验

视频批量剪辑新境界：一键转码MP4至MP3并自动删除原文件，轻松优化存储空间与播放体验

随着数字媒体的飞速发展，视频文件已成为我们生活中不可或缺的一部分。然而，大量视频文件的累积不仅占据了宝贵的存储空间，而且在某些情况下，我们更希望提取视频中的音频内容。为了满足这一需求，我们推出了全新的视频批量剪辑方案，让你轻松实现MP4到MP3的转码，并自动删除原文件，优化存储空间，提升播放体验。 首先，让我们进入“视频剪辑高手”的主页面。简洁明了的界面设计，让您一眼就能找到所需的功能板
阅读更多...
视频讲解|【双层模型】分布式光伏储能系统的优化配置方法

视频讲解|【双层模型】分布式光伏储能系统的优化配置方法

1 主要内容 该讲解视频对应的程序链接为【双层模型】分布式光伏储能系统的优化配置方法，模型参考《分布式光伏储能系统的优化配置方法》，分为上下层求解方式，上层采用粒子群算法确定储能的选址和容量方案，以全年购电成本、网络损耗、光伏运行成本、储能充放电和投资成本为目标；下层采用混合整数规划算法（默认求解器为cplex，也可替换成gurobi），以IEEE33节点配电网为研究对象，通过二阶锥模型求解，以
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2