面试真题

题目：统计用户在搜索某个关键词且“无搜索结果”后，10 分钟内通过底部“为您推荐”位成功下单的用户比例。

表数据准备

用户行为日志表 (user_behavior_log)

数据逻辑说明：

• 分母（无结果搜索用户）：用户 101, 102, 104, 105, 106（共 5 人）。用户 103 搜索结果为 5，不计入分母。
• 分子（10分钟内推荐位下单用户）：
  • 101：无结果搜索后 5 分钟在推荐位下单（满足条件）。
  • 102：无结果搜索后 15 分钟在推荐位下单（超时，不满足）。
  • 104：无结果搜索后 5 分钟在“搜索结果”位下单（来源不符，不满足）。
  • 105：无下单行为（不满足）。
  • 106：无结果搜索后 8 分钟在推荐位下单（满足条件）。
  • 满足条件的用户为 101 和 106（共 2 人）。
• 最终期望比例：2 / 5 = 40.00%。

SparkSQL 解决方案

WITH no_result_search AS (
    -- 步骤 1：筛选出所有搜索且“无结果”的用户及时间（去重，防单用户单日多次重复搜索干扰）
    SELECT DISTINCT 
        user_id, 
        action_time AS search_time
    FROM user_behavior_log
    WHERE action_type = 'search' 
      AND result_count = 0
),
recommend_order AS (
    -- 步骤 2：筛选出所有通过“为您推荐”成功下单的用户及时间
    SELECT DISTINCT 
        user_id, 
        action_time AS order_time
    FROM user_behavior_log
    WHERE action_type = 'order' 
      AND referral_pos = 'recommend'
),
user_conversion AS (
    -- 步骤 3：通过左关联（Left Join）进行时间窗口匹配
    SELECT 
        s.user_id,
        MAX(CASE WHEN o.order_time IS NOT NULL THEN 1 ELSE 0 END) AS is_converted
    FROM no_result_search s
    LEFT JOIN recommend_order o 
      ON s.user_id = o.user_id
     AND o.order_time >= s.search_time
     -- 限制在 10 分钟以内（600 秒）
     AND unix_timestamp(o.order_time) - unix_timestamp(s.search_time) <= 600
    GROUP BY s.user_id
)
-- 步骤 4：计算最终的转化率
SELECT 
    COUNT(user_id) AS total_no_result_users,
    SUM(is_converted) AS converted_users,
    ROUND(SUM(is_converted) / COUNT(user_id) * 100, 2) AS conversion_rate_percent
FROM user_conversion;

SparkSQL 深度解析与面试应对指南

在回答这道经典的漏斗转化（Funnel Analysis）面试题时，仅仅写出 SQL 是不够的。面试官往往会针对你的实现细节进行追问，以下是突破面试的关键点：

1. 业务逻辑陷阱与处理

• 去重防噪：同一个用户可能在一天内多次触发“无结果搜索”，也可能多次下单。如果在 Join 前不对 user_id 和 action_time 进行限制或去重，Join 后会产生笛卡尔积，导致数据膨胀。
• 聚合的科学性：在 user_conversion 临时表中，我们使用了 GROUP BY s.user_id 结合 MAX(CASE WHEN...)。这种写法确保了无论用户有多少次符合/不符合的点击，每个用户在分母和分子中只被精确计算一次，完美契合“用户比例”的要求。

2. SparkSQL 时间差计算技巧

在 SparkSQL 中计算时间差有多种方式：

• unix_timestamp(t1) - unix_timestamp(t2)：推荐使用。将时间转为秒级时间戳进行减法计算，对于“分钟级”或“秒级”的时间窗口判定最为精准，且不容易出错。
• o.order_time <= s.search_time + interval 10 minutes：可读性极佳，SparkSQL 原生支持 Interval 语法，但在某些传统 Hive/Spark 混合引擎中兼容性可能欠佳。

3. 性能优化与面试加分项（高频追问）

当面试官问你：“如果这张表每天有几十亿行，你的 SQL 该如何优化？”你可以从以下几个维度作答：

• 区间关联优化（Interval Join）：
  在 Spark 3.x 中，对于类似 o.order_time >= s.search_time AND unix_timestamp(o.order_time) - unix_timestamp(s.search_time) <= 600 的条件，Spark 会自动识别并优化为 Range Join (Interval Join)。它避免了全表 Shuffle 后做大 Cartesian Product，而是在分区内利用时间排序后滑动窗口扫描，性能极大提升。
• 广播连接（Broadcast Hash Join）：
  “无结果搜索”通常在搜索日志中占比极低（通常 < 5%）。过滤后的 no_result_search 表会非常小。可以在 SQL 中显式加入 Hint 提示：/*+ BROADCAST(s) */，将小表广播到各个 Executor，避免大表（订单表）的 Shuffle。
• 数据倾斜（Data Skew）：
  若某些热门无结果词（如突发热词）导致大批用户在同一时间搜索，可能会导致 user_id 倾斜。面试中可以提出：对 user_id 加盐（Add Salt）打散后关联，最后再聚合去掉盐值，以此展示处理大数据的实战经验。