Возврат общих строк с другим пользователем в SQL (совместная фильтрация)

Я пытаюсь создать базовую систему рекомендаций по совместной фильтрации с использованием MySQL. У меня есть таблица рейтинга пользователей, как это:

    user_id movie_id rating
1   131     342      3      <<< User 131 has rated movie 342 
2   131     312      5      <<< and also 312
3   122     312      4  
4   213     342      5
5   141     342      5      <<< User 141 has rated 342
6   141     312      4      <<< and also 312 (2 movies in common) 
7   141     323      3

Итак, я пытаюсь найти пользователей, похожих на 131. Я хочу вернуть пользователей, у которых есть как минимум два общих фильма и рейтинг выше 3. Поэтому он должен возвращать строки 5 и 6 (как показано выше).

Это то, что у меня есть до сих пор:

SELECT * from user_ratings
WHERE rating >= 3
AND movie_id IN (SELECT movie_id from user_rating WHERE user_id = 131)
AND user_id != 131

Это возвращает:

    user_id movie_id rating
3   122     312      4     <<< Don't want these two
4   213     342      5     <<<
5   141     342      5     
6   141     312      4

Он возвращает фильмы, общие для пользователей с 131, но мне нужно, чтобы он показывал только тех пользователей, у которых есть как минимум два общих элемента. Как я мог это сделать? Я не уверен, как действовать :(


mysql sql collaborative-filtering
person Hasan Aslam    schedule 25.04.2016    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Сначала вы можете найти user_id, у которых больше или равное количество фильмов, чем user_id = 131 с rating > 3. Затем используйте IN в предложении WHERE, чтобы получить дополнительные данные:

скрипт SQL

SELECT *
FROM user_ratings
WHERE
    user_id IN(
        SELECT user_id
        FROM user_ratings 
        WHERE
            movie_id IN (SELECT movie_id FROM user_ratings WHERE user_id = 131)
            AND rating > 3
        GROUP BY user_id
        HAVING
            COUNT(*) >= (SELECT COUNT(*) FROM user_ratings WHERE user_id = 131)
    )
    AND rating > 3
person Felix Pamittan    schedule 25.04.2016
comment
Спасибо, именно то, что я искал! - person Hasan Aslam; 25.04.2016

arrow_upward
0
arrow_downward

Вы можете использовать самостоятельное присоединение к movie_id, чтобы получить список user_id, которые оценили по крайней мере 2 из тех же фильмов, что и пользователь 131, с рейтингом 4 или выше:

select ur2.user_id
from user_ratings ur1
join user_ratings ur2 
    on ur2.movie_id = ur1.movie_id
    and ur2.user_id <> ur1.user_id
where ur1.user_id = 131
    and ur2.rating > 3
group by ur2.user_id
having count(*) >= 2

http://sqlfiddle.com/#!9/06b56/4

person FuzzyTree    schedule 25.04.2016