获取收入十分位数及对应分位数下唯一用户数的Presto SQL实现

需求说明 #

将表中Income字段的取值范围（示例中为4.00-80.00）等距划分为10个十分位区间（不考虑Income值的出现频率），并统计每个分位阈值以下的唯一User ID数量（即累计到该阈值的唯一用户数）。

实现思路 #

1. 先计算Income的最小值和最大值，确定整体取值范围。
2. 基于该范围生成10个等距的分位阈值（对应10%到100%的分位位置）。
3. 关联用户的Income数据，统计每个阈值下的唯一用户数（只要用户存在至少一条Income记录≤当前阈值，就计入统计）。

完整SQL查询 #

WITH income_stats AS (
    -- 计算Income的最小、最大值，确定范围
    SELECT 
        MIN(Income) AS min_income,
        MAX(Income) AS max_income
    FROM user_income -- 替换为你的表名
),
decile_thresholds AS (
    -- 生成10个等距的分位阈值，对应10%到100%分位
    SELECT 
        (seq * 10) AS percentile,
        ROUND(min_income + (max_income - min_income) * (seq / 10.0), 2) AS threshold
    FROM income_stats,
    GENERATE_SERIES(1, 10) AS seq
),
user_income_unique AS (
    -- 去重用户的Income记录，避免重复计算同一用户
    SELECT DISTINCT
        "User ID" AS user_id,
        Income AS income
    FROM user_income -- 替换为你的表名
)
SELECT 
    dt.percentile AS percentile_rank,
    dt.threshold AS income_threshold,
    COUNT(DISTINCT uiu.user_id) AS unique_users_below
FROM decile_thresholds dt
LEFT JOIN user_income_unique uiu ON uiu.income <= dt.threshold
GROUP BY dt.percentile, dt.threshold
ORDER BY dt.percentile;

结果说明 #

执行后会得到10条结果，每条对应一个分位（10%到100%），包含分位排名、Income阈值，以及该阈值以下的唯一用户数量。

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