如何在使用MYSQL生成分数时检查所有联接

Question

我使用MYSQL为查询返回的每个结果生成一个分数。然后根据分数对结果进行排序。

似乎工作不正常的部分是，当我试图为每个已搜索的标签添加分数，并将结果分配给它时。所以，假设我搜索标签"example"、"test"one_answers"tag"，并将我的一个结果分配给标签"example"、"test"one_answers"someothertag"，它应该得到10分，因为有2个匹配。

实际情况是，无论匹配多少标签，如果有匹配，我都会得到5分。如果没有标签匹配，则为0。

以下是从搜索中生成的一个查询的示例。

        SELECT DISTINCT results.*,
                    ( 
                        5*(MATCH(tags.name) AGAINST('"self employed"' IN BOOLEAN MODE)) +
            5*(MATCH(tags.name) AGAINST('"rental income"' IN BOOLEAN MODE)) +
            5*(MATCH(tags.name) AGAINST('"commission income"' IN BOOLEAN MODE)) +
            5*(MATCH(tags.name) AGAINST('"bankruptcy"' IN BOOLEAN MODE)) +
            5*(MATCH(tags.name) AGAINST('"condo approval"' IN BOOLEAN MODE)) +
                        1*usefulness + 
                        10*shares 
                    ) AS score 
        FROM results
        INNER JOIN categories c on results.ID = c.RESULT_ID
        INNER JOIN tags ON results.id = tags.result_id
        WHERE c.name in ('purchase', 'condo', 'va')
        AND ( tags.name = 'self employed' OR tags.name = 'rental income' OR tags.name = 'commission income' OR tags.name = 'bankruptcy' OR tags.name = 'condo approval'  )
        AND ( results.scope = 'all' OR results.scope = 'hi' )
        AND published = 1
        GROUP BY results.ID
        having count(distinct c.c_id) = 3
        ORDER BY score DESC 
        LIMIT 8 OFFSET 0

Answer 1

根据Sam Dufel的建议，您可能不需要全文搜索，尤其是因为您在WHERE子句中使用了精确的字符串比较。

此外，由于results和categories之间的多对多关系(根据HAVING COUNT(c_id) = 3子句假设(，我认为决不能将categories和tags都加入到同一个查询中。

如果没有GROUP BY子句，对于一个给定的result，您将为每个匹配的category获得一行。对于每个匹配对(result、category(，您将为每个匹配的tag.name获得一行。我认为没有办法处理这样的结果。

我的建议是：

步骤1：使results出现在所有三个类别中

SELECT results.ID
FROM results
JOIN categories ON results.id = categories.result_id
WHERE categories.name IN ('purchase', 'condo', 'va')
GROUP BY results.ID
HAVING COUNT(DISTINCT c.c_id) = 3

步骤2：计算与至少一个搜索字符串匹配的任何results的分数

SELECT
    DISTINCT results.*, -- DISTINCT is redundant because of the GROUP BY clause
    ( 
        5*(COUNT(tags.result_id)) + -- you actually want to count the number of matches!
        1*usefulness +  -- warning, see below 
        10*shares       -- warning, see below
    ) AS score 
FROM results
INNER JOIN tags ON results.id = tags.result_id
WHERE
    tags.name = 'self employed'
    OR tags.name = 'rental income'
    OR tags.name = 'commission income'
    OR tags.name = 'bankruptcy'
    OR tags.name = 'condo approval'
GROUP BY results.ID

第3步：将所有放在一起

SELECT
    results.*,
    ( 
        5*(COUNT(tags.result_id)) +
        1*usefulness +  -- warning, see below 
        10*shares       -- warning, see below
    ) AS score 
FROM (
        SELECT results.id
        FROM results
        JOIN categories ON results.id = categories.result_id
        WHERE
            categories.name IN ('purchase', 'condo', 'va')
            AND ( results.scope = 'all' OR results.scope = 'hi' )
            AND published = 1
        GROUP BY results.id
        HAVING COUNT(DISTINCT categories.c_id) = 3
) AS results_subset
JOIN results ON results_subset.id = results.id
JOIN tags ON results.id = tags.result_id
WHERE
    tags.name = 'self employed'
    OR tags.name = 'rental income'
    OR tags.name = 'commission income'
    OR tags.name = 'bankruptcy'
    OR tags.name = 'condo approval'
GROUP BY results.ID

请注意，我选择在scope和published上包含where条件的位置。这种选择是基于应尽早说明过滤器的原则。如果将它们放在外部查询中，可能会获得更好的性能，但这实际上取决于基数。

警告：字段usefulness和shares都不属于未包含在聚合函数中的GROUP BY函数。这是MySQL允许的，但非常危险。如果usefulness和shares属于result以外的表(该表为GROUP’ed BY(，则查询中返回的值是未定义的。

Answer 2

按如下方式编写：

   "sum((5*(MATCH(tags.name) AGAINST('"self employed"' IN BOOLEAN MODE))), 
        (5*(MATCH(tags.name) AGAINST('"rental income"' IN BOOLEAN MODE))) ,
        (5*(MATCH(tags.name) AGAINST('"commission income"' IN BOOLEAN MODE))),
        (5*(MATCH(tags.name) AGAINST('"bankruptcy"' IN BOOLEAN MODE))),
        (5*(MATCH(tags.name) AGAINST('"condo approval"' IN BOOLEAN MODE))),
      (1*usefulness), (10*shares)) as score"

Answer 3

您需要求和((分数，因为一行只匹配一个标记。

在您的查询中，选择了多个行，并按ID对它们进行分组，因此您只得到一行的结果，在您的情况下，结果总是5。

Answer 4

我认为您的查询太复杂了。试试这个：

SELECT
    results.*,
    5 * count(distinct tags.name) + 1*usefulness + 10*shares AS score 
FROM results
JOIN categories c on results.ID = c.RESULT_ID
    AND c.name in ('purchase', 'condo', 'va')
JOIN tags ON results.id = tags.result_id
    AND tags.name in ('self employed', 'rental income', 'commission income', 'bankruptcy', 'condo approval')
WHERE results.scope in ('all', 'hi')
AND published = 1
GROUP BY 1, 2, 3, 4, 5 -- list as many numbers here as there are columns in "results" 
HAVING count(distinct c.c_id) = 3
ORDER BY score DESC 
LIMIT 8 OFFSET 0

您遇到的一个关键问题是分组——为了使其正常工作，您需要命名或通过所选位置引用results表的所有列。你还没有给出表模式，所以我不知道该写什么。我猜出了5列，因此是GROUP BY 1, 2, 3, 4, 5，但您需要确保这是正确的。

我通过将OR s更改为IN s来整理它们——这样做将允许在这些列上使用索引，如果存在这样的索引("OR"不会使用索引(。

我已经将一些WHERE子句条件移到了JOIN条件中，这是有意义的——这应该会提高性能。