mysql - 如何使用这种协同过滤形式实现相关文章算法

Question

如标题所示，我在实现相关文章算法时遇到了问题。让我首先列出数据库中的表:

[articles]
id_article
id_category
name
content
publish_date
is_deleted

[categories]
id_category
id_parent
name

[tags_to_articles]
id_tag
id_article

[tags]
id_tag
name

[articles_to_authors]
id_article
id_author

[authors]
id_author
name
is_deleted

[related_articles]
id_article_left
id_article_right
related_score

算法

除了 related_articles 之外的所有其他表中都有数据。现在我想用文章之间的分数填充 related_articles(非常重要:该表将用作定向图，文章 A 与文章 B 的分数可能不同于 B 和 A 之间的分数，请参阅列表)。分数是这样计算的:

• 如果所讨论的两篇文章属于同一类别，则在分数中添加一个数字(x)
• 对于他们共同的每个作者，一个数字(y)被添加到分数中
• 对于他们共有的每个标签，一个数字 (z) 被添加到分数中
• 如果我们计算文章 A 和文章 B 的分数，则 now() 和文章 B 的 publish_date 之间的差异将生成一个数字 (t)，将从分数中减去该数字

我的第一个(低效)方法

我试过这样查询:

SELECT a.id, b.id, a.id_category, a.publish_date,
    b.id_category, b.publish_date,
    c.id_tag,
    e.id_author
FROM `articles` a, articles b, 
        tags_to_articles c, tags_to_articles d,
        articles_to_authors e, articles_to_authors f
WHERE a.id_article <> b.id_article AND 
(
    (a.id_article=c.id_article and c.id_tag=d.id_tag and d.id_article=b.id_article)
    OR
    (a.id=e.id_article and e.id_author=f.id_author and f.id_article=b.id_article)
    OR
    (a.id_category=b.id_category)
)

理论上，这会列出每个值得计算分数的元素。但是，这会占用太多时间和资源。

还有别的办法吗？如果得到可行的解决方案，我也愿意调整算法或表格。另外值得注意的是，分数计算是在一个 cron 中完成的，当然我不希望它在每个页面请求上运行。

Answer 1

我严重怀疑您是否能够通过单个语句执行类似的操作并获得任何类型的性能。把它分成几 block 。使用临时表。使用集合操作。

-- First, let's list all tables that share a category.
SELECT   a1.id_article as 'left_article',
         a2.id_article as 'right_article',
         1 as 'score'
INTO     #tempscore
FROM     #articles a1
   INNER JOIN #articles a2 ON
         a1.id_category = a2.id_category
     AND a1.id_article <> a2.id_article

-- Now, let's add up everything that shares an author
INSERT INTO #tempscore (left_article, right_article, score)
SELECT   ata1.id_article,
         ata2.id_article,
         2
FROM     #articles_to_authors ata1
   INNER JOIN #articles_to_authors ata2 ON
         ata1.id_author = ata2.id_author

-- Now, let's add up everything that shares a a tag
INSERT INTO #tempscore (left_article, right_article, score)
SELECT   ata1.id_article,
         ata2.id_article,
         4
FROM     #tags_to_articles ata1
   INNER JOIN #tags_to_articles ata2 ON
         ata1.id_tag = ata2.id_tag

-- We haven't looked at dates, yet, but let's go ahead and consolidate what we know.
SELECT   left_article as 'left_article',
         right_article as 'right_article',
         SUM (score) as 'total_score'
INTO     #cscore
FROM     #tempscore
GROUP BY left_article,
         right_article

-- Clean up some extranneous stuff
DELETE FROM #cscore WHERE left_article = right_article

-- Now we need to deal with dates
SELECT   DateDiff (Day, art1.publish_date, art2.publish_date) as 'datescore',
         art1.id_article as 'left_article',
         art2.publish_date as 'right_article'
INTO     #datescore
FROM     #cscore
   INNER JOIN #articles art1 ON
         #cscore.left_article = art1.id_article
   INNER JOIN #articles art2 ON
         #cscore.right_article = art2.id_article
WHERE    art1.publish_date > art2.publish_date

-- And finally, put it all together
INSERT INTO #related_articles (id_article_left, id_article_right, related_score)
SELECT   s1.left_article,
         s1.right_article,
         s1.total_score + IsNull (s2.datescore, 0)
FROM     #cscore s1
   LEFT  JOIN #datescore s2 ON
         s1.left_article = s2.left_article
     AND s1.right_article = s2.right_article

在我的测试中，分数似乎是正确的，但我没有任何真实的样本数据可以作为依据，所以我不能确定。如果不出意外，这应该为您提供了一个开始的基础。