播面

面试真题：计算剔除头部异常值后的用户每日平均浏览量

1. 题目背景

在互联网数据分析中，网络爬虫、刷单账号或极度活跃的超级用户（Outliers）会极大地拉高均值，导致大盘指标失真。为了看清大盘真实用户的活跃情况，通常需要进行“去极值”处理。

本题要求编写 SQL 查询，计算每日用户的平均浏览量，但在计算前，需要剔除每日浏览量排名前 1% 的用户。

2. 示例数据

用户每日浏览明细表：user_page_views

3. 期望输出结果（以剔除每日 Top 20% 异常值演示效果）

注：因示例数据量较小，若剔除 1% 无法排除任何用户（5个用户中 1% 不足 1 人）。以下展示若剔除每日 Top 20%（即每日浏览量最高的 1 个用户，11-01剔除U005，11-02剔除U004）后的计算结果：

(计算逻辑：11-01 剩余 U001~U004，平均值为 (10+15+12+8)/4 = 11.25)

4. SQL 解答

方案一：使用窗口函数 ROW_NUMBER() 与 COUNT() (通用性强，推荐)

WITH ranked_users AS (
    SELECT 
        user_id,
        visit_date,
        page_views,
        ROW_NUMBER() OVER (PARTITION BY visit_date ORDER BY page_views DESC) as pv_rank,
        COUNT(1) OVER (PARTITION BY visit_date) as total_users
    FROM user_page_views
)
SELECT 
    visit_date,
    ROUND(AVG(page_views), 2) as avg_page_views
FROM ranked_users
WHERE pv_rank > total_users * 0.01  -- 剔除排名前 1% 的用户
GROUP BY visit_date
ORDER BY visit_date;

方案二：使用窗口百分比函数 PERCENT_RANK() (代码更简洁)

WITH ranked_users AS (
    SELECT 
        user_id,
        visit_date,
        page_views,
        PERCENT_RANK() OVER (PARTITION BY visit_date ORDER BY page_views DESC) as rank_pct
    FROM user_page_views
)
SELECT 
    visit_date,
    ROUND(AVG(page_views), 2) as avg_page_views
FROM ranked_users
WHERE rank_pct >= 0.01  -- 剔除排名前 1% 的用户（百分比在 0 到 0.01 之间的用户）
GROUP BY visit_date
ORDER BY visit_date;

5. 核心考点与深度解析

① 为什么不能用普通的 LIMIT 或全局 WHERE 过滤？

• 分组过滤问题：本题要求剔除每日排名前 1% 的用户。爬虫和异常用户的活跃时间不同，每天的 Top 1% 阈值也不同。因此必须使用窗口函数（PARTITION BY visit_date）在每天的维度上独立计算排名和总量，而不能用全局的 ORDER BY ... LIMIT。

② 核心窗口函数对比

• ROW_NUMBER() + COUNT()：
  • 原理：先用 ROW_NUMBER() OVER(PARTITION BY visit_date ORDER BY page_views DESC) 给每天的用户按浏览量从大到小标上序号（1, 2, 3...），再用 COUNT(1) OVER(PARTITION BY visit_date) 计算每天的总人数。
  • 过滤条件：pv_rank > total_users * 0.01。例如，某天有 1000 个用户，则 total_users * 0.01 = 10，过滤条件为 pv_rank > 10，即剔除了前 10 名（Top 1%），保留第 11 到 1000 名。
  • 优点：逻辑极其清晰，兼容几乎所有的主流数据库（Hive, Spark SQL, MySQL 8.0+, PostgreSQL）。
• PERCENT_RANK()：
  • 原理：返回某个值在分组数据集中的相对排名，公式为 (rank - 1) / (rows - 1)。排在第一（最大值）的用户 PERCENT_RANK 为 0，排在最后（最小值）的用户为 1。
  • 过滤条件：rank_pct >= 0.01 即可剔除前 1% 的高流量用户。
  • 优点：省去了计算 total_users 的步骤，代码更加优雅。

③ 面试官可能追问的边界与优化问题（加分项）

• 并列排名问题：如果存在大量浏览量相同的用户，用 ROW_NUMBER() 可能会随机截断。如果业务要求“只要浏览量并列第一，即使超过 1% 也要全部剔除”，则应改用 DENSE_RANK() 或 RANK()。
• 数据倾斜与性能优化：在大数据场景下（如 Hive/Spark），PARTITION BY visit_date 会导致同一天的数据全部 shuffle 到同一个 Reduce 节点。如果某天数据量极大，会导致数据倾斜。
  • 解决方案：可以先将用户按 visit_date 和 user_id 进行 GROUP BY 聚合（如果原始表是明细流水表），减少窗口阶段的数据输入量。