谁读了这个也读了那个算法 [英] Who read this also read that algorithm
问题描述
Hello All，
我试图找出最好的算法，因为谁读了这个也读了。基本上有一个包含所有数据的表格，即用户ID和文章ID。
1.说我去了文章ID 11.
2.现在系统需要找出所有查看过第11条的用户。
3.现在拿这些用户id找出他们查看过的其他文章，并填写最好的3篇文章谁读了这也读到了
第2步很简单。但可能有1000多名用户查看了第11条。所以说现在系统得到的信息2222用户查看了第11条。
现在令人困惑的部分是如何计算步骤3 ??
有什么建议吗？
我可以举例说明如何在 SQL Server 中做这样的事情（在 MySQL 中应该非常相似）。
首先，假设您有一个名为UserAndArticle的表格，其中包含以下数据：
ID UserID ArticleID
1 10 101
2 10 102
3 10 103
4 20 101
5 20 103
6 30 101
7 30 103
8 40 101
9 40 102
10 40 103
11 50 102
12 50 103
13 60 101
14 60 102
15 70 101
16 70 102
17 70 104
18 70 105
19 70 106
SQL Server 脚本会是这样的（在 MySQL 中你需要改变TOP with LIMIT子句）：
DECLARE @ ArticleID INT = 102
SELECT TOP 3 t2.ArticleID，
COUNT（t2.ArticleID ） AS NoOfViews
FROM UserAndArticle AS t1
INNER JOIN UserAndArticle AS t2
ON t1.UserID = t2.UserID
WHERE t1 .ArticleID = @ ArticleID
AND t2.ArticleID<> @ ArticleID
GROUP BY t2.ArticleID
ORDER BY NoOfViews DESC
在这个例子中，我按阅读第102条的观点数搜索最好的3篇文章。
结果：
ArticleID NoOfViews
101 4
103 3
105 1
此外，您可能希望从结果中消除当前用户的ArticleID。
我希望这有用。
您可以参考下面的示例：
首先，您的mysql数据库中需要3个表，最小字段为如下：
1.'读者'表与'readerid'和'readername'（'readid'是主键）
2.'article'表与'articleid'和'articlename '（'articleid'是主键）
3.'reader_article'表，带有'readerid'和'articleid'（两个复合主键）
第3步的答案是这样的：
SELECT a.articlename，COUNT（ra1 .articleid） FROM reader_article ra1 INNER JOIN article a ON ra1.articleid = a.articleid WHERE ra1.readerid IN
（ SELECT ra2.readerid FROM reader_article ra2 WHERE ra2.articleid = 11）
GROUP BY ra1.aricleid 订购 BY COUNT（ra1.articleid） DESC LIMIT 3
Hello All,
Am trying to work out on best possible algorithm for "who read this also read that". Basically there is a table with all the data i.e. user id and article id.
1. Say I go to article ID 11.
2. Now the system needs to find out all the users who viewed article 11.
3. Now take those user ids and find out the other articles they viewed and populate the best 3 articles for "who read this also read that"
Step 2 is easy. But there might be 1000s of users who viewed article 11. So say now system got the information 2222 users viewed article 11.
Now the confusing part is how to calculate step 3??
Any suggestions??
Hi,
I can give you example how to do something like this in SQL Server (should be very similar in MySQL too).
First, let’s say that you have table named UserAndArticle with the following data:
ID UserID ArticleID 1 10 101 2 10 102 3 10 103 4 20 101 5 20 103 6 30 101 7 30 103 8 40 101 9 40 102 10 40 103 11 50 102 12 50 103 13 60 101 14 60 102 15 70 101 16 70 102 17 70 104 18 70 105 19 70 106
SQL Server script would be something like this (in MySQL you need to change TOP with LIMIT clause):
DECLARE @ArticleID INT = 102 SELECT TOP 3 t2.ArticleID, COUNT(t2.ArticleID) AS NoOfViews FROM UserAndArticle AS t1 INNER JOIN UserAndArticle AS t2 ON t1.UserID = t2.UserID WHERE t1.ArticleID = @ArticleID AND t2.ArticleID <> @ArticleID GROUP BY t2.ArticleID ORDER BY NoOfViews DESC
In this example, I am searching the best 3 articles by the number of views who read article 102.
Result:
ArticleID NoOfViews 101 4 103 3 105 1
Also, you may want to eliminate current user ArticleIDs from the result.
I hope this was helpful.
You may take reference from my example below:
First, you need 3 tables in your mysql database with minimum fields as follows:
1. 'reader' table with 'readerid' and 'readername' ('readid' is primary key)
2. 'article' table with 'articleid' and 'articlename' ('articleid' is primary key)
3. 'reader_article' table with 'readerid' and 'articleid' (both composite primary key)
The answer to step 3 is like this:
SELECT a.articlename, COUNT(ra1.articleid) FROM reader_article ra1 INNER JOIN article a ON ra1.articleid = a.articleid WHERE ra1.readerid IN (SELECT ra2.readerid FROM reader_article ra2 WHERE ra2.articleid="11") GROUP BY ra1.aricleid ORDER BY COUNT(ra1.articleid) DESC LIMIT 3
这篇关于谁读了这个也读了那个算法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
