根据用户行为日志表（包含用户ID、行为类型：click/add_to_cart/pay、时间戳），计算“点击 -> 漏斗加入购物车 -> 支付”的转化率。要求三种行为必须在同一个用户身上依次发生，且全链路在 24 小时内完成

面试真题题目：根据用户行为日志表（包含用户ID、行为类型： / / 、时间戳），计算“点击（click） -> 漏斗加入购物车（addtocart） -> 支付（pay）”的转化率。要求： 1. 三种行为必须在同一个用户身上依次发生（时间顺序：点击 $\rightarrow$ 加购 $\rightarrow$ 支付）。 2. 全链路（从点击到支付）必须在 24小时内完成。 3. 输出结果需包含：总点击人数、总加购人数、总支付人数，以及两两之间的转化率。 --- 具体表数据 () | userid | behaviortype | actiontime | | :--- | :--- | :--- | | 101 | click | 2023-10-01 10:00:00 | | 101 | addtocart | 2023-10-01 12:00:00 | | 101 | pay | 2023-10-01 14:00:00 | | 102 | click | 2023-10-01 10:00:00 | | 102 | addtocart | 2023-10-01 11:...

根据用户行为日志表（包含用户ID、行为类型：click/add_to

面试真题

题目：
根据用户行为日志表 user_behavior_log（包含用户ID、行为类型：click / add_to_cart / pay、时间戳），计算“点击（click） -> 漏斗加入购物车（add_to_cart） -> 支付（pay）”的转化率。

要求：

三种行为必须在同一个用户身上依次发生（时间顺序：点击 $\rightarrow$ 加购 $\rightarrow$ 支付）。
全链路（从点击到支付）必须在 24小时 内完成。
输出结果需包含：总点击人数、总加购人数、总支付人数，以及两两之间的转化率。

具体表数据 (`user_behavior_log`)

user_id	behavior_type	action_time
101	click	2023-10-01 10:00:00
101	add_to_cart	2023-10-01 12:00:00
101	pay	2023-10-01 14:00:00
102	click	2023-10-01 10:00:00
102	add_to_cart	2023-10-01 11:00:00
103	click	2023-10-01 10:00:00
103	pay	2023-10-01 11:00:00
104	click	2023-10-01 10:00:00
104	add_to_cart	2023-10-01 15:00:00
104	pay	2023-10-02 11:00:00
105	add_to_cart	2023-10-01 09:00:00
105	click	2023-10-01 10:00:00
105	pay	2023-10-01 11:00:00

测试用例逻辑说明：

User 101：完成全链路，且在4小时内完成（符合要求）。
User 102：仅完成“点击 $\rightarrow$ 加购”（符合前两阶段）。
User 103：有点击和支付，但漏掉了“加购”阶段（不符合顺序）。
User 104：完成了全链路，但点击（10-01 10:00）到支付（10-02 11:00）历时25小时，超过了24小时窗口（支付不计入）。
User 105：虽然三个行为都有，但顺序是“加购 $\rightarrow$ 点击 $\rightarrow$ 支付”，不符合“点击 $\rightarrow$ 加购 $\rightarrow$ 支付”的顺序（加购和支付不计入）。

SparkSQL 解决方案

sql

WITH click_events AS (
    SELECT user_id, action_time AS click_time
    FROM user_behavior_log
    WHERE behavior_type = 'click'
),
cart_events AS (
    SELECT user_id, action_time AS cart_time
    FROM user_behavior_log
    WHERE behavior_type = 'add_to_cart'
),
pay_events AS (
    SELECT user_id, action_time AS pay_time
    FROM user_behavior_log
    WHERE behavior_type = 'pay'
),
funnel_stage AS (
    SELECT 
        c.user_id,
        c.click_time,
        ca.cart_time,
        p.pay_time
    FROM click_events c
    -- 关联加购：必须在点击之后，且在点击后的24小时之内
    LEFT JOIN cart_events ca 
        ON c.user_id = ca.user_id 
        AND ca.cart_time > c.click_time 
        AND ca.cart_time <= c.click_time + INTERVAL 24 HOURS
    -- 关联支付：必须在加购之后，且全链路在点击后的24小时之内
    LEFT JOIN pay_events p 
        ON c.user_id = p.user_id 
        AND p.pay_time > ca.cart_time 
        AND p.pay_time <= c.click_time + INTERVAL 24 HOURS
),
user_funnel_status AS (
    SELECT 
        user_id,
        1 AS is_click,
        MAX(CASE WHEN cart_time IS NOT NULL THEN 1 ELSE 0 END) AS is_cart,
        MAX(CASE WHEN pay_time IS NOT NULL THEN 1 ELSE 0 END) AS is_pay
    FROM funnel_stage
    GROUP BY user_id
)
SELECT 
    SUM(is_click) AS total_click_users,
    SUM(is_cart) AS total_cart_users,
    SUM(is_pay) AS total_pay_users,
    ROUND(SUM(is_cart) / SUM(is_click) * 100, 2) AS click_to_cart_rate_pct,
    ROUND(SUM(is_pay) / SUM(is_cart) * 100, 2) AS cart_to_pay_rate_pct,
    ROUND(SUM(is_pay) / SUM(is_click) * 100, 2) AS overall_conversion_rate_pct
FROM user_funnel_status;

输出结果预测

total_click_users	total_cart_users	total_pay_users	click_to_cart_rate_pct	cart_to_pay_rate_pct	overall_conversion_rate_pct
5	3	1	60.00%	33.33%	20.00%

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

在SparkSQL面试中，漏斗分析是一道高频且能拉开档次的题目。面试官不仅看你能不能写出 SQL，更看重你对性能、边界条件、大数据量下优化的理解。

1. 核心解题思路剖析

多表左关联（Left Join）定义阶段：
这是最直观的漏斗构建方式。我们将同一个日志表拆分成“点击”、“加购”、“支付”三个虚拟表，通过 user_id 进行 LEFT JOIN。
非等值连接（Non-Equi Join）控制时序与窗口：
- 时序控制：ca.cart_time > c.click_time 确保加购发生在点击之后；p.pay_time > ca.cart_time 确保支付发生在加购之后。
- 窗口控制：p.pay_time <= c.click_time + INTERVAL 24 HOURS 锁定了全链路必须在点击后 24 小时内完成。
用户去重与状态聚合：
一个用户可能在一天内有多次点击或加购。使用 GROUP BY user_id 并配合 MAX 函数，能够确保每个用户在漏斗的每个阶段只被计入一次（按人头去重统计）。

2. 面试官高频追问与性能优化

在大数据场景下，上述 LEFT JOIN 方案可能会面临数据倾斜和Shuffle开销过大的问题。面试官通常会问：“如果数据量达到百亿级，怎么优化这个 SQL？”

优化方案 A：利用窗口函数避免多表 Join（更高级的写法）

多表 Join 会引入多次 Shuffle。我们可以利用 Spark Window 函数在一轮 Scan 中完成时序标记，减少 Shuffle。

sql

WITH ranked_events AS (
    SELECT 
        user_id,
        behavior_type,
        action_time,
        -- 获取当前用户上一次点击的时间
        LAG(CASE WHEN behavior_type = 'click' THEN action_time END) 
            IGNORE NULLS OVER (PARTITION BY user_id ORDER BY action_time) AS last_click_time,
        -- 获取当前用户上一次加购的时间
        LAG(CASE WHEN behavior_type = 'add_to_cart' THEN action_time END) 
            IGNORE NULLS OVER (PARTITION BY user_id ORDER BY action_time) AS last_cart_time
    FROM user_behavior_log
)
-- 后续通过对 ranked_events 进行条件聚合，能够极大减少 Join 产生的磁盘与网络 IO。

优化方案 B：数据倾斜与广播连接（Broadcast Join）

如果行为日志表极其庞大，而“支付”或“加购”阶段的数据过滤后非常小，可以考虑将过滤后的轻量级表进行 Broadcast Hash Join，避免大表之间大范围的 Shuffle。
倾斜 Key 处理：电商场景中，热点商品或爬虫用户（如 user_id 为空或超级大 V）会导致数据倾斜。在 Join 前，必须过滤掉异常的 user_id，或对热点 user_id 进行加盐（Salt）打散。

优化方案 C：Spark 3.x 动态分区裁剪（DPP）与自适应查询执行（AQE）

提到 Spark 3.x 的 AQE（Adaptive Query Execution）。告诉面试官，在实际生产中，我们会开启 spark.sql.adaptive.enabled=true，让 Spark 在运行时自动合并 Shuffle 分区、处理倾斜连接（Optimize Skew Join），并自动将 SortMergeJoin 转换为 BroadcastHashJoin。

3. 生产环境的业务边界问题探讨

在面试中，主动提及以下业务边界问题，会显得你实际项目经验非常丰富：

跨天/跨周期问题：24小时的跨度意味着数据可能分布在不同的日期分区。在实际运行作业时，不能只读当天（dt=YYYY-MM-DD）的分区，通常需要读取 t 和 t+1 两天的分区数据，否则会漏掉跨天完成转化的用户。
归因问题（Attribution）：如果一个用户在24小时内点击了多次，加购了一次，最后支付了。应该归因给哪一次点击？上述 SQL 采用的是“任意一次符合时序和窗口的点击即可”。如果业务要求“末次归因”或“首次归因”，则需要在第一步过滤 click_events 时，使用 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY action_time DESC/ASC) 提前锁定唯一的触发起点。

右滑查看面试常问

播面