7、刷牛客网SQL题(一)

自学大数据的菜鸡

已于 2022-03-29 11:40:26 修改

阅读量1.2k

点赞数

分类专栏： SQL 文章标签： sql 数据库 mysql

于 2022-03-22 21:56:46 首次发布

本文链接：https://blog.csdn.net/yunweijie_cn/article/details/123672471

版权

SQL 专栏收录该内容

11 篇文章 1 订阅

订阅专栏

题目来源

牛客网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

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

输出示例：
示例数据的输出结果如下：
在这里插入图片描述
解释：
示例数据中表tb_user_video_log里只有视频2001和2002的播放记录，都来自创作者901，播放时间在2021年9月和10月；其中9月里加粉量为1，掉粉量为0，播放量为2，因此涨粉率为0.500（保留3位小数）；其中10月里加粉量为2，掉份量为1，播放量为4，因此涨粉率为0.250，截止当前总粉丝数为2。

2、思路分析

要注意的地方：
1）年份限定为2021年
2）结果保留三位小数
3）结果按创作者ID、总粉丝量升序排序

思路分析：
先观察最后要输出的结果，author可以通过关联得到，month可以通过start_time得到，fans_growth_rate涨粉率要获得对应的加粉量、掉粉量以及总的播放量，total_fans是截至当月的总粉丝量。
首先，通过两张表的inner join，获得作者ID、月份信息、是否关注这三个字段的表，因为要求的是每个创作者、每个月的XXXX，所以要按创作者ID和月份进行分组，count(if())计算加粉量和减粉量，count(*)计算总播放量，然后通过公式可以算出来每个作者每个月的涨粉率。最后要算截至当月的总粉丝量，注意“截至当月”，所以要用到窗口函数，按作者ID分组、月份升序排列，sum()求和每个月的净粉丝量（加粉-减粉），算出截止当月的总粉丝量。

3、语句实现

select
    author,
    the_month month,
    cast(left_count*1.0/see_count as decimal(16,3)) fans_growth_rate,
    sum(left_count) over(partition by author order by the_month) total_fans
from
(
    select
        author,
        the_month,
        count(*) see_count,
        count(if(if_follow=1,1,null))-count(if(if_follow=2,1,null)) left_count
    from
    (
        select
            author,
            if_follow,
            the_month
        from
        (
            select
                video_id,
                if_follow,
                DATE_FORMAT(start_time,'%Y-%m') the_month
            from tb_user_video_log
            where year(start_time)=2021
        )t1
        inner join
        (
            select
                video_id,
                author
            from tb_video_info
        )t2
        on t1.video_id=t2.video_id
    )t3
    group by author,the_month
)t4
order by author,total_fans

四、SQL4（中等）

1.题目内容

描述
用户-视频互动表tb_user_video_log
在这里插入图片描述
短视频信息表tb_video_info

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

注：转发率＝转发量÷播放量。结果按转发率降序排序。

输出示例：
示例数据的输出结果如下
在这里插入图片描述
解释：
由表tb_user_video_log的数据可得，数据转储当天为2021年10月1日。近30天内，影视类视频2001共有3次播放记录，被转发2次，转发率为0.667；美食类视频2002共有2次播放记录，1次被转发，转发率为0.500。

2.思路分析

题目要求的是有用户互动的最近一个月每类视频的转发量和转发率。
首先，要弄明白，最近一个月是从哪一天开始的。实际上，这题是把所有播放记录的最晚的时间作为当天，然后它的最近30天是最近的一个月。
弄清楚了时间范围，所以可以先从所有的记录中找到end_time中最大的时间，然后过滤出来播放记录里播放时间在它的30天内的数据。接着，因为最终结果有tag字段，所以要跟tb_video_info内连接得到tag字段，然后按tag字段分组，count(*)可以求出对应的播放次数，count(if())可以求出对应的转发量，那么就可以得到最终结果了。

3.语句实现

select
    tag,
    sum(if(if_retweet=1,1,0)),
    round(sum(if(if_retweet=1,1,0))/count(*),3)
from
(
    select
        video_id,
        if_retweet
    from
    (
        select
            video_id,
            if_retweet,
            end_time
        from tb_user_video_log
    )t1
    inner join
    (
        select
            max(date(end_time)) today
        from tb_user_video_log
    )t2
    on 1=1
    where datediff(today,end_time)<=29
)t3
inner join
(
    select
        video_id,
        tag
    from tb_video_info
)t4
on t3.video_id=t4.video_id
group by tag
order by round(sum(if(if_retweet=1,1,0))/count(*),3) desc

五、SQL5（较难）

1.题目内容

描述
用户-视频互动表tb_user_video_log
在这里插入图片描述
短视频信息表tb_video_info

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

输出示例：
示例数据的输出结果如下
在这里插入图片描述
解释：
由表tb_user_video_log里的数据可得只有旅游类视频的播放，2021年9月25到10月3日每天的点赞量和转发量如下：

因此国庆头3天（10.01_{10.03）里10.01的近7天（9.25}10.01）总点赞量为5次，单天最大转发量为2次（9月25那天最大）；同理可得10.02和10.03的两个指标。

2.思路分析

看到这个题想了很久，刚开始觉得有点复杂，写出来之后就不觉得复杂了。。。

让求得是国庆头3天里每天中每类视频近一周的点赞量和一周内最大的单天转发量，因为让求的是3天的数据，所以我们可以先去求一天的，会求一天的，那么也就能求出来三天的数据了。

2.1 思路一

先求2020-10-01天的数据，最终的结果中要有tag、dt，所以要将两个表关联，拿到tag，在关联前第一张表前可以做一些过滤操作（例如国庆三天每天的一周的时间范围为2020-09-25~2020-10-03，可以先过滤出来属于这个时间段的数据），然后按tag和dt分组，这样可以求出来每天的数据，接下来可以只算2020-10-01这一天的，直接按tag分组，sum(if())计算它的一周之内的数据。同理，可以算出来2020-10-02、2020-10-03的数据，将这些数据union起来即可。

2.2 思路二

思路一中分别求出三天的数据，然后union起来，显然效率不高。
思路二尝试一次求出来三天的数据，我们在求出来2020-09-25~2020-10-03这些天当天的数据后，我们需要的是2020-10-01拿到它一周内每天的所有的数据，所以我们可以想到自连接，用tag进行自连接，这样2020-10-01就可以连接到2020-09-25到2020-10-03的所有的数据，然后把左表的dt当作要求的国庆的三天的日期，将2020-10-01、2020-10-02、2020-10-03这三天的数据过滤出来（注意：这个时候2020-10-01仍然包含了2020-09-25到2020-10-03的数据），最后就是按tag、dt分组，sum(if())对应时间范围内的数据，然后排序即可。

2.3 思路三

思路二可以一次求出来所有的数据，但是进行了自连接，类似于笛卡尔积。
如果在Hive中还有一种写法，用炸裂函数，炸裂出三份数据（炸裂的数据元素就是2020-10-01、2020-10-02、2020-10-03这三天），对于每一份数据用炸裂出来的时间和表中的dt进行范围的比较(date_add(recent_days,-6)<=dt and dt<=recent_days)，选取对应范围的数据，最后进行分组求和。

这方法应该是可以，不过我没有尝试…

3.语句实现

3.1 思路一语句

select
    tag,
    dt,
    sum_like_cnt_7d,
    max_retweet_cnt_7d
from
(
    select
        tag,
        '2021-10-01' dt,
        sum(if(date_add('2021-10-01',INTERVAL '-6' DAY)<=dt and dt<='2021-10-01',like_count,0)) sum_like_cnt_7d	,
        max(if(date_add('2021-10-01',INTERVAL '-6' DAY)<=dt and dt<='2021-10-01',retweet_count,0)) max_retweet_cnt_7d
    from
    (
        select
            tag,
            dt,
            sum(if(if_like=1,1,0)) like_count,
            sum(if(if_retweet=1,1,0)) retweet_count
        from
        (
            select
                tag,
                see_time dt,
                if_like,
                if_retweet
            from 
            (
                select
                    video_id,
                    date_format(start_time,'%Y-%m-%d') see_time,
                    if_like,
                    if_retweet
                from tb_user_video_log
                where date_format(start_time,'%Y-%m-%d')>='2021-09-25' and date_format(start_time,'%Y-%m-%d')<='2021-10-03'
            )t1
            inner join
            (
                select
                    video_id,
                    tag
                from tb_video_info
            )t2
            on t1.video_id=t2.video_id
        )t3
        group by tag,dt
    )t4
    group by tag
    union all
    select
        tag,
        '2021-10-02',
        sum(if(date_add('2021-10-02',INTERVAL '-6' DAY)<=dt and dt<='2021-10-02',like_count,0)),
        max(if(date_add('2021-10-02',INTERVAL '-6' DAY)<=dt and dt<='2021-10-02',retweet_count,0))
    from
    (
        select
            tag,
            dt,
            sum(if(if_like=1,1,0)) like_count,
            sum(if(if_retweet=1,1,0)) retweet_count
        from
        (
            select
                tag,
                see_time dt,
                if_like,
                if_retweet
            from 
            (
                select
                    video_id,
                    date_format(start_time,'%Y-%m-%d') see_time,
                    if_like,
                    if_retweet
                from tb_user_video_log
                where date_format(start_time,'%Y-%m-%d')>='2021-09-25' and date_format(start_time,'%Y-%m-%d')<='2021-10-03'
            )t1
            inner join
            (
                select
                    video_id,
                    tag
                from tb_video_info
            )t2
            on t1.video_id=t2.video_id
        )t3
        group by tag,dt
    )t4
    group by tag
    union all
    select
        tag,
        '2021-10-03',
        sum(if(date_add('2021-10-03',INTERVAL '-6' DAY)<=dt and dt<='2021-10-03',like_count,0)),
        max(if(date_add('2021-10-03',INTERVAL '-6' DAY)<=dt and dt<='2021-10-03',retweet_count,0))
    from
    (
        select
            tag,
            dt,
            sum(if(if_like=1,1,0)) like_count,
            sum(if(if_retweet=1,1,0)) retweet_count
        from
        (
            select
                tag,
                see_time dt,
                if_like,
                if_retweet
            from 
            (
                select
                    video_id,
                    date_format(start_time,'%Y-%m-%d') see_time,
                    if_like,
                    if_retweet
                from tb_user_video_log
                where date_format(start_time,'%Y-%m-%d')>='2021-09-25' and date_format(start_time,'%Y-%m-%d')<='2021-10-03'
            )t1
            inner join
            (
                select
                    video_id,
                    tag
                from tb_video_info
            )t2
            on t1.video_id=t2.video_id
        )t3
        group by tag,dt
    )t4
    group by tag
)t5
order by tag desc,dt asc

3.2 思路二语句

with tmp as
    (
        select
            tag,
            dt,
            sum(if(if_like=1,1,0)) like_count,
            sum(if(if_retweet=1,1,0)) retweet_count
        from
        (
            select
                tag,
                see_time dt,
                if_like,
                if_retweet
            from 
            (
                select
                    video_id,
                    date_format(start_time,'%Y-%m-%d') see_time,
                    if_like,
                    if_retweet
                from tb_user_video_log
                where date_format(start_time,'%Y-%m-%d')>='2021-09-25' and date_format(start_time,'%Y-%m-%d')<='2021-10-03'
            )t1
            inner join
            (
                select
                    video_id,
                    tag
                from tb_video_info
            )t2
            on t1.video_id=t2.video_id
        )t3
        group by tag,dt
    )
    
select
    tag,
    dt1,
    sum(like_count),
    max(retweet_count)
from
(
    select
        t1.tag,
        t1.dt dt1,
        t2.dt dt2,
        t2.like_count,
        t2.retweet_count
    from
    tmp t1
    inner join
    tmp t2
    on t1.tag=t2.tag
    where t1.dt in ("2021-10-01","2021-10-02","2021-10-03")
    and date_add(t1.dt,INTERVAL '-6' DAY)<=t2.dt and t2.dt<=t1.dt
)t1
group by tag,dt1

自学大数据的菜鸡

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
7、刷牛客网SQL题(一)

SQL题题目来源一、SQL11、题目内容2、思路分析3、代码实现题目来源牛客网SQL大厂面试题——某音短视频一、SQL11、题目内容描述用户-视频互动表tb_user_video_log短视频信息表tb_video_info问题：计算2021年里有播放记录的每个视频的完播率(结果保留三位小数)，并按完播率降序排序注：视频完播率是指完成播放次数占总播放次数的比例。简单起见，结束观看时间与开始播放时间的差>=视频时长时，视为完成播放。2、思路分析首先看清题目要求，有以下几个点
复制链接

扫一扫