MySQl 专栏持续更新 不说晦涩难懂的东西 尽量输出容易理解 和 使用的SQL技巧 和 初中级开发不是很常用的但很有用的知识

欢迎查看👉🏻👉🏻👉🏻SQL 专栏 查漏补缺 指教一二

前言

通过思路解析 分析SQL书写 拆分逻辑 简单易懂 跟着学习 等系列更新完 SQL编写能力 和 SQL思维都会有提升 欢迎关注专栏 如果有更简单的接替方法 可以发在评论区会补充完善

为什么 进行SQL 思维训练 真正做过实战项目的人明白 其实开发思维很重要 开发思维是大于 开发能力的 SQL思维能力也一样 因为SQL是和数据直接打交道的 需要去训练自己的编写能力和编写思路 能够最快的找到最优解

创建表

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-10-01 10:00:00', '2021-10-01 10:00:20', 0, 1, 1, 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', 0, 1, 0, 1732526)
  ,(102, 2002, '2021-09-10 11:00:00', '2021-09-10 11:00:30', 1, 0, 1, null)
  ,(103, 2002, '2021-10-01 10:59:05', '2021-10-01 11:00:05', 1, 0, 0, null);

INSERT INTO tb_video_info(video_id, author, tag, duration, release_time) VALUES
   (2001, 901, '影视', 30, '2021-01-01 7:00:00')
  ,(2002, 901, '美食', 60, '2021-01-01 7:00:00')
  ,(2003, 902, '旅游', 90, '2020-01-01 7:00:00');

结构图

用到的知识点

• sum 进行求和

• ROUND() 函数

ROUND() 函数将数字四舍五入到指定的小数位数。如果您只需要按位数截取小数位，请使用 TRUNCATE() 函数。 ROUND() 语法

先看一下ROUND() 的基本语法结构：

ROUND(必需的,被处理的数字, 必需的,需要保留的小数位数)

ROUND() 函数将数字四舍五入到指定的小数位数。

如果 d 大于等于 x 的小数位数，则返回原数字。
如果 d 小于 x 的小数位数，则将 x 的小数位四舍五入到 d 位后返回。
如果 d 为负数，ROUND() 函数将会从小数点开始向整数部分的 d 位数字替换为 0。
如果任意一个参数为 NULL，ROUND() 函数将会返回 NULL。

示例：

SELECT ROUND(4234.645, 1),
       ROUND(4234.645, 2),
       ROUND(4234.645, 4),
       ROUND(4234.645, 0),
       ROUND(4234.645, -1),
       ROUND(4234.645, -2),
       ROUND(4234.645, NULL)

补充 ：如果需要返回数字的整数部分，请使用 FLOOR(), CEIL(), CEILING() 函数

• avg 求平均数
• dateDiff 时间比较 返回两个日期之间的时间间隔 上一篇文章说过这个
• max 求最大值
• date 函数提取日期或日期/时间表达式的日期部分
• using

连接查询时如果是同名字段作为连接条件，using可以代替on出现（比on更好）
using 是针对同名字段（using(id)===on A.id=B.id）
using 关键字使用后会自动合并对应字段为一个
using 可以同时使用多个字段作为条件

实战练习

问题

统计在有用户互动的最近一个月（按包含当天在内的近30天算，比如10月31日的近30天为10.2~10.31之间的数据）中，每类视频的转发量和转发率（保留3位小数）

• 转发率 = 转发量/播放量 ，按转发率降序排序，保留三位小数
• 最近一个月的数据 时间截取

思路一

首先进行表连接 因为需要两个表中的信息

我们然后进行时间截取 三十天内的数据 刚才上面说到dateDiff 就是我们时间截取的切入点 SQL如下：

截取三天内的时间 然后再进行下一步处理 是每个类型所以我们需要分组 通过Tag 然后通过sum 进行求和

最后一步就是 转发率 通过 round avg 去满足条件

结尾

这篇主要是练习函数的使用和相互组合 之前很少用到的函数再次理解一下