SPEARMAN_TV

Updated 2024-02-13 20:15:36.253000

Syntax

SELECT * FROM [westclintech].[wct].[SPEARMAN_TV](
   <@x_y_Query, nvarchar(max),>)

Description

Use the table-valued function SPEARMAN_TV to calculate Spearman’s rank correlation coefficient (ρ). Spearman’s ρ 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

@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

table

{"columns": [{"field": "colName", "headerName": "Name", "header": "name"}, {"field": "colDatatype", "headerName": "Type", "header": "type"}, {"field": "colDesc", "headerName": "Description", "header": "description", "minWidth": 1000}], "rows": [{"id": "1f9428e4-98b7-4653-bf47-1af43dc7e1b7", "colName": "R", "colDatatype": "float", "colDesc": "Spearman's rho"}, {"id": "ca69c2fa-8ea5-4128-bb83-88fa7773f8d2", "colName": "P", "colDatatype": "float", "colDesc": "the p-value"}, {"id": "b9248b35-c264-4d14-8928-53bc7497d01c", "colName": "T", "colDatatype": "float", "colDesc": "the t-statistic"}, {"id": "61ac84d5-0f92-4ae5-94ec-da839d511359", "colName": "DF", "colDatatype": "float", "colDesc": "the number of pairs minus 2"}]}

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.*
FROM wct.SPEARMAN_TV('SELECT x,y from #s') k
--This CROSS APPLY UNPIVOTS the tvf columns for formatting
CROSS APPLY(
      SELECT 'P', R UNION ALL
      SELECT 'R', P UNION ALL
      SELECT 'T', T UNION all
      SELECT 'DF', DF
      )p(stat_name, stat_value);
 
DROP TABLE #s;

This produces the following result.

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