我目前正在编写一个基于已回答问题匹配用户的网络应用程序。我已经在一个查询中实现了我的匹配算法，并将其调整到目前需要 8.2 毫秒来计算 2 个用户之间的匹配百分比。但是我的 webapp 必须获取用户列表并遍历执行此查询的列表。对于 5000 个用户，在我的本地机器上花费了 50 秒。是否可以将所有内容都放在一个查询中，该查询返回一列包含 user_id 和一列包含计算出的匹配项？还是存储过程是一个选项？

我目前正在使用 MySQL，但如果需要我愿意切换数据库。

对于任何对模式和数据感兴趣的人，我创建了一个 SQLFiddle:http://sqlfiddle.com/#!2/84233/1

和我的匹配查询:

SELECT COALESCE(SQRT( (100.0*as1.actual_score/ps1.possible_score) * (100.0*as2.actual_score/ps2.possible_score) ) - (100/ps1.commonquestions), 0) AS perc
  FROM (SELECT SUM(imp.value) AS actual_score 
      FROM user_questions AS uq1
      INNER JOIN importances imp ON imp.id = uq1.importance
      INNER JOIN user_questions uq2 ON uq2.question_id = uq1.question_id AND uq2.user_id = 101
        AND (uq1.accans1 = uq2.answer_id 
          OR uq1.accans2 = uq2.answer_id
          OR uq1.accans3 = uq2.answer_id
          OR uq1.accans4 = uq2.answer_id)
      WHERE uq1.user_id = 1) AS as1, 
  (SELECT SUM(value) AS possible_score, COUNT(*) AS commonquestions
      FROM user_questions AS uq1
      INNER JOIN importances ON importances.id = uq1.importance
      INNER JOIN user_questions uq2 ON uq1.question_id = uq2.question_id AND uq2.user_id = 101
      WHERE uq1.user_id = 1) AS ps1,
  (SELECT SUM(imp.value) AS actual_score 
      FROM user_questions AS uq1
      INNER JOIN importances imp ON imp.id = uq1.importance
      INNER JOIN user_questions uq2 ON uq2.question_id = uq1.question_id AND uq2.user_id = 1
        AND (uq1.accans1 = uq2.answer_id 
          OR uq1.accans2 = uq2.answer_id
          OR uq1.accans3 = uq2.answer_id
          OR uq1.accans4 = uq2.answer_id)
      WHERE uq1.user_id = 101) AS as2, 
  (SELECT SUM(value) AS possible_score 
      FROM user_questions AS uq1
      INNER JOIN importances ON importances.id = uq1.importance
      INNER JOIN user_questions uq2 ON uq1.question_id = uq2.question_id AND uq2.user_id = 1
      WHERE uq1.user_id = 101) AS ps2

最佳答案

我很无聊，所以:这是您的查询的重写版本 - 基于您模式的 PostgreSQL 端口 - 一次计算所有用户配对的匹配:

http://sqlfiddle.com/#!12/30524/6

我已经检查过，它为用户对 (1,5) 产生了相同的结果。

WITH
userids(uid) AS (
    select distinct user_id from user_questions
),
users(u1,u2) AS (
    SELECT u1.uid, u2.uid FROM userids u1 CROSS JOIN userids u2 WHERE u1 <> u2
),
scores AS (
        SELECT
            sum(CASE WHEN uq2.answer_id IN (uq1.accans1, uq1.accans2, uq1.accans3, uq1.accans4) THEN imp.value ELSE 0 END) AS actual_score,
            sum(imp.value) AS potential_score,
            count(1) AS common_questions,
            users.u1,
            users.u2
        FROM user_questions AS uq1
        INNER JOIN importances imp ON imp.id = uq1.importance
        INNER JOIN user_questions uq2 ON uq2.question_id = uq1.question_id
        INNER JOIN users ON (uq1.user_id=users.u1 AND uq2.user_id=users.u2)
        GROUP BY u1, u2
),
score_pairs(u1,u2,u1_actual,u2_actual,u1_potential,u2_potential,common_questions) AS (
    SELECT s1.u1, s1.u2, s1.actual_score, s2.actual_score, s1.potential_score, s2.potential_score, s1.common_questions
    FROM scores s1 INNER JOIN scores s2 ON (s1.u1 = s2.u2 AND s1.u2 = s2.u1)
    WHERE s1.u1 < s1.u2
)
SELECT
    u1, u2, 
    COALESCE(SQRT( (100.0*u1_actual/u1_potential) * (100.0*u2_actual/u2_potential) ) - (100/common_questions), 0) AS "match"
FROM  score_pairs;

您没有理由不能将它移植回 MySQL，因为 CTE 只是为了提高可读性，并且不会做您不能用 FROM (SELECT ...) 做的任何事情.没有 WITH RECURSIVE 子句，也没有从多个其他 CTE 引用 CTE。您可能会遇到一些可怕的嵌套查询，但这只是格式方面的挑战。

变化:

• 生成一组不同的用户
• 自行加入这组不同的用户以创建一组用户配对
• 然后加入分数查询中的配对列表以生成分数表
• 通过组合 possiblescore1 和 possiblescore2、actualscore1 和 actualscore2 的大量重复查询来生成分数表。
• 然后在最终的外部查询中总结

我没有优化查询；如所写，它在我的系统上运行 5 毫秒。在更大的数据上，您可能需要重组其中的一些数据或使用一些技巧，例如将一些 CTE 子句转换为 SELECT ... INTO TEMPORARY TABLE 临时表创建语句，然后在查询之前对其进行索引。

您也可能希望将 users 行集的生成从 CTE 中移出并移到 scoresFROM 子查询子句中。这是因为 WITH 需要充当子句之间的优化栅栏，因此数据库必须具体化行，并且不能使用像向上或向下推送子句这样的技巧。

关于mysql - SQL:返回具有匹配百分比计算列的用户表？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/12987413/