In this article we will learn Percentile_Count Function of SQL Server 2012 (Denali)
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.
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.
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
SET ANSI_NULLS ON
--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]
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
,Percentile_Cont =PERCENTILE_Cont(.5) WITHIN GROUP (ORDER BY MatchID)OVER(PARTITION BY [MatchGroup])
How it worked?
- For EVEN element count, find the two middle digits and divide by two.
- 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.
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.