0

This is my first question here, I'll try my best to be clear and factual. I've googled for quite a long time but never got the result I wanted. My Mysql knowledge isn't the best and maybe that's why I can't get this answer to work with my wanted function.

At first, here's my Mysql data

user | speed | strength | stamina | precision
---------------------------------------------
1    | 4     | 3        | 5       | 2
2    | 2     | 5        | 3       | 4
3    | 3     | 4        | 6       | 3

Question

I want a Mysql query that find the most similar row to a specific user. For example, if I want to see who's most similar to user 1, I want it to find user 3. User 1 and 2 have in total the same value (14) but 1 and 3 are more similar, see the picture for a better view.

enter link description here

I'd be so glad and grateful if someone knew what Mysql function I should look at, or if you have any ideas.

Stefano Zanini
  • 5,876
  • 2
  • 13
  • 33
Mer Porberg
  • 3
  • 1
  • I think the main thing is to come up with a way of specifying what you mean by the most similar. This helps in writing code to implement it. – Nigel Ren May 10 '17 at 09:05
  • Thank you for answer Nigel! Maybe "most similar" is not the right expression to describe it. I'll later today test if Stefanos code works. – Mer Porberg May 10 '17 at 13:29

2 Answers2

0

I think your requirement translated into functions would be "the minimum value of the average of the differences between users scores at ability level".

If that's the case, it can be translated in SQL like this

select  t2.user,
        (
            abs(t1.speed - t2.speed) +
            abs(t1.strength - t2.strength) +
            abs(t1.stamina - t2.stamina) + 
            abs(t1.precision - t2.precision)
        ) / 4 as diff_avg
from    users t1
cross join
        users t2
where   t2.user <> t1.user and
        t1.user = 1 /* the starting user id goes here */
order by 2 asc
limit 1
Stefano Zanini
  • 5,876
  • 2
  • 13
  • 33
  • Thank you so much Stefano! I'll dive into your code later on today, but it looks like you understood my issue. I'll write again when I've tested it. :) – Mer Porberg May 10 '17 at 11:30
  • I've tested your code now and it works like a charm! Thank you so much Stefano. I really appreciate it! – Mer Porberg May 11 '17 at 10:09
0

The most accurate solution to do this numerically is by using profile similarity - by getting the rows with the highest correlation coefficient to User1

I have been looking for a way to do this in MySQL but can't seem to find a way to. Hope someone knows enough about this to help us

raistie
  • 11
  • 1
  • 2