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:

Priya Kannan said...

Great post! I am see the great contents and step by step read really nice information.I am gather this concepts and more information. It's helpful for me my friend. Also great blog here with all of the valuable information you have.
SQL Server Training in Chennai