SQL使用窗口函数计算百分位数数据库qq41652136的博客-

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

下面给出3种计算方式：

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

返回某列或某列组合后每行的百分比排序，返回值在0～1之间，使用此函数可以直接得出百分位数

2. RANK() OVER(ORDER BY …..) 

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

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

COUNT(1) OVER(ORDER BY ….. RANGE BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) 

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

2020-05-07修改：COUNT(1) OVER(ORDER BY ….. RANGE BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) 

改成了：COUNT(1) OVER(ORDER BY ….. RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

因为 1 FOLLOWING 计算时得到了预期之外的结果，算出来的百分位数可能不准确，具体情况详见文章末尾

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

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

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

创建学生成绩表：

 CREATE TABLE TEST.STUDENT_SCORE(  name varchar(20),  --学生姓名  course varchar(20), --科目  score NUMBER(5,2) --成绩  );

写入测试数据：

 INSERT INTO TEST.STUDENT_SCORE (name,course,score) VALUES ('张三','政治',90.5); INSERT INTO TEST.STUDENT_SCORE (name,course,score) VALUES ('李四','政治',79.0); INSERT INTO TEST.STUDENT_SCORE (name,course,score) VALUES ('王五','政治',85.5); INSERT INTO TEST.STUDENT_SCORE (name,course,score) VALUES ('赵六','政治',93.0); INSERT INTO TEST.STUDENT_SCORE (name,course,score) VALUES ('小明','政治',92.5); INSERT INTO TEST.STUDENT_SCORE (name,course,score) VALUES ('小红','政治',88.0); INSERT INTO TEST.STUDENT_SCORE (name,course,score) VALUES ('小吕','政治',76.5); INSERT INTO TEST.STUDENT_SCORE (name,course,score) VALUES ('小高','政治',93.0);  INSERT INTO TEST.STUDENT_SCORE (name,course,score) VALUES ('张三','外语',87.0); INSERT INTO TEST.STUDENT_SCORE (name,course,score) VALUES ('李四','外语',92.0); INSERT INTO TEST.STUDENT_SCORE (name,course,score) VALUES ('王五','外语',69.5); INSERT INTO TEST.STUDENT_SCORE (name,course,score) VALUES ('赵六','外语',76.0); INSERT INTO TEST.STUDENT_SCORE (name,course,score) VALUES ('小高','外语',76.0);  INSERT INTO TEST.STUDENT_SCORE (name,course,score) VALUES ('张三','高数',95.0); INSERT INTO TEST.STUDENT_SCORE (name,course,score) VALUES ('李四','高数',70.5); INSERT INTO TEST.STUDENT_SCORE (name,course,score) VALUES ('王五','高数',65.0); INSERT INTO TEST.STUDENT_SCORE (name,course,score) VALUES ('赵六','高数',88.5);  INSERT INTO TEST.STUDENT_SCORE (name,course,score) VALUES ('张三','算法',59.5);  INSERT INTO TEST.STUDENT_SCORE (name,course,score) VALUES ('张三','数据结构',99.5); INSERT INTO TEST.STUDENT_SCORE (name,course,score) VALUES ('李四','数据结构',89.0); INSERT INTO TEST.STUDENT_SCORE (name,course,score) VALUES ('王五','数据结构',69.5); INSERT INTO TEST.STUDENT_SCORE (name,course,score) VALUES ('赵六','数据结构',90.5);

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

 --用法非常简单，此处将百分位数乘100，使百分位数在0～100之间 SELECT  name 姓名, course 科目, score 成绩, ROUND(PERCENT_RANK() OVER(PARTITION BY COURSE ORDER BY SCORE DESC)*100, 2) 百分位数 FROM TEST.STUDENT_SCORE ;

结果：

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

 --这种写法使用总人数和排名来计算百分位，复杂一些，但是算法可以自己修改 SELECT  name 姓名, course 科目, score 成绩, score_rank 排名, students 总人数, CASE WHEN students > 1       THEN ROUND(score_rank * 100 / (students - 1), 2)      ELSE 0      END 百分位数 FROM ( SELECT  name, course, score, RANK() over(PARTITION BY course ORDER BY score DESC)-1 score_rank, --当前行的排名 count(1) over(PARTITION BY course) students --当前科目的总人数 FROM TEST.STUDENT_SCORE  );

结果（排名从0开始）：

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

 --这种写法更复杂一些，效果与前面的是一样的，可以调整的地方更多一些 SELECT  name 姓名, course 科目, score 成绩, students-score_rank 排名, students 总人数, CASE WHEN students > 1       THEN ROUND((students-score_rank) * 100 / (students - 1), 2)      ELSE 0      END 百分位数 FROM ( SELECT  name, course, score, count(0) over(PARTITION BY course ORDER BY score RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) score_rank, --当前行的排名 count(0) over(PARTITION BY course) students --当前科目的总人数 FROM TEST.STUDENT_SCORE  );

结果（排名从0开始）：

扩展知识：

 --OVER()函数的子句，可以选择窗口的范围，需要配合ORDER BY子句使用 ROWS｜RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW --从第一行到当前行 ROWS｜RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING --从当前行到结尾行 ROWS BETWEEN CURRENT ROW AND n FOLLOWING --从当前行到随后的n行 ROWS BETWEEN n PRECEDING AND CURRENT ROW --从前n行到当前行 ROWS BETWEEN n FOLLOWING AND UNBOUNDED FOLLOWING --从下n行到结尾行 --RANGE 不能使用 n FOLLOWING 或 n PRECEDING ，因为得到的结果可能在预料之外，有兴趣的同学可以试验一下 

ROWS、RANGE的区别：

ROWS选项使用相对于当前行的偏移行数来定义框架的起点和终点

RANGE选项可以使用框架起/终点的值与当前行的值的差异来定义偏移的行数

举例：

 SELECT  name 姓名, course 科目, score 成绩, count(0) over(PARTITION BY course) 总人数, count(0) over(PARTITION BY course ORDER BY score RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  RANK1, count(0) over(PARTITION BY course ORDER BY score ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  RANK2 FROM TEST.STUDENT_SCORE ;

结果：

由上述结果就可以看出，取下一行时：

ROWS关键字在碰见值相同的行时，会按照ORDER BY的排序取当前行到最后一行

RANGE关键字会取到所有相同的值，然后到最后一行

注：计算百分位数时，相同值的百分位数也应该一样，所以第三种方式使用RANGE关键字实现

RANGE 关键字配合 n FOLLOWING 使用出现的BUG：

 SELECT  name 姓名, course 科目, score 成绩, count(0) over(PARTITION BY course) 总人数, count(0) over(PARTITION BY course ORDER BY score RANGE BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)  RANK1, count(0) over(PARTITION BY course ORDER BY score ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)  RANK2 FROM TEST.STUDENT_SCORE ;

结果：

可以看见，使用1 FOLLOWING AND UNBOUNDED FOLLOWING来选择窗口范围时，成绩为92.5时，与93的排名居然是一样的，oracle、mysql环境都出现了这种情况，具体原因还没搞清楚