提前查询。在mysql中对最相关的字段进行排名

小编典典

提前查询。在mysql中对最相关的字段进行排名

sql

假设我们有一个这样的数据库：

Project_tbl:

-----------------
id | Project_name
-----------------
1  | A 
2  | B
3  | C
-----------------

personel_project_tbl:

--------------------
user_id | Project_id
--------------------
1       | 1
2       | 2
3       | 1
3       | 2
2       | 3
--------------------

instrument_project_tbl:

--------------------------
instrument_id | Project_id
--------------------------
1             | 1
1             | 2
2             | 2
2             | 1
1             | 3
--------------------------

现在，我需要对项目列表进行排序，并根据它们与项目A的相似性对其进行排名。

例如：

A和B在3个用户中共有1个用户，在2个工具中有2个用户，因此它们的相似度排名是（1/2 + 2/2）/ 2 = 75％

A和C没有共同的用户，但拥有2台以上的仪器中的1台，因此（1/2）/ 2 = 25％

所以B比be更相似，输出应该是

--------------
项目| 秩
--------------
2 | 75
3 | 25

这是我想到的第一个解决方案…
如果我是在PHP和MySQL中完成的，那将是这样的：

 for all tables as table_x
    for all projects (except A) as prj_y
         unique = (Select distinct count(items) from table_x where project is A)
         count += (Select distinct count(items) from table_x
                   where project is prj_x and items are in
                     (select distinct items from table_x where project is a)
                  )/unique

因此，复杂度将为O（ n 2），并且使用索引编制索引时，选择也将花费O（log n ），而这是无法承受的。

您是否有想法完全在MySQL中完成或以更好，更快的方式进行？

*更多信息和注释：*

我仅限于PHP和MySQL。
这只是一个例子，在我的实际项目中，表有20多个表，因此该解决方案应该具有 高性能 。
这个问题是对这个问题的补充问题：如果yr解决方案可以同时或以某种方式应用于两者（在某种程度上），那么可以在MySQL数据库中获得重复最多的相似字段（这将是巨大的）。我想将相关项目的价值乘以项目的相似性以获得最佳选择…

总之，这两个问题将是：获得最相关的项目，获得所有项目的相似项目，并为当前项目找到最相似的项目，其中该项目也与当前项目相似！哟

感谢您的智力回答，如果您能对情况有所了解，我们将不胜感激

阅读 168

2021-04-28

共1个答案

小编典典

你可以做这样：

SET @Aid = (SELECT id
            FROM Project_tbl
            WHERE Project_name = 'A');

SELECT P.id
  , (IFNULL(personel.prop, 0) +
     IFNULL(instrument.prop, 0)
    )/2*100 Rank
  , personel.prop AS personell
  , instrument.prop AS instrument
FROM Project_tbl P
LEFT JOIN
  ( SELECT B.Project_id pid, COUNT(*)/C.ref prop
    FROM personel_project_tbl A,
         personel_project_tbl B,
         (SELECT COUNT(*) AS ref
          FROM personel_project_tbl
          WHERE Project_id = @Aid
         ) AS C
    WHERE A.user_id = B.user_id
    AND A.Project_id = @Aid
    GROUP BY B.Project_id
  ) personel ON P.id = personel.pid
LEFT JOIN
  ( SELECT B.Project_id pid, COUNT(*)/C.ref prop
    FROM instrument_project_tbl A,
        instrument_project_tbl B,
         (SELECT COUNT(*) AS ref
          FROM instrument_project_tbl
          WHERE Project_id = @Aid
         ) AS C
    WHERE A.instrument_id = B.instrument_id
    AND A.Project_id = @Aid
    GROUP BY B.Project_id
  ) instrument ON P.id = instrument.pid
WHERE P.id <> @Aid
ORDER BY Rank DESC

想法是每个表都有一个子查询，并且这些子查询中的每一个将项目ID映射到给定表的对应率。

我什么都没说关于性能。您将不得不尝试看看它是否足够快地满足您的需求，但是正如我所看到的那样，由于必须检查所有数据，因此无法克服您提到的O（ n
2）复杂性。

2021-04-28