SQL 搜索关键词热度排名及趋势：GROUP BY + ORDER BY（小红书面试题）
一、题目
统计各关键词在 5 月和 6 月的搜索用户数（去重），计算 6 月较 5 月的环比变化率，按 6 月搜索量降序输出 Top10。
假设有搜索日志表 t7_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)提取年月维度； - 按关键词分组，用 CASE WHEN 条件聚合分别统计5月和6月的去重搜索人数；
- 计算环比变化率 = (6月 - 5月) / 5月，按6月搜索量降序取Top10。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️ |
三、逐步推导
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 t7_search_log
group by keyword
查询结果
+----------+----------+----------+
| keyword | may_cnt | jun_cnt |
+----------+----------+----------+
| 防晒霜 | 1 | 4 |
| 连衣裙 | 2 | 3 |
| 露营 | 0 | 3 |
+----------+----------+----------+
3 rows selected (0.691 seconds)(https://www.dwsql.com)
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 t7_search_log
group by keyword
) t
order by jun_cnt desc
limit 10
查询结果
+----------+----------+----------+-------------+
| keyword | may_cnt | jun_cnt | mom_change |
+----------+----------+----------+-------------+
| 防晒霜 | 1 | 4 | 3.0 |
| 连衣裙 | 2 | 3 | 0.5 |
| 露营 | 0 | 3 | NULL |
+----------+----------+----------+-------------+
3 rows selected (0.481 seconds)(https://www.dwsql.com)
四、常见坑点
坑1：环比计算中除零处理 — 上月搜索量可能为0（如"露营"的may_cnt=0），直接除法 (jun_cnt - may_cnt) / may_cnt 会触发除零错误。使用 case when may_cnt > 0 then ... else null end 安全处理，上月无数据时环比不可计算，返回NULL。
坑2：COUNT DISTINCT vs COUNT(*) — 同一用户在同月内可能多次搜索同一关键词（如u01在5月和6月都搜索过"连衣裙"），使用 count(distinct user_id) 统计去重搜索人数，反映真实用户覆盖；若用 count(*) 会重复计数，高估搜索热度。
坑3：时间范围需精确到月 — substr(search_time, 1, 7) 提取年月前缀（格式"YYYY-MM"），确保只统计目标月份的数据。注意前提是 search_time 为统一格式的字符串，若为 timestamp 类型需改用 date_format(search_time, 'yyyy-MM')。
五、知识点总结
| 考点 | 说明 |
|---|---|
| CASE WHEN 条件聚合 | 结合 substr 提取年月，在单个聚合查询中按条件分别统计各月搜索量，避免多次子查询或自连接 |
| COUNT DISTINCT 去重 | 同一用户同月多次搜索同一关键词应去重，count(distinct user_id) 反映真实搜索人数而非搜索次数 |
| GROUP BY + ORDER BY + LIMIT | 分组聚合后按目标列降序排序取Top N，是排行榜类需求的通用写法 |
| 环比计算与除零保护 | case when 判断分母 > 0 再计算除法，防止上月数据为0导致的除零异常，无数据时返回NULL |
六、建表语句
点击展开 DDL & DML
-- 建表语句
create table t7_search_log (
user_id string comment '用户id',
keyword string comment '搜索关键词',
search_time string comment '搜索时间'
) comment '搜索日志表';
-- 插入数据
insert into t7_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真题
