Login     Register

        Contact Us     Search

XLeratorDB/statistics Documentation

SQL Server PERCENTRANK function


PERCENTRANK_q
 
Updated: 6 August 2010

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.
 
Syntax
SELECT [wctStatistics].[wct].[PERCENTRANK_q] (
  <@Values_RangeQuery, nvarchar(4000),>
 ,<@X, float,>
 ,<@Significance, 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.
@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.
 
@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.
 
 
Return Types
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

----------------------
0
 
(1 row(s) affected)

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

----------------------
0.74074
 
(1 row(s) affected)

 



Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service