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.

Tagged: Analytical functions in SQL Server 2012, Microsoft SQL Server, New features in SQL Server 2012, New Functions in SQL Server 2012, Percentile_disc()