# percent sql_使用SQL Server中的PERCENT_RANK函数计算SQL百分位数

percent sql

This article explores the SQL Server PERCENT_RANK analytical function to calculate SQL Percentile and its usage with various examples.

## PERCENT_RANK（）函数概述 (Overview of PERCENT_RANK() function)

In a relational database, we store the data in SQL tables. Many times, we fetch the data without any data manipulations. Let’s say you want to generate the SSRS report that contains the top 5% sales for a product in a region. In this case, you need to use SQL Server Analytical functions to perform the data manipulations such as SQL Percentile.

We have the following analytical functions in SQL Server.

• CUME_DIST()

CUME_DIST（）
• FIRST_VALUE()

FIRST_VALUE（）
• LAST_VALUE()

LAST_VALUE（）

铅（）
• LAG()

落后（）
• PERCENTILE_COUNT()

PERCENTILE_COUNT（）
• PERCENTILE_DISC()

PERCENTILE_DISC（）
• PERCENT_RANK()

PERCENT_RANK（）

You can also direct to SQL Rank functions to understand how you can specify rank to each row of your result set over a partition using these window functions.

## PERCENT_RANK（） (PERCENT_RANK())

The PERCENT_RANK function in SQL Server calculates the relative rank SQL Percentile of each row. It always returns values greater than 0, and the highest value is 1. It does not count any NULL values. This function is nondeterministic.

SQL Server中的PERCENT_RANK函数计算每行的相对排名SQL百分位数。 它始终返回大于0的值，并且最大值为1。它不计算任何NULL值。 此功能是不确定的。

The syntax of the PERCENT_RANK() function is as below:

PERCENT_RANK（）函数的语法如下：

PERCENT_RANK() OVER (
[PARTITION BY partition_expression, ... ]
ORDER BY sort_expression [ASC | DESC], ...
)


In this syntax, we use the following parameters.

• PARTITION BY: By default, SQL Server treats the whole data set as a single set. We can specify the PARTITION By clause to divide data into multiple sets. On each set, the Percent_Rank function performs the analytical calculations. It is an optional parameter

PARTITION BY：默认情况下，SQL Server将整个数据集视为单个集合。 我们可以指定PARTITION By子句将数据分为多个集合。 在每个集合上，Percent_Rank函数都会执行分析计算。 它是一个可选参数
• Order By: We use Order by clause to sort the data in either ascending or descending order. It is a compulsory parameter

Order By：我们使用Order by子句对数据进行升序或降序排序。 这是一个强制性参数

Let’s look at the examples to understand it.

### 示例1：PERCENT_RANK函数，用于计算学生成绩SQL百分位数 (Example 1: PERCENT_RANK function for calculating the SQL Percentile of student marks)

In this example, let’s use a CTP ( common table expression) to hold the student marks, and we use PERCENT_RANK function from this data for marks in ascending order.

WITH Student
AS (SELECT Marks
FROM(VALUES(75), (59), (69), (88), (72), (86), (60), (67), (49), (81), (93)) Val(Marks)),
Percentiles
AS (SELECT Marks,
PERCENT_RANK() OVER(
ORDER BY Marks) AS Percent_Rank
FROM Student)
SELECT *
FROM Percentiles;


In the output, we can see following outcomes

• A lowest mark 49 gets Percent_Rank value zero

最低分数49的Percent_Rank值为零
• The highest marks 93 gets Percent_Rank value one

最高分93获得Percent_Rank值1

### 示例2：用于计算具有NULL值SQL Percentile的PERCENT_RANK函数 (Example 2: PERCENT_RANK function with to calculate SQL Percentile having NULL values)

Let’s add a NULL value in the student marks and rerun the query from example 1.

WITH Student
AS (SELECT Marks
FROM(VALUES(75), (59), (69), (88), (72), (86), (60), (67), (49), (81), (93),(NULL)) Val(Marks)),
Percentiles
AS (SELECT Marks,
PERCENT_RANK() OVER(
ORDER BY Marks) AS Percent_Rank
FROM Student)
SELECT *
FROM Percentiles;


As shown in the following screenshot, you always get zero for the NULL values.

### 示例3：PERCENT_RANK函数来计算具有重复值SQL Percentile (Example 3: PERCENT_RANK function to calculate SQL Percentile having duplicate values)

Suppose multiple students are having similar marks, and we want to calculate the percent rank. It is important to know that the function will return the same or different rank for a student with similar marks.

	WITH Student
AS (SELECT Marks
FROM(VALUES(75), (59), (69), (88), (72), (86), (60), (67), (49), (81),(81), (93)) Val(Marks)),
Percentiles
AS (SELECT Marks,
PERCENT_RANK() OVER(
ORDER BY Marks) AS Percent_Rank
FROM Student)
SELECT *
FROM Percentiles;


We have two students with marks 81. In the following example, we can see that percent_rank function returns the same value.

Here is a catch, Let’s say two students have got the highest marks 93 and we want to calculate the percent_rank for these.

WITH Student
AS (SELECT Marks
FROM(VALUES(75), (59), (69), (88), (72), (86), (60), (67), (49), (81),(93), (93)) Val(Marks)),
Percentiles
AS (SELECT Marks,
PERCENT_RANK() OVER(
ORDER BY Marks) AS Percent_Rank
FROM Student)
SELECT *
FROM Percentiles;


In this example, we have the highest marks 93, and two students have the same marks. Previously, we get percent rank 1 for the highest rank. Now, we do not get the rank 1, and it calculates the percent rank according to the similar highest marks.

### 示例4：PERCENT_RANK函数以降序计算带有标记SQL Percentile (Example 4: PERCENT_RANK function to calculate SQL Percentile with marks in descending order)

In previous examples, we did not specify ascending or descending order for the marks. By default, it sorts the data in ascending order. We can specify the DESC clause to sort the data in descending order.

WITH Student
AS (SELECT Marks
FROM(VALUES(75), (59), (69), (88), (72), (86), (60), (67), (49), (81),(93)) Val(Marks)),
Percentiles
AS (SELECT Marks,
PERCENT_RANK() OVER(
ORDER BY Marks desc) AS Percent_Rank
FROM Student)
SELECT *
FROM Percentiles;


In the descending order, we can see that the highest marks get 0 ranks and the lowest marks get rank 1.

In the descending order, for the student with similar highest marks always get zero rank.

WITH Student
AS (SELECT Marks
FROM(VALUES(75), (59), (69), (88), (72), (86), (93),(60), (67), (49), (81),(93)) Val(Marks)),
Percentiles
AS (SELECT Marks,
PERCENT_RANK() OVER(
ORDER BY Marks desc) AS Percent_Rank
FROM Student)
SELECT *
FROM Percentiles;


Similar to example 3, two students with the lowest marks do not get percent_rank 1.

### 示例4：带有PARTITION BY子句SQL Percentile的PERCENT_RANK函数 (Example 4: PERCENT_RANK function for SQL Percentile with PARTITION BY clause)

In the previous example, we did not use the PARTITION by clause to divide the data into multiple small data sets.

Suppose we want to check the Percent_Rank of an employee pay rate per the department name from the AdventureWorks sample database. In the following query, we use PARTITION BY Department, ORDER BY Rate to define partition on the department as per the rates in the ascending order.

SELECT Department,
Firstname,
Rate,
PERCENT_RANK() OVER(PARTITION BY Department
ORDER BY Rate) AS PctRank
FROM HumanResources.vEmployeeDepartmentHistory AS edh
WHERE Department IN(N'Engineering', N'Finance')
ORDER BY Department,
Rate DESC;


In the following image, we have two departments group, Engineering and Finance.

• For the Engineering group, Terri has the highest pay rate, and its percent_rank is 1

对于工程组，Terri的薪水最高，其percent_rank为1
• Rob has the lowest pay rate, and it gets percent_rank zero in the engineering group

Rob的薪水最低，在工程组中它的percent_rank为零
• In another group, Finance, Laura gets percent_rank 1 and David gets percent_rank zero according to their pay rates

在另一组财务中，劳拉根据他们的薪水比率获得percent_rank 1，而David获得percent_rank 0

If we do not want to have partitions, remove the PARTITION BY clause and it treats the input as single data set.

SELECT Department,
Firstname,
Rate,
PERCENT_RANK() OVER(
ORDER BY Rate) AS PctRank
FROM HumanResources.vEmployeeDepartmentHistory AS edh
WHERE Department IN(N'Engineering', N'Finance')
ORDER BY Department,
Rate DESC;


## 结论 (Conclusion)

In this article, we learned the SQL PERCENT_RANK() function for calculating SQL Percentile. It calculates the relative rank of a row within a group or subset of data. Here is a quick summary of what we learned about SQL Server PERCENT_RANK() function in this article:

• PERCENT_RANK calculate the relative rank of a row within a group of rows

PERCENT_RANK计算一组行中一行的相对排名
• We use the sort data in ascending or descending order using order by clause

我们使用order by子句以升序或降序使用排序数据
• We can partition data using the PARTITION BY clause, and it divides data into specific small data sets

我们可以使用PARTITION BY子句对数据进行分区，并将其分为特定的小数据集
• A NULL value also gets percent_rank value zero in the output

NULL值也会在输出中使percent_rank值变为零

You should learn this SQL Server Analytical function and be familiar with this.

percent sql

• 1
点赞
• 0
评论
• 4
收藏
• 一键三连
• 扫一扫，分享海报

03-12 4661

11-07 84
12-06 54
09-18 734
10-28 1万+
02-25 1995
09-27 195
06-07 3699
05-17 1125