This percentile function takes a looooong time.
Using 10,000 rows of data it took 2 minutes and 10 seconds on my computer.
This is how to get the 10,000 rows into the table:
Declare @TestScores table (StudentID int identity(1,1), Score int)
insert @TestScores (Score) Values (20)
insert @TestScores (Score) Values (03)
insert @TestScores (Score) Values (40)
insert @TestScores (Score) Values (45)
insert @TestScores (Score) Values (50)
insert @TestScores (Score) Values (20)
insert @TestScores (Score) Values (90)
insert @TestScores (Score) Values (20)
insert @TestScores (Score) Values (11)
insert @TestScores (Score) Values (30)
while @@ROWCOUNT > 0
insert @TestScores select t.Score from @TestScores t
cross join
(select max (StudentID) MaxRowNum from @TestScores) x
where
t.StudentID <= 10000 - x.MaxRowNum
That code puts the same scores in 1000 times, so you should get the same results as in the original article.
Here is a modification that is a bit faster (takes less than 1 second):
declare @pp float
set @pp = .5
declare @k int, @d float, @ax float, @bx float
declare @values table (i int identity(1,1), x float)
insert @values select Score from @TestScores order by Score
select @k=floor(kf), @d=kf-floor(kf)
from (select 1+@pp*(count(*)-1) as kf from @values) as x1
select @ax=x from @values where i=@k
select @bx=x from @values where i=@k+1
select @pp as factor,
dbo.LERP(@d, 0.0, 1.0, @ax, @bx) as percentile
Personally I think LINTERP would be a nicer name than LERP.
Article: Computing Percentiles in SQL Server
Posted - 05/20/2004 : 10:47:15
