提问人：Andrew G. Johnson 提问时间：12/21/2008 最后编辑：Andrew G. Johnson 更新时间：12/21/2008 访问量：774
在带标签的视频系统上查找有关“相关视频”查询的建议
Looking for advice on a "related videos" query on a tagged video system
问：
好吧，我经营着一个小型视频网站，在实际的视频页面上，有一条类似于大多数视频方面（例如 YouTube）的“相关视频”，目前我所做的只是随机获取其中一个标签并查找具有相同标签的其他视频。毫不奇怪，这不是一个好方法，因为有些标签非常模糊，有些视频被错误标记。
当前查询示例：
SELECT video_name FROM videos INNER JOIN videotags ON videos.id=videotags.video_id INNER JOIN tags ON tags.id=videotags.tag_id WHERE tag_name='x' AND videos.id<>'y' LIMIT 5
其中 x 是当前视频中的任意一个标签，y 是当前视频中的 ID。（附言我正在使用参数化查询，别担心）
我只是好奇你们会如何处理这个问题，也许合并类似的视频标题会更好？
以下是我的数据库表的设置方式：
VIDEOS TABLE
------------
video_id [PK,auto_increment] int(11)
video_name varchar(255)
TAGS TABLE
----------
tag_id [PK,auto_increment] int(11)
tag_name varchar(255)
VIDEOTAGS TABLE
---------------
tag_id [PK,FK] int(11)
video_id [PK,FK] int(11)
视频表中显然有更多的列，但这只是说明了两端自动递增主键的简单多对多关系
该网站是基于PHP构建的，带有MySQL数据库，但这并不重要:)
编辑：有人说要走一条有机路线，所以我想我会发布另外两个与视频观看量和视频收视率有关的半相关的表格。现在请注意，由于隐私问题，我无意专门向视频观看次数表添加更多列（是的，我知道我将 IP 存储在评级表中）
VIDEOVIEWS TABLE
----------------
video_id [FK] int(11)
view_time datetime
VIDEORATINGS TABLE
------------------
video_id [PK,FK] int(11)
ip_address [PK] varchar(15)
rating int(1)
rate_time datetime
答：
1赞
frankodwyer
12/21/2008
#1
非常有趣的问题。
这只是大声思考，但我能想到的一些选择是：
1） 使用所有标签 - 例如，假设查询包含此视频所做的每个标签的视频列表。生成一个视频列表，按它们出现的列表数量排序，即它们与该视频共有的标签数量。具有更多共同标签的那些可能是“更相关”的。
（我不是建议你在现实中做多个查询，只是想解释我的想法......比我更好的 SQL-fu 的人可能会想出一个查询来做到这一点。也许您还可以按受欢迎程度或您可能拥有的其他信息进行排序）。
2）尝试使用一种算法，让相关视频自然出现，就像亚马逊一样，“买了这个的人也买了这个”。例如，如果跟踪谁查看了哪些内容，则可以设计生成此类列表的查询。
评论
0赞
Andrew G. Johnson
12/21/2008
第一个想法基本上是我的想法，但我不知道如何将其转化为 SQL。正如你所说，我的 SQL-fu 不够强大
0赞
frankodwyer
12/21/2008
嗯，这是一个问的好地方......也许将该特定查询作为单独的 SQL 问题提出想法，并将其链接到此问题？但我认为这个问题也值得保留，这是一个很好的问题。
4赞
Bill Karwin
12/21/2008
#2
此查询应返回具有与给定视频 （v1） 相同的标记的视频 （v2） 的 ID，按共同标记数的降序排列。
SELECT v2.video_id
FROM VideoTags AS v1
JOIN VideoTags AS v2
USING (tag_id)
WHERE v1.video_id = ?
AND v1.video_id <> v2.video_id
GROUP BY v2.video_id
ORDER BY COUNT(*) DESC;
评论
0赞
lpfavreau
12/21/2008
您还可以添加 LIMIT 5（例如，将相关视频的数量限制为 5 个）并将最后一行更改为 ORDER BY COUNT（*） DESC， RAND（），以便在每次视频具有相同分数时获得随机视频。
0赞
Andrew G. Johnson
1/1/2009
在完成工作时给出了这个最佳答案，希望得到一些其他意见，但有什么:)
评论