如何在MySQL中实现Excel PERCENTRANK.EXC函数的一致行为？

一、完全匹配Excel PERCENTRANK.EXC()的计算逻辑 #

Excel的PERCENTRANK.EXC()和MySQL内置的PERCENT_RANK()核心区别在于：前者返回的百分位不会出现0或1（最小值的百分位大于0，最大值的百分位小于1），而后者刚好把最小值设为0、最大值设为1。

要在MySQL中完全模拟PERCENTRANK.EXC()，可以手动实现其计算公式：针对每个分组的n条数据，按revenue升序排列后，某条数据的百分位为当前排名/(n+1)（排名从1开始）。示例SQL如下：

SELECT 
    market,
    campaign_count,
    revenue,
    -- 模拟PERCENTRANK.EXC的计算逻辑
    RANK() OVER (PARTITION BY market ORDER BY revenue) / (COUNT(*) OVER (PARTITION BY market) + 1) AS percentile_exc
FROM data_table
GROUP BY market, campaign_count, revenue
ORDER BY market ASC, revenue DESC;

二、先排除分组首尾值，再计算百分排名 #

如果只是需要先剔除每个分组里的最小和最大revenue记录，再用PERCENT_RANK()计算，可以先用CTE过滤数据，再执行排名：

WITH filtered_data AS (
    SELECT 
        market,
        campaign_count,
        revenue
    FROM data_table
    WHERE NOT EXISTS (
        SELECT 1
        FROM (
            -- 先算出每个分组的最小、最大revenue
            SELECT market, MIN(revenue) AS min_rev, MAX(revenue) AS max_rev
            FROM data_table
            GROUP BY market
        ) AS group_bounds
        WHERE group_bounds.market = data_table.market
          AND (data_table.revenue = group_bounds.min_rev OR data_table.revenue = group_bounds.max_rev)
    )
)
SELECT 
    market,
    campaign_count,
    revenue,
    PERCENT_RANK() OVER (PARTITION BY market ORDER BY revenue) AS percentile
FROM filtered_data
GROUP BY market, campaign_count, revenue
ORDER BY market ASC, revenue DESC;

注意点： #

• 如果分组内有多条记录的revenue等于最小值或最大值，上述查询会把这些记录全部排除，符合“排除首尾值”的要求。
• 如果分组内的记录数不足3条（比如只有2条），过滤后会没有数据，需要根据业务场景调整过滤逻辑。

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