牛客-SQL大厂真题（某音）

牛客网SQL大厂真题部分贴近实际业务场景，做的时候是觉得比较难的，在这里回顾几种典型题型，整理解题思路和注意点。

SQL 156 各个视频的平均完播率（表连接、子查询）

计算2021年里有播放记录的每个视频的完播率(结果保留三位小数)，并按完播率降序排序。（结束观看时间与开始播放时间的差>=视频时长时，视为完成播放。）

思路：

• 判断是否完成播放，子查询添加相应字段（if_end）并连接两表和筛选时间条件。分组聚合计算完播率，按顺序输出。

注意点：

1. 使用子查询避免select子句过于繁琐。
2. 这里if(end_time-start_time>=duration,1,0)相对于case when会更简洁。
3. sum和count的适用情况。

select video_id,round(sum(if_end)/count(if_end),3) avg_comp_play_rate
from 
(select video_id,
    if(end_time-start_time>=duration,1,0) if_end
from tb_user_video_log a
left join tb_video_info b using(video_id)
where year(start_time) = 2021) t1
group by video_id
order by avg_comp_play_rate desc

SQL 157 平均播放进度大于60%的视频类别（表连接、子查询、常见函数）

计算各类视频的平均播放进度，将进度大于60%的类别输出。（当播放时长大于视频时长时，播放进度均记为100%。）

思路：

• 和上一题类似，子查询添加字段判断是否完成播放，再计算完播率。

注意点：

• 各种函数的用法。时间函数timestampdiff表示两个时间的间隔，可以灵活设置单位参数，这里不要直接把两个时间相减。substring_index提取字符串文本。

select tag,concat(round(100*avg(view_time/duration),2),'%') avg_play_progress 
from 
(select tag,duration,if(timestampdiff(second,start_time,end_time)>=duration,duration,timestampdiff(second,start_time,end_time)) view_time
from tb_user_video_log
left join tb_video_info using(video_id)
) t1
group by tag
having substring_index(avg_play_progress,'%',1) > 60
order by avg_play_progress desc

SQL 158 每类视频近一个月的转发量/率（表连接、where子查询）

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

思路：

• 理解近一个月怎么表示，然后删选近一个月的数据，分组聚合计算转发率。

注意点：

• where子句不能用聚合函数，所以采用子查询表示最近三十天。子查询里date_sub找到与当前日期间隔三十天的日期。

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 using(video_id)
where date(start_time) > 
    (select date_sub(max(start_time),interval 30 day)
    from tb_user_video_log)
group by tag
order by retweet_rate desc

SQL 159 每个创作者每月的涨粉率及截止当前的总粉丝量（窗口函数，表连接、子查询、case when）

计算2021年里每个创作者每月的涨粉率及截止当月的总粉丝量。

思路：

• 涨粉率通过计算字段得到，和前面类似。当月的总粉丝量是综合前面每个月的涨粉情况合计出来的，所以要用聚合窗口函数sum计算累计到每个月的粉丝数。

注意点：

1. case when表示不同的关注情况。
2. 窗口函数的目标和用法。
3. group by不能写“month”，因为group by先于select执行，这个时候还没有month字段。

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

SQL 160 国庆期间每类视频点赞量和转发量（窗口函数，表连接、外查询）

统计2021年国庆头3天每类视频每天的近一周总点赞量和一周内最大单天转发量，结果按视频类别降序、日期升序排序。假设数据库中数据足够多，至少每个类别下国庆头3天及之前一周的每天都有播放记录。

思路：

• 1、分组后先求和每天的点赞和转发 2、用窗口函数求每天一周内的点赞总和以及最大转发数。3、选择国庆三天的数据。

注意点：

1. 时间范围为9.25-10.3，where过滤到其他无用数据。
2. 7天总和要用到两个sum，内层的是每天的多条数据求和。

with t1 as (
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) sum_like_cnt_7d	,
        max(sum(if_retweet))over(partition by tag order by date_format(start_time,'%Y-%m-%d') rows 6 preceding)	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  dt,tag
)
select * from t1 where dt between '2021-10-01' and '2021-10-03' order by tag desc,dt asc;

SQL 161 近一个月发布的视频中热度最高的top3视频（表连接、子查询、datediff）

找出近一个月发布的视频中热度最高的top3视频。

思路：

• 子查询定义各个指标，并且筛选时间为最近30天，然后外层将各个字段代入公式。

注意点：

1. 用datediff求最近无播放天数，即该视频最后播放日期和当前日期的时间差。
2. date_sub找到当前日期的前30天，设置日期范围。

select 
    video_id,
    round((100*wb+5*dz+3*pl+2*zf)/(d_cnt+1),0) hot_index
from 
    (select 
        video_id,
        avg(timestampdiff(second,start_time,end_time)>= duration) wb,
        sum(if_like) dz,
        count(comment_id) pl,
        sum(if_retweet) zf,
        datediff((select max(date(end_time)) from tb_user_video_log),max(date(end_time))) d_cnt
     from tb_user_video_log join tb_video_info using(video_id)
     where datediff(date((select max(end_time) from tb_user_video_log)), date(release_time)) <= 29 #理解题意是发布后的一个月内，而不是从最近的日期往前推一个月的时间范围。
     group by video_id) t1
order by hot_index desc
limit 3