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_7d
    FROM
    (
        SELECT  a.tag
               ,substr(b.end_time,1,10) dt -- 提取日期的年月日部分
               ,SUM(if_retweet) if_retweet -- 计算每天的转发量
               ,SUM(if_like) if_like -- 计算每天的点赞量
        FROM tb_video_info a
        LEFT JOIN tb_user_video_log b
        ON 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 -- 按视频类别降序和日期升序排序

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/718594.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

如何在Windows 10中恢复已删除的文件？

如何在Windows 10中恢复已删除的文件？

在 Windows 10 电脑上删除文件是一种常见的操作。如果你不需要某个文件，你会删除它。如果电脑空间用完了，你会尝试删除一些文件以释放更多空间。此外，你可以尝试清理电脑，使用电脑清理工具删除文件。 但是，如果您在 W…
阅读更多...
云服务器部署Neo4j

云服务器部署Neo4j

文章目录 导读安装Neo4j先去官网看看下载安装包如果真的下载了rpm安装包 插件 导读 大模型，他终于来了。 不过呢，大模型相关，现在也就跟着热点去尝试一下multi-agent的RAG方向，看看能做到什么地步。总之我们先从安装neo4j开始。…
阅读更多...
IAP固件升级进阶（Qt上位机）

IAP固件升级进阶（Qt上位机）

前言 时隔近一年，再次接触IAP固件升级，这次修改了以前的一些bug，同时新增一些实用性的功能。 有纰漏请指出，转载请说明。 学习交流请发邮件 1280253714qq.com。 上位机界面 视频演示 当Up对iap固件升级的机制有了更深的理解后…
阅读更多...
44. 【Java教程】方法引用

44. 【Java教程】方法引用

通过前两个小节对Lambda表达式的学习，本小节我们来介绍一个更加深入的知识点 —— 方法引用。通过本小节的学习，你将了解到什么是方法引用，方法引用的基础语法，方法引用的使用条件和使用场景，方法引用的分类&#xff0…
阅读更多...
实用软件下载:UltraEditUEStudio最新安装包及详细安装教程

实用软件下载:UltraEditUEStudio最新安装包及详细安装教程

​UEStudio简介：UEStudio建立在上文本编辑器UltraEdit的功能基础上，并为团队和开发人员提供了其他功能，例如深度Git集成，您可以直接在UEStudio中克隆，签出，更新，提交，推入/拉入等操作…
阅读更多...
《汇编语言程序设计》例子之查找最大数

《汇编语言程序设计》例子之查找最大数

以下是第5章中讲到的 CMOV 的指令的例子，原来的源码是这样的： # cmovtest.s - An example of the CMOV instructions .section .data output:.asciz "The largest value is %d\n" values:.int 105, 235, 61, 315, 134, 221, 53, 145, 117, 5 …
阅读更多...
反向海淘代购系统集成功能详解：从商品对接到物流转运的一体化解决方案

反向海淘代购系统集成功能详解：从商品对接到物流转运的一体化解决方案

随着全球化进程的加速，反向海淘（即从国外购买商品至国内）的需求日益增长。为满足这一市场趋势，反向海淘代购系统正不断进化，集成多种功能以提供更加便捷、高效的服务。本文将深入探讨反向海淘代购系统的核心集成功能&a…
阅读更多...
HTML基础结构入门

HTML基础结构入门

HTML&#xff08;超文本标记语言&#xff09;是构建网页的基础语言。它用于描述网页的结构和内容。让我们从最基本的HTML文档开始。 HTML基础结构 一个基本的HTML文档结构如下&#xff1a; <!DOCTYPE html> <html lang"zh-CN"> <head><meta …
阅读更多...
【Linux硬盘数据读取】WIN10访问linux分区解决方案：ext2fsd

【Linux硬盘数据读取】WIN10访问linux分区解决方案：ext2fsd

<div id"content_views" class"htmledit_views" style"user-select: auto;"><p>尝试ext2explore、Paragon ExtFS都不好用&#xff0c;强烈安利ext2fsd&#xff0c;可读写&#xff0c;很强大</p> 转自&#xff1a;https://blog…
阅读更多...
SpringBoot三层架构

SpringBoot三层架构

目录 一、传统方式 二、三层架构 三、代码拆分 1、dao层 2、service层 3、control层 四、运行结果 一、传统方式 上述代码存在一定的弊端&#xff0c;在进行软件设计和软件开发中提倡单一责任原则&#xff0c;使代码的可读性更强&#xff0c;复杂性更低&#xff0c;可扩展性…
阅读更多...
PFA烧杯带把手带刻度1000ml3000mlPFA氟树脂温度范围－270～250℃

PFA烧杯带把手带刻度1000ml3000mlPFA氟树脂温度范围－270～250℃

随着越来越多的痕量分析实验需要对ppb和ppt级的浓度进行测定。目前所使用的一般材料由于无特别处理&#xff0c;不可避免会与所储存的样品&#xff0c;试剂或标准液反应&#xff0c;导致痕量分析实验得到不正确的结果。但我厂的PFA产品刚好能弥补其不足。PFA金属元素空白值低&a…
阅读更多...
AI存储解决案例分享

AI存储解决案例分享

AI数据管道&#xff08;Data Pipeline&#xff09;是指在AI项目中&#xff0c;数据从原始状态到最终可用模型的整个处理流程&#xff0c;包括数据采集、清洗、转换、分析、训练模型、验证模型直至部署和监控等多个环节。 在AI训练和推理过程中&#xff0c;多个管道可能同时读取…
阅读更多...
一道session文件包含题

一道session文件包含题

目录 环境说明 session文件包含getshell 审计源码 session包含 base64在session中的解码分析 题目&#xff1a; 链接&#xff1a;https://pan.baidu.com/s/1Q0BN08b8gWiVE4tOnirpTA?pwdcate 提取码&#xff1a;cate 环境说明 这里我用的是linux&#xff0c;也可以用p…
阅读更多...
【计算机毕业设计】235基于微信小程序点餐系统

【计算机毕业设计】235基于微信小程序点餐系统

&#x1f64a;作者简介&#xff1a;拥有多年开发工作经验&#xff0c;分享技术代码帮助学生学习&#xff0c;独立完成自己的项目或者毕业设计。 代码可以私聊博主获取。&#x1f339;赠送计算机毕业设计600个选题excel文件&#xff0c;帮助大学选题。赠送开题报告模板&#xff…
阅读更多...
从GAN到WGAN（02/2）

从GAN到WGAN（02/2）

文章目录 一、说明二、GAN中的问题2.1 难以实现纳什均衡(Nash equilibrium)2.2 低维度支撑2.3 梯度消失2.4 模式坍缩2.5 缺乏适当的评估指标 三、改进的GAN训练四、瓦瑟斯坦&#xff08;Wasserstein&#xff09;WGAN4.1 什么是 Wasserstein 距离&#xff1f;4.2 为什么 Wassers…
阅读更多...
大模型太贵？找找自己的原因好吧？

大模型太贵？找找自己的原因好吧？

什么&#xff1f; 炼个大模型还嫌贵&#xff1f; 到底哪里贵了&#xff01;&#xff1f; 大模型算力贵&#xff1f;哪里贵了&#xff01;&#xff1f; 争先恐后训练大模型&#xff0c; 搞得现在“算力慌”“一卡难求”&#xff0c; 算力当然水涨船高了! “特供版”GPU又…
阅读更多...
守护电力心脏：国网电力监控运维平台的智慧使命

守护电力心脏：国网电力监控运维平台的智慧使命

国网电力监控运维平台&#xff0c;以其强大的数据分析和处理能力&#xff0c;实现了对电网运行的实时监控。无论是电压波动、电流异常&#xff0c;还是设备故障&#xff0c;平台都能迅速捕捉并发出预警&#xff0c;确保电力供应的稳定和安全。 山海鲸可视化电力监控运维平台 想…
阅读更多...
RPC协议

RPC协议

3.8 既然有 HTTP 协议&#xff0c;为什么还要有 RPC 假设我们需要在 A 电脑的进程发一段数据到 B 电脑的进程&#xff0c;我们一般会在代码里使用 Socket 进行编程。 这时候&#xff0c;我们可选项一般也就 TCP 和 UDP 二选一。TCP 可靠&#xff0c;UDP 不可靠。 类似下面这…
阅读更多...
AI办公自动化：批量根据Excel表格内容制作Word文档

AI办公自动化：批量根据Excel表格内容制作Word文档

工作任务&#xff1a;Excel表格中有大量文本&#xff0c;根据这些文本自动生成word文档 在chatgpt中输入提示词&#xff1a; 你是一个Python编程专家&#xff0c;写一个Python脚本&#xff0c;具体步骤如下&#xff1a; 读取Excel文件&#xff1a;"F:\AI自媒体内容\AI视…
阅读更多...
AcWing 1273：天才的记忆 ← ST算法求解RMQ问题

AcWing 1273：天才的记忆 ← ST算法求解RMQ问题

【题目来源】https://www.acwing.com/problem/content/1275/【题目描述】 从前有个人名叫 WNB&#xff0c;他有着天才般的记忆力&#xff0c;他珍藏了许多许多的宝藏。 在他离世之后留给后人一个难题&#xff08;专门考验记忆力的啊&#xff01;&#xff09;&#xff0c;如果谁…
阅读更多...
最新文章

Copyright @ 2022~2023