SPEARMAN

Updated 2023-11-06 16:47:49.070000

Syntax

SELECT [westclintech].[wct].[SPEARMAN](
  <@x_y_Query, nvarchar(max),>
 ,<@RV, nvarchar(4000),>)

Description

Use the scalar function SPEARMAN to calculate Spearman’s rank correlation coefficient (ρ). ρ is defined as Pearson’s correlation coefficient for the ranks of x- and y-values. Ranks are calculated such that tied ranks receive the average of the ranks for the rank interval populated by the tie.

Arguments

@RV

the value to be returned by the function. Use the following values:

{
    "columns": [
        {
            "field": "RV"
        },
        {
            "field": "Description"
        }
    ],
    "rows": [
        {
            "RV": "'R'",
            "Description": "Spearman’s rho"
        },
                {
            "RV": "'P'",
            "Description": "the p-value"
        },
        {
            "RV": "'T'",
            "Description": "the t-statistic"
        },
        {
            "RV": "'DF'",
            "Description": "the number of pairs minus 2"
        }
    ]
}

@x_y_Query

a T-SQL statement, as a string, that specifies the x- and y-values. The x- and y-values must be of the type float or of a type that implicitly converts to float.

Return Type

float

Remarks

The function is insensitive to order.

The function only includes pairs where x-value is NOT NULL and y-value is NOT NULL.

Use the RANK_AVG table-valued function to see the x- and y-ranks.

Examples

SELECT *

INTO #s

FROM

(

    SELECT 106,

           7

    UNION ALL

    SELECT 86,

           0

    UNION ALL

    SELECT 100,

           27

    UNION ALL

    SELECT 101,

           50

    UNION ALL

    SELECT 99,

           28

    UNION ALL

    SELECT 103,

           29

    UNION ALL

    SELECT 97,

           20

    UNION ALL

    SELECT 113,

           12

    UNION ALL

    SELECT 112,

           6

    UNION ALL

    SELECT 110,

           17

) n(x, y);

SELECT p.stat,

       wct.SPEARMAN('SELECT x,y from #s', p.stat) as stat_value

FROM

(

    SELECT 'P'

    UNION ALL

    SELECT 'R'

    UNION ALL

    SELECT 'T'

    UNION all

    SELECT 'DF'

) p(stat);

DROP TABLE #s;

This produces the f ollowing result.

{"columns":[{"field":"stat"},{"field":"stat_value","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"stat":"P","stat_value":"0.631967363906089"},{"stat":"R","stat_value":"-0.175757575757576"},{"stat":"T","stat_value":"-0.504978249175835"},{"stat":"DF","stat_value":"8"}]}