SQL Server 2012 introduced a new analytical function called PERCENT_RANK(). This function allows you to determine the relative standing of a value within a query result set or partition. In this blog post, we will explore the functionality of PERCENT_RANK() through a brief example.
For the purpose of this example, we will be using the AdventureWorks sample database, which is commonly used by developers for experimentation. Let’s dive into the following query:
USE AdventureWorks
GO
SELECT SalesOrderID, OrderQty, RANK() OVER (ORDER BY SalesOrderID) AS Rnk,
PERCENT_RANK() OVER (ORDER BY SalesOrderID) AS PctDist
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY PctDist DESC
GO
The above query will produce the following result:
SalesOrderID | OrderQty | Rnk | PctDist |
---|---|---|---|
43670 | 5 | 4 | 1.000000 |
43669 | 3 | 3 | 0.666667 |
43667 | 2 | 2 | 0.333333 |
43663 | 1 | 1 | 0.000000 |
Let’s analyze the result set. In addition to the PERCENT_RANK() function, I have also included the RANK() function in the query. The reason for this is that the PERCENT_RANK() function is based on the RANK() function and calculates the relative standing of the query. The formula to calculate PERCENT_RANK() is as follows:
PERCENT_RANK() = (RANK() - 1) / (Total Rows - 1)
If you want to learn more about this function, you can read the documentation here.
Now, let’s attempt the same example with the PARTITION BY clause:
USE AdventureWorks
GO
SELECT SalesOrderID, OrderQty, ProductID, RANK() OVER (PARTITION BY SalesOrderID ORDER BY ProductID) AS Rnk,
PERCENT_RANK() OVER (PARTITION BY SalesOrderID ORDER BY ProductID) AS PctDist
FROM Sales.SalesOrderDetail s
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY PctDist DESC
GO
You will notice that the same logic is applied in the resulting dataset. The PARTITION BY clause allows you to divide the result set into partitions based on a specific column, in this case, SalesOrderID. The PERCENT_RANK() function is then calculated within each partition, providing a relative standing for each value within its respective partition.
So, how many of you were familiar with the logic/formula of PERCENT_RANK() before reading this blog post? Understanding the functionality of SQL Server’s PERCENT_RANK() function can greatly enhance your analytical capabilities when working with query result sets.