How to use the Statistical Analytical Function PERCENTILE_RANK in SQL Server

The Statistical Analytical Function PERCENTILE_RANK in SQL Server

The PERCENTILE_RANK() function calculates the relative rank of a row within a group of rows in SQL Server. Also, we can use PERCENT_RANK to evaluate the relative position rank of a value within a query result set or partition. 

The PERCENT_RANK is similar to the CUME_DIST function. The difference is, CUME_DIST will returns the relative position and PERCENT_RANK will return the Rank of the position.

Syntax:

PERCENT_RANK( )  

    OVER ( [ partition_by_clause ] order_by_clause )

Arguments :

OVER ( [ partition_by_clause ] order_by_clause) 

The partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. 

The order_by_clause determines the logical order in which the operation is performed. 

The order_by_clause is required. 

The <rows or range clause> of the OVER syntax cannot be specified in a PERCENT_RANK function.

Return Type : The return type or result is Float(53).

General Remarks :

The range of values returned by PERCENT_RANK () is greater than 0 and less than or equal to 1. The first row in any set has a PERCENT_RANK of 0. NULL values are included by default and are treated as the lowest possible values.

Examples :

Suppose we have a dataset with sample of orders as follows.
Select
Order_Id, Order_Date, Prod_Id, Units_Sold
From tbl_Sample

Scenario 1 :

Computing Percentile Rank from the above dataset with Order by Units_Sold and without Partition.

Select 

Order_Id, Order_date, Prod_Id, Units_Sold,

ROW_NUMBER ( ) Over ( Order by Units_Sold) as Rec_No,

COUNT (*) Over  ( ) as Total_Rec_Count,

CUME_DIST ( ) Over (Order by Units_Sold) as Cum_Dist,

PERCENT_RANK ( ) Over (Order by Units_Sold) as Percentile_Rank

From tbl_Sample

# Result:

The above query will computes the Percentile Rank on entire dataset based on the order by Units_Sold

Scenario 2 :

Computing Percentile Rank from the above dataset with Order by Units_Sold and Partition by Prod_Id.

Select 

Order_Id, Order_date, Prod_Id, Units_Sold,

ROW_NUMBER ( ) Over (Partition By Prod_Id  Order by Units_Sold) as Partition_Rec_No,

COUNT (*) Over  (Partition By Prod_Id ) Partition_Rec_Count,

CUME_DIST ( ) Over (Partition By Prod_Id  Order by Units_Sold) as Cum_Dist,

PERCENT_RANK ( ) Over (Partition By Prod_Id  Order by Units_Sold) as Percentile_Rank

From tbl_Sample

# Result:

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------