MySQL: how to order the result of an SQL query by numerically distinguishing several rows?

Question

I have the following table, and my goal is to find customers who are most reminiscent of another given customer in how they rated some manufacturers. This example can be found in this SQL script .

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,” which is rated for two manufacturers, manuf_A and manuf_B.

Desired Results:

 customer difference D 4 B 7 C 11 

Expected Algorithm:

• The smaller the difference, the closer the relationship between the two customers
• If the client does not have any manufacturer ratings from the client entity, their non-existent rating should be zero.

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 

Difference

D:

 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 will be well received along with any suggestions for an alternative approach.

Answer 1

Here is one approach. Create all lines for customers and manufacturers that "A" rated. Then use left join to find the ones that are rated by any client. 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 the SQLFiddle . Note. I don't know, it's a good idea to create your own schema in SQL Fiddle.