我有下表,我的目标是搜索与其他特定客户最相似的客户如何评价某些制造商.此示例可在此
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’,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
差异:
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中完成此任务的任何建议都会得到很好的接受以及替代方法的任何建议.