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)