Skip to main content
 首页 » 数据库

PostgreSQL PERCENT_RANK 和 CUMM_DIST 函数比较

2022年07月19日12752php

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 函数的差异。


本文参考链接:https://blog.csdn.net/neweastsun/article/details/116243197
阅读延展