SparkSQL 面试题：计算用户最大连续登录天数

1. 题目描述

给定用户登录日志表 user_login_log（包含 user_id 和 login_date），计算每个用户在历史记录中的最大连续登录天数。

2. 样例数据

输入数据：user_login_log

数据特点说明：

• 用户 A 在 2023-10-02 登录了两次（去重测试）。
• 用户 A 有两段连续登录：10-01 至 10-03（3天），10-05 至 10-06（2天），最大连续天数为 3。
• 用户 B 每天登录不连续，最大连续天数为 1。
• 用户 C 连续登录 4 天，最大连续天数为 4。

期望输出结果

3. SparkSQL 核心解法

WITH temp_distinct_login AS (
    -- Step 1: 去除同用户同天重复登录的数据，并将日期转为 DATE 类型
    SELECT DISTINCT 
        user_id, 
        CAST(login_date AS DATE) AS login_date
    FROM user_login_log
),
temp_row_number AS (
    -- Step 2: 按用户分组，按日期升序排序，生成行号 (rn)
    SELECT 
        user_id,
        login_date,
        ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY login_date) AS rn
    FROM temp_distinct_login
),
temp_consecutive_groups AS (
    -- Step 3: 日期减去行号得到基准日期 (base_date)，相同基准日期的代表是连续登录
    SELECT 
        user_id,
        DATE_SUB(login_date, rn) AS base_date,
        COUNT(1) AS consecutive_days
    FROM temp_row_number
    GROUP BY user_id, DATE_SUB(login_date, rn)
)
-- Step 4: 取出每个用户最大的连续登录天数
SELECT 
    user_id,
    MAX(consecutive_days) AS max_consecutive_days
FROM temp_consecutive_groups
GROUP BY user_id;

4. 核心解题思路剖析（面试官心算逻辑）

此题是大数据面试中极其高频的“连续性问题”。核心思想是利用“等差数列”的差值恒定原理。

第一步：去重（temp_distinct_login）

同一天内用户可能会多次登录。如果不去重，窗口函数 ROW_NUMBER() 会为同一天的多次登录生成不同的行号，导致后续差值计算错乱。

第二步：引入行号（temp_row_number）

使用窗口函数 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)。

• 连续的日期：10-01, 10-02, 10-03
• 对应的行号：1, 2, 3

第三步：差值分组（temp_consecutive_groups）

这是本题的灵魂所在。

• 用 login_date 减去行号 rn（单位为天）。
• 观察计算结果：
  • 2023-10-01 - 1 = 2023-09-30
  • 2023-10-02 - 2 = 2023-09-30
  • 2023-10-03 - 3 = 2023-09-30
  • 发生中断后：2023-10-05 - 4 = 2023-10-01
• 结论：只要日期是连续的，login_date - rn 得到的 base_date 必然是同一个值。因此，GROUP BY user_id, base_date 并执行 COUNT(1) 即可得到每一段连续登录的天数。

第四步：求最大值

最后通过 GROUP BY user_id 结合 MAX(consecutive_days) 汇聚出每个用户的历史最长连续天数。

5. 面试加分项与 Spark 性能优化

在面试中，如果能主动提及以下优化和边界问题，能极大提升面试评级：

1. 数据倾斜（Data Skew）处理：

   • 问题：ROW_NUMBER() OVER (PARTITION BY user_id ...) 会导致相同 user_id 的所有数据被 Shuffle 到同一个 Reduce 分区。如果某些“大V”用户登录极为频繁，会导致单点瓶颈（OOM 或长尾作业）。
   • 解决方案：如果是超大活跃用户，可以先在第一步去重时加盐（Salt）打散，或者在第一步通过 GROUP BY user_id, login_date 进行预聚合。对于极其严重的倾斜，可单独过滤出热点用户进行特殊处理。

2. 日期函数的兼容性：

   • 在 SparkSQL 中，DATE_SUB(date, days) 要求第一个参数必须是 DATE 或 TIMESTAMP 类型，第二个参数必须是 INT。在书写 SQL 时显式进行 CAST(login_date AS DATE) 能够体现编码的严谨性，避免隐式转换报错。

3. 替代解法（Lead/Lag 思想）：

   • 面试官可能会问：“如果不允许用 ROW_NUMBER，怎么做？”
   • 答：可以使用 LAG(login_date, 1) OVER (PARTITION BY user_id ORDER BY login_date) 获取上一次登录日期。计算当前日期与上一次日期的差值，如果 diff > 1 说明连续中断，标记为 1，否则标记为 0。然后通过累加这个标志位构造分组 ID。该方法在处理“允许中断 $N$ 天以内仍算连续”的变种题时更为通用。