PERCENTRANKS
Updated: 07 August 2015
Use the table-valued function PERCENTRANKS to calculate the relative standing of values within a dataset.
Syntax
SELECT * FROM [wct].[PERCENTRANKS](
<@xQuery, nvarchar(max),>
,<@P, nvarchar(max),>
,<@Pmeth, nvarchar(4000),>)
Arguments
@xQuery
A SQL statement which returns a resultant table consisting of a single column of numeric data sorted in ascending order.
@P
A comma separated list of the ranks to be evaluated.
@Pmeth
The percent rank calculation method. Use 'INC' to return the equivalent of the Excel PERCENTRANK.INC function and 'EXC' for return the equivalent of the PERCENTRANK.EXC function.
Return Types
RETURNS TABLE (
[P] [float] NULL,
[V] [float] NULL
)
Table Description
P
the evaluated rank as supplied in @P.
V
a value between 0 and 1 representing the relative standing of the rank in the dataset.
Remarks
· Any values in @P which are less than the minimum value or greater than the maximum value returned by @xQuery return a NULL.
· If @P IS NULL, then PERCENTRANKS returns each DISTINCT value returned by @xQuery.
· Percentile ranks are interpolated when the requested rank lies between 2 values in the ordered resultant table.
· If the results of @xQuery are not in ascending order, then the percent ranks returned by the function will be unreliable.
· If @Pmeth is NULL, then @Pmeth = 'INC'.
Examples
In this example we return the rank for each value in the dataset using the 'INC' method.
SELECT
*
FROM
wct.PERCENTRANKS(
'SELECT
x
FROM (VALUES
(70),(70),(70),(80),(80),(90),(90),(100),(100),(100),(110),(110),(110),(120),(120)
)n(x)
ORDER BY 1',
NULL,
'INC')
This produces the following result.
Using the same data, we calculate the percent ranks using the 'EXC' method.
SELECT
*
FROM
wct.PERCENTRANKS(
'SELECT
x
FROM (VALUES
(70),(70),(70),(80),(80),(90),(90),(100),(100),(100),(110),(110),(110),(120),(120)
)n(x)
ORDER BY 1',
NULL,
'EXC')
This produces the following result.
In this example we use the SeriesFloat function to insert 100,000 randomly generated rows from a normal distribution having a mean of 100 and a standard deviation of 15 and we return all the percent ranks from 75 to 150 in increments of 1 using the 'INC' method. We will the use NMATRIX2STRING function from the XLeratorDB/math2008 library to create @P.
SELECT
ROUND(SeriesValue,0) as X
INTO
#g
FROM
wct.SeriesFloat(100,15,NULL,100000,'N')
DECLARE @P as varchar(max) =(
SELECT
wct.NMATRIX2STRING(0,k.seq,k.seriesvalue)
FROM
wct.SeriesFloat(75,150,1,NULL,'L')k
)
SELECT
*
FROM
wct.PERCENTRANKS(
'SELECT x FROM #g ORDER BY x ASC',
@P,
NULL
)
This produces the following result (your results will be different).
In this example we insert 10,000 randomly generated rows from a normal distribution with a mean of 50 and a standard deviation of 2.5 into a table and calculate the percent ranks for all the members of the set using the 'EXC' method.
SELECT
ROUND(SeriesValue,0) as X
INTO
#g
FROM
wct.SeriesFloat(50,2.5,NULL,10000,'N')
SELECT
*
FROM
wct.PERCENTRANKS(
'SELECT X FROM #g ORDER BY 1',
NULL,
'EXC')
ORDER BY
1
This produces the following result (your results will be different).
See Also