百度大数据面试SQL-搜索结果点击率CTR统计
⚠️ 待修正
一、题目背景
这道题来自百度搜索广告部门的数据分析岗面试。CTR（Click Through Rate）是搜索业务最核心的指标——衡量搜索结果对用户的吸引力和相关性。高CTR说明搜索结果匹配了用户意图，低CTR说明用户看了搜索结果但不愿点击，可能是标题不吸引人或者排序位置太靠后。
业务场景：搜索质量团队每天按查询词看CTR排名。低CTR的词需要人工review搜索结果页（SERP）——是摘要写得不好？还是被竞品买了品牌词广告？这道SQL就是CTR看板的基础查询。
二、题目
现有一张搜索曝光表和一张搜索点击表，请统计每个查询词的搜索结果点击率（CTR = Click Through Rate）。
CTR = 搜索结果被点击的次数 / 搜索结果被曝光的次数
搜索曝光表 t7_search_impression：
+----------+-------------------+---------------------+
| user_id | query_word | imp_time |
+----------+-------------------+---------------------+
| u01 | 天气预报 | 2023-03-01 10:00:00 |
| u02 | 天气预报 | 2023-03-01 10:05:00 |
| u01 | 天气预报 | 2023-03-01 10:10:00 |
| u03 | 股票行情 | 2023-03-01 10:15:00 |
| u04 | 世界杯 | 2023-03-01 11:00:00 |
| u02 | 股票行情 | 2023-03-01 11:30:00 |
+----------+-------------------+---------------------+
搜索点击表 t7_search_click：
+----------+-------------------+---------------------+
| user_id | query_word | click_time |
+----------+-------------------+---------------------+
| u01 | 天气预报 | 2023-03-01 10:00:30 |
| u03 | 股票行情 | 2023-03-01 10:15:30 |
| u04 | 世界杯 | 2023-03-01 11:00:30 |
| u02 | 股票行情 | 2023-03-01 11:31:00 |
+----------+-------------------+---------------------+
三、思路分析
CTR计算需要两张表的数据分别聚合后再关联：
- 曝光数：从曝光表
GROUP BY query_word统计每个词的出现次数 - 点击数：从点击表同样按 query_word 聚合
- LEFT JOIN：以曝光表为主表关联点击统计（有些词有曝光无点击，CTR=0）
- COALESCE 处理NULL：无点击记录的词，点击数置为 0 而不是 NULL
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
步骤1：分别统计曝光数和点击数
-- 曝光数
select query_word, count(1) as imp_cnt
from t7_search_impression group by query_word;
-- 点击数
select query_word, count(1) as click_cnt
from t7_search_click group by query_word
步骤2：LEFT JOIN关联 + 计算CTR
执行SQL
select t1.query_word,
t1.imp_cnt,
coalesce(t2.click_cnt, 0) as click_cnt,
round(coalesce(t2.click_cnt, 0) / t1.imp_cnt, 4) as ctr
from (
select query_word, count(1) as imp_cnt
from t7_search_impression group by query_word
) t1
left join (
select query_word, count(1) as click_cnt
from t7_search_click group by query_word
) t2 on t1.query_word = t2.query_word
order by ctr desc
执行结果
+-------------------+---------+-----------+--------+
| query_word | imp_cnt | click_cnt | ctr |
+-------------------+---------+-----------+--------+
| 股票行情 | 2 | 2 | 1.0000 |
| 世界杯 | 1 | 1 | 1.0000 |
| 天气预报 | 3 | 1 | 0.3333 |
+-------------------+---------+-----------+--------+
"天气预报"虽然曝光最多（3次），但CTR最低（33%），可能因为搜索结果摘要不够吸引人，也可能是用户只是瞄一眼天气不需要点击。
五、常见坑点
坑1：用INNER JOIN会丢失零点击的查询词
如果某个查询词只有曝光没有点击，INNER JOIN会让它直接从结果中消失。但你恰恰需要看到CTR=0的词来优化。始终用 LEFT JOIN 保留所有曝光数据。
坑2：COALESCE防止除零但不是防分母为零
COALESCE(click_cnt, 0) / imp_cnt 处理了分子为NULL的情况。但如果 imp_cnt = 0（没有曝光的查询词出现在点击表中——数据异常），除法会报错。可以加 WHERE imp_cnt > 0 防护。
六、举一反三
- 按位置看CTR：如果曝光表有
position字段（排名1/2/3...），GROUP BY position 分析各排名位置的CTR衰减曲线 - 按用户分群看CTR：关联用户画像表，按性别/年龄/地域分组看CTR差异
- 时间趋势：按天统计每个词的CTR变化，用 LAG 计算CTR日环比，发现CTR突然下降的关键词
- 长尾词 vs 头部词：按曝光量分层（曝光>1000次 / 100-1000次 / <100次），对比不同量级词的CTR特征
七、知识点总结
| 考点 | 说明 |
|---|---|
| LEFT JOIN 保留主表 | 以曝光表为主，确保无点击的词也出现在结果中 |
| COALESCE 处理NULL | 无点击记录时点击数返回0而非NULL，避免CTR计算出错 |
| ROUND格式化比率 | 保留4位小数，0.3333 = 33.33% |
| 子查询聚合 + 关联 | 两张表分别GROUP BY后再JOIN，逻辑清晰 |
八、建表语句和数据插入
点击展开 DDL & DML
CREATE TABLE t7_search_impression (
user_id string COMMENT '用户ID',
query_word string COMMENT '查询词',
imp_time string COMMENT '曝光时间'
) COMMENT '搜索曝光表';
CREATE TABLE t7_search_click (
user_id string COMMENT '用户ID',
query_word string COMMENT '查询词',
click_time string COMMENT '点击时间'
) COMMENT '搜索点击表';
INSERT INTO t7_search_impression VALUES
('u01', '天气预报', '2023-03-01 10:00:00'),
('u02', '天气预报', '2023-03-01 10:05:00'),
('u01', '天气预报', '2023-03-01 10:10:00'),
('u03', '股票行情', '2023-03-01 10:15:00'),
('u04', '世界杯', '2023-03-01 11:00:00'),
('u02', '股票行情', '2023-03-01 11:30:00');
INSERT INTO t7_search_click VALUES
('u01', '天气预报', '2023-03-01 10:00:30'),
('u03', '股票行情', '2023-03-01 10:15:30'),
('u04', '世界杯', '2023-03-01 11:00:30'),
('u02', '股票行情', '2023-03-01 11:31:00');
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
