小红书大数据面试SQL-搜索关键词热度排名及趋势
⚠️ 待修正
一、题目背景
这道题来自小红书的数据分析岗面试。小红书是生活方式社区和电商，数据分析师需要从海量业务数据中挖掘洞见。
业务场景：搜索行为相关需求在日常工作中频繁出现，这类型的SQL题是面试高频考点。
一、题目
统计2025年6月小红书搜索日志中搜索量Top10的关键词，并计算每个关键词较上月的搜索量环比变化。
假设有搜索日志表 search_log：
+---------+----------+-------------------+
| user_id | keyword | search_time |
+---------+----------+-------------------+
| u01 | 连衣裙 | 2025-05-15 10:00 |
| u02 | 连衣裙 | 2025-05-18 14:00 |
| u01 | 连衣裙 | 2025-06-02 09:00 |
| u03 | 连衣裙 | 2025-06-05 11:00 |
| u04 | 连衣裙 | 2025-06-10 16:00 |
| u01 | 防晒霜 | 2025-05-20 08:00 |
| u02 | 防晒霜 | 2025-06-01 10:00 |
| u03 | 防晒霜 | 2025-06-03 12:00 |
| u04 | 防晒霜 | 2025-06-08 15:00 |
| u05 | 防晒霜 | 2025-06-12 09:00 |
| u02 | 露营 | 2025-06-05 08:00 |
| u03 | 露营 | 2025-06-10 14:00 |
| u04 | 露营 | 2025-06-15 17:00 |
+---------+----------+-------------------+
三、思路分析
- 提取搜索时间的年月维度
substr(search_time, 1, 7)； - 按关键词和月份分组统计搜索次数（用户去重或不去重根据需求）；
- 分别计算5月和6月各关键词的搜索量，使用
LAG或分别聚合后JOIN计算环比。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️ |
四、逐步推导
1.分别统计每个关键词在5月和6月的搜索量
执行SQL
select keyword,
count(distinct case when substr(search_time, 1, 7) = '2025-05' then user_id end) as may_cnt,
count(distinct case when substr(search_time, 1, 7) = '2025-06' then user_id end) as jun_cnt
from search_log
group by keyword
查询结果
+----------+---------+---------+
| keyword | may_cnt | jun_cnt |
+----------+---------+---------+
| 连衣裙 | 2 | 3 |
| 防晒霜 | 1 | 4 |
| 露营 | 0 | 3 |
+----------+---------+---------+
2.计算环比变化并取Top10
执行SQL
select keyword,
may_cnt,
jun_cnt,
case when may_cnt > 0 then round((jun_cnt - may_cnt) / may_cnt, 4) else null end as mom_change
from (
select keyword,
count(distinct case when substr(search_time, 1, 7) = '2025-05' then user_id end) as may_cnt,
count(distinct case when substr(search_time, 1, 7) = '2025-06' then user_id end) as jun_cnt
from search_log
group by keyword
) t
order by jun_cnt desc
limit 10
查询结果
+----------+---------+---------+------------+
| keyword | may_cnt | jun_cnt | mom_change |
+----------+---------+---------+------------+
| 防晒霜 | 1 | 4 | 3.0000 |
| 连衣裙 | 2 | 3 | 0.5000 |
| 露营 | 0 | 3 | NULL |
+----------+---------+---------+------------+
五、常见坑点
坑1：RANK vs DENSE_RANK vs ROW_NUMBER — 并列值处理不同：ROW_NUMBER硬区分，RANK跳跃，DENSE_RANK连续。统计"前N名"需明确业务倾向。
坑2：NULL值排序位置 — ORDER BY 默认 NULLS LAST，如果排序列有 NULL，Top N可能被挤掉。
六、举一反三
-
分组Top N：
PARTITION BY category实现每个品类/区域的前N名 -
含占比的排名：
SUM() OVER()算总量，每个条目除总量得贡献度 -
滚动时间窗口Top N：限定过去30天数据，更适合分析趋势变化
七、知识点总结
| 考点 | 说明 |
|---|---|
| LAG / LEAD | 获取前/后一行数据，用于环比计算、状态变更检测 |
| CASE WHEN 条件聚合 | 灵活实现分段统计、条件计数、标签化处理 |
| 多表JOIN | LEFT JOIN保留主表所有数据，COALESCE处理NULL |
| GROUP BY + 聚合函数 | 分组聚合是数据分析的基础，配合HAVING筛选分组结果 |
八、建表语句和数据插入
点击展开 DDL & DML
--建表语句
CREATE TABLE search_log (
user_id string COMMENT '用户ID',
keyword string COMMENT '搜索关键词',
search_time string COMMENT '搜索时间'
) COMMENT '搜索日志表';
-- 插入数据
insert into search_log(user_id, keyword, search_time) values
('u01','连衣裙','2025-05-15 10:00'),
('u02','连衣裙','2025-05-18 14:00'),
('u01','连衣裙','2025-06-02 09:00'),
('u03','连衣裙','2025-06-05 11:00'),
('u04','连衣裙','2025-06-10 16:00'),
('u01','防晒霜','2025-05-20 08:00'),
('u02','防晒霜','2025-06-01 10:00'),
('u03','防晒霜','2025-06-03 12:00'),
('u04','防晒霜','2025-06-08 15:00'),
('u05','防晒霜','2025-06-12 09:00'),
('u02','露营','2025-06-05 08:00'),
('u03','露营','2025-06-10 14:00'),
('u04','露营','2025-06-15 17:00');
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
