In this article I've put together a summary of what I've discovered during my research.
The following code snippets are taken from the following blog - please visit it for more detailed information:
Mean is another name for average so we can use AVG function to calculate mean.
-- function for floating point division
CREATE FUNCTION dbo.FDIV
(@numerator float,
@denominator float)
RETURNS float
AS
BEGIN
RETURN CASE WHEN @denominator = 0.0
THEN 0.0
ELSE @numerator / @denominator
END
END
GO
-- function for linear interpolation
CREATE FUNCTION dbo.LERP
(@value float, -- between low and high
@low float,
@high float,
@newlow float,
@newhigh float)
RETURNS float -- between newlow and newhigh
AS
BEGIN
RETURN CASE
WHEN @value between @low and @high and @newlow <= @newhigh THEN @newlow + dbo.FDIV((@value-@low), (@high-@low)) * (@newhigh - @newlow)
WHEN @value = @low and @newlow is not NULL THEN @newlow
WHEN @value = @high and @newhigh is not NULL THEN @newhigh
ELSE NULL
END
END
GO
Declare @TestScores table (StudentID int, Score int)
insert @TestScores (StudentID, Score) Values (1, 20)
insert @TestScores (StudentID, Score) Values (2, 03)
insert @TestScores (StudentID, Score) Values (3, 40)
insert @TestScores (StudentID, Score) Values (4, 45)
insert @TestScores (StudentID, Score) Values (5, 50)
insert @TestScores (StudentID, Score) Values (6, 20)
insert @TestScores (StudentID, Score) Values (7, 90)
insert @TestScores (StudentID, Score) Values (8, 20)
insert @TestScores (StudentID, Score) Values (9, 11)
insert @TestScores (StudentID, Score) Values (10, 30)
--Find the percentile rank of a given score
-- The derived table makes one scan over the data values to compute some aggregates. The outer select interpolates
-- between the pth percentile of the nearest samples below and above the given value.
-- The result is 25, 0.5. That means a score of 25 is at the 50th percentile, the median, of the distribution.
declare @val float
set @val = 25
select
@val as val,
dbo.LERP(@val, scoreLT, scoreGE,
dbo.FDIV(countLT-1,countMinus1),
dbo.FDIV(countLT,countMinus1)) as percentrank
from (
select
SUM(CASE WHEN Score < @val
THEN 1 ELSE 0 END) as countLT,
count(*)-1 as countMinus1,
MAX(CASE WHEN Score < @val
THEN Score END) as scoreLT,
MIN(CASE WHEN Score >= @val
THEN Score END) as scoreGE
from @TestScores
) as x1
-- Find the percentile (the score) that characterizes a given percentage
declare @pp float
set @pp = .75
select
@pp as factor,
dbo.LERP(max(d), 0.0, 1.0, max(a.Score), max(b.Score)) as percentile
from
(
select floor(kf) as k, kf-floor(kf) as d
from (
select 1+@pp*(count(*)-1) as kf from @TestScores
) as x1
) as x2
join @TestScores a
on
(select count(*) from @TestScores aa
where aa.Score < a.Score) < k
join @TestScores b
on
(select count(*) from @TestScores bb
where bb.Score < b.Score) < k+1
