如何在AWS Athena中筛选各home下前75%高num_events的用户

在AWS Athena中，你可以通过窗口函数实现按home分组过滤掉最低四分之一用户的需求，以下是两种可行的方法：

方法一：使用PERCENT_RANK()窗口函数 #

这个函数会为每个home分区内的用户计算相对排名，排名范围是0到1。我们只需要保留排名大于0.25的用户即可：

WITH ranked_users AS (
    SELECT
        home,
        user,
        num_events,
        -- 按home分区，num_events升序计算相对排名
        PERCENT_RANK() OVER (PARTITION BY home ORDER BY num_events ASC) AS pct_rank
    FROM your_table_name  -- 替换成你的实际表名
)
SELECT
    home,
    COUNT(DISTINCT user) AS retained_user_count
FROM ranked_users
WHERE pct_rank > 0.25  -- 过滤掉最低25%的用户
GROUP BY home
ORDER BY home;

说明： #

• PERCENT_RANK()会为每个home里的用户按num_events从小到大分配排名，比如某个home有4个用户，排名分别是0、0.333、0.666、1，此时过滤pct_rank>0.25会保留后3个用户（前75%）。
• 如果home内用户数无法被4整除，Athena会自动按比例处理排名，确保过滤逻辑符合预期。

方法二：先计算每个home的25分位数再筛选 #

如果你需要更精确的分位数判断，可以先计算每个home的25%分位数，再关联原表筛选：

WITH home_percentiles AS (
    SELECT
        home,
        -- 计算每个home的25%分位数（离散型分位数，匹配实际存在的num_events值）
        APPROX_PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY num_events ASC) AS p25
    FROM your_table_name
    GROUP BY home
),
filtered_users AS (
    SELECT
        t.home,
        t.user
    FROM your_table_name t
    JOIN home_percentiles hp ON t.home = hp.home
    WHERE t.num_events > hp.p25  -- 保留分位数以上的用户
)
SELECT
    home,
    COUNT(DISTINCT user) AS retained_user_count
FROM filtered_users
GROUP BY home
ORDER BY home;

说明： #

• APPROX_PERCENTILE_DISC返回的是home内实际存在的num_events值，适合离散型数据；如果需要连续型分位数，可以用APPROX_PERCENTILE_CONT。
• 这种方法避免了窗口函数的排名计算，直接通过分位数阈值过滤，逻辑更直观。

你之前查询的问题点 #

1. 笔误问题：CTE中的GROUP BY home_cusec是无效字段，原表只有home，属于拼写错误。
2. 全局分位数错误：APPROX_PERCENTILE(num_events, 0.25)没有按home分区，计算的是全表的25分位数，不是每个home单独的分位数，导致过滤逻辑错误。

内容的提问来源于stack exchange，提问作者ElTitoFranki