PERCENTRANK_q

Updated 2023-10-24 13:31:16.340000

Syntax

SELECT [westclintech].[wct].[PERCENTRANK_q] (
  <@Values_RangeQuery, nvarchar(4000),>
 ,<@X, float,>
 ,<@Significance, float,>)

Description

Use PERCENTRANK_q to return the rank of a value in a dataset as a percentage of the dataset. This function can be used to evaluate the relative standing of a value within a dataset.

Arguments

@Values_RangeQuery

the select statement, as text, used to determine the first set of values to be used in the PERCENTILE_q calculation.

@Significance

is a value that identifies the number of significant digits for the returned percentage value. If @Significance is NULL, PERCENTRANK uses three digits (0.xxx). @Significance is an expression of type float or of a type that can be implicitly converted to float.

@X

is the value for which you want to know the rank. @X is an expression of type float or of a type that can be implicitly converted to float.

Return Type

float

Remarks

If @Significance is less than one or greater than 15, 3 significant digits are returned.

If @X does not match one of the values in the dataset, PERCENTRANK_q interpolates to determine the correct percentage rank.

No GROUP BY is required for this function even though it produces aggregated results.

If there is only one value in the dataset and it is equal to @x, the value 1 will be returned by the function. If it does not match @x then NULL is returned.

Examples

CREATE TABLE #p1

(

    [num] [float] NOT NULL

);

INSERT INTO #p1

VALUES

(1  );

INSERT INTO #p1

VALUES

(1  );

INSERT INTO #p1

VALUES

(1  );

INSERT INTO #p1

VALUES

(2  );

INSERT INTO #p1

VALUES

(3  );

INSERT INTO #p1

VALUES

(4  );

INSERT INTO #p1

VALUES

(8  );

INSERT INTO #p1

VALUES

(11 );

INSERT INTO #p1

VALUES

(12 );

INSERT INTO #p1

VALUES

(13 );

To find the PERCENTRANK of 1

select wct.PERCENTRANK_q('Select num from #p1', 1, NULL);

This produces the following result

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

To find the PERCENTRANK of 10 to 5 decimal places

select wct.PERCENTRANK_q('Select num from #p1', 10, 5);

This produces the following result

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