MySQL如何根据多个属性行的相似性对对象进行排序

Question

大家好，新年快乐

情况:

我在 MySQL 数据库中有一些表:

分数:(唯一ID，唯一(objectID，metricID))

| ID     | ObjectID | MetricID | Score    |
|--------+----------+----------+----------|
|0       | 1        | 7        | 0        | 
|1       | 5        | 3        | 13       |
|2       | 7        | 2        | 78       |
|3       | 7        | 3        | 22       |
|.....
|--------+----------+----------+----------|

对象:(唯一ID，唯一ObjectName)

| ID     | ObjectName |
|--------+------------|
|0       | Ook        | 
|1       | Oop        |
|2       | Oww        |
|3       | Oat        |
|.....
|--------+------------|

指标:(唯一ID，唯一MetricName)

| ID     | MetricName |
|--------+------------|
|0       | Moo        | 
|1       | Mar        |
|2       | Mee        |
|3       | Meep       |
|.....
|--------+------------|

对于给定的对象 ID:

• 在“0”和“每个指标一个”之间会有多个分数

要求:

对于给定的 ObjectID，我想返回一个基于以下标准的排序列表:

• 返回的行按照与提供的对象的相似度排序
• 返回的行不包含提供的对象
• (这是我认为的难点)相似度的顺序是由对象与提供对象的“分数距离”决定的，该距离基于其分数与提供对象分数的数字偏移量/差异提供的和当前检查的对象的条目
• 包含对象ID、对象名称、分数差异(或类似内容)

问题陈述:

我不知道为此使用正确的 SQL 语法，到目前为止我的实验都失败了。我想尽可能多地在数据库中完成这项工作，而很少或根本不在代码或类似的讨厌的 for 循环中完成这项工作。

其他非功能性

• 目前 Scores 表中只有 200 行。我的计算表明最终可能有大约 2,000,000 行，但可能不会更多。
• 对象表最多只能有大约 5000 行
• Metrics 表最多只能有大约 400 行

Answer 1

这是一种根据对象与对象 1 的相似性对对象进行排序的方法:

select  other.ObjectID
,       avg(abs(target.Score - other.Score)) as Delta
from    Scores target
join    Scores other
on      other.MetricID = target.MetricID
        and other.ObjectID <> target.ObjectID
where   target.ObjectID = 1
group by
        other.ObjectID
order by
        Delta

相似性定义为共同指标的平均差异。不与对象 1 共享至少一个指标的对象未列出。如果这个答案做出了错误的假设，请随时澄清您的问题:)

Live example at SQL Fiddle.