如何基于金额百分比范围使用PERCENT_RANK()实现行排名？

普通的PERCENT_RANK() OVER(PARTITION BY ...)只能基于固定列值分区，无法适配每行专属的±10%动态金额范围，因此需要通过自关联+窗口函数的组合方式实现，核心思路是先为每行找出符合金额范围的所有行，再在这个动态集合内计算百分位排名。

实现思路 #

1. 对每行数据，计算其金额的±10%区间（Amount*0.9 到 Amount*1.1）
2. 通过自关联匹配所有落入该区间的行，形成每行专属的数据集
3. 在这个专属数据集内按Score排序，结合PERCENT_RANK的原生公式(排名-1)/(总行数-1)计算结果

通用SQL实现（兼容大多数关系型数据库） #

假设表名为products，包含列PRODUCT_ID、Amount、Score：

WITH product_range_data AS (
    SELECT
        t1.PRODUCT_ID,
        t1.Amount,
        t1.Score,
        -- 计算当前行在其范围数据集内的排序位置
        ROW_NUMBER() OVER (PARTITION BY t1.PRODUCT_ID ORDER BY t2.Score) AS row_rank,
        -- 统计当前行范围数据集的总行数
        COUNT(*) OVER (PARTITION BY t1.PRODUCT_ID) AS total_in_range
    FROM products t1
    -- 自关联匹配±10%金额范围内的所有行
    JOIN products t2 
        ON t2.Amount BETWEEN t1.Amount * 0.9 AND t1.Amount * 1.1
)
SELECT
    PRODUCT_ID,
    Amount,
    Score,
    -- 套用PERCENT_RANK公式，处理只有一行的特殊情况
    CASE 
        WHEN total_in_range = 1 THEN 0.0 
        ELSE (row_rank - 1)::FLOAT / (total_in_range - 1) 
    END AS Percent_Rank
FROM product_range_data
-- 只保留当前行的计算结果
WHERE Score = (SELECT Score FROM products WHERE PRODUCT_ID = product_range_data.PRODUCT_ID);

支持LATERAL JOIN数据库的优化写法（PostgreSQL、SQL Server） #

如果你的数据库支持LATERAL JOIN（SQL Server可用CROSS APPLY替代），可以用更简洁的方式实现：

SELECT
    t1.PRODUCT_ID,
    t1.Amount,
    t1.Score,
    PERCENT_RANK() OVER (PARTITION BY t1.PRODUCT_ID ORDER BY t2.Score) AS Percent_Rank
FROM products t1
LEFT JOIN LATERAL (
    SELECT Score
    FROM products t2
    WHERE t2.Amount BETWEEN t1.Amount * 0.9 AND t1.Amount * 1.1
) t2 ON true
-- 去重，只保留当前行的结果
GROUP BY t1.PRODUCT_ID, t1.Amount, t1.Score;

示例验证 #

以你提到的场景为例：

• 第一行Amount=2.3，范围为2.07-2.53，匹配到PRODUCT_ID1、5、11三行
• 假设这三行按Score排序后，目标行排名为2
• 代入公式计算：(2-1)/(3-1)=0.5，与预期结果一致

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