Monday, March 6, 2017

Pulling 95th Percentile from a SQL query

A few times I've been asked to give 95th percentile of query results to help sharpen results on reporting where there a some outliers.

-- Select the 95th Percentile of a given query

declare @ct as int
,@ctout as int
,@cut as int
,@cut1 as int
,@cut2 as int
,@q as nvarchar(255)
,@q2 as nvarchar(255)
,@ParmDefinition nvarchar(500);

SET @ParmDefinition = N'@ctout int OUTPUT';

set @q = N'select top 257 * from msdb.dbo.backupfile T ' -- Source Query
set @q2 = N'select @ctout=count(*) from (' + @q + ') x2'
exec sp_executesql @q2,@ParmDefinition, @ctout=@ct OUTPUT;
set @cut = ((@ct-@ct*0.95) / 2 )
set @cut1 = @ct-@cut
set @cut2 = @cut1-@cut

print @ct
print @cut
print @cut1
print @cut2


exec('select top ' + @cut1 + ' * from (' + @q + ') x3 Order by backup_set_id desc')

exec('select top ' + @cut2 + ' * from (select top ' + @cut1 + ' * from (' + @q + ') x3 Order by backup_set_id desc)x4 Order by backup_set_id')

select top 257 * from msdb.dbo.backupfile T Order by backup_set_id desc


1 comment:

Block said...

Whoa! I’m enjoying the template/theme of this website. It’s simple, yet
effective. A lot of times it’s very hard to get that “perfect balance”
between superb usability and visual appeal. I must say you’ve done a
very good job with this.
oracle apps dba training in Chennai
best java training institute in Chennai
node js developer course in Chennai