MySQL: How to order an SQL query result by numerical difference of multiple rows?

Question

I have the following table and my goal is to search for customers that most closely resemble another given customer in how they have rated certain manufacturers. This example can be found in this SQL fiddle.

customer    manufacturer    rating
A           Manuf_A         8
A           Manuf_B         3
B           Manuf_A         4
B           Manuf_Y         3        
C           Manuf_X         3
C           Manuf_Y         7
D           Manuf_A         8
D           Manuf_B         7

Example:

We want to find which customers best match customer 'A' who has a rating for two manufacturers, manuf_A and manuf_B.

Desired results:

customer    difference
D           4          
B           7
C           11

Expected algorithm:

• The smaller the difference value is, the closer related the two customers are
• If a customer doesn't share any manufacturer ratings with the subject customer, their non-existent rating should be zero instead.

B difference:

abs(A.manuf_A.rating (8) - B.manuf_A.rating(4)) = 4 
abs(A.manuf_B.rating (3) - B.manuf_B.rating(Doesn't exist/0)) = 3
= 4 + 3
= 7

C difference:

abs(A.manuf_A.rating (8) - C.manuf_A.rating(Doesn't exist/0)) = 8
abs(A.manuf_B.rating (3) - C.manuf_B.rating(Doesn't exist/0)) = 3
= 8 + 3
= 11

D difference:

abs(A.manuf_A.rating (8) - D.manuf_A.rating(8)) = 0
abs(A.manuf_B.rating (3) - D.manuf_A.rating(7)) = 4
= 0 + 4
= 4

Any advice on how this can be done in MySQL would be well received along with any suggestions of an alternative approach.

Answer 1

Here is one approach. Generate all the rows for customers and the manufacturers that "A" has rated. Then use left join to find the ones rated by any given customer. The rest is just arithmetic:

select c.customer,
       sum(abs(am.rating - coalesce(cd.rating, 0))) as similarity
from (select cd.manufacturer, cd.rating
      from centraldatabase cd
      where cd.customer = 'A'
     ) am cross join
     customers c left join
     centraldatabase cd
     on cd.manufacturer = am.manufacturer and cd.customer = c.customer
group by c.customer
order by similarity asc;

Here is a SQLFiddle. Note: I don't know if it is a good idea to create your own schema in SQL Fiddle.