SparkSQL 面试题：2025年10月活跃用户每周平均活跃天数统计

1. 题目背景与要求

题目描述：
给定一张 2025 年 10 月的用户日活表 user_active_log，记录了用户每天的活跃情况。请编写 SparkSQL 统计在该月活跃过的每个用户平均每周活跃多少天。

具体要求：

1. 仅统计 2025 年 10 月份（2025-10-01 至 2025-10-31）的数据。
2. 需要处理跨周问题。自然周定义为：周一至周日为一周。使用 SparkSQL 内置函数将日期转换为年-周。
3. 如果某周跨月（例如 2025-10-01 是周三，属于 2025 年第 40 周，该周的周一和周二在 9 月），仅统计该用户在 10 月份该周内的活跃天数。
4. 平均每周活跃天数 = 该用户在 10 月的总活跃天数 / 该用户在 10 月活跃过的周数（去重），结果保留 2 位小数。

2. 示例数据

输入表：user_active_log

期望输出结果

• 数据分析说明：
  • user_01 在 10 月活跃了 4 天，分布在第 40 周和第 41 周（共 2 周），平均每周活跃 $4 / 2 = 2.00$ 天。
  • user_02 在 10 月活跃了 5 天，分布在第 41、42、44 周（共 3 周），平均每周活跃 $5 / 3 \approx 1.67$ 天。

3. SparkSQL 解决方案

WITH filtered_active AS (
    SELECT 
        user_id,
        active_date,
        -- 使用 weekofyear 获取 ISO 标准周（周一为第一天）
        -- 如果跨年，weekofyear 会返回 1，为防止跨年周冲突，结合年份使用
        CONCAT(YEAR(active_date), '_', weekofyear(active_date)) AS year_week
    FROM user_active_log
    WHERE active_date >= '2025-10-01' AND active_date <= '2025-10-31'
)
SELECT 
    user_id,
    COUNT(DISTINCT active_date) AS total_active_days,
    COUNT(DISTINCT year_week) AS active_weeks,
    ROUND(COUNT(DISTINCT active_date) / COUNT(DISTINCT year_week), 2) AS avg_weekly_active_days
FROM filtered_active
GROUP BY user_id;

4. 深度剖析与面试加分项

在面试中，仅仅写出上述 SQL 是不够的。面试官往往会针对你的实现方案进行深度追问。以下是针对该题目的 SparkSQL 技术细节分析和调优方向：

① 日期到周转换的陷阱（Week of Year）

• 陷阱： 如果直接使用 weekofyear(active_date)，在跨年时（例如 12 月 31 日和 1 月 1 日在同一周）可能会出现周数计算不一致。
• 解答： 本题中虽然限定了 2025 年 10 月，但在生产环境中，构建 year_week 的唯一标识（如 CONCAT(YEAR(active_date), '_', weekofyear(active_date))）是最佳实践。
• 标准说明： Spark 的 weekofyear 基于 ISO 8601 标准，周一是一周的第一天。如果面试官要求“周日”作为一周的第一天，则需要使用 date_format(active_date, 'u') 或 pmod(datediff(active_date, 'origin_sunday'), 7) 进行自定义转换。

② 数据倾斜与性能优化

在实际生产中，用户日活表（DAU）的数据量通常是百亿级，直接使用 COUNT(DISTINCT) 极易引发数据倾斜和 OOM（内存溢出）。

• 优化方案一：两阶段聚合（消减 Distinct）
  COUNT(DISTINCT) 会将所有相同 key 的数据分发到同一个 Reducer 上进行去重，导致单点瓶颈。可以使用两阶段聚合来优化：

  sql

  -- 阶段一：先对 user_id, active_date, year_week 进行去重（规避 Count Distinct）
  WITH detail_distinct AS (
      SELECT user_id, active_date, year_week
      FROM filtered_active
      GROUP BY user_id, active_date, year_week
  )
  -- 阶段二：进行常规聚合
  SELECT 
      user_id,
      COUNT(active_date) AS total_active_days,
      COUNT(DISTINCT year_week) AS active_weeks, -- 此时 year_week 数据量已大幅减少
      ROUND(COUNT(active_date) / COUNT(DISTINCT year_week), 2) AS avg_weekly_active_days
  FROM detail_distinct
  GROUP BY user_id;

• 优化方案二：使用 HyperLogLog（近似估算）
  如果业务对精确度要求不是 100% 绝对精准（如画像分析、大盘看板），可以向面试官提出使用 SparkSQL 的近似去重函数 approx_count_distinct(col, rsd)，利用 HLL 算法大幅提升计算速度并节省内存。

③ 跨月边界的业务理解

• 题目要求“在 10 月活跃过的用户平均每周活跃天数”。对于 10 月 1 日（周三，第 40 周），其周一（9 月 29 日）和周二（9 月 30 日）活跃的数据不应被计入。
• 我们的 SQL 先通过 WHERE active_date >= '2025-10-01' ... 过滤了非 10 月数据，再提取 year_week。这样，对于第 40 周，分母（active_weeks）仍会计为 1，但分子（total_active_days）只会计入该周在 10 月份内的实际活跃天数，完全符合“在 10 月份内的活跃情况”的业务逻辑。