Login     Register

        Contact Us     Search

XLeratorDB/statistics Documentation

SQL Server percentile function


 
Updated: 6 August 2010

Use PERCENTILE_q to calculate the kth percentile of value in a dataset. You can use this function to establish criteria for acceptance or rejection.
Syntax
SELECT [wctStatistics].[wct].[PERCENTILE_q] (
   <@Values_RangeQuery, nvarchar(4000),>
 ,<@K, float,>)
Arguments
@Values_RangeQuery
the select statement, as text, used to determine the first set of values to be used in the PERCENTILE_q calculation.
@K
is the percentile value in the range 0 through 1, inclusive. @K is an expression of type float or of a type that can be implicitly converted to float.
 
Return Types
float
Remarks
·         If @K < 0 or @K > 1 one, PERCENTILE_q returns an error.
·         If @K is not a multiple of 1/(n-1), PERCENTILE_q interpolates to determine the value at the kth percentile.
·         No GROUP BY is required for this function even though it produces aggregated results.
Examples
CREATE TABLE #p1(
      [num] [float] NOT NULL
)
INSERT INTO #p1 VALUES (1000)
INSERT INTO #p1 VALUES (8000)
INSERT INTO #p1 VALUES (8000)
INSERT INTO #p1 VALUES (13000)
 

To select the first percentile:

select wct.PERCENTILE_q('Select num from #p1',.01)

This produces the following result

----------------------
1210
 
(1 row(s) affected)
 

To select the 25th percentile:

select wct.PERCENTILE_q('Select num from #p1',.25)

This produces the following result

 
----------------------
6250
 
(1 row(s) affected)
 

To select the 75th percentile:

select wct.PERCENTILE_q('Select num from #p1',.75)

This produces the following result

----------------------
9250
 

(1 row(s) affected)



Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service