1.题目

现有一份用户搜索日志，包含用户ID，时间，用户搜索内容。定义无效搜索：如果用户下一次搜索内容中包含本次搜索内容，则认为本次搜索为无效搜索。请查询用户无效搜索记录

样例数据

+---------+---------------------+------------------------+
| user_id |     search_time      |     search_content      |
+---------+---------------------+------------------------+
|       1 | 2022-01-01 10:00:00 |          apple           |
|       1 | 2022-01-01 11:30:00 |    banana and apple      |
|       1 | 2022-01-01 12:45:00 |        fruit salad       |
|       1 | 2022-01-01 15:00:00 |         apple pie        |
|       1 | 2022-01-01 16:20:00 |   applesauce recipe      |
|       2 | 2022-01-01 10:00:00 |        cat food          |
|       2 | 2022-01-01 11:30:00 |   wet vs dry cat food    |
|       2 | 2022-01-01 12:45:00 | homemade cat food recipe |
|       2 | 2022-01-01 14:00:00 | cat food brands to avoid |
|       2 | 2022-01-01 16:20:00 | best cat food for i...   |
|       3 | 2022-01-01 10:00:00 |          book            |
|       3 | 2022-01-01 11:30:00 |  books like Harry Potter|
|       3 | 2022-01-01 13:00:00 |best selling books ...   |
|       3 | 2022-01-01 14:30:00 |   bookstores near me    |
|       3 | 2022-01-01 15:45:00 |    how to publish a book|
+---------+---------------------+------------------------+

2.题目分析

有序计算，即对上下两行数据进行比较，考察的是lead()函数，用法参考：hive开窗函数-lag和lead函数
字符串包含判断，instr()函数。

INSTR(str, substr)

其中，str是要搜索的字符串，substr是要查找的子字符串。该函数返回子字符串在指定字符串中第一次出现的位置，如果未找到则返回0。

3.SQL

step1:查询出下一行数据，并把下一行搜索内容作为新字段放到本行

selectuser_id,search_time,search_content,lead(search_content)over(partition by user_id order by search_time asc) as next_search_contentfrom user_search_log

查询结果

step2:比较搜索内容是否为下一次搜索内容的子字符串，给判断逻辑打标记（如果是返回1，否则返回0）

selectuser_id,search_time,search_content,lead(search_content)over(partition by user_id order by search_time asc) as next_search_content,If(instr(lead(search_content)over(partition by user_id order by search_time asc),search_content)>0,1,0) as flagfrom user_search_log

查询结果

step3:限制标签为1，查询出最后结果

select    user_id,    search_time,    search_contentfrom(    select        user_id,        search_time,        search_content,        if(instr(lead(search_content)over(partition by user_id order by search_time asc),search_content)>0,1,0) as flag        from     user_search_log) twhere flag =1;

查询结果

4.数据准备

建表语句

CREATE TABLE user_search_log (  user_id STRING,  search_time TIMESTAMP,  search_content STRING) STORED AS PARQUET;

数据插入语句

INSERT INTO user_search_logVALUES  ('1', '2022-01-01 10:00:00', 'apple'),  ('1', '2022-01-01 11:30:00', 'banana and apple'),  ('1', '2022-01-01 12:45:00', 'fruit salad'),  ('1', '2022-01-01 15:00:00', 'apple pie'),  ('1', '2022-01-01 16:20:00', 'applesauce recipe'),  ('2', '2022-01-01 10:00:00', 'cat food'),  ('2', '2022-01-01 11:30:00', 'wet vs dry cat food'),  ('2', '2022-01-01 12:45:00', 'homemade cat food recipe'),  ('2', '2022-01-01 14:00:00', 'cat food brands to avoid'),  ('2', '2022-01-01 16:20:00', 'best cat food for indoor cats'),  ('3', '2022-01-01 10:00:00', 'book'),  ('3', '2022-01-01 11:30:00', 'books like Harry Potter'),  ('3', '2022-01-01 13:00:00', 'best selling books of all time'),  ('3', '2022-01-01 14:30:00', 'bookstores near me'),  ('3', '2022-01-01 15:45:00', 'how to publish a book');

点点关注不迷路，天天学习有进步

分享↓收藏↓点赞↓再看↓

ITPub博客

SQL总结大厂真题-无效搜索

最新文章