PERCENTILE_DISC is one the new analytical\statistical functions that we have in SQL Server 2012. In this article we will see how we can use this function. To begin with let us see the syntax of this function as stated in BOL.
Syntax of PERCENT_DISC
PERCENTILE_DISC ( numeric_literal ) WITHIN GROUP
( ORDER BY order_by_expression [ ASC | DESC ] )
OVER ( [ <partition_by_clause> ] )
Now let us implement PERCENT_DISC and understand how it works.We will be using the below script to create a database and insert some values in it before we call this function.
IF EXISTS(SELECT name FROM sys.databases
WHERE name = 'PERCENTRANK')
DROP DATABASE PERCENTRANK;
CREATE DATABASE PERCENTRANK;
CREATE TABLE PERCENTRANKDEMO
insert into PERCENTRANKDEMO
Noe let’s use the below code to implement this function. With this code we are trying to find the 50th percentile from table we created.
select ProductId, SalesQty, Year,
WITHIN GROUP(ORDER BY SalesQty)
OVER(PARTITION BY ProductID) as [50Percentile]
WHERE ProductId = 20
order by ProductId;
Snapshot of the output of the above code
So what is the inference that we draw from the output. The function PERCENTILE_DISC calculates the Nth percentile based on a discrete distribution of the column values. The result set would have the Nth percentile value which would equal to a specific value in the column. Any NULLS would be ignored.