php - 在标记视频系统上寻找有关 "related videos"查询的建议

Question

好吧，我经营着一个小型视频网站，在实际的视频页面上有一条与大多数视频端(例如 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

Answer 1

此查询应返回具有与给定视频 (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;