mysql - 在 SQL 中跨多个连接表记录查找最近匹配的最有效方法

Question

假设您有一些类似的记录

+--------+--------------+-------+
| person | attribute_id | value |
+--------+--------------+-------+
|      1 |            1 |     4 |
|      1 |            2 |     2 |
|      1 |            3 |     0 |
|      2 |            1 |     0 |
|      2 |            2 |     5 |
|      2 |            3 |     5 |
|      3 |            1 |     3 |
|      3 |            2 |     4 |
|      3 |            3 |     1 |
+--------+--------------+-------+

如果我们以 person=1 为中心，根据这些从 0 到 5 的属性分数查找最接近的匹配人员的最有效方法是什么？

理想情况下，我想在 SQL (MySQL) 而不是应用程序层中执行此操作。

编辑更多说明

我可以看到，如果您有架构，建模会容易得多

+-------+--------------+-------------+-------------+
|person | attribute_1  | attribute_2 | attribute_3 |
|1      | 4            | 2           | 0           |
|2      | 0            | 5           | 5           |
|3      | 3            | 4           | 1           |
+-------+--------------+-------------+-------------+

因为你可以做类似的事情

选择 ABS($search_attr_1-attribute_1) AS diff_1、ABS($search_attr_2-attribute_2) AS diff_2、ABS($search_attr_3-attribute_3) AS diff_3
来自分数
按 diff_1 ASC、diff_2 ASC、diff_3 ASC 排序

Answer 1

要直接转置该表，您可以使用以下查询:

create table data_transpose as
select person
    , case when attribute_id = 1 then value else null end as attribute_1
    , case when attribute_id = 2 then value else null end as attribute_2
    , case when attribute_id = 3 then value else null end as attribute_3
    , case when attribute_id = 4 then value else null end as attribute_4
 ;

就最近匹配而言，您可以在多种距离/相似性度量之间进行选择。您可能希望考虑欧几里德和余弦相似性度量等。要执行余弦相似度(我认为它对于您正在做的事情具有更好的属性)，请使用下面的内容，假设您的数据如下:

+-------+--------------+-------------+-------------+-------+---------------+---------------+---------------+
|person | attribute_1  | attribute_2 | attribute_3 |person2| attribute_1_2 | attribute_2_2 | attribute_3_2 |
|1      | 4            | 2           | 0           |2      | 0             | 5             | 5             |
+-------+--------------+-------------+-------------+-------+---------------+---------------+---------------+

在创建上表的交叉联接或其他联接后，您可以计算余弦相似度，如下所示:

select person
, person2
,  (attribute_1 * attribute_1_2 + attribute_2 * attribute_2_2 + attribute_3 * attribute_3_2 + attribute_4 * attribute_4_2)/
    (
    sqrt(pow(abs(attribute_1),2) + pow(abs(attribute_2),2) + pow(abs(attribute_3),2) + pow(abs(attribute_3),2) + pow(abs(attribute_4),2))
   *
    sqrt(pow(abs(attribute_1-2),2) + pow(abs(attribute_2_2),2) + pow(abs(attribute_3_2),2) + pow(abs(attribute_3_2),2) + pow(abs(attribute_4_2),2))
    ) as cosine_similarity
from
some_join_of_transposed_table_here
;

这里的代码不是调试代码。祝你好运。