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。

相关文章

流媒体平台/视频监控/安防视频汇聚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录制一段视频保存在指定目录... 文章提供测试代码讲解，整体代码贴出、测试效果图 目录 阶段一：录制一段
阅读更多...
基于树梅派的视频监控机器人Verybot

基于树梅派的视频监控机器人Verybot

最近这段时间做了一个基于树梅派 ( raspberry pi ) 的视频监控机器人平台 Verybot ，现在打算把这个机器人的一些图片、视频、设计思路进行公开，并且希望跟大家一起研究相关的各种问题，下面是两张机器人的照片：         图片1：                   图片2                    这个平台的基本组成是：
阅读更多...
PC与android平板通过浏览器监控Verybot的视频

PC与android平板通过浏览器监控Verybot的视频

下面这个视频是PC与android平板通过浏览器监控Verybot的视频：           http://v.youku.com/v_show/id_XNjYzNzYyMTIw.html
阅读更多...
Verybot的几个视频

Verybot的几个视频

1、Verybot的运动控制                 http://v.youku.com/v_show/id_XNjYxNjg4MTM2.html           2、Verybot比较初步的网络视频监控           http://v.youku.com/v_show/id_XNjYxNjkyMjg0.html           3、V
阅读更多...
java计算机毕设课设—停车管理信息系统(附源码、文章、相关截图、部署视频)

java计算机毕设课设—停车管理信息系统(附源码、文章、相关截图、部署视频)

这是什么系统？ 资源获取方式在最下方 java计算机毕设课设—停车管理信息系统(附源码、文章、相关截图、部署视频) 停车管理信息系统是为了提升停车场的运营效率和管理水平而设计的综合性平台。系统涵盖用户信息管理、车位管理、收费管理、违规车辆处理等多个功能模块，旨在实现对停车场资源的高效配置和实时监控。此外，系统还提供了资讯管理和统计查询功能，帮助管理者及时发布信息并进行数据分析，为停车场的科学
阅读更多...
一款支持同一个屏幕界面同时播放多个视频的视频播放软件

一款支持同一个屏幕界面同时播放多个视频的视频播放软件

GridPlayer 是一款基于 VLC 的免费开源跨平台多视频同步播放工具，支持在一块屏幕上同时播放多个视频。其主要功能包括： 多视频播放：用户可以在一个窗口中同时播放任意数量的视频，数量仅受硬件性能限制。支持多种格式和流媒体：GridPlayer 支持所有由 VLC 支持的视频格式以及流媒体 URL（如 m3u8 链接）。自定义网格布局：用户可以配置播放器的网格布局，以适应不同的观看需求。硬
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2