基于本文回答

播面 播面

文图音视，全方位拆解八股文
0
评论
请从左侧选择一个分类

找出注册时间超过 30 天、且在过去 30 天内没有过任何登录，但今天突然登录并产生购买行为的用户 ID 列表

1. 面试题：回流且突然消费用户分析

题目背景：
在电商或App运营中，“流失回流用户”是一个核心关注群体。本题要求找出注册时间超过 30 天、且在过去 30 天内（不含今天）没有任何登录行为，但今天突然登录并产生购买行为的高价值回流用户。

假设“今天”是 2023-10-31。

2. 模拟数据准备

为了方便验证，我们构建以下三张表的数据。

表 1：user_info (用户基本信息表)

user_id (用户ID) register_date (注册日期) 备注
101 2023-08-01 注册 > 30天
102 2023-10-15 注册 <= 30天（不符合）
103 2023-08-15 注册 > 30天
104 2023-08-20 注册 > 30天
105 2023-08-25 注册 > 30天

表 2：user_login_log (用户登录日志表)

user_id (用户ID) login_date (登录日期) 备注
101 2023-08-01 注册当天登录
101 2023-10-31 今天登录（过去30天无登录）
102 2023-10-15 注册当天登录
102 2023-10-31 今天登录
103 2023-10-15 过去30天内有登录（10-15）
103 2023-10-31 今天登录
104 2023-10-31 今天登录，但今天无购买（见下表）
105 2023-10-10 过去30天内有登录（10-10）
105 2023-10-31 今天登录且购买

表 3：user_order_log (用户订单表)

user_id (用户ID) order_date (订单日期) amount (金额) 备注
101 2023-10-31 99.0 今天购买
102 2023-10-31 50.0 今天购买（但注册未满30天）
103 2023-10-31 120.0 今天购买（但过去30天登录过）
104 2023-08-21 10.0 历史购买，今天未购买
105 2023-10-31 200.0 今天购买（但过去30天登录过）

预期输出结果

根据上述规则，只有 101 满足所有条件：

  • 注册时间 2023-08-01（距离今天 > 30天）
  • 过去30天内（10-01 至 10-30）无登录
  • 今天（10-31）有登录且有购买
user_id
101

3. SparkSQL 解决方案

在实际生产中，直接使用硬编码日期不具备通用性。我们使用 SparkSQL 内置的时间函数 current_date()（或在测试时使用特定的日期占位符）。

以下提供两种解法：解法一（清晰易懂，适合初中级） 和 解法二（极致性能，适合高级/调优面试）。

解法一：多表关联与排他（CTE 结构法）

此方法逻辑极其清晰，通过 LEFT JOIN 的 IS NULL 来做排除法。

sql 
-- 假设当前日期为 '2023-10-31'，实际生产中使用 current_date()
WITH today_const AS (
    SELECT CAST('2023-10-31' AS DATE) AS today
),

-- 1. 筛选注册超过30天的用户
eligible_users AS (
    SELECT u.user_id
    FROM user_info u, today_const t
    WHERE u.register_date < date_sub(t.today, 30)
),

-- 2. 筛选过去30天内（不含今天）登录过的用户（用于排除）
recent_active_users AS (
    SELECT DISTINCT l.user_id
    FROM user_login_log l, today_const t
    WHERE l.login_date >= date_sub(t.today, 30)
      AND l.login_date < t.today
),

-- 3. 今天登录过的用户
today_login_users AS (
    SELECT DISTINCT l.user_id
    FROM user_login_log l, today_const t
    WHERE l.login_date = t.today
),

-- 4. 今天有购买行为的用户
today_buyer_users AS (
    SELECT DISTINCT o.user_id
    FROM user_order_log o, today_const t
    WHERE o.order_date = t.today
)

-- 5. 拼装最终结果
SELECT eu.user_id
FROM eligible_users eu
INNER JOIN today_login_users tlu ON eu.user_id = tlu.user_id
INNER JOIN today_buyer_users tbu ON eu.user_id = tbu.user_id
LEFT JOIN recent_active_users rau ON eu.user_id = rau.user_id
WHERE rau.user_id IS NULL; -- 排除近30天活跃过的用户

解法二：聚合过滤法（高性能，推荐）

在 SparkSQL 中，过多的 Join 会产生大量的 Shuffle。我们可以先对登录表进行一次扫描和聚合（Group By），利用 HAVING 子句里的条件判断来代替复杂的 Join 排除，以此大幅度提升执行效率。

sql 
WITH today_const AS (
    SELECT CAST('2023-10-31' AS DATE) AS today
),

-- 聚合登录表，找出“过去30天未登录、但今天登录”的用户
login_filtered_users AS (
    SELECT l.user_id
    FROM user_login_log l, today_const t
    WHERE l.login_date >= date_sub(t.today, 30) -- 只扫描近30天到今天的数据
    GROUP BY l.user_id, t.today
    HAVING SUM(CASE WHEN l.login_date = t.today THEN 1 ELSE 0 END) > 0 -- 今天登录了
       AND SUM(CASE WHEN l.login_date < t.today THEN 1 ELSE 0 END) = 0  -- 过去30天（不含今天）没登录过
)

-- 关联注册表与今天购买表
SELECT u.user_id
FROM user_info u
INNER JOIN login_filtered_users lfu ON u.user_id = lfu.user_id
INNER JOIN user_order_log o ON u.user_id = o.user_id
-- 隐式隐式笛卡尔积常量关联
CROSS JOIN today_const t 
WHERE u.register_date < date_sub(t.today, 30) -- 注册 > 30天
  AND o.order_date = t.today                  -- 今天有购买
GROUP BY u.user_id;                           -- 防止购买多次产生重复数据

4. 关键面试考点与底层深度剖析

如果你能在回答完 SQL 之后，主动向面试官补充以下技术细节，你的面试评价将直接提升到 Senior/Lead 级别。

考点一：时间函数的陷阱

  • 边界问题：过去30天内（不含今天），在写 SQL 时一定要注意区间是 [today - 30, today - 1]。
    • 在解法一中，我们通过 login_date >= date_sub(today, 30) AND login_date < today 严格限定了不包含今天。
    • date_sub(date, n) 返回的是 date 往前推 n 天的日期。

考点二：Spark 性能调优——如何避免数据倾斜与过多的 Shuffle

  1. Reduce Join (Shuffle-less) 思想：
    • 解法一使用了多个 JOIN（包含 LEFT JOIN ... IS NULL 和多个 INNER JOIN）。在 Spark 中，这会引发多次 Shuffle Exchange，容易因为某些回流用户登录过于频繁导致数据倾斜。
    • 解法二通过 GROUP BY + HAVING (SUM(CASE WHEN...)) 将“近30天未登录”与“今天登录”合并到了一次 Map 端聚合中，极大地减少了 Shuffle 的数据量。
  2. 广播连接 (Broadcast Hash Join)：
    • 这里的 today_const 只是一个单行单列的临时表，Spark 优化器（Catalyst）会自动对其进行 Broadcast，避免了笛卡尔积带来的大 Shuffle。
    • 在实际线上，user_info 通常是一个超大维度表。如果过滤后的回流用户 login_filtered_users 结果集很小（通常回流用户占比极低），在与 user_info 关联时，可以使用 /*+ BROADCAST(lfu) */ 强制触发广播连接，避免大表大 Shuffle。

考点三：窗口函数与 GROUP BY 的抉择

  • 有些候选人可能会尝试用 ROW_NUMBER() 或 LAG() 等窗口函数来计算用户上一次的登录时间与本次的差值。
  • 面试官避坑指南：窗口函数需要对整个用户登录日志进行 PARTITION BY user_id ORDER BY login_date，这会导致全局排序，在大规模数据集下极易发生 OOM (Out Of Memory)。而在本题中，我们只需要知道近30天的登录状态，因此采用 GROUP BY + CASE WHEN 的局部聚合性能远优于窗口函数。
00:00
00:00