题目一: 广告点击的高峰期
题目要求:
牛客购买点击表`user_ad_click_time`, 支付成功表`user_payment_time`,求出哪个小时为广告点击的高峰期,以及发生的点击次数(假设高峰期唯一),查询返回结果名称和顺序为
click_hour|click_cnt
表结构:
运行结果示例:
思路:
本题需要我们提取表中“click_time”字段中的“时”位,并根据此字段进行分组聚合统计。对于时间的统计非常简单,有多种方式可以完成,这里我们就还是按照题目要求的使用时间函数提取,也就是HOUR()函数。提取出时间字段后,我们直接按照该字段分许,统计每组的点击次数数量(每条数据对应一次点击),点击次数数量最多的即为点击的高峰,我们将统计结果倒序排序并只查询出第一条即可。
运行代码示例:
select hour(click_time) as click_hour,count(click_time) as click_cnt
from user_ad_click_time
group by click_hour
order by click_cnt desc
limit 1
题目二: 输出在5min内完成点击购买的用户ID
题目要求:
牛客购买点击表`user_ad_click_time`, 支付成功表`user_payment_time`,输出在5min内完成点击并购买的用户ID(倒排),查询返回结果名称和顺序为
uid
表结构:
运行结果示例:
思路:
本题中,我们需要连接两张表,并寻找出在点击记录后5分钟内有付款记录的数据。首先在连接表时,由于我们需要寻找两张表中都存在的数据,所以我们直接连接即可,无需使用左连接/右连接查询。其次需要注意我们的连接条件需要将user_id和trace_id都考虑进去,只考虑user_id会有同一用户点击进多个广告的情况。最后就是对于时间的判断,我们使用时间函数timestampdiff()计算两个时间的差值即可,令差值小于5
运行代码示例:
select c.user_id as uid
from user_ad_click_time c
join user_payment_time p
on c.user_id = p.user_id and c.trace_id = p.trace_id and
timestampdiff(minute,c.click_time,p.pay_time)<5
order by uid desc
题目三: 字符函数正则匹配1
题目要求:
牛客有评论记录表`comment_detail`,输出 comment 以 '是' 或 '求' 开头的的 id,comment列,按照id升序排序,查询返回结果名称和顺序为
id|comment
表结构:
运行结果示例:
思路:
本题题目有误,按照给出的测试用例结果来看,本题想要考察的是“comment 中包含'是' 或 '试' 开头的的 id,comment列,按照id升序排序。”
我们直接使用like关键字进行模糊匹配即可。
运行代码示例:
select id,comment
from comment_detail
where comment like '%是%' or comment like '%试%'
order by id
题目四:字符函数正则匹配2
题目要求:
牛客有评论记录表`comment_detail`,输出 comment 以 '是' 或 '求' 开头的的 id,comment列,查询返回结果名称和顺序
id|comment
表结构:
运行结果示例:
思路:
和上一题一样的思路,我们也可以使用正则表达式来完成:
select id, commentfrom comment_detailwhere comment regexp '^[是|求]';
运行代码示例:
select id,comment
from comment_detail
where comment like '是%' or comment like '求%'
order by id
题目五:话题的分布情况
题目要求:
牛客有评论记录表`comment_detail`,输出所有第二话题为1002的评论对应的第一话题subject_id1的数量cnt,按照第一话题升序排序,查询返回结果名称和顺序为
subject_id1|cnt
表结构:
运行结果示例:
思路:
先简单翻译一下题目,所谓第N话题就是表中subject_set字段中排在第N位的值,以表格中第一条数据为例,第一话题就是1001,第二话题就是1002,第三话题就是1003。本题中,我们需要提取出所有评论的话题,找到第二话题为1002的数据,再根据这些数据的第一话题进行分组,统计其每组的数量。
不难看出本题的难点就在于如何将subject_set字段中的数字进行拆分,我们可以用substring_index()函数来实现,该函数用于截取字符串,包括三个参数:待截取字符串、截取字符、截取位置,如substring_index("a,b,c,d,e", ',' ,1)的结果就是a,substring_index("a,b,c,d,e", ',' ,2)的结果就是a,b。其中截取位置也可以是负数,负数就表示从末尾向前截取,例如substring_index("a,b,c,d,e", ',' ,-1)的结果就是e。
本题中我们需要截取的字符串有两个,也就是“第一话题”和“第二话题”,“第一话题”的截取比较容易实现,我们直接将截取位置 设置为1即可。但是“第二话题”的截取需要绕一个弯,我们正向截取的话只能将一、二话题都截取出来,反着截取的话则无法判定第二话题具体会是第几个值。因此这里我们需要先将一、二话题都截取出来,再在此基础上进行一次截取,取出倒数第一个值,也就是第二话题的内容。
完成了字符串截取,剩下的工作就很简单了,我们只需要分组统计排序就可以。
运行代码示例:
select substring_index(subject_set,',',1) as subject_id1, count(*) as cnt
from comment_detail
where substring_index(substring_index(subject_set,',',2),',',-1) = 1002
group by subject_id1
order by subject_id1
题目六:评论替换和去除
题目要求:
牛客有评论记录表`comment_detail`,输出字数大于3的评论的id,comment,且去除其中的中文逗号,查询返回结果名称和顺序为
id|comment
表结构:
运行结果示例:
思路:
本题考察了字符串长度的统计和字符替换。我们都可以用字符串函数来实现。字符串长度的统计我们通常都会使用length()来实现,但是本题中要求我们统计的是“字数”大于3的数量,因此我们应该使用char_length()函数,这两者的区别在于length()返回的是字符串的字节数,char_length()返回的是字符串的字符数,由于评论中涉及到了中文汉字,因此我们应该统计字符数,例如:
select CHAR_LENGTH('a,b,c,111,222,爱,我,是') as leng1 , length('a,b,c,111,222,爱,我,是') as leng2
如果没有中文字符的话,其实两者结果是一样的:
select CHAR_LENGTH('a,b,c,111,222') as leng1 , length('a,b,c,111,222') as leng2
对于字符串的截取,我们直接使用replace()函数即可,replace()函数有三个参数,分别是原始字符串、待替换的字符内容、替换的字符内容。本题中我们直接使用replace(comment,',','')语句来使用空值替换评论中的“,”就可以。
运行代码示例:
select id,replace(comment,',','') as comment
from comment_detail
where char_length(comment)>3