SQL SERVER – Introduction to PERCENT_RANK() – Analytic Functions Introduced in SQL Server 2012
- by pinaldave
SQL Server 2012 introduces new analytical functions PERCENT_RANK(). This function returns relative standing of a value within a query result set or partition. It will be very difficult to explain this in words so I’d like to attempt to explain its function through a brief example. Instead of creating a new table, I will be using the AdventureWorks sample database as most developers use that for experiment purposes.
Now let’s have fun following query:
USE AdventureWorks
GO
SELECT SalesOrderID, OrderQty,
RANK() OVER(ORDER BY SalesOrderID) 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 give us the following result:
Now let us understand the resultset. You will notice that I have also included the RANK() function along with this query. The reason to include RANK() function was as this query is infect uses RANK function and find the relative standing of the query.
The formula to find PERCENT_RANK() is as following:
PERCENT_RANK() = (RANK() – 1) / (Total Rows – 1)
If you want to read more about this function read here.
Now let us attempt the same example with PARTITION BY clause
USE AdventureWorks
GO
SELECT SalesOrderID, OrderQty, ProductID,
RANK() OVER(PARTITION BY SalesOrderID
ORDER BY ProductID ) 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
Now you will notice that the same logic is followed in follow result set.
I have now quick question to you – how many of you know the logic/formula of PERCENT_RANK() before this blog post?
Reference: Pinal Dave (http://blog.SQLAuthority.com)
Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL Function, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology
