sql - MySQL > find users with same interests -

i not sure if want doable purely in sql, let's try.

let's assume 3 tables:

• user (id, username)
• topic (id, topicname)
• affinty (user, topic, affinity)

in affinity table, store, each user/topic, affinity -2 (hate topic) +2 (love topic). doing abs(affinity_u1 - affinity_u2) give me, single topic, between 2 users, score between 0 (perfect affinity) 4 (big difference).

my question is, in sql, how can calculate how similar interests other users practices can rank them same interests me no common interest.

sample input, 3 users, 3 topics, different affinities:

insert affinity (user, topic, affinity) values (1, 1, 2), (2, 1, 2), (3, 1, -1), (1, 2, 2), (2, 2, 1), (3, 2, 0), (1, 3, 1), (2, 3, 2), (3, 3, -2); 

assuming user 1, output expect:

select other_user, overall_average_affinity_compared_to_me from... row1: 2, 0.66 row2: 3, 2.66 

(the closest other user me 2, average affinity delta of 0.66, comes user 3, average affinity delta of 2.66).

(i don't care value itself, ranking)

please tell me if question not clear

  select u2.username, avg(abs(a1.affinity - a2.affinity)) similarity     `user` u1     join affinity a1       on a1.user = u1.id     join affinity a2       on a2.topic = a1.topic      , a2.user != a1.user     join `user` u2       on u2.id = a2.user    u1.id = :user_id group u2.username order similarity 

without username:

  select a2.user, avg(abs(a1.affinity - a2.affinity)) similarity     affinity a1     join affinity a2       on a2.topic = a1.topic      , a2.user != a1.user    a1.user = :user_id group a2.user order similarity   

it worth noting isn't great way of comparing similarities in event users rank varying number of topics.