面试题目：流失潜在VIP用户占比分析

题目背景：
在电商或内容运营中，我们需要识别出曾经的高频活跃用户，但近期处于流失状态的群体（即“沉默VIP”），以便进行精准召回。

题目要求：
请编写 SparkSQL 语句，找出曾经连续 3 个月、每个月都至少消费过一次，但在最近 30 天内没有任何消费和登录记录的用户占总用户的比例。
注：假设当前分析的基准日期（今天）为 2023-11-01，最近 30 天指 2023-10-02 至 2023-11-01（含）。

数据准备

表 1：用户消费记录表 (user_consumption)

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

SparkSQL 核心解题思路

本题是典型的“连续区间判定（Gaps and Islands）”与“排除特定条件（Exclusion）”结合的综合指标计算题。

1. 确定分母（总用户数）：
   合并消费表和登录表中的所有独立 user_id，作为大盘总用户。
2. 判定“连续 3 个月消费”：
   • 首先，将用户的消费日期截断到月度（yyyy-MM-01），并去重，确保每个用户每个月只有一条记录。
   • 使用窗口函数 ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY month) 对消费月份进行排序。
   • 核心技巧（等差数列法）： 用当前月份减去行号（使用 add_months(month, -rn)）。如果是连续月份，减去行号后会得到相同的基准月。
   • 按照 user_id 和 基准月 进行分组（GROUP BY），如果某个分组的数量 COUNT(1) >= 3，说明该用户曾经连续 3 个月消费过。
3. 判定“最近 30 天无活跃（消费和登录）”：
   • 筛选出在 2023-10-02 到 2023-11-01 期间有消费或登录行为的用户名单（活跃用户名单）。
   • 用第 2 步筛选出的 VIP 候选人，左排除（LEFT ANTI JOIN 或 LEFT JOIN IS NULL） 掉最近 30 天的活跃用户，剩下的即为“流失潜在 VIP”。
4. 计算占比：
   将目标用户数除以总用户数，保留 4 位小数。

SparkSQL 完整代码

WITH all_users AS (
    -- 1. 获取系统内所有的独立用户（分母）
    SELECT user_id FROM user_consumption
    UNION
    SELECT user_id FROM user_login
),

user_monthly_consume AS (
    -- 2.1 提取每个用户有消费的月份，并去重
    SELECT DISTINCT 
        user_id,
        date_format(consume_date, 'yyyy-MM-01') AS consume_month
    FROM user_consumption
),

consecutive_groups AS (
    -- 2.2 使用窗口函数计算行号，并通过 add_months 构造差值分组键
    SELECT 
        user_id,
        consume_month,
        add_months(cast(consume_month as date), - row_number() over (partition by user_id order by consume_month)) AS base_month
    FROM user_monthly_consume
),

vip_candidates AS (
    -- 2.3 找出曾经连续3个月及以上有消费的用户
    SELECT DISTINCT user_id
    FROM consecutive_groups
    GROUP BY user_id, base_month
    HAVING count(1) >= 3
),

recent_active_users AS (
    -- 3. 找出最近30天（2023-10-02 至 2023-11-01）有消费或登录的用户
    SELECT user_id 
    FROM user_consumption 
    WHERE consume_date >= date_sub(cast('2023-11-01' as date), 30)
    
    UNION
    
    SELECT user_id 
    FROM user_login 
    WHERE login_date >= date_sub(cast('2023-11-01' as date), 30)
),

target_users AS (
    -- 4. 曾经是VIP，但最近30天没有任何活跃的用户（使用 LEFT ANTI JOIN 优化性能）
    SELECT c.user_id
    FROM vip_candidates c
    LEFT ANTI JOIN recent_active_users r ON c.user_id = r.user_id
)

-- 5. 计算占比
SELECT 
    round(count(t.user_id) / count(a.user_id), 4) AS silent_vip_ratio
FROM all_users a
LEFT JOIN target_users t ON a.user_id = t.user_id;

面试官考点剖析与 Spark 优化建议

1. 连续性问题的通用解法：等差数列法（Difference-based Grouping）

• 原理：如果一系列数字/日期是连续的，那么它们与一个递增的序列（如 ROW_NUMBER）的差值将是恒定的。
• Spark 适配：在 SparkSQL 中，日期操作函数 add_months 或 date_sub 非常适合用来做这个减法。需要注意将字符串显式转换为 date 类型以确保兼容性。

2. 大数据量下的 exclusion（排除）性能优化

• 普通写法：很多人喜欢用 NOT IN 或 NOT EXISTS。
• Spark 优化：在 Spark 中，NOT IN 如果遇到 NULL 值会有坑，且执行计划不够优化。
• 推荐方案：使用 LEFT ANTI JOIN。Spark 优化器对 Anti Join 有非常好的支持（如 Broadcast Nested Loop Join 或 Shuffle Hash Join），能直接过滤掉右表匹配的行，无需在内存中缓存右表的所有 Key，性能通常优于 LEFT JOIN + IS NULL。

3. 数据倾斜（Data Skew）防范

• 在 all_users 的 UNION 以及 consecutive_groups 的 row_number() over (partition by user_id ...) 过程中，如果某些大热 user_id（如爬虫或公共账号）产生海量日志，会导致严重的数据倾斜。
• 应对策略：
  • 在进行 row_number 之前，先进行 DISTINCT 去重（如代码中 user_monthly_consume 阶段已经按 user_id + month 去重），这极大地减少了单个 user_id 的输入数据量，从而规避了窗口函数阶段的倾斜。