7、刷牛客网SQL题(一)

---

题目来源

牛客网SQL大厂面试题——某音短视频

---

一、SQL1（简单）

1、题目内容

描述
用户-视频互动表tb_user_video_log

短视频信息表tb_video_info

问题：计算2021年里有播放记录的每个视频的完播率(结果保留三位小数)，并按完播率降序排序

注：视频完播率是指完成播放次数占总播放次数的比例。简单起见，结束观看时间与开始播放时间的差>=视频时长时，视为完成播放。

2、思路分析

首先看清题目要求，有以下几个点需要注意：
1）2021年有播放记录，所以要求视频的startTime里的年份要在2021年
2）结果保留三位小数
3）按完播率降序排序

看完注意事项，分析思路：
最终要求的是完播率，可以转化为求完成播放的次数和总共播放的次数。完成播放的次数要拿到对应视频的总时长和每次播放时长来进行判断，所以要先用用户-视频互动表与短视频信息表进行内连接（内连接前可以先过滤出来在2021年播放的视频，减少join次数；还可以先把每个视频的播放时长给算出来）。这样得到了video_id、duration、播放时长这三个字段的一张表，题目要求的是每个视频，所以按video_id分组，count(*)的得到的是总的播放次数，count(if())可以算出来完成播放的次数，相除并保留三位小数再降序排列即可。

3、语句实现

select
    t3.video_id,
    cast(count(if(cha>=duration,1,null))*1.0/count(*) as DECIMAL(16,3)) avg_comp_play_rate
from
(
    select
        t1.video_id,
        cha,
        duration
    from
    (
        select
            video_id,
            UNIX_TIMESTAMP(end_time)-UNIX_TIMESTAMP(start_time) cha
        from tb_user_video_log
        where year(start_time)=2021
    )t1
    inner join
    (
        select
            video_id,
            duration
        from tb_video_info
    )t2
    on t1.video_id=t2.video_id
)t3
group by t3.video_id
order by avg_comp_play_rate desc

二、SQL2（简单）

1、题目内容

描述
用户-视频互动表tb_user_video_log

短视频信息表tb_video_info

问题：计算各类视频的平均播放进度，将进度大于60%的类别输出。

注：
播放进度=播放时长÷视频时长*100%，当播放时长大于视频时长时，播放进度均记为100%。
结果保留两位小数，并按播放进度倒序排序。

输出示例：
示例数据的输出结果如下：

解释：
影视类视频2001被用户101、102、103看过，播放进度分别为：30秒（100%）、21秒（70%）、30秒（100%），平均播放进度为90.00%（保留两位小数）；
美食类视频2002被用户102、103看过，播放进度分别为：30秒（50%）、60秒（100%），平均播放进度为75.00%（保留两位小数）；

2、思路分析

同样有几个点要注意：
1）播放时长大于视频时长时，播放进度记为100%
2）结果保留两位小数
3）按播放进度倒序排序

思路如下：
最后输出到结果是一个类别名称和一个播放进度，类别名称要通过两个表连接得到；播放进度要拿到每个播放的时长和每种类型的视频的时长，然后相除再按类型分组，再过滤和排序。

3、语句实现

select
    tag,
    concat(avg_rate,'%') avg_play_progress
from
(
    select
        video_id,
        tag,
        cast(avg(single_rate) as decimal(16,2)) avg_rate
    from
    (
        select
            t1.video_id,
            t2.tag,
            cast(if(cha*100.0/duration>100,100,cha*100.0/duration) as DECIMAL(16,2)) single_rate
        from
        (
            select
                video_id,
                TIMESTAMPDIFF(second,start_time,end_time) cha
            from tb_user_video_log
        )t1
        inner join
        (
            select
                video_id,
                tag,
                duration
            from tb_video_info
        )t2
        on t1.video_id=t2.video_id
    )t3
    group by video_id
)t4
where avg_rate>60
order by avg_rate desc

三、SQL3（中等）

1、题目内容

描述
用户-视频互动表tb_user_video_log

短视频信息表tb_video_info