correlation - Get average of multiple ranks in mysql -

I'm trying to calculate the spearmans rank correlation for some data in mysql. For this, I have to rank my data on descending order. I got this job but when there is only one variable in 2 rows then the rank of ranks 2 or more ranks should be average. For example, there are some example data with the current rank and expected rank

  | ID | Up | Rank | | 8 | 1 | 1 | | 2 | 2 | 2 | # Rank should be 2.5. 2 | 3 | # Rank should be 2.5? 3 | 4 | | 5 | 4 | 5 | | 1 | 5 | 6 | | 3 | 6 | 7 | # Should be ranked 8 | 7 | 6 | 8 | # Should be ranked 8 | 9 | 6 | 9 | # Ranks should be 8  

My query looks like this now:

  SET @rownum: = 0; SET @ rownum2: = 0; SELECT rank_x.id as rank_x, rank_x.var1, rank_x.rk_x FROM (SELECT id, @rownum: = @rownum + 1 as rk_x, var1 by sampledata command by var1 aBC var1);  

You can calculate serial numbers and then take an average. This requires some nested subkeys, but this is possible.

Assign sequential values ​​in advance

Then find the maximum for each ID.

Then find the average

then the average

query looks like this:

  SELECT id, var1 , From (minrn + maxrn) / 2 (SELECT sd. * (@maxrn: = if (@ v2 = var1, @maxrn, if (@ v2: = var1, rn, rn)) as maxrn (SELECT sd . *, (@minrn: = if (@v = var1, @minran, if (@V: = var1, rn, rn))) as minrn FROM (SELECT id, var1, (@rn: = @rn + 1) as sampledata sd CROSS JOIN (SELECT @rn: = 0) vars ORDER BY var1 asc) sd CROSS JOIN (SELECT @minrn: = 0, @v: = -1) by wars command var1, rn ) SD cross join (SELECT @maxrn: = 0, @ v2: = -1) by Var command var1, rn desc) sd;