mysql - 使用 MYSQL 生成分数时如何检查所有连接

Question

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

似乎无法正常工作的部分是当我尝试为已搜索的每个标签添加分数并将结果分配给时。所以假设我搜索标签“example”、“test”和“tag”，我的结果之一被分配给标签“example”、“test”、“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

第二步:计算任何匹配至少一个搜索字符串的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 函数的一部分，也不包含在聚合函数中。这是allowed by MySQL但非常危险。如果 usefulness 和 shares 属于 result 以外的表(该表被 GROUP'ed BY)，则查询中返回的值未定义.