PERCENTRANKS

Updated 2023-06-15 22:08:29.247000

Syntax

SELECT * FROM [westclintech].[wct].[PERCENTRANKS](
  <@xQuery, nvarchar(max),>
 ,<@P, nvarchar(max),>
 ,<@Pmeth, nvarchar(4000),>)

Description

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

Arguments

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

@xQuery

A SQL statement which returns a resultant table consisting of a single column of numeric data sorted in ascending order.

Return Type

table

{"columns": [{"field": "colName", "headerName": "Name", "header": "name"}, {"field": "colDatatype", "headerName": "Type", "header": "type"}, {"field": "colDesc", "headerName": "Description", "header": "description", "minWidth": 1000}], "rows": [{"id": "1542d475-7b3c-4e8b-b3f9-dad1706b7610", "colName": "P", "colDatatype": "float", "colDesc": "the evaluated rank as supplied in @P."}, {"id": "604ab6e2-7638-41a8-b8fa-d8ec3a057855", "colName": "V", "colDatatype": "float", "colDesc": "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.

{"columns":[{"field":"P","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"V","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"P":"70","V":"0"},{"P":"80","V":"0.214285714285714"},{"P":"90","V":"0.357142857142857"},{"P":"100","V":"0.5"},{"P":"110","V":"0.714285714285714"},{"P":"120","V":"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.

{"columns":[{"field":"P","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"V","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"P":"70","V":"0.0625"},{"P":"80","V":"0.25"},{"P":"90","V":"0.375"},{"P":"100","V":"0.5"},{"P":"110","V":"0.6875"},{"P":"120","V":"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).

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

This produces the following result (your results will be different).

{"columns":[{"field":"P","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"V","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"P":"41","V":"9.99900009999E-05"},{"P":"42","V":"0.0003999600039996"},{"P":"43","V":"0.0017998200179982"},{"P":"44","V":"0.0042995700429957"},{"P":"45","V":"0.0152984701529847"},{"P":"46","V":"0.036996300369963"},{"P":"47","V":"0.0826917308269173"},{"P":"48","V":"0.163483651634837"},{"P":"49","V":"0.28007199280072"},{"P":"50","V":"0.422657734226577"},{"P":"51","V":"0.58004199580042"},{"P":"52","V":"0.726327367263274"},{"P":"53","V":"0.842915708429157"},{"P":"54","V":"0.917308269173083"},{"P":"55","V":"0.965903409659034"},{"P":"56","V":"0.988201179882012"},{"P":"57","V":"0.996500349965003"},{"P":"58","V":"0.999300069993001"},{"P":"59","V":"0.999900009999"}]}

See Also

PERCENTILES - table-valued function to calculate one or many percentile values from a single dataset

PERCENTRANK - Aggregate function to calculate a percent rank using the 'INC' method

PERCENTRANK_EXC - Calculate the rank of a value in dataset

PERCENTRANK_Q - Calculate the rank of a value in a dataset as a percentage of the dataset.