播面

面试真题：二级裂变销售额统计

题目描述：
已知商品分享记录表 share_records（包含分享者ID、接受者ID、商品ID、分享时间）和订单记录表 order_records（包含购买者ID、商品ID、下单时间、订单金额）。
请编写 SparkSQL 统计由于 A 分享给 B，B 又分享给 C 这种二级裂变关系带来的总销售额。

判定规则：

1. 时间先后顺序：A 分享给 B 的时间必须早于 B 分享给 C 的时间；C 购买的时间必须晚于或等于 B 分享给 C 的时间。
2. 归因原则（末次归因）：若 C 在购买前收到过多个人的分享，以购买前最后一次收到的分享作为链条的终点。
3. 排重控制：防止由于 B 收到多次分享而导致 C 的购买金额被重复计算。

1. 模拟数据集

表 1：share_records（商品分享记录表）

表 2：order_records（订单记录表）

预期输出结果

测试数据逻辑分析：

• C 购买 (100.00元)：购买前最后一次收到分享是 B -> C (12:00)，而 B 收到分享是 A -> B (10:00)。满足 10:00 < 12:00 <= 14:00，属于合法的 $A \rightarrow B \rightarrow C$ 二级裂变，计入 100.00 元。
• D 购买 (150.00元)：购买前最后一次收到分享是 B -> D (13:00)，而 B 收到分享是 A -> B (10:00)。满足 10:00 < 13:00 <= 15:00，属于合法的 $A \rightarrow B \rightarrow D$ 二级裂变，计入 150.00 元。
• Z 购买 (200.00元)：购买前收到 Y -> Z (08:00)，但 Y 收到 X -> Y 的时间是 09:00。由于分享顺序颠倒，不属于二级裂变，不计入。
• B 购买 (80.00元)：B 只经历了 $A \rightarrow B$ 的一级裂变，不属于二级裂变，不计入。
• 最终总额：$100.00 + 150.00 = 250.00$。

2. SparkSQL 解决方案

WITH last_share_attribution AS (
    -- 步骤 1：利用窗口函数进行末次归因，找出购买者在购买前最邻近的一次分享
    SELECT 
        o.buyer_id,
        o.product_id,
        o.order_time,
        o.amount,
        s.sharer_id AS direct_sharer,       -- 对应 B
        s.share_time AS direct_share_time,   -- 对应 B->C 的分享时间
        ROW_NUMBER() OVER(
            PARTITION BY o.buyer_id, o.product_id, o.order_time 
            ORDER BY s.share_time DESC
        ) AS rn
    FROM order_records o
    LEFT JOIN share_records s
      ON o.buyer_id = s.receiver_id
     AND o.product_id = s.product_id
     AND s.share_time <= o.order_time
),
valid_secondary_attribution AS (
    -- 步骤 2：筛选出符合末次归因的记录
    SELECT 
        buyer_id,
        product_id,
        amount,
        direct_sharer,
        direct_share_time
    FROM last_share_attribution
    WHERE rn = 1 AND direct_sharer IS NOT NULL
)
-- 步骤 3：使用 EXISTS 子查询判断 direct_sharer (B) 之前是否接受过 A 的分享（二级裂变判定）
-- 使用 EXISTS 可以完美避免由于 A1->B, A2->B 导致 B 收到多次分享而使 C 的金额被 Double Count
SELECT 
    COALESCE(SUM(v.amount), 0.0) AS total_secondary_sales
FROM valid_secondary_attribution v
WHERE EXISTS (
    SELECT 1 
    FROM share_records s_first
    WHERE s_first.receiver_id = v.direct_sharer
      AND s_first.product_id = v.product_id
      AND s_first.share_time < v.direct_share_time
);

3. 面试官视角：深度解析与 Spark 性能调优

在实际面试中，写出 SQL 只是第一步。面试官往往会针对你的方案进行追问，以下是针对该题目的深度剖析，帮助你在面试中展现超越普通开发者的架构思维。

解析一：为何使用 ROW_NUMBER() 进行归因？

• 业务考量：在裂变营销中，用户 C 可能会在朋友圈、微信群看到多个好友（B1, B2）分享的链接。如果不做归因，直接 Join 会导致订单金额被重复计算（Double Counting）。
• 技术实现：通过 ROW_NUMBER() OVER(PARTITION BY ... ORDER BY share_time DESC)，锁定了 C 下单前最后一次接触的分享，符合工业界常用的 Last-Touch Attribution（末次归因） 模型。

解析二：为何在步骤 3 中使用 EXISTS 而不是 JOIN？

• 防重防炸：如果 B 用户非常活跃，收到了来自 A1, A2, A3 的多次分享，如果此处使用 JOIN s_first ON s_first.receiver_id = v.direct_sharer，会导致 valid_secondary_attribution 的数据被“炸开”（1条变多条），最终 SUM(amount) 偏大。
• Spark 优化器（Optimizer）行为：
  • 在 Spark SQL 中，EXISTS 子查询会被催化剂优化器（Catalyst Optimizer）转化为 Left Semi Join（左半连接）。
  • Left Semi Join 在遇到右表匹配的第一条记录时就会立即返回，不会产生数据膨胀，且不需要把右表的所有数据拉取到内存中，性能远优于普通的 Inner Join 后再 Group By Key。

解析三：应对大数据量下的倾斜（Data Skew）优化

在电商大促期间，该逻辑如果直接运行，可能会遇到数据倾斜，面试官通常会问：“如果某个网红（A）分享的商品引发了百万级传播，你的 SQL 怎么优化？”

1. 广播连接（Broadcast Hash Join, BHJ）：
   如果 order_records 很大，但符合条件的 valid_secondary_attribution 过滤后比较小，可以强行广播：
   sql

   -- 在 Spark 中可以使用 hint 提示
   SELECT /*+ BROADCAST(v) */ SUM(v.amount) ...

2. 倾斜 Key 随机加盐（Salting）：
   如果分享表 share_records 中的 receiver_id 存在极热 Key（例如大V、官方机器人），可以对两边的 Join Key 进行加盐。
   • 实现思路：给 direct_sharer 拼接 _1, _2..._N 的随机数，同时将被关联的 s_first.receiver_id 复制 N 份（膨胀 N 份），从而将倾斜的数据分摊到不同的 Executor 上执行。