mysql - 是否有最佳/有效的方法来比较多行的相似性/差异？

Question

我正在尝试将一个值列表与另一个值列表进行比较 - 目标是计算出项目之间的总差异/相似性，并根据几个标准(以摆动来衡量)之间的相似程度对项目进行评分从默认值 (0) 开始。

Criteria 值仅在 -8 和 +8 之间变化(如果未指定，则为 NULL)。例如:

        Colour:     Flavour:    Temperature:
Item A:     -4              2               5
Item B:     3               0               3 
Item C:     2               -3              1

因此，要计算项目 A 和 C 之间的差异:颜色 (2 - -4) = -6， flavor (-3 - 2) = -5，温度 (1 - 5)=-4。因此 -6 + -5 + -4 = 给出的分数为 15。假设在获得项目总计/分数之前使用 ABS() 对差异进行标准化。我将通过省略一些数学来稍微简化问题 - 这里的主要问题是获取每行的分数以及匹配的标准数量。

在查看我当前的解决方案之前，请先考虑一下问题，以避免先入为主:http://sqlfiddle.com/#!9/11c0f/1

有没有更好的方法，但我不知道？可能有大约 150 个条件，以及多达数百个项目/行需要显示并按彼此之间的相似性进行排序，这将是相当大的处理量。

缓存这些昂贵查询的结果是理所当然的。这个问题更多的是关于差异/相似性计算本身 - 是否有一些深层的 SQL 魔法可以比使用纯算术更有效地进行实际比较？如果需要，可以以其他方式存储数据，以便于处理。

还有一个问题 - 并非所有项目都始终具有所有条件，如果没有适用的字段将为 NULL。

我故意从这个问题的正文中省略了任何 SQL，以避免预先渲染观众的意见。我目前正在使用 MySQL，但如果另一个系统提供了一种有效的方法来处理这个问题，那么这是完全可以改变的。

预先感谢您的经验和见解。

Answer 1

考虑将相似度计算封装到带有两个参数的函数中。假设此函数名为 similarity() 那么可能的解决方案是:

select
    item_1,
    item_2,
    sum(sim) as similarity
from
    (
    select
        a.item_name as item_1,
        b.item_name as item_2,
        similarity(a.criteria_value, b.criteria_value) as sim
    from
        ourdata as a
        inner join ourdata as b
            on b.item_name > a.item_name and b.attribute_name = a.attribute_name
     ) as x
 group by
     item_1,
     item_2;

如果合适，您可以在 items_attributes 表而不是 ourdata 表上运行它 - 我不清楚为什么它们有不同的行数。