The series so far:
- Statistics in SQL: Pearson’s Correlation
- Statistics in SQL: Kendall’s Tau Rank Correlation
- Statistics in SQL: Simple Linear Regressions
- Statistics in SQL: The Kruskal–Wallis Test
- Statistics in SQL: The Mann–Whitney U Test
- Statistics in SQL: Student's T Test
Kendall’s Tau rank correlation is a handy way of determining how correlated two variables are, and whether this is more than chance. If you just want a measure of the correlation then you don’t have to assume very much about the distribution of the variables. Kendall’s Tau is popular with calculating correlations with non-parametric data. Spearman’s Rho is possibly more popular for the purpose, but Kendall’s tau has a distribution with better statistical properties (the sample estimate is close to a population variance) so confidence levels are more reliable, but in general, Kendall’s tau and Spearman’s rank correlation coefficient are very similar. The obvious difference between them is that, for the standard method of calculation, Spearman’s Rank correlation required ranked data as input, whereas the algorithm to calculate Kendall’s Tau does this for you. Kendall’s Tau consumes any non-parametric data with equal relish.
Kendall’s Tau is easy to calculate on paper, and makes intuitive sense. It deals with the probabilities of observing the agreeable (concordant) and non-agreeable (discordant) pairs of rankings. All observations are paired with each of the others, A concordant pair is one whose members of one observation are both larger than their respective members of the other paired observation, whereas discordant pairs have numbers that differ in opposite directions. Kendall’s Tau-b takes tied rankings into account.
So, let’s imagine that we ask two observers to rank ten glasses of Loire valley wine in order. (These figures aren’t real since I prefer Vouvray and Gros Plant du Pays Nantais)
Wine |
Phil |
Tony |
Menetou-Salon |
1 |
3 |
Muscadet-Sèvre et Maine |
2 |
4 |
Quincy |
3 |
2 |
Reuilly |
4 |
5 |
Muscadet-Coteaux de la Loire |
5 |
11 |
Chinon |
6 |
6 |
Muscadet-Côtes de Grand Lieu |
7 |
12 |
Vouvray |
8 |
9 |
Tourraine |
9 |
8 |
Anjou-Saumur |
10 |
10 |
Sancerre |
11 |
1 |
Pouilly-Fumé |
12 |
7 |
We’ve sorted the rankings in terms of my choices, so we can compare the two. In SQL, we do this anyway.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
DECLARE @OurData TABLE ( row INT IDENTITY PRIMARY KEY, x int NOT NULL, y int NOT NULL ); INSERT INTO @OurData (x, y) SELECT x,y FROM (VALUES (1,3), --Menetou-Salon (2,4), --Muscadet-Sèvre et Maine (3,2), --Quincy (4,5), --Reuilly (5,11), --Muscadet-Coteaux de la Loire (6,6), --Chinon (7,12), --Muscadet-Côtes de Grand Lieu (8,9), --Vouvray (9,8), --Tourraine (10,10), --Anjou-Saumur (11,1), --Sancerre (12,7) --Pouilly-Fumé )f(x,y) ORDER BY x SELECT --Kendall's rank correlation sample estimate τ CONVERT(NUMERIC(8,2),(SUM(CASE WHEN (i.x < j.x AND i.y < j.y) OR (i.x > j.x AND i.y > j.y) THEN 1 ELSE 0 END)) -- concordant - SUM(CASE WHEN (i.x < j.x AND i.y > j.y) OR (i.x > j.x AND i.y < j.y) THEN 1 ELSE 0 END)) -- discordant /COUNT(*) AS Tau FROM @OurData i CROSS JOIN @OurData j WHERE i.row<>j.row /* # which we can confirm in R. x <- c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) y <- c(3, 4, 2, 5, 11, 6, 12, 9, 8, 10, 1, 7) cor.test(x, y, method = "kendall", alternative = "two.sided") Kendall's rank correlation sample estimate τ= : 0.242424 alternate hypothesis: true τ ≠0 T -statistic: 41.0 p-value: 0.310810 */ |
…and that gives us
1 2 3 |
Tau 0.2424242424242 (1 row(s) affected) |
Notice that we are not concerned with the individual wines, only with the extent to which the two judges agree in their subjective rankings of the wines.
Using the same data, we can also calculate Spearman’s Rho.
1 2 3 |
SELECT 1 - (6 * SUM(POWER(x - y, 2))) / CONVERT(NUMERIC(8, 2), (COUNT(*) * (POWER(COUNT(*), 2) - 1))) AS spearman_rho FROM @OurData; |
which gives
1 2 3 4 |
spearman_rho 0.307692308 (1 row(s) affected) |
You can convince yourself that the calculation for Kendall’s Rho doesn’t need ranked data. You can pretend that, instead of ranking their wines in order, Tony and I scored each wine on a zany scale between 1 and 100. Here is this un-ranked data.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT x,y FROM (VALUES (5,20), --Menetou-Salon (13,28), --Muscadet-Sèvre et Maine (23,12), --Quincy (28,33), --Reuilly (38,75), --Muscadet-Coteaux de la Loire (41,43), --Chinon (50,82), --Muscadet-Côtes de Grand Lieu (56,61), --Vouvray (61,56), --Tourraine (71,71), --Anjou-Saumur (80,6), --Sancerre (81,50) --Pouilly-Fumé )f(x,y) ORDER BY x |
You’ll see that, with this un-ranked data, Kendall’s Tau gives precisely the same result.
Now what if we couldn’t really distinguish some of the wines and gave them tied rankings? What if I put Menetou-Salon and Muscadet-Sèvre et Maine first in my ranking? Well, Spearman’s rho wouldn’t work. We now have to use a variety of Kendall’s tau called Tau-B. Kendall’s Tau is pretty resilient to ties, but Tau-B is better where there are ties.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
DECLARE @OurData TABLE ( row INT IDENTITY PRIMARY KEY, x INT NOT NULL, y INT NOT NULL ); INSERT INTO @OurData (x, y) SELECT f.x, f.y FROM (VALUES (1, 3), --Menetou-Salon (1, 4), --Muscadet-Sèvre et Maine (3, 2), --Quincy (3, 4), --Reuilly (3, 10), --Muscadet-Coteaux de la Loire (6, 6), --Chinon (7, 10), --Muscadet-Côtes de Grand Lieu (7, 9), --Vouvray (8, 8), --Tourraine (10, 10), --Anjou-Saumur (11, 1), --Sancerre (1, 7) --Pouilly-Fumé ) f(x, y) ORDER BY f.x; DECLARE @n1 NUMERIC(18, 6), @n2 NUMERIC(18, 6), @n0 NUMERIC(18, 6), @nc NUMERIC(18, 6), @nd NUMERIC(18, 6); SELECT @n1 = COALESCE(SUM(ties.t * (ties.t - 1)) / 2, 0) FROM (SELECT COUNT(*) AS t FROM @OurData GROUP BY [@OurData].x HAVING COUNT(*) > 1 ) ties; SELECT @n2 = COALESCE(SUM(ties.t * (ties.t - 1)) / 2, 0) FROM (SELECT COUNT(*) AS t FROM @OurData GROUP BY [@OurData].y HAVING COUNT(*) > 1 ) ties; SELECT @n0 = COUNT(*), @nc = SUM(CASE WHEN (i.x < j.x AND i.y < j.y) OR (i.x > j.x AND i.y > j.y) THEN 1 ELSE 0 END), -- concordant @nd = SUM(CASE WHEN (i.x < j.x AND i.y > j.y) OR (i.x > j.x AND i.y < j.y) THEN 1 ELSE 0 END) -- discordant FROM @OurData i CROSS JOIN @OurData j WHERE i.row <> j.row; SELECT CONVERT(NUMERIC(8, 2), (@nc - @nd) / SQRT((@n0 - @n1) * (@n0 - @n2))) AS 'Tau (τ)'; |
… Which gives…
1 2 |
Tau (τ) 0.21 |
So there we have it. If your data really can’t be called parametric, then Kendall’s Tau is pretty useful. Your data doesn’t have to be rank orders, the statistic will consume all manner of data. Data scientists like it because it irons out those pesky outliers that chance throws at you. I’m wary of using it without understanding the data properly, because it is easy to draw unwarranted conclusions from it, but otherwise it is a useful inference tool.