播面

面试题：找出过去 1 年中，在购买“美妆”品类时，有超过 80% 的消费金额都集中在同一个品牌的用户 ID

1. 示例数据

为了便于演示和验证，假设当前系统时间为 2023-12-31（查询中我们会使用动态时间函数 add_months(current_date(), -12)，以下数据均在“过去 1 年”的时间范围内）。

用户订单表 user_orders

数据分析说明：

• 用户 101：在美妆品类的总消费为 $1000 + 800 + 200 = 2000$（数码品类 O4 排除）。其中“雅诗兰黛”消费 $1800$，占比 $1800 / 2000 = 90\% > 80\%$。（符合条件）
• 用户 102：美妆总消费 $1100$，单一品牌最高消费 $600$（兰蔻），占比 $600 / 1100 = 54.5\% < 80\%$。（不符合条件）
• 用户 103：订单 O8 超过 1 年（假设当前是 2023-12-31），过去 1 年内美妆总消费 $2000$（香奈儿），占比 $2000 / 2000 = 100\% > 80\%$。（符合条件）
• 用户 104：美妆总消费 $1000$，“迪奥”消费 $900$，占比 $900 / 1000 = 90\% > 80\%$。（符合条件）

2. SparkSQL 核心解法

WITH beauty_sales AS (
    -- Step 1: 过滤出过去1年中“美妆”品类的订单
    SELECT 
        user_id,
        brand,
        amount
    FROM user_orders
    WHERE category = '美妆'
      AND order_date >= add_months(current_date(), -12)
),
user_brand_summary AS (
    -- Step 2: 按用户和品牌分组，计算每个用户在每个品牌上的消费总额，以及该用户在美妆品类的总消费额
    SELECT 
        user_id,
        brand,
        SUM(amount) AS brand_amount,
        SUM(SUM(amount)) OVER(PARTITION BY user_id) AS total_beauty_amount
    FROM beauty_sales
    GROUP BY user_id, brand
)
-- Step 3: 筛选出单一品牌消费占比超过 80% 的用户 ID
SELECT DISTINCT
    user_id
FROM user_brand_summary
WHERE brand_amount / total_beauty_amount > 0.8;

3. 面试官视角：考点深度分析

此题是典型的大厂数仓与数据分析面试题，表面上考察基础的聚合，实际上暗含了多个 SparkSQL 的核心考点：

考点一：时间窗口过滤的动态实现

• 面试通关点：避免在 SQL 中写死时间（如 where order_date > '2022-10-01'）。
• Spark 核心函数：使用 current_date() 获取当前系统日期，结合 add_months(..., -12) 或 date_sub(..., 365) 动态计算一年前的边界。这展示了代码的线上生产环境可用性。

考点二：窗口函数与 Group By 的混用（高级 SQL 技巧）

在 user_brand_summary 步骤中，使用了：

SUM(SUM(amount)) OVER(PARTITION BY user_id)

• 原理解析：内层的 SUM(amount) 是配合 GROUP BY user_id, brand 进行的普通聚合，计算的是每个用户在每个品牌上的消费额；外层的 SUM(...) OVER(PARTITION BY user_id) 是窗口函数，它在聚合后的数据集上运行，计算的是*该用户所有品牌（即美妆总额）*的汇总。
• 面试加分：很多候选人会写两个独立的 GROUP BY 子查询再进行 JOIN。利用窗口函数直接在一步中完成“细粒度（用户-品牌）”与“粗粒度（用户）”的指标计算，避免了昂贵的 JOIN 操作，能极大地提升 Spark 任务的执行效率。

考点三：Spark 性能优化与数据倾斜（高频追问）

当面试官看到你写出上述 SQL 后，通常会进行追问：“如果某些头部用户有海量的美妆购买记录，导致数据倾斜怎么办？”

• 应对方案：
  1. 两阶段聚合（放盐法）：如果是因为某个 user_id 的购买记录极多导致 GROUP BY 倾斜，可以先给 user_id 加上随机前缀（如 concat(user_id, '_', cast(rand()*10 as int))）进行局部聚合，再去掉前缀进行全局聚合。
  2. 过滤无用大键：在第一步过滤中，确保排除了无意义的恶意刷单账号或爬虫账号。
  3. Broadcast Join（若涉及维度表关联）：如果后续需要关联用户维度表，对小表使用 broadcast 提示，避免 Shuffle 引起的倾斜。