PostgreSQL PERCENT_RANK 和 CUMM_DIST 函数比较

PostgreSQL 提供了一些系列窗口函数。其中有些非常相似，但也有细微差别。本文主要讨论PERCENT_RANK 和 CUMM_DIST 两个函数。两者都是返回当前行的相对位置，值在0~1之间；其中PERCENT_RANK的计算公示为： (rank-1) / (total rows – 1)；CUMM_DIST对应公示为：(rank) / (total rows) 。

一般情况说明

首先，cume_dist计算“小于或等于”的行的百分比，而percent_rank计算“小于”当前行的类似百分比，只不过它还假设当前行不在分区中。请看示例：

SELECT x, (PERCENT_RANK() OVER w)::numeric(10, 2),
       (CUME_DIST() OVER w)::numeric(10, 2)
FROM generate_series(1, 5) AS f(x)
WINDOW w AS (ORDER BY x);
x | percent_rank | cume_dist
---+--------------+-----------
1 |         0.00 |      0.20
2 |         0.25 |      0.40
3 |         0.50 |      0.60
4 |         0.75 |      0.80
5 |         1.00 |      1.00

返回结果基本能解释，cume_dist函数如我们期望一样，包括当前行。但percent_rank的最后一行不是0.80，而是1.00。大概的解释是既然所有行都小于最后一行，当然不包括自身，因此为1.00。

重复记录情况

下面再考虑包括重复记录的情况：

WITH cte (x) AS (
        SELECT 0
        UNION ALL
        SELECT 1 FROM generate_series(1, 5)
        UNION ALL
        SELECT 2
)
SELECT x, (PERCENT_RANK() OVER w)::numeric(10, 2),
       (CUME_DIST() OVER w)::numeric(10, 2)
FROM cte
WINDOW w AS (ORDER BY x);

x | percent_rank | cume_dist
---+--------------+-----------
0 |         0.00 |      0.14
1 |         0.17 |      0.86
1 |         0.17 |      0.86
1 |         0.17 |      0.86
1 |         0.17 |      0.86
1 |         0.17 |      0.86
2 |         1.00 |      1.00

这一次percent_rank和cume_dist看起来都没有产生合理的结果。如果当前数值是1，就是说0.17行比我小，或者0.86等于或小于我，结果差异非常大。如果是考试成绩，则一大半人要么都没有在0.5之内或之外。

总结

本文通过示例比较PERCENT_RANK 和 CUMM_DIST 函数的差异。