mysql|mysql牛客网大厂面试刷题深度解析篇，每日一更（持续更新）数据库|mysql

SQL158：
SQL158 每类视频近一个月的转发量/率

文章图片

select tag,concat(round(sum(if_retweet)/count(1),2)*100,'%') from tb_user_video_log ul left join tb_video_info tvi on ul.video_id = tvi.video_id group by tag

文章图片

WHERE DATEDIFF(DATE((select max(start_time) FROM tb_user_video_log)), DATE(ul.start_time)) <= 29
近一个月内的时间此时将他建成一张表然后进行

#interval作为一个关键字时，表示为时间间隔， #常用在date_add()、date_sub()函数中，常用于时间的加减法。 interval ：间隔 #start_time,interval 30 DAY :开始之前的 # SELECT DATE_SUB(MAX(DATE(start_time)) ：表中最新的一天 #subData ：日期减法 #subdate(max(start_time,interval 30 DAY))过去三十天 select DATE(start_time) >subdate(max(start_time,interval 30 DAY)) FROM tb_user_video_logselect tag,concat(round(sum(if_retweet)/count(1),2)*100,'%') retweet_rate from tb_user_video_log ul left join tb_video_info tvi on ul.video_id = tvi.video_id WHERE DATE(start_time) > ( SELECT DATE_SUB(MAX(DATE(start_time)), INTERVAL 30 DAY) FROM tb_user_video_log ) group by tag order by retweet_rate desc ;

SQL159 每个创作者每月的涨粉率及截止当前的总粉丝量涨粉率=(加粉量 - 掉粉量) / 播放量。结果按创作者ID、总粉丝量升序排序。
if_follow-是否关注为1表示用户观看视频中关注了视频创作者，为0表示此次互动前后关注状态未发生变化，为2表示本次观看过程中取消了关注。
前置知识： over（）函数的使用

每个月的涨粉率（加粉- 掉粉）/ 总粉丝量
肯定需要按月份进行分组，然后再计算他的涨粉率
当前总粉丝量就是count（*）

// 按照月份进行分组 where year(start_time)=2021 group by author,month// 涨粉率 round( // 保留三位小数 sum( if(log.if_follow=2,-1,if_follow))/count(author),3) fans_growth_rate// 总粉丝量(方案 ) //方案1： sum( sum(// 分区函数的累加规则 case when if_follow=1 then 1 when if_follow=2 then -1 else 0 end)) // over(partition by author order by date_format(start_time,'%Y-%m')) total_fans// 方案2：先对作者进行分区，然后对月份进行排序 SELECT author, month, ROUND(fans_add_count / pv_count,3) AS fans_growth_rate, SUM(fans_add_count) over(partition by author order by month) AS total_fans FROM ( SELECT b.author, DATE_FORMAT(a.start_time,'%Y-%m') AS month, SUM(IF(a.if_follow = 2,-1,a.if_follow)) AS fans_add_count, COUNT(a.video_id) AS pv_count FROM tb_user_video_log AS a JOIN tb_video_info b USING(video_id) WHERE YEAR(a.start_time) = 2021 GROUP BY b.author,month ) AS author_monthly_fans_play_cnt ORDER BY author,total_fans;

文章图片

SQL162 2021年11月每天的人均浏览文章时长

文章图片

统计2021年11月每天的人均浏览文章时长（秒数），结果保留1位小数，并按时长由短到长排序。
分析：人均浏览文章时长（秒数）：总时长/ 人头数
2021年11月每天的人均浏览文章时长（秒数），需要按照天数进行分组
并按时长由短到长排序。普通的排序字段
【mysql|mysql牛客网大厂面试刷题深度解析篇，每日一更（持续更新）】

文章图片

selectround(sum(timestampdiff(second,in_time,out_time)) / count(distinct uid),1) avg_lensec, date(in_time) day from tb_user_log where date_format(in_time,'%Y-%m') = '2021-11' and artical_id != 0 group by day order by avg_lensec;