寻找有关“相关视频"的建议；在带标签的视频系统上查询 [英] Looking for advice on a "related videos" query on a tagged video system

问题描述

好吧，我经营一个小型视频网站，在实际的视频页面上，有一条类似于大多数视频方面(例如YouTube)的相关视频"，目前我正在做的是随机抽取其标签之一并查找其他具有相同标签的视频.毫不奇怪，这不是一个好方法，因为有些标记非常模糊，有些视频标记有误.

Well I run a small video website and on the actual video page there is a strip of "related videos" similar to most video sides (e.g. YouTube) and currently all I'm doing is taking one of its tags randomly and finding other videos with the same tag. Not surprisingly this isn't a great method as some tags are very vague and some videos are mis-tagged.

当前查询的示例:

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. (P.S.我正在使用参数化查询，不用担心)

Where x is any one of the tags from the current video and y is the ID from the current video. (P.S. I'm using parameterized queries don't worry)

我只是想知道大家如何处理这个问题，也许合并类似的视频标题会更好?

I'm just curious as to how you all would handle this, maybe it would be better to incorporate similar video titles?

这是我的数据库表的设置方式:

Here is how my database tables are setup:

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)

在视频表中显然有更多的列，但这仅说明了简单的多对多关系，并自动在两侧增加了主键

There's obviously more columns in the videos table but this just illustrates the simple many-to-many relationship with auto-incrementing primary keys on both sides

该网站基于带有MySQL数据库的PHP构建，但这没关系:)

The site is built on PHP with a MySQL database, but that really doesn't matter :)

编辑:有人谈论顺其自然，所以我认为我要发布另外两个与视频观看次数和视频收视率相关的表格.现在请注意，由于隐私问题(我知道我将IP存储在分级表中)，因此我无意在视频观看表中添加更多的列

There's been some talk of going down an organic route so I figure I'd post my other two tables that are semi-related that are to do with video views and video ratings. Now note I don't have any intention of adding more columns specifically to the video views table because of privacy issues (yes I know I store IPs in the rating table)

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

推荐答案

此查询应返回视频(v2)的ID，这些视频的ID与给定视频(v1)的标签相同，且顺序为常见的.

This query should return the id's of videos (v2) that have tags in common with your given video (v1), in descending order of the number of tags in common.

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;

这篇关于寻找有关“相关视频"的建议；在带标签的视频系统上查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！