Let's learn Percentile_Count Function of SQL Server 2012 (Denali)

Introduction

Sql Server 2012 has brought a lot of new functions for the T-Sql developers. In this article we will look into Percentile_Count Function.

Purpose

An inverse distribution function which takes a percentile value and a sort specification and returns an interpolated value that would fall into that percentile value with respect to the sort specification.It accepts any numeric data-type or any non-numeric data-type that can be implicitly converted to a numeric data-type as it's argument and returns the same data-type as the numeric data-type of the argument.

Syntax

Percentile_Cont ( numeric_literal ) within group(Order by sort_expression [asc|desc]) OVER ([partition_by_clause])

A case study

Let us first create the environment

 IF EXISTS (SELECT * FROM sys.objects WHERE name = N'MatchTable' AND type = 'U')
    DROP TABLE MatchTable
GO
SET ANSI_NULLS ON
GO
--Create the table
CREATE TABLE [dbo].[MatchTable](
	[MatchID] [int] IDENTITY(1,1) NOT NULL,
	[MatchGroup] [varchar](8) NULL,
	[MatchBetween] [varchar](50) NULL,
	[ScheduleDate] [date] NULL
) ON [PRIMARY]
GO

--Insert records
Insert Into MatchTable Values
('Group-A','India VS Australia','08/14/2011')
,('Group-A','India VS Pakistan','08/15/2011')
,('Group-A','India VS Newzealand','08/16/2011')
,('Group-A','Australia VS Pakistan','08/17/2011')
,('Group-A','Australia VS Newzealand','08/18/2011')
,('Group-A','Newzealand VS Pakistan','08/19/2011')
,('Group-B','USA VS WestIndies','08/20/2011')
,('Group-B','USA VS Ireland','08/21/2011')
,('Group-B','USA VS Bangaladesh','08/22/2011')
,('Group-B','WestIndies VS Ireland','08/23/2011')
,('Group-B','WestIndies VS Bangaladesh','08/24/2011')
,('Group-B','Ireland VS Bangaladesh','08/25/2011')

-- Project the records
Select * From MatchTable

Now let us run the below query

SELECT [MatchID]
      ,[MatchGroup]
      ,[MatchBetween]
      ,[ScheduleDate]	 
      ,Percentile_Cont =PERCENTILE_Cont(.5) WITHIN GROUP (ORDER BY MatchID)OVER(PARTITION BY [MatchGroup])		  
  FROM MatchTable
  

How it worked?

1. For EVEN element count, find the two middle digits and divide by two.
2. For ODD element count, find the middle digit.

So let us see, how it works. For "Match Group - A", the total record count is 6 which is Even. The middle elements are 3 and 4 and so as per the thumb rule the Percentile Count is (3/4)/2 = 3.5.

Conclusion

SQL Server 2012 (Denali) seems to be very mature and promising and has embedded with many new functions.In this article we have looked into the Percentile_Count function and it's internal working.Hope the article will be useful.