PERCENTILE_q

Updated 2023-10-23 20:54:09.450000

Syntax

SELECT [westclintech].[wct].[PERCENTILE_q] (
   <@Values_RangeQuery, nvarchar(4000),>
 ,<@K, float,>)

Description

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.

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 Type

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

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"1210"}]}

To select the 25th percentile:

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

This produces the following result

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"6250"}]}

To select the 75th percentile:

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

This produces the following result

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"9250"}]}