阿里巴巴大数据面试SQL-搜索排序点击率衰减

一、题目背景

淘宝搜索每天承载数十亿次查询，搜索结果的前3个位置占据了超过60%的点击量。搜索算法团队的核心KPI是"让用户更快找到想要的商品"，而位置CTR（Click Through Rate）衰减曲线是衡量搜索质量的关键指标——理想情况下CTR应该随位置平缓下降，如果前3位CTR断崖式下跌说明排序模型有严重问题。

业务场景：搜索产品经理每周看"各位置CTR衰减曲线"，如果发现第1位CTR异常低（说明最匹配的商品没有被排到第1），或者第5位之后CTR几乎为0（说明用户只看前几个结果），就需要调整排序策略。

二、题目

现有一张搜索点击日志表 t12_search_log，记录了每次搜索请求中各位置商品的曝光和点击情况。请统计各展示位置的点击率（CTR），分析CTR随位置的衰减趋势。

CTR = 该位置被点击的次数 / 该位置被曝光的次数

搜索点击日志表 t12_search_log：

+----------+----------+----------+----------+---------------------+
| query_id | user_id  | item_id  | position |      is_click       |
+----------+----------+----------+----------+---------------------+
| Q001     | u01      | I1001    | 1        | 1                   |
| Q001     | u01      | I1002    | 2        | 0                   |
| Q001     | u01      | I1003    | 3        | 1                   |
| Q001     | u01      | I1004    | 4        | 0                   |
| Q001     | u01      | I1005    | 5        | 0                   |
| Q002     | u02      | I2001    | 1        | 1                   |
| Q002     | u02      | I2002    | 2        | 1                   |
| Q002     | u02      | I2003    | 3        | 0                   |
| Q003     | u01      | I3001    | 1        | 0                   |
| Q003     | u01      | I3002    | 2        | 1                   |
| Q003     | u01      | I3003    | 3        | 0                   |
| Q003     | u01      | I3004    | 4        | 0                   |
+----------+----------+----------+----------+---------------------+

is_click: 1-被点击, 0-未点击。 每条记录代表一次曝光（搜索结果展示），同一query_id下一次搜索有多个位置的曝光。

三、思路分析

核心是按位置聚合 + CTR计算 + 衰减观察：

1. 按位置统计：GROUP BY position，COUNT(1) 得总曝光次数，SUM(is_click) 得总点击次数
2. CTR计算：点击数 / 曝光数，注意确保分母不为0
3. 衰减分析：观察CTR从位置1到位置N的下降趋势，可补充"相邻位置CTR下降率"

维度	评分
题目难度	⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️⭐️⭐️

四、逐步推导

步骤1：按位置统计曝光次数和点击次数

select position,
       count(1) as imp_cnt,
       sum(is_click) as click_cnt
from t12_search_log
group by position
order by position

执行结果

+----------+---------+-----------+
| position | imp_cnt | click_cnt |
+----------+---------+-----------+
| 1        | 3       | 2         |
| 2        | 3       | 2         |
| 3        | 3       | 1         |
| 4        | 2       | 0         |
| 5        | 1       | 0         |
+----------+---------+-----------+

位置1被曝光3次（Q001/Q002/Q003各一次），被点击2次。位置5只曝光1次，样本太少CTR不稳定。

步骤2：计算CTR并添加衰减指标

执行SQL

select position,
       imp_cnt,
       click_cnt,
       round(click_cnt * 1.0 / imp_cnt, 4) as ctr,
       round(1.0 * click_cnt / nullif(first_value(click_cnt) over (order by position), 0), 4) as ctr_vs_pos1_ratio
from (
    select position,
           count(1) as imp_cnt,
           sum(is_click) as click_cnt
    from t12_search_log
    group by position
) t
order by position

执行结果

+----------+---------+-----------+--------+-----------------+
| position | imp_cnt | click_cnt |  ctr   | ctr_vs_pos1_ratio |
+----------+---------+-----------+--------+-----------------+
| 1        | 3       | 2         | 0.6667 | 1.0000           |
| 2        | 3       | 2         | 0.6667 | 1.0000           |
| 3        | 3       | 1         | 0.3333 | 0.5000           |
| 4        | 2       | 0         | 0.0000 | 0.0000           |
| 5        | 1       | 0         | 0.0000 | 0.0000           |
+----------+---------+-----------+--------+-----------------+

位置1-2 CTR=66.7%，位置3降到33.3%，位置4-5 CTR=0。实际淘宝搜索的位置1 CTR通常在20%-40%，位置5之后在1%-3%。示例数据样本较小，趋势仅供参考。

五、常见坑点

坑1：曝光量太少的位置CTR不稳定

位置10可能只有几十次曝光，某一次偶然点击就让CTR从0%跳到10%。实际分析时需加最小曝光阈值（如 HAVING imp_cnt >= 100）过滤不可靠数据。

坑2：位置偏差（Position Bias）

高CTR不一定是商品好，可能只是因为排在前面。用户更信任前几个结果，天然会多点第1位。修正方法：用"位置1的CTR"做基准归一化，或使用IPW（逆概率加权）去偏。

坑3：不同query的搜索结果数不同

有些搜索词只有3个结果，有些有20个，导致后面位置的曝光量天然不同。分析时需要确认是按query去重（一个query下每个位置只算一次曝光）还是按用户去重。

六、举一反三

1. 按query意图分类：关联query分类表，对比"导航型query（搜具体品牌）"和"探索型query（搜品类）"的CTR衰减曲线差异
2. 位置×CTR热力图：加 query_page（第1页/第2页/第3页）维度，分析不同页面的CTR分布
3. CTR衰减速度：LAG(ctr) OVER (ORDER BY position) 计算相邻位置CTR下降幅度，位置5之后CTR下降>50%说明"翻页率"有问题
4. 个性化vs非个性化：加 is_personalized 字段，AB对比个性化排序是否能将高CTR商品保持在靠前位置

七、知识点总结

考点	说明
GROUP BY position	按位置维度聚合曝光和点击
SUM(is_click) / COUNT(1)	CTR = 点击数 / 曝光数
FIRST_VALUE 窗口函数	以位置1的CTR为基准做归一化
HAVING 过滤小样本	曝光量不足的位置CTR不可靠

八、建表语句和数据插入

点击展开 DDL & DML

CREATE TABLE t12_search_log (
    query_id  string COMMENT '搜索请求ID',
    user_id   string COMMENT '用户ID',
    item_id   string COMMENT '商品ID',
    position  int COMMENT '展示位置(1-N)',
    is_click  int COMMENT '是否点击: 1-是, 0-否'
) COMMENT '搜索点击日志表';

INSERT INTO t12_search_log VALUES
('Q001', 'u01', 'I1001', 1, 1),
('Q001', 'u01', 'I1002', 2, 0),
('Q001', 'u01', 'I1003', 3, 1),
('Q001', 'u01', 'I1004', 4, 0),
('Q001', 'u01', 'I1005', 5, 0),
('Q002', 'u02', 'I2001', 1, 1),
('Q002', 'u02', 'I2002', 2, 1),
('Q002', 'u02', 'I2003', 3, 0),
('Q003', 'u01', 'I3001', 1, 0),
('Q003', 'u01', 'I3002', 2, 1),
('Q003', 'u01', 'I3003', 3, 0),
('Q003', 'u01', 'I3004', 4, 0);

📱关注公众号

「数据仓库技术」文章同步更新，不错过每一篇干货

💬加群交流

备注「数据仓库技术」加入社群，每日一道大厂SQL真题