SQL SERVER – Introduction to PERCENTILE_CONT() – Analytic Functions Introduced in SQL Server 2012

Posted by pinaldave on SQL Authority See other posts from SQL Authority or by pinaldave
Published on Sun, 20 Nov 2011 01:30:01 +0000 Indexed on 2011/11/20 2:02 UTC
Read the original article Hit count: 576

SQL Server 2012 introduces new analytical function PERCENTILE_CONT().

The book online gives following definition of this function: Computes a specific percentile for sorted values in an entire rowset or within distinct partitions of a rowset in Microsoft SQL Server 2012 Release Candidate 0 (RC 0). For a given percentile value P, PERCENTILE_DISC sorts the values of the expression in the ORDER BY clause and returns the value with the smallest CUME_DIST value (with respect to the same sort specification) that is greater than or equal to P.

If you are clear with understanding of the function – no need to read further. If you got lost here is the same in simple words – it is lot like finding median with percentile value.

Now let’s have fun following query:

USE AdventureWorks
GO
SELECT SalesOrderID, OrderQty, ProductID,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ProductID)
OVER (PARTITION BY SalesOrderID) AS MedianCont
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY SalesOrderID DESC
GO

The above query will give us the following result:

You can see that I have used PERCENTILE_COUNT(0.5) in query, which is similar to finding median. Let me explain above diagram with little more explanation. The defination of median is as following:

In case of Even Number of elements = In ordered list add the two digits from the middle and devide by 2
In case of Odd Numbers of elements = In ordered list select the digits from the middle

I hope this example gives clear idea how PERCENTILE_CONT() works.

Reference: Pinal Dave (http://blog.SQLAuthority.com)


Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL Function, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology

© SQL Authority or respective owner

Related posts about Pinal Dave

Related posts about PostADay