播面

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

1. 题目背景与业务场景

在用户行为分析中，网络爬虫、刷单账号或平台超级活跃用户的行为数据往往会极大地拉高大盘指标（如平均日PV）。为了真实反映普通用户的活跃度，数据分析师通常需要剔除每日浏览量最高的前 1% 的“异常”用户，再计算每日的平均用户浏览量。

2. 候选数据集

表名：user_daily_pv（用户每日浏览量聚合表）

SparkSQL 解决方案

在实际面试中，给出标准解法（窗口函数）是及格线，而给出高并发/大数据量下的优化解法（近似百分位数）则是拿 Offer 的关键。

解法一：标准窗口函数法（使用 PERCENT_RANK）

利用窗口函数 PERCENT_RANK() 对每日用户的 PV 进行降序百分比排名，排除排在最前 1%（即百分比值小于 0.01）的用户。

WITH ranked_users AS (
    SELECT 
        user_id,
        log_date,
        pv,
        PERCENT_RANK() OVER (PARTITION BY log_date ORDER BY pv DESC) as rank_pct
    FROM 
        user_daily_pv
)
SELECT 
    log_date,
    ROUND(AVG(pv), 2) as avg_pv,
    COUNT(user_id) as active_user_count -- 剔除后的有效用户数
FROM 
    ranked_users
WHERE 
    rank_pct > 0.01 -- 过滤掉每日 PV 排名前 1% 的用户
GROUP BY 
    log_date
ORDER BY 
    log_date;

解法二：Spark 高性能优化法（使用 approx_percentile + Broadcast Join）

痛点分析： 解法一在千万级/亿级数据量下，PARTITION BY log_date ORDER BY pv 会触发全局排序（Sort），导致大量数据 Shuffle 到少数 Reduce 节点，极易引发 OOM（内存溢出） 或 数据倾斜。

优化方案：

1. 先通过 approx_percentile 算出每天 99% 分位数对应的 PV 阈值（该操作是聚合操作，速度极快）。
2. 将此阈值表作为小表，与原表进行 Join（若每天数据量不大，阈值表极小，Spark 会自动采用 Broadcast Hash Join 避免 Shuffle 重分区）。
3. 过滤掉 PV 大于该阈值的数据，最后计算平均值。

WITH daily_threshold AS (
    -- 计算每日 99% 分位数的 PV 临界值
    SELECT 
        log_date,
        approx_percentile(pv, 0.99) AS pv_limit_99
    FROM 
        user_daily_pv
    GROUP BY 
        log_date
)
SELECT 
    a.log_date,
    ROUND(AVG(a.pv), 2) as avg_pv,
    COUNT(a.user_id) as active_user_count
FROM 
    user_daily_pv a
INNER JOIN 
    daily_threshold t
ON 
    a.log_date = t.log_date
WHERE 
    a.pv < t.pv_limit_99 -- 过滤掉大于等于 99% 分位数的用户
GROUP BY 
    a.log_date
ORDER BY 
    a.log_date;

SparkSQL 核心考点与深度解析

1. 为什么选择 PERCENT_RANK 而不是 ROW_NUMBER？

• ROW_NUMBER() 返回的是绝对行号。如果在不同日期（Log_date）下的用户基数不同（例如 10-01 有 100 万用户，10-02 有 10 万用户），使用绝对行号很难统一过滤前 1% 的比例。
• PERCENT_RANK() 计算公式为：(rank - 1) / (total_rows - 1)，其返回值区间为 [0, 1]。这使得我们能极其精准地过滤特定百分比的数据，而无需关心当天的总样本量。

2. 区分 PERCENT_RANK 与 CUME_DIST

在面试中，面试官可能会追问这两个窗口函数的区别：

• PERCENT_RANK()：基于排名的百分比，首行一定是 0，末行一定是 1。
• CUME_DIST()（累积分布）：计算小于或等于当前值的行数占总行数的比例。首行大于 0，末行一定是 1。如果是剔除 Top 1%，使用 PERCENT_RANK() > 0.01 或 CUME_DIST() < 0.99 均可实现，但 PERCENT_RANK 配合 DESC 排序逻辑在语意上更直观。

3. SparkSQL 性能调优（数据倾斜与 Shuffle 优化）

当被问及“如果数据量达到百亿级，你的 SQL 该如何优化”时，可以从以下三个维度作答：

• 避免全局排序：窗口函数带有 ORDER BY 时，Spark 会强制进行 RangePartitioning。如果单日数据量过大，单个 Executor 的内存将面临巨大压力。
• 使用近似算法：SparkSQL 的 approx_percentile(col, percentage, [accuracy]) 使用了 Greenwald-Khanna 算法，可以在不进行全局精确排序的情况下，以极低的内存消耗和极快的速度估算出分位数。
• 广播连接（Broadcast Join）：在解法二中，daily_threshold 每天只有一行数据（代表当天 99% 线的 PV 值），这是一个典型的“大表 Join 超极小表”场景。Spark 默认开启 spark.sql.autoBroadcastJoinThreshold（默认 10MB），会将阈值表广播到所有 Executor 节点，在 Map 端直接完成过滤，消除了最耗时的 Shuffle 阶段。