SQL实现：基于trade_counts总量创建it_score维度的分位数

核心思路 #

要按trade_counts总量均分10个桶，需先计算累计trade_counts，再根据累计值占总总量的比例确定每个it_score所属的十分位，确保每个桶的总trade_counts约为129607（1296070/10）。

步骤1：基础实现（自动计算桶编号） #

按it_score升序排序（匹配你示例中低it_score对应高十分位的逻辑），计算累计值后通过取整函数分配桶：

WITH ranked_data AS (
    SELECT
        it_score,
        trade_counts,
        SUM(trade_counts) OVER (ORDER BY it_score ASC) AS cumulative_trade
    FROM your_table
)
SELECT
    it_score,
    trade_counts,
    -- 用向上取整，确保每个桶累计量不超过目标容量
    CEIL(cumulative_trade / 129607) AS tileno
FROM ranked_data;

步骤2：自定义桶编号规则（精准匹配你的示例） #

如果需要明确把累计占比最低的10%设为十分位10，次低的设为9，以此类推，可改用CASE WHEN判断：

WITH total_stats AS (
    SELECT SUM(trade_counts) AS total_trade FROM your_table
),
cumulative_data AS (
    SELECT
        it_score,
        trade_counts,
        SUM(trade_counts) OVER (ORDER BY it_score ASC) AS cumulative_trade,
        (SELECT total_trade FROM total_stats) AS total_trade
    FROM your_table
)
SELECT
    it_score,
    trade_counts,
    CASE
        WHEN cumulative_trade <= total_trade * 0.1 THEN 10
        WHEN cumulative_trade <= total_trade * 0.2 THEN 9
        WHEN cumulative_trade <= total_trade * 0.3 THEN 8
        WHEN cumulative_trade <= total_trade * 0.4 THEN 7
        WHEN cumulative_trade <= total_trade * 0.5 THEN 6
        WHEN cumulative_trade <= total_trade * 0.6 THEN 5
        WHEN cumulative_trade <= total_trade * 0.7 THEN 4
        WHEN cumulative_trade <= total_trade * 0.8 THEN 3
        WHEN cumulative_trade <= total_trade * 0.9 THEN 2
        ELSE 1
    END AS tileno
FROM cumulative_data;

关键说明 #

• 若需要高it_score对应高十分位，只需将ORDER BY it_score ASC改为DESC即可。
• 同一it_score的所有记录会被归到同一个桶，不会拆分该维度的度量值，符合维度聚合需求。
• 若总量无法被10整除，最后一个桶的trade_counts会略小于目标值，属于正常情况。

内容的提问来源于stack exchange，提问作者A. Oli