php - 这个复杂的查询可以在 MySQL 中实现吗？还是我需要 PHP？

Question

我正在规划一个数据库驱动的网站，根据用户回答问题的方式来匹配用户。我认为最好的方法是在 SELECT 查询中运行匹配计算，但我不知道如何编写查询。

假设我有一个名为 user_answer 的表，它看起来像这样:

+--------+-------------+--------+------------------+--------+
| userid | question_id | answer | preferred_answer | weight |
+--------+-------------+--------+------------------+--------+
| 1      | 20          | 3      |                  | 0      |
| 1      | 24          | 3      | 2, 3             | 1      |
| 1      | 36          | 2      | 2                | 10     |
| 1      | 37          | 3      | 1, 2, 3          | 50     |
| 1      | 40          | 3      | 3                | 250    |
| 2      | 20          | 3      | 3                | 10     |
| 2      | 24          | 3      | 2                | 1      |
| 2      | 25          | 2      |                  | 0      |
| 2      | 26          | 2      |                  | 0      |
| 2      | 40          | 3      | 2                | 250    |
+--------+-------------+--------+------------------+--------+

我想按ma​​tch_percentage进行选择和排序 - match_percentage 应按以下方式计算:

1. 给定用户 ID = 1 (current_user)
2. 选择与 Question_id 匹配的用户 (ma​​tch_user userid = 2)
3. total_weight1 = 匹配的 Question_id 的权重总和当前用户
4. 如果ma​​tch_user的答案位于current_user preferred_answer中，则ma​​tch1_weight = < strong>ma​​tch1_weight + 当前用户的权重
5. total_weight2 = 匹配question_id的权重总和ma​​tch_user
6. 如果 current_user 的答案位于 ma​​tch_user preferred_answer 中，ma​​tch2_weight = ma​​tch2_weight + ma​​tch_user的权重
7. match_percentage = sqrt((ma​​tch1_weight/total_weight1) *(ma​​tch2_weight/total_weight2))

我不知道这是否可能。我预计数据库会变得非常大，因此加载所有数据库并在 PHP 中进行计算可能不是最佳选择 - 但如果我错了，请纠正我。

是否可以在查询中进行所有这些计算？

Answer 1

是的，我相信所有指定的计算都可以在查询中执行。

假设 (userid, Questionid) 是唯一的，我们首先查找具有“匹配”问题的 userid。我们可以通过这样的查询来做到这一点:

SELECT u.answer
     , u.preferred_answer
     , u.weight
     , m.userid           AS m_userid
     , m.question_id      AS m_question_id
     , m.answer           AS m_answer
     , m.preferred_answer AS m_preferred_answer
     , m.weight           AS m_weight
  FROM user_answer u
  JOIN user_answer m
    ON m.question_id = u.question_id
   AND m.userid <> u.userid
   AND u.userid = 1 
 ORDER
    BY m.userid
     , m.question_id

一旦我们完成了这项工作，我们就可以努力获取总权重并从中进行计算。

假设 preferred_answer 列是 VARCHAR 类型，并且包含逗号分隔的元素列表，没有空格，例如'2' 或 '2,3,5'，您可以使用 MySQL FIND_IN_SET 函数返回特定元素的索引位置列表内。如果未找到“匹配”，则返回 0。

我相信此查询符合规范。

SELECT m.userid           AS m_userid
     , SUM(u.weight)      AS total_weight1
     , SUM(IF(FIND_IN_SET(m.answer,u.preferred_answer),u.weight,0)) AS match1_weight
     , SUM(m.weight)      AS total_weight2
     , SUM(IF(FIND_IN_SET(u.answer,m.preferred_answer),m.weight,0)) AS match2_weight
     , SQRT(
         ( SUM(IF(FIND_IN_SET(m.answer,u.preferred_answer),u.weight,0)) / SUM(u.weight) )
       * ( SUM(IF(FIND_IN_SET(u.answer,m.preferred_answer),m.weight,0)) / SUM(m.weight) )
       ) AS match_percentage
  FROM user_answer u
  JOIN user_answer m
    ON m.question_id = u.question_id
   AND m.userid <> u.userid
   AND u.userid = 1 
 GROUP
    BY m.userid
 ORDER
    BY match_percentage DESC

注意:

这些查询仅经过桌面检查。我没有设置 SQL Fiddle 来测试。

第 4 项似乎是当前用户权重的总和，但仅包含匹配答案。如果没有匹配答案，我们将返回 0。与第 6 项相同，但只是相反。)

如果用户 ID 1 和其他用户 ID 之间没有匹配问题，则不会为其他用户 ID 返回任何行。

对于大型设备，这可能会持续一段时间。合适的覆盖索引应该可以提高性能。

为了提高查询性能，您可能需要考虑将此查询的结果“缓存”到单独的表中。仅当原始表中的行被插入、更新、删除时，才需要刷新“缓存”表的内容。并且之前计算的结果对于正常访问来说可能仍然“足够好”。

如果您存储了结果，您还希望将 u.userid 作为 SELECT 列表和 GROUP BY 中的列返回。