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。

相关文章

Nginx如何进行流量按比例转发

Nginx如何进行流量按比例转发

《Nginx如何进行流量按比例转发》Nginx可以借助split_clients指令或通过weight参数以及Lua脚本实现流量按比例转发,下面小编就为大家介绍一下两种方式具体的操作步骤吧... 目录方式一：借助split_clients指令1. 配置split_clients2. 配置后端服务器组3. 配
阅读更多...
Java如何获取视频文件的视频时长

Java如何获取视频文件的视频时长

《Java如何获取视频文件的视频时长》文章介绍了如何使用Java获取视频文件的视频时长,包括导入maven依赖和代码案例,同时,也讨论了在运行过程中遇到的SLF4J加载问题,并给出了解决方案... 目录Java获取视频文件的视频时长1、导入maven依赖2、代码案例3、SLF4J: Failed to lo
阅读更多...
Python实现多路视频多窗口播放功能

Python实现多路视频多窗口播放功能

《Python实现多路视频多窗口播放功能》这篇文章主要为大家详细介绍了Python实现多路视频多窗口播放功能的相关知识,文中的示例代码讲解详细,有需要的小伙伴可以跟随小编一起学习一下... 目录一、python实现多路视频播放功能二、代码实现三、打包代码实现总结一、python实现多路视频播放功能服务端开
阅读更多...
Python实现视频转换为音频的方法详解

Python实现视频转换为音频的方法详解

《Python实现视频转换为音频的方法详解》这篇文章主要为大家详细Python如何将视频转换为音频并将音频文件保存到特定文件夹下,文中的示例代码讲解详细,感兴趣的小伙伴可以跟随小编一起学习一下... 目录1. python需求的任务2. Python代码的实现3. 代码修改的位置4. 运行结果5. 注意事项
阅读更多...
Python视频处理库VidGear使用小结

Python视频处理库VidGear使用小结

《Python视频处理库VidGear使用小结》VidGear是一个高性能的Python视频处理库,本文主要介绍了Python视频处理库VidGear使用小结,文中通过示例代码介绍的非常详细,对大家的... 目录一、VidGear的安装二、VidGear的主要功能三、VidGear的使用示例四、VidGea
阅读更多...
流媒体平台/视频监控/安防视频汇聚EasyCVR播放暂停后视频画面黑屏是什么原因？

流媒体平台/视频监控/安防视频汇聚EasyCVR播放暂停后视频画面黑屏是什么原因？

视频智能分析/视频监控/安防监控综合管理系统EasyCVR视频汇聚融合平台，是TSINGSEE青犀视频垂直深耕音视频流媒体技术、AI智能技术领域的杰出成果。该平台以其强大的视频处理、汇聚与融合能力，在构建全栈视频监控系统中展现出了独特的优势。视频监控管理系统EasyCVR平台内置了强大的视频解码、转码、压缩等技术，能够处理多种视频流格式，并以多种格式（RTMP、RTSP、HTTP-FLV、WebS
阅读更多...
综合安防管理平台LntonAIServer视频监控汇聚抖动检测算法优势

综合安防管理平台LntonAIServer视频监控汇聚抖动检测算法优势

LntonAIServer视频质量诊断功能中的抖动检测是一个专门针对视频稳定性进行分析的功能。抖动通常是指视频帧之间的不必要运动，这种运动可能是由于摄像机的移动、传输中的错误或编解码问题导致的。抖动检测对于确保视频内容的平滑性和观看体验至关重要。 优势 1. 提高图像质量 - 清晰度提升：减少抖动，提高图像的清晰度和细节表现力，使得监控画面更加真实可信。 - 细节增强：在低光条件下，抖
阅读更多...
《x86汇编语言：从实模式到保护模式》视频来了

《x86汇编语言：从实模式到保护模式》视频来了

《x86汇编语言：从实模式到保护模式》视频来了 很多朋友留言，说我的专栏《x86汇编语言：从实模式到保护模式》写得很详细，还有的朋友希望我能写得更细，最好是覆盖全书的所有章节。 毕竟我不是作者，只有作者的解读才是最权威的。 当初我学习这本书的时候，只能靠自己摸索，网上搜不到什么好资源。 如果你正在学这本书或者汇编语言，那你有福气了。 本书作者李忠老师，以此书为蓝本，录制了全套视频。 试
阅读更多...
SAM2POINT：以zero-shot且快速的方式将任何 3D 视频分割为视频

SAM2POINT：以zero-shot且快速的方式将任何 3D 视频分割为视频

摘要 我们介绍 SAM2POINT，这是一种采用 Segment Anything Model 2 (SAM 2) 进行零样本和快速 3D 分割的初步探索。 SAM2POINT 将任何 3D 数据解释为一系列多向视频，并利用 SAM 2 进行 3D 空间分割，无需进一步训练或 2D-3D 投影。 我们的框架支持各种提示类型，包括 3D 点、框和掩模，并且可以泛化到不同的场景，例如 3D 对象、室
阅读更多...
树莓派5_opencv笔记27：Opencv录制视频（无声音）

树莓派5_opencv笔记27：Opencv录制视频（无声音）

今日继续学习树莓派5 8G：（Raspberry Pi，简称RPi或RasPi）  本人所用树莓派5 装载的系统与版本如下:  版本可用命令 (lsb_release -a) 查询: Opencv 与 python 版本如下： 今天就水一篇文章，用树莓派摄像头，Opencv录制一段视频保存在指定目录... 文章提供测试代码讲解，整体代码贴出、测试效果图 目录 阶段一：录制一段
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2