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.
USE [Master] GO IF EXISTS(SELECT name FROM sys.databases WHERE name = 'PERCENTRANK') BEGIN DROP DATABASE PERCENTRANK; END GO CREATE DATABASE PERCENTRANK; GO USE PERCENTRANK GO CREATE TABLE PERCENTRANKDEMO ( [ProductId] int, [SalesQty] int, [Year] int ); insert into PERCENTRANKDEMO VALUES (10,1000,2005),(20,1215,2005),(30,3327,2005), (10,2000,2006),(20,2415,2006),(30,3429,2006), (10,3050,2007),(20,3216,2007),(30,3737,2007), (10,3026,2008),(20,1618,2008),(30,3452,2008), (10,2004,2009),(20,4315,2009),(30,5435,2009), (10,3040,2010),(20,4015,2010),(30,4343,2010), (10,3060,2011),(20,4345,2011),(30,3237,2011), (10,3075,2012),(20,4035,2012),(30,7450,2012), (10,3055,2013),(20,5012,2013),(30,3321,2013), (10,3059,2014),(20,6019,2014),(30,3245,2014), (10,3150,2015),(20,6815,2015),(30,4000,2015);
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.
USE PERCENTRANK GO select ProductId, SalesQty, Year, PERCENTILE_DISC(.5) WITHIN GROUP(ORDER BY SalesQty) OVER(PARTITION BY ProductID) as [50Percentile] from PERCENTRANKDEMO 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.