【SQL每日一练】day13：牛客高频习题练习：时间函数+字符函数专题

题目一： 广告点击的高峰期

题目要求：

牛客购买点击表`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