播面

面试题：计算剔除极值后的用户每日平均浏览量

1. 题目描述

在电商或内容平台上，网络爬虫、刷单团伙或极度活跃用户的异常行为（高频访问）会严重拉高大盘的平均浏览量（PV），导致该指标无法真实反映普通用户的活跃情况。

请编写 SparkSQL 语句，计算每日每个用户的浏览量，并在计算大盘每日用户平均浏览量时，剔除每日浏览量排名前 1% 的用户。

2. 样例数据

输入表：user_page_views（用户访问日志表）

注：为了便于演示计算过程，假设在实际计算中，通过百分比函数算出的“前 1% 极值”正好对应了各天 PV 最高的爬虫或极度活跃账号。

期望输出表

3. SparkSQL 解决方案

WITH user_daily_pv AS (
    -- Step 1: 聚合计算每日每个用户的总 PV
    SELECT 
        visit_date,
        user_id,
        COUNT(1) AS pv
    FROM 
        user_page_views
    GROUP BY 
        visit_date, 
        user_id
),
ranked_users AS (
    -- Step 2: 使用窗口函数计算每日 PV 的百分比排名
    -- PERCENT_RANK() 的值域为 [0, 1]，PV 降序排列时，PV 最大的用户百分比排名接近 0
    SELECT 
        visit_date,
        user_id,
        pv,
        PERCENT_RANK() OVER (PARTITION BY visit_date ORDER BY pv DESC) AS pv_percent_rank
    FROM 
        user_daily_pv
)
-- Step 3: 过滤掉排名前 1% 的用户（即百分比排名 <= 0.01 的用户），并计算每日平均 PV
SELECT 
    visit_date,
    ROUND(AVG(pv), 2) AS avg_pv
FROM 
    ranked_users
WHERE 
    pv_percent_rank > 0.01
GROUP BY 
    visit_date
ORDER BY 
    visit_date;

4. 面试考点与 SparkSQL 深度解析

在面试中，这道题看似简单，实则暗藏玄机。面试官不仅看你能不能写出 SQL，更看重你对窗口函数、数据倾斜、百分比计算原理的底层理解。

考点一：百分比函数的选择与原理 (PERCENT_RANK vs CUME_DIST vs ROW_NUMBER)

• PERCENT_RANK()：计算公式为 (rank - 1) / (total_rows - 1)。
  • 如果某天有 100 个用户，按 PV 从大到小排序，第 1 名的 PERCENT_RANK 为 (1-1)/(100-1) = 0。
  • 过滤条件 pv_percent_rank > 0.01 可以精准剔除排在最前面的 1% 用户。
• CUME_DIST()：计算公式为 小于等于当前值的行数 / 总行数。在降序排列时，表示 大于等于当前值的行数 / 总行数。
• 不建议使用 ROW_NUMBER() / COUNT()：虽然也可以用 ROW_NUMBER() OVER(...) / COUNT(1) OVER(...) 来手动算比例，但写起来冗长，且在大数据集下性能不如内置的 PERCENT_RANK 经过了 Catalyst 优化器的原生支持。

考点二：处理用户量极少时的边界情况

• 问题：如果某天总共只有 10 个用户，前 1% 是多少人？
  • 若用 PERCENT_RANK()，第 1 名的 rank 是 0，其值必定 <= 0.01，因此第 1 名（占 10%）会被无情过滤。
• 方案：在实际业务中，如果日活（DAU）很小，剔除 1% 可能会误伤正常用户。面试中可以主动向面试官提及：“为了防止在小样本量下误伤，可以加上限制条件，比如只有当日活大于 1000 时才启用 1% 过滤，否则不过滤。” 这种业务 sense 会让面试官眼前一亮。

考点三：SparkSQL 性能优化（数据倾斜与 Shuffle）

• 物理执行计划分析：
  在 ranked_users 步骤中，OVER (PARTITION BY visit_date ORDER BY pv DESC) 会触发 Shuffle（Spark 会按照 visit_date 进行 Hash 分区）。
• 数据倾斜风险：
  如果某一天（例如双十一当天）的数据量呈现爆发式增长，而其他日期数据量很小，就会导致承载双十一当天数据的 Executor 发生数据倾斜（Data Skew），甚至 OOM（内存溢出）。
• 优化对策：
  1. 两阶段聚合（针对极度倾斜）：如果某一天的用户量极其庞大，可以考虑先对 user_id 进行加盐（Salt）局部聚合，再全局聚合。但在本题中，第一步已经是 GROUP BY visit_date, user_id，已经极大地压缩了数据量（从“页面浏览明细级”降到了“用户天级”）。
  2. 避免在窗口函数中使用全局排序：千万不能不写 PARTITION BY。不写会导致所有数据 Shuffle 到单个 Reduce 分区进行全局排序，直接挂掉。