Spearman's Rank Correlation Coefficient

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Spearman's Rank Correlation Coefficient

Author

Topic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-31 : 08:18:47

Spearman's rank correlation coefficient is a measure of rank correlation under the following circumstance; n individuals are ranked from 1 to n according to some specified characteristics by 2 observers. and we wish to know if the 2 rankings are substantially in agreement with one another.

rs = 1 indicates complete agreement in order of ranks and rs = -1 indicates complete agreement in the opposite order of the ranks.

z is approximately a standardized normal variable (for large n; say n >= 10)

-- Prepare test data
DECLARE	@Stats TABLE (PersonID INT, Rank1 INT, Rank2 INT)

INSERT	@Stats
SELECT	 1,  6,  7 UNION ALL
SELECT	 2, 14, 11 UNION ALL
SELECT	 3,  3,  4 UNION ALL
SELECT	 4,  1,  2 UNION ALL
SELECT	 5, 11,  8 UNION ALL
SELECT	 6, 15, 15 UNION ALL
SELECT	 7,  4,  1 UNION ALL
SELECT	 8,  2,  9 UNION ALL
SELECT	 9,  9,  6 UNION ALL
SELECT	10, 10, 10 UNION ALL
SELECT	11,  5,  5 UNION ALL
SELECT	12,  8, 13 UNION ALL
SELECT	13, 13, 12 UNION ALL
SELECT	14,  7,  3 UNION ALL
SELECT	15, 12, 14

-- Set up environment
DECLARE	@n INT,
	@z FLOAT,
	@rs FLOAT

-- Number of observations
SELECT	@n = COUNT(PersonID)
FROM	@Stats

-- Do the calculations
SELECT	@rs = 1.0E - 6.0E * SUM(1.0E * POWER(Rank1 - Rank2, 2.0E) / @n / (POWER(@n, 2.0E) - 1.0E)),
	@z = @rs * SQRT(@n - 1.0E)
FROM	@Stats

SELECT	@rs,
	@z

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources