Нужен совет по запросу "похожие видео" в видеосистеме с тегами
Что ж, у меня есть небольшой веб-сайт с видео, и на самой странице видео есть полоса «похожих видео», похожих на большинство сторон видео (например, 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 - идентификатор текущего видео. (P.S. Я использую параметризованные запросы, не беспокойтесь)
Мне просто интересно, как вы все справитесь с этим, может быть, было бы лучше включить похожие названия видео?
Вот как настраиваются мои таблицы базы данных:
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
Ответы 2
Очень интересный вопрос.
Это просто размышления вслух, но я могу придумать следующие варианты:
1) Используйте все теги - например, представьте себе запросы для списка видео, которые имеют каждый тег, который есть в этом видео. Создайте список видео, упорядоченный по количеству тех списков, в которых они появляются, то есть подсчету количества общих тегов с этим видео. Те, у кого больше общих тегов, по-видимому, «более связаны».
(Я не предлагаю вам выполнять несколько запросов в реальности, просто пытаюсь объяснить, что я имею в виду ... кто-то с лучшим SQL-фу, чем я, вероятно, может придумать единственный запрос, который сделает это. Возможно, вы можете дополнительно заказать по популярности или другой информации, которая у вас может быть).
2) Постарайтесь разработать алгоритм, позволяющий естественным образом отображать похожие видео, а-ля амазонка «люди, купившие это, тоже купили это». Например, если вы отслеживаете, кто что просматривал, вы можете разработать запрос, который будет создавать такой список.
Что ж, это хорошее место, чтобы спросить ... возможно, попросите идеи по этому конкретному запросу в виде отдельного вопроса SQL и связать его с этим? Но я думаю, что этот вопрос тоже стоит оставить, это хороший вопрос.
Этот запрос должен возвращать идентификаторы видео (v2), у которых есть общие теги с вашим видео (v1), в порядке убывания количества общих тегов.
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;
Вы также можете добавить LIMIT 5 (например, чтобы ограничить количество связанных видео до 5) и изменить последнюю строку на ORDER BY COUNT (*) DESC, RAND (), чтобы получать случайные видео каждый раз, когда они имеют одинаковую оценку.
Дал этот лучший ответ, поскольку он выполняет свою работу, надеялся получить несколько других мнений, но whatevz :)
Идея первая - это в основном то, о чем я думал, но я понятия не имею, как ее перевести на SQL. Как вы выразились, мой SQL-fu недостаточно силен