百分位数：如果将一组数据从小到大排序，并计算相应的累计百分位，则某一百分位所对应数据的值就称为这一百分位的百分位数。可表示为：一组n个观测值按数值大小排列。如，处于p%位置的值称第p百分位数。

下面给出3种计算方式：

1. PERCENT_RANK() OVER(ORDER BY .....) 

返回每行的百分比排序，返回值在0～1之间，使用此函数可以直接得出百分位数,等价于分组内当前行的RANK值-1/分组内总行数-1

 

2. RANK() OVER(ORDER BY .....) /COUNT(1) OVER() 

 使用rank()函数可以统计出当前行的排名，配合总数即可算出百分位数，总数使用COUNT(1) OVER() 即可得出

 

3. COUNT(1) OVER(ORDER BY ..... RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) /COUNT(1) OVER() 

 手动调整窗口范围，确认当前行的排名，配合总数即可算出百分位数，总数使用COUNT(1) OVER() 即可得出

 

下面将举例给出具体使用方法

举例场景：计算学生成绩的百分位数

注：本次测试在oracle环境下完成，不过使用到的函数大部分数据库都支持，大家有兴趣的话可以尝试一下其他数据库

创建学生成绩表：

写入测试数据：

1.使用 PERCENT_RANK() OVER(ORDER BY .....) 计算各个科目的百分位数：

结果：

2.使用 RANK() OVER(ORDER BY .....)  /COUNT(1) OVER() 计算各个科目的百分位数：

结果（排名从0开始）：

3.使用COUNT(1) OVER(ORDER BY ..... RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  /COUNT(1) OVER()  计算各个科目的百分位数：

结果（排名从0开始）：

扩展知识：

PARTITION BY ：分组子句，表示分析函数的计算范围，不同的组互不相干； 
ORDER BY： 排序子句，表示分组后，组内的排序方式； 
ROWS|RANGE ：窗口子句，是在分组(PARTITION BY)后，组内的子分组（也称窗口），可以选择窗口的范围，需要配合ORDER BY子句使用

注：一般ORDER BY子句后默认的窗口子句为 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

 

ROWS、RANGE的区别：

ROWS是物理窗口，即根据order by 子句排序后，取的前N行及后N行的数据计算（与当前行的值无关，只与排序后的行号相关）

RANGE是逻辑窗口，是指定当前行对应值的范围取值，行数不固定，只要列值在范围内，对应行都包含在内

举例：

结果：

 

RANGE 关键字配合 n FOLLOWING 使用时发现的问题：

结果：