SQL Server PERCENTRANKS Function

Updated 2023-06-15 22:08:29.247000

Description

Use the table-valued function PERCENTRANKS to calculate the relative standing of values within a dataset.

Syntax

SELECT * FROM [westclintech].[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 Type

table

colName	colDatatype	colDesc
P	float	the evaluated rank as supplied in @P.
V	float	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.

P	V
70	0
80	0.214285714285714
90	0.357142857142857
100	0.5
110	0.714285714285714
120	0.928571428571429

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.

P	V
70	0.0625
80	0.25
90	0.375
100	0.5
110	0.6875
120	0.875

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).

P	V
75	0.045630456304563
76	0.052110521105211
77	0.0597705977059771
78	0.0680806808068081
79	0.0773507735077351
80	0.0875708757087571
81	0.0987109871098711
82	0.110731107311073
83	0.123271232712327
84	0.137531375313753
85	0.153431534315343
86	0.16940169401694
87	0.185941859418594
88	0.204562045620456
89	0.223622236222362
90	0.243372433724337
91	0.263812638126381
92	0.285712857128571
93	0.30780307803078
94	0.331873318733187
95	0.355853558535585
96	0.380373803738037
97	0.406174061740617
98	0.432194321943219
99	0.458774587745877
100	0.486194861948619
101	0.512845128451284
102	0.53969539695397
103	0.566015660156602
104	0.592375923759238
105	0.618506185061851
106	0.643506435064351
107	0.667676676766768
108	0.691806918069181
109	0.714377143771438
110	0.737067370673707
111	0.758377583775838
112	0.77789777897779
113	0.797077970779708
114	0.815478154781548
115	0.832928329283293
116	0.849128491284913
117	0.863308633086331
118	0.87709877098771
119	0.889258892588926
120	0.902069020690207
121	0.913169131691317
122	0.92329923299233
123	0.932709327093271
124	0.940959409594096
125	0.948149481494815
126	0.9549995499955
127	0.961239612396124
128	0.96649966499665
129	0.971089710897109
130	0.975639756397564
131	0.978769787697877
132	0.982139821398214
133	0.985139851398514
134	0.987529875298753
135	0.989339893398934
136	0.991169911699117
137	0.992609926099261
138	0.993969939699397
139	0.995019950199502
140	0.99589995899959
141	0.996619966199662
142	0.997339973399734
143	0.997869978699787
144	0.998259982599826
145	0.998579985799858
146	0.998829988299883
147	0.99909999099991
148	0.999259992599926
149	0.999449994499945
150	0.999579995799958

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;