Percentile_Disc Function
Purpose: An inverse distribution function which takes a percentile value and a sort specification and returns an element from the set.It accepts any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype as it's argument and returns the same datatype as the numeric datatype of the argument.
Syntax: Percentile_Disc ( numeric_literal ) within group(Order by sort_expression [asc|desc]) OVER ([partition_by_clause])
Formula:
Percentile_Disc = Cumulative_Distribution * N
Where,
N => Total number of rows or records.
e.g. We have a set with 10 elements. We need to find the Percentile_Disc of the 7th row.Let us first find the Cumulative_distribution as
So, Cumulative_Distribution = (7)/(10) = 0.7
So, Percentile_Disc = Cumulative_Distribution * N = 0.7 * 10 = 7
The range of values return by this function is between 0 to 1 inclusive. The return data type is always a positive number.
Before going to look into some examples of First_Value we will first create the test environment
Run the below script for doing so
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
/* Result
MatchID MatchGroup MatchBetween ScheduleDate
1 Group-A India VS Australia 2011-08-14
2 Group-A India VS Pakistan 2011-08-15
3 Group-A India VS Newzealand 2011-08-16
4 Group-A Australia VS Pakistan 2011-08-17
5 Group-A Australia VS Newzealand 2011-08-18
6 Group-A Newzealand VS Pakistan 2011-08-19
7 Group-B USA VS WestIndies 2011-08-20
8 Group-B USA VS Ireland 2011-08-21
9 Group-B USA VS Bangaladesh 2011-08-22
10 Group-B WestIndies VS Ireland 2011-08-23
11 Group-B WestIndies VS Bangaladesh2011-08-24
12 Group-B Ireland VS Bangaladesh 2011-08-25
*/
Example
SELECT [MatchID]
,[MatchGroup]
,[MatchBetween]
,[ScheduleDate]
,PercentileDisc =PERCENTILE_DISC(.6) WITHIN GROUP (ORDER BY MatchID)OVER(PARTITION BY [MatchGroup])
,Cume_Dist() Over(Partition By [MatchGroup] Order By MatchID) As CumilativeDistribution
,PercentileDiscByFormula = Cume_Dist() Over(Partition By [MatchGroup] Order By MatchID) * (6)
FROM MatchTable
/* Result
MatchID MatchGroup MatchBetween ScheduleDate PercentileDisc CumilativeDistribution PercentileDiscByFormula
1 Group-A India VS Australia 2011-08-14 4 0.166666666666667 1
2 Group-A India VS Pakistan 2011-08-15 4 0.333333333333333 2
3 Group-A India VS Newzealand 2011-08-16 4 0.5 3
4 Group-A Australia VS Pakistan 2011-08-17 4 0.666666666666667 4
5 Group-A Australia VS Newzealand 2011-08-18 4 0.833333333333333 5
6 Group-A Newzealand VS Pakistan 2011-08-19 4 1 6
7 Group-B USA VS WestIndies 2011-08-20 10 0.166666666666667 1
8 Group-B USA VS Ireland 2011-08-21 10 0.333333333333333 2
9 Group-B USA VS Bangaladesh 2011-08-22 10 0.5 3
10 Group-B WestIndies VS Ireland 2011-08-23 10 0.666666666666667 4
11 Group-B WestIndies VS Bangaladesh2011-08-24 10 0.833333333333333 5
12 Group-B Ireland VS Bangaladesh 2011-08-25 10 1 6
*/
Hope this helps