牛客网SQL大厂真题二刷小白白话总结（一）某音短视频

年初断断续续学了一段时间sql，近一个月才开始慢慢刷题

之前都是对着答案和评论刷了第一次，第二次刷想自己总结一下～

一、某音短视频

目录

一、某音短视频

SQL1 各个视频的平均完播率（简单）

SQL2 平均播放进度大于60%的视频类别（简单）

SQL3 每类视频近一个月的转发量/率（中等）

SQL4 每个创作者每月的涨粉率及截止当前的总粉丝量（中等）

SQL5 国庆期间每类视频点赞量和转发量（较难）

---

SQL1 各个视频的平均完播率（简单）

完播率、点赞率、评论率和转发率四个指标可以用来判断一个视频的好坏，

视频完播率是指【完成播放次数】占【总播放次数】的比例

这题比较简单，直接上代码：

=select video_id,
round(sum(if(timestampdiff(second,start_time,end_time)>=duration,1,0))/count(uid),3)
as avg_comp_play_rate
from tb_user_video_log join tb_video_info using(video_id)
where year(start_time)=2021
group by video_id
order by avg_comp_play_rate desc

这里主要采用了sum(if()) 的求和+if判断 计算出满足一定条件，即视频用户播放时间长度大于视频长度的个数；分母用uid/start_time等的个数来表示都可以，因为也没有去重。

SQL2 平均播放进度大于60%的视频类别（简单）

这一题在上一题的基础上对平均播放进度做出了要求，并且数据呈现形式除了对小数点有要求之外还添加了单位（%），落实到代码如下：

select tag,
concat(round(play_progress,2),'%') as avg_play_progress
 from
 (select tag,avg(play_progress)  as play_progress
  from
 (select tag,
  if(timestampdiff(second,x.start_time,x.end_time)<duration,
   timestampdiff(second,x.start_time,x.end_time)/duration*100,100) as play_progress
 from
 tb_user_video_log x join tb_video_info y using(video_id)) t1
  group by tag
  having play_progress>60
  )t2
 order by avg_play_progress desc

虽然题目不难，但我也经历了几次报错才调整通过，主要是对“大于60 ”这个限定摆放的位置有争议。一开始我将group by 放在最外层，最后用having对数据大小限定，但是因为外层已经加了%单位，不能顺利完成比较大小，因此一定要在内层就计算平均数后与60进行比较，round()和concat()可以放进最外层。

SQL3 每类视频近一个月的转发量/率（中等）

这道题的重点在如何限定“近一个月”，其实题目暗含了最大的end_day应该就是今日，从今日开始往前推30天就是我们要统计的样本所在时间范围。代码如下：

select tag,retweet_cut,
round(retweet_rate,3) as retweet_rate
from 
(select tag,sum(if_retweet) as retweet_cut,
sum(if_retweet)/count(uid) as retweet_rate
              from tb_user_video_log join tb_video_info using(video_id)
where timestampdiff(day,date(start_time),(select max(date(end_time)) from tb_user_video_log)) <30
group by tag) t1
order by retweet_rate desc

易错点在于：where后面不能直接加聚合函数，因此用（select max())来表示max()；对于近一个月的限制条件则【使用播放时间和最新时间之差<30】代替；

SQL4 每个创作者每月的涨粉率及截止当前的总粉丝量（中等）

题目的关键在于计算每个月涨粉率=(加粉量 - 掉粉量) / 播放量以及累积到当月所有的粉丝数量，后者是前者中分子的累积表现形式（每个月新增的粉丝数加起来就是最后一个月所有的粉丝数）。

select author,date_format(start_time,'%Y-%m') as month,
 round(sum(case when if_follow=1 then 1
   when if_follow=2 then -1
   else 0 end)/count(author),3) as fans_growth_rate,
 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'))
as total_fans
from tb_user_video_log join tb_video_info using(video_id)
where year(start_time)=2021
group by author,month
order by author,total_fans

 其实对于这道题我试了几次都失败了，在于我套了很多层select，结果有的select里面group by操作后，select的部分变量会报错，因此最后只采用了一次select,出错举例如下：

​
select author,month,
 round(fans_new/count(author),3) as fans_growth_rate,
 sum(fans_new) over (partition by author order by date_format(start_time,'%Y-%m'))
as total_fans
from 
(select author,start_time,date_format(start_time,'%Y-%m') as month,
(case when if_follow=1 then 1
   when if_follow=2 then -1
   else 0 end) as fans_new
from
tb_user_video_log join tb_video_info using(video_id)
where year(start_time)=2021
group by author,month) t1
order by author,total_fans

​

这里的start_time就不能参与group by 。

接下来再来讲一讲每月新增和累积求和得到粉丝数：

首先是每月新增。每天用户有关注、取关、不变三种情况，因此对于这三种情况赋值（1，-1，0），那么每个月【求和】就是每个月的净粉丝数（每月新增），具体采用

(case when if_follow=1 then 1
   when if_follow=2 then -1
   else 0 end)

这个语句可以使用聚合函数sum()直接求和，就得到了每日新增粉丝数，再除以播放量（这里是数纪录里author的个数，没有去重）就得到每月涨粉率。

然后是累积粉丝数。使用窗口函数，对每个月的新增逐月求和即可。

sum(fans_new) over (partition by author order by date_format(start_time,'%Y-%m'))
as total_fans

这是因为author是分大类，每个大类里面按照时间的递增顺序累积求和，就可以知道每个作者每个月的当月粉丝数～

SQL5 国庆期间每类视频点赞量和转发量（较难）

目标是：统计2021年国庆头3天每类视频每天的近一周总点赞量和一周内最大单天转发量 。

这道题主要难在时间很复杂，又是三天，又是近一周，又是2021年，所以如何理清每一条时间限定很重要。

select tag,dt,sum_like_cnt_7d,max_retweet_cnt_7d
from(
SELECT  tag,DATE_FORMAT(start_time,'%Y-%m-%d') dt,
        SUM(SUM(if_like))OVER(PARTITION BY tag ORDER BY DATE_FORMAT(start_time,'%Y-%m-%d') rows 6 preceding) AS sum_like_cnt_7d   ,
        MAX(SUM(if_retweet))OVER(PARTITION BY tag ORDER BY DATE_FORMAT(start_time,'%Y-%m-%d') rows 6 preceding) AS    max_retweet_cnt_7d
FROM tb_user_video_log JOIN tb_video_info USING(video_id)
WHERE DATEDIFF('2021-10-03',DATE_FORMAT(start_time,'%Y-%m-%d'))<9
GROUP BY  tag,dt)t1
where dt BETWEEN '2021-10-01' AND '2021-10-03' ORDER BY tag DESC,dt

首先是要统计头三天，每一天，近一周总点赞量。很明显，近一周的总点赞量需要使用时间长度为7天的窗口函数计算，而累积相加的是每一天内的总点赞量，所以还是两层sum(sum())+over（），里面一层加总是一天内的加总，外面一层加总是7天内的累积相加，最后就可以得到站在每一天，往前推7天时间短内的点赞数量之和。

第二个统计数据是一周内最大单天转发量。【一周内的最大】，很明显，放在一周这个时间短内进行大小比较得到最大值也需要窗口函数：max()+over()，那么max()里面是什么呢？换句话说，比较的对象是什么呢？就是每一天的单天转发量，因此很简单的sum(if_retweet)加总就可以。

值得注意的是，以上【加总】都是group by tag,dt，即按照大类和天数加总就是每一类视频每一天的新增量。

再来说一下往前推7天在窗口函数中如何处理：

OVER(PARTITION BY tag ORDER BY DATE_FORMAT(start_time,'%Y-%m-%d') rows 6 preceding)

即可，因为是从当前一行开始，把当前一行也算在内，因此只用往前再推6行就可以，一共7行（一周）。

WHERE DATEDIFF('2021-10-03',DATE_FORMAT(start_time,'%Y-%m-%d'))<9

因为只涉及到从10.3号往前推到9.27号的数据，因此使用datediff()函数，限定到最早的数据日期。

以上就是某音短视频相关的数据分析内容，主要难点在于后面几题计算每日新增和利用窗口函数计算累积和或者时间段内的最值，需要我们好好审题，把思维理清楚。