SQL Server PERCENT_RANK(): Understanding Percentiles in SQL Server
Have you ever analyzed a dataset where the raw numbers themselves do not give you the complete picture? Your intuition tells you that some values are in the top or bottom percentages, but how do we quantify that?
This is where percentiles come in and SQL Server offers a PERCENT_RANK() function that provides that very information. In this article, we will dive deep into PERCENT_RANK() and its syntax.
We will also take a look at a few examples that will give you a better grasp on how to use this function effectively.
SQL Server PERCENT_RANK() Function Overview
Before we dive into the syntax of the PERCENT_RANK() function, lets first understand the concept of percentiles in SQL Server.
A percentile is a measure used in statistics to indicate the value below which a given percentage of observations fall.
So, if a sales team has achieved $100,000 worth of sales for the month and you’d like to know how they fared compared to other months, you would need to know what percentile they are in.
Syntax of the SQL Server PERCENT_RANK()
The PERCENT_RANK() function is used to calculate the percentile rank of a row within an ordered set of rows in SQL Server. The syntax for the function is as follows:
PERCENT_RANK() OVER (
[PARTITION BY partition_expression, ... ]
ORDER BY sort_expression [ASC | DESC], ...
)
The function returns a value between 0 and 1, with 0 indicating the first row in the ordered set and 1 indicating the last row in the set.
PARTITION BY Clause and Its Usage
The PARTITION BY clause is used to partition results into distinct subsets based on one or more columns. The results are then computed separately over each partition.
For example, if you have a sales table with the columns Year, Quarter, and SalesAmount, and you want to calculate the percentile of sales within each year and quarter, you can use the following statement:
SELECT Year, Quarter, SalesAmount,
PERCENT_RANK() OVER (PARTITION BY Year, Quarter
ORDER BY SalesAmount DESC)
AS 'Sales Percentile'
FROM SalesData
ORDER BY Year, Quarter, SalesAmount DESC
ORDER BY Clause and Its Importance
The ORDER BY clause is used to sort the partitions in a particular way. This arrangement plays a vital role in the percentile rank calculation, as it influences the calculation of the rank values.
For example, if youre looking to rank sales by percentage, youd want the ORDER BY clause to sort the sales from the highest to the lowest.
Return Value and Tie Values
The return value of the PERCENT_RANK() function is a percentage that shows the rank of the current row against all other rows in the set.
If there are identical values, the PERCENT_RANK() function returns the same rank value for all identical entries.
For example, if you have ten rows with SalesAmount of $1000, the function would assign the same rank values to all 10 rows.
Handling of Null Values
When a null value is encountered, the function skips that row and continues with the next row in the partition. The null values are not included in the percentile rank calculation, as they do not contribute to the rank ordering.
SQL Server PERCENT_RANK() Examples
Creating a New View for the Demonstration
To demonstrate the PERCENT_RANK() function, we will create a view that shows the sales data for the past five years.
CREATE VIEW SalesByYear AS
SELECT YEAR(OrderDate) as 'Year',
SUM(TotalDue) as 'Revenue'
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
HAVING YEAR(OrderDate) >= YEAR(getdate()) - 5;
Using the PERCENT_RANK() Function Over a Result Set
Once we have created our view, we can calculate the sales percentile for each year.
SELECT Year, Revenue,
PERCENT_RANK() OVER(ORDER BY Revenue DESC)
AS 'Rank'
FROM SalesByYear
Formatting the Percent Rank for a More Readable Output
Since the PERCENT_RANK() function returns a value between 0 and 1, wed like to format the values as percentages for a more readable output. We can use the FORMAT() function to format the values.
SELECT Year, Revenue,
FORMAT(PERCENT_RANK() OVER(ORDER BY Revenue DESC), 'P')
AS 'Rank'
FROM SalesByYear
Using the PERCENT_RANK() Function Over Partitions
Let’s say you want to know the sales percentile for each year and quarter. In that case, you can use the PARTITION BY clause to partition the results by Year and Quarter.
SELECT Year, Quarter, Revenue,
PERCENT_RANK() OVER(PARTITION BY Year, Quarter ORDER BY Revenue DESC)
AS 'Rank'
FROM (
SELECT YEAR(OrderDate) as 'Year',
DATEPART(Quarter, OrderDate) as 'Quarter',
SUM(TotalDue) as 'Revenue'
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate), DATEPART(Quarter, OrderDate)
) AS SalesData
Recomputed Rank
Lastly, any changes in sort and/or partition can result in different rank values. If youd like to recompute rank values against the modified set, you can use the RANK() function.
CONCLUSION
In this article, we’ve covered the PERCENT_RANK() function in SQL Server. We learned how to use the PARTITION BY and ORDER BY clauses, how the function handles null values, and how to format the return values.
We also covered a few examples to illustrate how the function can be used in real-life scenarios. We hope this article has provided a better understanding of the PERCENT_RANK() function and how to quantify commonly used statistics like percentiles in SQL Server.
In conclusion, understanding percentiles is crucial in analyzing datasets where the raw numbers do not give a complete picture. SQL Server’s PERCENT_RANK() function is an effective tool to calculate percentiles of a row in an ordered set.
The PARTITION BY and ORDER BY clauses play significant roles in the function. Additionally, formatting and re-computing rank values using the FORMAT() and RANK() functions are vital in translating percentile values into meaningful business insights.
By using examples, we hope this article has provided a better understanding of SQL Server’s PERCENT_RANK() function and how it can be applied in real-life scenarios.