MySQL：如何通过多行数字差异对SQL查询结果进行排序？

Question

MySQL：如何通过多行数字差异对SQL查询结果进行排序？

5

我有以下表格，我的目标是搜索与另一个给定客户在如何评价某些制造商方面最相似的客户。此示例可以在此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

例子：

我们想找到与客户 'A' 最匹配的客户，客户 'A' 对两个制造商 manuf_A 和 manuf_B 进行了评估。

期望的结果：

customer    difference
D           4          
B           7
C           11

期望算法：

差异值越小，两个客户之间的关联度越高
如果一个客户与目标客户没有共享任何制造商评级，则他们不存在的评级应为零。

B差异：

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的区别：

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差异：

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

如果您有关于如何在MySQL中实现此操作的建议，以及任何替代方法的建议，都将受到欢迎。

- mgibson

1

只是一条评论：这是一个非常好的问题。您提供了一个可用的SQL Fiddle示例。您已经清楚地解释了问题。作为奖励，这是一个有趣的问题。 - Gordon Linoff

欧几里得距离可能是更好的方法。 - Paul Spiegel

@PaulSpiegel……如果这让你感觉好一些，这被称为曼哈顿距离，在像这样的问题中是完全合理的。 - Gordon Linoff

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Gordon Linoff · Accepted Answer

这里有一种方法。生成所有由"A"评级的客户和制造商的行。然后使用left join查找任何给定客户评级的行。其余部分只是算术运算：

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;

这里有一个SQLFiddle链接。请注意：在SQL Fiddle中创建自己的模式是否是个好主意，我不确定。