大厂sql面试题

不会Hive的啊扬

已于 2023-05-05 18:18:05 修改

阅读量194

点赞数

文章标签：面试 sql

于 2023-03-24 20:01:16 首次发布

本文链接：https://blog.csdn.net/m0_70949976/article/details/129750222

版权

一、某音面试题

用户-视频互动表tb_user_video_log

id	uid	video_id	start_time	end_time	if_follow	if_like	if_retweet	comment_id
1	101	2001	2021-10-01 10:00:00	2021-10-01 10:00:30	0	1	1	NULL
2	102	2001	2021-10-01 10:00:00	2021-10-01 10:00:24	0	0	1	NULL
3	103	2001	2021-10-01 11:00:00	2021-10-01 11:00:34	0	1	0	1732526
4	101	2002	2021-09-01 10:00:00	2021-9-01 10:00:42	1	0	1	NULL
5	102	2002	2021-10-01 11:00:00	2021-10-01 10:00:30	1	0	1	NULL

（uid-用户ID, video_id-视频ID, start_time-开始观看时间, end_time-结束观看时间, if_follow-是否关注, if_like-是否点赞, if_retweet-是否转发, comment_id-评论ID）

短视频信息表tb_video_info

id	video_id	author	tag	duration	release_time
1	2001	901	影视	30	2021-01-01 07:00:00
2	2002	901	美食	60	2021-01-01 07:00:00
3	2003	902	旅游	90	2021-01-01 07:00:00

（video_id-视频ID, author-创作者ID, tag-类别标签, duration-视频时长（秒）, release_time-发布时间）

DROP TABLE IF EXISTS tb_user_video_log, tb_video_info;
CREATE TABLE tb_user_video_log (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid INT NOT NULL COMMENT '用户ID',
    video_id INT NOT NULL COMMENT '视频ID',
    start_time datetime COMMENT '开始观看时间',
    end_time datetime COMMENT '结束观看时间',
    if_follow TINYINT COMMENT '是否关注',
    if_like TINYINT COMMENT '是否点赞',
    if_retweet TINYINT COMMENT '是否转发',
    comment_id INT COMMENT '评论ID'
) CHARACTER SET utf8 COLLATE utf8_bin;

CREATE TABLE tb_video_info (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    video_id INT UNIQUE NOT NULL COMMENT '视频ID',
    author INT NOT NULL COMMENT '创作者ID',
    tag VARCHAR(16) NOT NULL COMMENT '类别标签',
    duration INT NOT NULL COMMENT '视频时长(秒数)',
    release_time datetime NOT NULL COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_user_video_log(uid, video_id, start_time, end_time, if_follow, if_like, if_retweet, comment_id) VALUES
  (101, 2001, '2021-10-01 10:00:00', '2021-10-01 10:00:30', 0, 1, 1, null),
  (102, 2001, '2021-10-01 10:00:00', '2021-10-01 10:00:24', 0, 0, 1, null),
  (103, 2001, '2021-10-01 11:00:00', '2021-10-01 11:00:34', 0, 1, 0, 1732526),
  (101, 2002, '2021-09-01 10:00:00', '2021-09-01 10:00:42', 1, 0, 1, null),
  (102, 2002, '2021-10-01 11:00:00', '2021-10-01 11:00:30', 1, 0, 1, null);

INSERT INTO tb_video_info(video_id, author, tag, duration, release_time) VALUES
  (2001, 901, '影视', 30, '2021-01-01 7:00:00'),
  (2002, 901, '美食', 60, '2021-01-01 7:00:00'),
  (2003, 902, '旅游', 90, '2021-01-01 7:00:00');

每道问题表里数据可能略微不同，以上数据做个参考，注重思路就行，我就只展示一次了

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

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

输出示例：

示例数据的结果如下：

video_id	avg_comp_play_rate
2001	0.667
2002	0.000

解释：

视频2001在2021年10月有3次播放记录，观看时长分别为30秒、24秒、34秒，视频时长30秒，因此有两次是被认为完成播放了的，故完播率为0.667；

视频2002在2021年9月和10月共2次播放记录，观看时长分别为42秒、30秒，视频时长60秒，故完播率为0.000。

题解：

select
t1.video_id,
round(
    avg(if(end_time - start_time >= duration, 1, 0)), 3
    ) as avg_comp_play_rate
from tb_user_video_log t1 
left join tb_video_info t2 using(video_id)
where year(start_time) = 2021
group by t1.video_id
order by avg_comp_play_rate desc

思路：

视频ID分组，分完求每个组内完播率，根据题里给的逻辑去计算，avg函数里套个if完事，啥，using啥意思？跟on一个意思，俩表链接字段相同就可以这么写，我是为了简便

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

注：

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

输出示例：

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

tag	avg_play_progress
影视	90.00%
美食	75.00%

解释：

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

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

题解：

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

思路：

这次不那么直接了，不再是视频ID分组了，改成标签分组，不过道理都一样，分完组后求的逻辑也不一样了，需要注意要对是否超过视频时长做个判断，最后要的结果也要过滤一下，我外层用where过滤掉不满足的也行，但是还是内层直接having过滤更方便一点，但这个没办法，必须套一层处理，因为having里没办法对60%比大小，还有注意先乘100再保留两位，但如果要精确到百分号值的整数部分，也就是比如精确到60%而不是60.00%，那就先保留两位再乘100

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

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

输出示例：

示例数据的输出结果如下

tag	retweet_cut	retweet_rate
影视	2	0.667
美食	1	0.500

解释：

由表tb_user_video_log的数据可得，数据转储当天为2021年10月1日。近30天内，影视类视频2001共有3次播放记录，被转发2次，转发率为0.667；美食类视频2002共有2次播放记录，1次被转发，转发率为0.500。

题解：

select
tag,
sum(if_retweet) as retweet_cut,
round(avg(if_retweet), 3) as retweet_rate
from tb_user_video_log
left join tb_video_info using(video_id)
where date(start_time) >=
(
    select
    date_sub(date(max(start_time)), interval 29 day)
    from tb_user_video_log
)
group by tag
order by retweet_rate desc

思路：首先让求的是整体的最近一个月，不是每类视频的最近一个月，不然就得开窗了，我先求出maxdate的最近30天，过滤出大于这个日期的即可，然后正常的一个求sum求avg即可

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

注：

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

输出示例：

示例数据的输出结果如下

author	month	fans_growth_rate	total_fans
901	2021-09	0.500	1
901	2021-10	0.250	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。

题解：

select
author,
month,
fans_growth_rate,
sum(month_total) over(partition by author order by month) as total_fans
from
(
    select
    author,
    date_format(end_time, '%Y-%m') as month,
    round(avg(if(if_follow = 2, -1, if_follow)), 3) as fans_growth_rate,
    sum(if(if_follow = 2, -1, if_follow)) as month_total
    from tb_user_video_log
    left join tb_video_info using(video_id)
    where year(end_time) = 2021
    group by author,month   
)t1
order by author,total_fans

思路：

首先按照作者，月份分组，关于这个涨粉量，聚合的时候，如果是掉粉，我们需要把2设为-1后巧妙的进行sum求和即可，最后还让求一个截止当月的粉丝量，这明显就是外层一个开窗的思路，但是内层我们需要给出当月的涨粉量方便外层累加

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

输出示例：

示例数据的输出结果如下

tag	dt	sum_like_cnt_7d	max_retweet_cnt_7d
旅游	2021-10-01	5	2
旅游	2021-10-02	5	3
旅游	2021-10-03	6	3

解释：

由表tb_user_video_log里的数据可得只有旅游类视频的播放，2021年9月25到10月3日每天的点赞量和转发量如下：

tag	dt	like_cnt	retweet_cnt
旅游	2021-09-25	1	2
旅游	2021-09-26	0	1
旅游	2021-09-27	1	0
旅游	2021-09-28	0	1
旅游	2021-09-29	0	1
旅游	2021-09-30	1	1
旅游	2021-10-01	2	1
旅游	2021-10-02	1	3
旅游	2021-10-03	1	0

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

题解：

select
*
from
(
    select
    tag,
    dt,
    sum(like_cnt) over(partition by tag order by dt rows 6 preceding) as sum_like_cnt_7d,
    max(retweet_cnt) over(partition by tag order by dt rows 6 preceding) as max_retweet_cnt_7d
    from
    (
        select
        tag,
        date(start_time) as dt,
        sum(if_like) as like_cnt,
        sum(if_retweet) as retweet_cnt
        from tb_user_video_log
        left join tb_video_info using(video_id)
        where date(start_time) between '2021-09-25' and '2021-10-03'
        group by tag, dt
    )t
)tt
where dt >= '2021-10-01'
order by tag desc, dt

--可简化成
select
*
from
(
    select
    tag,
    date(start_time) as dt,
    sum(sum(if_like)) over(partition by tag order by date(start_time) rows 6 preceding) as sum_like_cnt_7d,
    max(sum(if_retweet)) over(partition by tag order by date(start_time) rows 6 preceding) as max_retweet_cnt_7d
    from tb_user_video_log
    left join tb_video_info using(video_id)
    where date(start_time) between '2021-09-25' and '2021-10-03'
    group by tag, dt
)t
where dt >= '2021-10-01'
order by tag desc, dt

--炸裂方式示例
select
tag,
dt,
sum(if_like) as sum_like_cnt_7d
from tb_user_video_log
lateral view explode(array('2021-10-01','2021-10-02','2021-10-03')) tmp as dt
left join tb_video_info using(video_id)
where datediff(dt, date(start_time)) <= 6
group by tag, dt

思路：

本来打算使用炸裂，炸三份过滤不同区间分别分组求值的，结果炸裂函数在mysql里用起来一直有问题，只好采取开窗方式取固定行了，仔细想一下，我目标是求10月份分别3天的聚合数据，但我需要的是这三天每天的前六天数据，我不妨先对这十天所有数据分组求一次聚合结果，最后开窗排序取前六行到当前行聚合即可，因为题目中说了每天都有数据，因此不用担心多取，结果求出来了后只过滤出该三天的即可，当然由于我也可以省去一层直接对聚合结果进行开窗处理

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

注：

热度=(a*视频完播率+b*点赞数+c*评论数+d*转发数)*新鲜度；
新鲜度=1/(最近无播放天数+1)；
当前配置的参数a,b,c,d分别为100、5、3、2。
最近播放日期以end_time-结束观看时间为准，假设为T，则最近一个月按[T-29, T]闭区间统计。
结果中热度保留为整数，并按热度降序排序。

输出示例：

示例数据的输出结果如下

video_id	hot_index
2001	122
2002	56
2003	1

解释：

最近播放日期为2021-10-03，记作当天日期；近一个月（2021-09-04及之后）发布的视频有2001、2002、2003、2004，不过2004暂时还没有播放记录；

视频2001完播率1.0（被播放次数4次，完成播放4次），被点赞3次，评论1次，转发2次，最近无播放天数为0，因此热度为：(100*1.0+5*3+3*1+2*2)/(0+1)=122

同理，视频2003完播率0，被点赞数1，评论和转发均为0，最近无播放天数为3，因此热度为：(100*0+5*1+3*0+2*0)/(3+1)=1（1.2保留为整数）。

题解：

select
video_id,
round(
    (avg(if(timestampdiff(second,start_time, end_time) >= duration, 1, 0)) * 100
    + sum(if_like) * 5
    + count(comment_id) * 3
    + sum(if_retweet) * 2)
    /
    (datediff(max(cur_date), date(max(end_time))) + 1)
    , 0) as hot_index
from tb_video_info
join tb_user_video_log using(video_id)
left join 
(
    select max(date(end_time)) as cur_date from tb_user_video_log
)t on 1
group by video_id
having datediff(max(cur_date), date(max(release_time))) < 30
order by hot_index desc
limit 3

思路：

我们需要根据日志表，求出当前日期，即最大的end_time，然后根据当前日期，选出最近30天发布的视频，以及求出最近无播放天数，由于该数据需要加入表中多次带入计算，故提前将其算出Join关联至表中，需注意mysql中无关联条件需要on 1, 语法才可通过，由于该数据是其他表计算得出，无法提前对tb_video_info表做where过滤，而该表数据量又小，故最后做一个having过滤来代替where过滤的效果，而having中必须得是聚合函数，因此加上max，此外，该需求中，两表之间关联的关系为join，因为求热度排行前提是该视频有热度，即有日志

二、某度面试题

用户行为日志表tb_user_log

id	uid	artical_id	in_time	out_time	sign_cin
1	101	9001	2021-11-01 10:00:00	2021-11-01 10:00:31	0
2	102	9001	2021-11-01 10:00:00	2021-11-01 10:00:24	0
3	102	9002	2021-11-01 11:00:00	2021-11-01 11:00:11	0
4	101	9001	2021-11-02 10:00:00	2021-11-02 10:00:50	0
5	102	9002	2021-11-02 11:00:01	2021-11-02 11:00:24	0

（uid-用户ID, artical_id-文章ID, in_time-进入时间, out_time-离开时间, sign_in-是否签到）

场景逻辑说明：artical_id-文章ID代表用户浏览的文章的ID，artical_id-文章ID为0表示用户在非文章内容页（比如App内的列表页、活动页等）。

DROP TABLE IF EXISTS tb_user_log;
CREATE TABLE tb_user_log (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid INT NOT NULL COMMENT '用户ID',
    artical_id INT NOT NULL COMMENT '视频ID',
    in_time datetime COMMENT '进入时间',
    out_time datetime COMMENT '离开时间',
    sign_in TINYINT DEFAULT 0 COMMENT '是否签到'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_user_log(uid, artical_id, in_time, out_time, sign_in) VALUES
  (101, 9001, '2021-11-01 10:00:00', '2021-11-01 10:00:31', 0),
  (102, 9001, '2021-11-01 10:00:00', '2021-11-01 10:00:24', 0),
  (102, 9002, '2021-11-01 11:00:00', '2021-11-01 11:00:11', 0),
  (101, 9001, '2021-11-02 10:00:00', '2021-11-02 10:00:50', 0),
  (102, 9002, '2021-11-02 11:00:01', '2021-11-02 11:00:24', 0);

问题1：统计2021年11月每天的人均浏览文章时长（秒数），结果保留1位小数，并按时长由短到长排序。

输出示例：

示例数据的输出结果如下

dt	avg_viiew_len_sec
2021-11-01	33.0
2021-11-02	36.5

解释：

11月1日有2个人浏览文章，总共浏览时长为31+24+11=66秒，人均浏览33秒；

11月2日有2个人浏览文章，总共时长为50+23=73秒，人均时长为36.5秒。

题解：

select
date(in_time) as dt,
round(
    sum(timestampdiff(SECOND,in_time,out_time)) 
    / count(distinct uid), 1
) as avg_viiew_len_sec
from tb_user_log
where date_format(in_time, '%Y-%m') = '2021-11' and artical_id != 0
group by dt
order by avg_viiew_len_sec

思路：

统计2021年11月和浏览文章都代表时间限定，where先进行过滤，每天代表聚合粒度，放入group by，人均浏览时长代表指标，使用聚合函数进行处理，但这里需要注意的是，人均浏览时长，不是平均浏览时长，需要用总时长除以人数，而人数需要distinct去重

问题2：统计每篇文章同一时刻最大在看人数，如果同一时刻有进入也有离开时，先记录用户数增加再记录减少，结果按最大人数降序。

输出示例：

示例数据的输出结果如下

artical_id	max_uv
9001	3
9002	2

解释：10点0分10秒时，有3个用户正在浏览文章9001；11点01分0秒时，有2个用户正在浏览文章9002。

题解：

select
artical_id,
max(sum_uv) as max_uv
from
(
    select
    artical_id,
    sum(flag) over(partition by artical_id order by time, flag desc) as sum_uv
    from
    (
        select
        artical_id, in_time as time, 1 as flag
        from tb_user_log
        where artical_id != 0 
        union all
        select
        artical_id, out_time as time, -1 as flag
        from tb_user_log
        where artical_id != 0 
    )t
)tt
group by artical_id
order by max_uv desc

思路：

首先我们将该问题等化为一个类似问题，一个公交车从起点站出发，到终点站为止，中途每个站点有人上车，也可能有人下车，求该次列车的最大承载人数，如果我们纠结于每个站点到底有多少人上车和下车，那显然会很混乱，我们不妨直接计算，从公交车出发开始，到该站点，一共有多少人上车，又一共有多少人下车，用上车人数减去到此刻的下车人数，即可求出每个到站时刻，车上有多少人，而最大人数，只需要求最大值即可。

该题也是一样思路，首先我们将intime和outtime进行union all并打上标记flag，代表其为上车还是下车，之后方便sum开窗求和，每次开窗范围，都为起始站点到此刻站点，但需要注意一个点，如果同一时刻有进入也有离开时，先记录用户数增加再记录减少，这样正好，当time相同时，我将flag倒序排序即可

问题3：统计2021年11月每天新用户的次日留存率（保留2位小数）

注：

次日留存率为当天新增的用户数中第二天又活跃了的用户数占比。
如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过，结果按日期升序。

输出示例：

示例数据的输出结果如下

dt	uv_left_rate
2021-11-01	0.67
2021-11-02	1.00
2021-11-03	0.00

解释：

11.01有3个用户活跃101、102、103，均为新用户，在11.02只有101、103两个又活跃了，因此11.01的次日留存率为0.67；

11.02有104一位新用户，在11.03又活跃了，因此11.02的次日留存率为1.00；

11.03有105一位新用户，在11.04未活跃，因此11.03的次日留存率为0.00；

11.04没有新用户，不输出。

题解：

with t as (
    select
    uid,
    dt,
    row_number() over(partition by uid order by dt) as rn
    from
    (
        select
        uid,
        date(in_time) as dt
        from tb_user_log
        union
        select
        uid,
        date(out_time) as dt
        from tb_user_log
    )a
)
select
t1.dt as dt,
round(
    avg(if(datediff(ifnull(t2.dt,'9999-12-31'),t1.dt) = 1, 1, 0)),2
) as uv_left_rate
from t as t1
left join t as t2
on t1.uid = t2.uid and t2.rn = 2
where t1.rn = 1 and date_format(t1.dt,'%Y-%m') = '2021-11'
group by dt
order by dt

思路：

1、题目要求我们统计新用户的留存率，翻译成人话就是，计算用户的第一次登录和第二次登录（跨天）是否相隔一天，不在乎后续的登录操作，因此，我们直接row_number分别取rn为1和2的即可，然后用rn为1的作为主表，关联rn为2的，datediff函数判断结果是否为1即可

2、而何时登录，数据里并未直接告诉我们，但是数据里有intime和outtime，我们只需对这两个时间进行一个简单的列转行即可，但要注意应该用union而非union all，因为如果同一天登入又登出，或者同一天多次登入或登出，我们应将其看做一次登录，而上一题用union all是因为我们求解的维度是文章，而非用户

3、由于我们要对t表数据进行两次应用，因此我们将t表使用with as 作为一个临时表提前查出其结果，之后left join的时候，可能存在一个情况，那就是用户就今天登录一次，之后就再无登录过了，但由于这部分数据我们也要作为分母参与平均，因此不将其过滤，而是如果链接不上为null，就给其一个最大的日期即可，反正相减结果不为1

4、题目里限制要求，只要11月份的新用户数据，因此我们对t1进行相应的月份过滤即可，这个过滤千万不要写在union的两个子句中，因为写在那里，会直接将10月的新用户，又在11月登录的数据，看成第一次登录，这显然是不合理的，我们直接在最外层过滤即可，反正我将全部数据都给你，最后你只要11月的，我给你过滤就好了