牛客在线编程（SQL大厂面试真题）

1.各个视频的平均完播率_牛客题霸_牛客网

ROP 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:30', 0, 1, 1, null),(102, 2001, '2021-10-01 10:00:00', '2021-10-01 10:00:24', 0, 0, 1, null),(103, 2001, '2021-10-01 11:00:00', '2021-10-01 11:00:34', 0, 1, 0, 1732526),(101, 2002, '2021-09-01 10:00:00', '2021-09-01 10:00:42', 1, 0, 1, null),(102, 2002, '2021-10-01 11:00:00', '2021-10-01 11:00:30', 1, 0, 1, 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, '2021-01-01 7:00:00');

 考察函数  

UNIX_TIMESTAMP

• 学习链接:MySQL中的 UNIX_TIMESTAMP 函数使用总结（附详例）_sql unix_timestamp-CSDN博客

• 将数据转为时间戳

CAST

• 学习链接：[Mysql] CAST函数_mysql cast函数-CSDN博客
• 语法：cast(value as datetype)as 关键字用于分割俩个参数，在as之前的是要处理的数据，在as之后是要转化吧的数据

DATE    将value转换成'YYYY-MM-DD'格式
DATETIME    将value转换成'YYYY-MM-DD HH：MM：SS'格式
TIME    将value转换成'HH：MM：SS'格式
CHAR    将value转换成CHAR(固定长度的字符串)格式
SIGNED    将value转换成INT(有符号的整数)格式
UNSIGNED    将value转换成INT(无符号的整数)格式
DECIMAL    将value转换成FLOAT(浮点数)格式 
BINARY    将value转换成二进制格式
 

保留小数位

•  

  CAST('9.0' AS DECIMAL) ->

• DECIMAL(数值精度，小数点保留长度)

• -- DECIMAL(10,2)可以存储最多具有8位整数和2位小数的数字

• -- 精度与小数位数分别为10与2

• -- 精度是总的数字位数，包括小数点左边和右边位数的总和

• -- 小数位数是小数点右边的位数

题解

SELECTt3.video_id,CAST(COUNT(if(cha>=duration,1,null))*1.0 / COUNT(*) AS DECIMAL(16,3)) AS avg_comp_play_rate
FROM(SELECTt1.video_id,cha,durationFROM(SELECTvideo_id,UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time) AS chaFROMtb_user_video_logWHEREYEAR(start_time) = 2021) AS t1JOIN (SELECTvideo_id,durationFROMtb_video_info) AS t2 ON t1.video_id = t2.video_id) t3
GROUP BY t3.video_id
ORDER BY avg_comp_play_rate DESC;

2.平均播放进度大于60%的视频类别_牛客题霸_牛客网

 题目

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

select t3.tag as tag,CONCAT(avg_play_progress,'%') as avg_play_progress
from 
(
SELECT
t1.tag,
CAST(CAST(sum( if(cha>duration,1,CAST(cha/duration as DECIMAL(6,4))) )/ count(*) AS DECIMAL ( 4, 4 )) *100   as decimal(4,2))  AS avg_play_progress
FROM(SELECTlog.video_id,tag,UNIX_TIMESTAMP( end_time )- UNIX_TIMESTAMP( start_time ) AS cha FROMtb_user_video_log AS logJOIN tb_video_info AS info ON log.video_id = info.video_id ) t1JOIN ( SELECT tag, duration FROM tb_video_info ) AS t2 ON t1.tag = t2.tag 
GROUP BYt1.tagHAVING avg_play_progress>60
order by avg_play_progress DESC) as t3

3.每类视频近一个月的转发量率_牛客题霸_牛客网

 

 

考察函数

ROUND

• 学习链接：MySQL ROUND() 函数 (w3schools.cn)
• ROUND(number, decimals)

• number	必需。要四舍五入的数字
  decimals	可选。number 要四舍五入的小数位数。 如果省略，则返回整数（无小数）

 DATEDIFF

• 学习链接：SQL Server DATEDIFF() 函数 (w3school.com.cn)
• DATEDIFF() 函数返回两个日期之间的时间。

• SELECT DATEDIFF(day,'2008-12-29','2008-12-30') AS DiffDate

• 结果：1

题解 

SELECT tag,SUM(if_retweet) retweet_cut,ROUND(SUM(if_retweet)/COUNT(start_time),3) retweet_rate 
FROM tb_user_video_log a LEFT JOIN tb_video_info b ON a.video_id=b.video_idWHERE DATEDIFF((SELECT MAX(start_time) FROM tb_user_video_log),start_time)<=29 GROUP BY tagORDER BY retweet_rate DESC;

4.每个创作者每月的涨粉率及截止当前的总粉丝量_牛客题霸_牛客网

题目

 

 

自测输入

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-01 10:00:00', '2021-09-01 10:00:20', 0, 1, 1, null),(105, 2002, '2021-09-10 11:00:00', '2021-09-10 11:00:30', 1, 0, 1, null),(101, 2001, '2021-10-01 10:00:00', '2021-10-01 10:00:20', 1, 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', 1, 1, 0, 1732526),(106, 2002, '2021-10-01 10:59:05', '2021-10-01 11:00:05', 2, 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'),(2004, 902, '美女', 90, '2020-01-01 8:00:00');

 自测输入正确结果

 

题解

select author, `month`, round(fans_add_cnt / play_cnt, 3) as fans_growth_rate,sum(fans_add_cnt) over(partition by author order by `month`) as total_fans
from (select author,DATE_FORMAT(start_time, "%Y-%m") as `month`,sum(if(if_follow=2, -1, if_follow)) as fans_add_cnt,count(1) as play_cntfrom tb_user_video_logjoin tb_video_info USING(video_id)where YEAR(start_time) = 2021group by author, `month`
) as t_author_monthly_fans_play_cnt
order by author, total_fans;

考察函数 

•  学习链接：MySQL窗口函数 - 知乎

• 函数解读：
  函数分为两个部分
  第一部分是函数名称，开窗函数的数量较少，只有11个窗口函数+聚合函数（所有聚合函数都可以用作开窗函数），根据函数性质，有的要写参数，有的不需要写参数；
  
  第二部分是over语句，over()是必须要写的，里面有三个参数，都是非必须参数，根据需求选写：
  1.第一个参数是 partition by +分组字段，将数据根据此字段分成多份，如果不加partition by参数，那会把整个数据当做一个窗口。
  2.第二个参数是 order by +排序字段，每个窗口的数据要不要进行排序。
  3.第三个参数 rows/range between 起始位置 and 结束位置，这个参数仅针对滑动窗口函数有用，是在当前窗口下分出更小的子窗口。
  其中起始位置和结束位置可写：

• current row 边界是当前行
• unbounded preceding 边界是分区中的第一行
• unbounded following 边界是分区中的最后一行
• expr preceding 边界是当前行减去expr的值
• expr following 边界是当前行加上expr的值。rows是基于行数，range是基于值的大小，到讲解到滑动窗口函数时再详细介绍

---