ЗАДАЧА
Есть таблица истории просмотров фильмов в онлайн-кинотеатре.
Два поля: Пользователь, Фильм
Множество строк: тысячи пользователей и фильмов.
Нужно сделать SQL, который бы реализовал логику алгоритма рекомендаций
фильмов к просмотру, которые пользователи еще не смотрели.
Логику рекомендаций предлагайте на свое усмотрение.
На выходе нужна таблица: Пользователь, Рекомендованный фильм, Рейтинг
рекомендации
РЕШЕНИЕ
Система рекомендаций будет построена на принципе коллаборативной
фильтрации. Коллаборативная фильтрация позволяет предсказать интересы
пользователя на базе интересов другого пользователя. Основная идея
состоит в том, что если пользователи А и Б выбирают какой-то фильм, то
высока вероятность схожести их интересов и по другим фильмам.
В рамках данной задачи схожесть интересов пользователей определяется как
N% общих просмотренных фильмов. То есть если у пользователя А N% общих
просмотренных фильмов с пользователем Б, то мы можем рекомендовать
пользователю А фильмы, которые посмотрел пользователь Б. Рейтинг
рекомендации построен индивидуально для каждого пользователя на
основании количества просмотров фильма у пользователей со схожими
интересами.
**База данных **
В качестве базы данных используется таблица likes, которая имеет два
столбца – id пользователя (user_id) и id фильма (movie_id):
create table likes (
user_id int,
movie_id int
);
Пример базы данных
| **user_id ** | **movie_id ** |
|---|---|
| 1 | 4 |
| 1 | 8 |
| 2 | 2 |
| 2 | 3 |
| 2 | 1 |
| 2 | 6 |
| 2 | 7 |
| 2 | 9 |
| 2 | 11 |
| 2 | 12 |
| 3 | 13 |
| 3 | 6 |
| 3 | 8 |
| 3 | 3 |
| 3 | 11 |
| 3 | 2 |
| 4 | 1 |
| 4 | 7 |
| 4 | 8 |
| 4 | 11 |
| 4 | 3 |
| 5 | 1 |
| 5 | 8 |
| 5 | 7 |
| 5 | 2 |
| 5 | 3 |
SQL-запрос (PostgreSQL)
with users as (
/* 2. Таблица users содержит сгруппированную информацию о пользователях,
которые имеют >= 10% общих просмотренных фильмов */
select
distinct user1_id,
user2_id
from
/* 1. Кросс-джойн таблица following для всех пользователей и фильмов
со столбцом common_interest позволяет отследить какие фильмы
были просмотрены обоими пользователями */
(select
cus.user_id as user1_id,
ous.user_id as user2_id,
cus.movie_id as movie1_id,
ous.movie_id as movie2_id,
(cus.movie_id = ous.movie_id) as common_interest
from
likes as ous
join likes as cus
on cus.user_id <> ous.user_id) following
group by
user1_id, user2_id
having
((count(*) filter (where "common_interest")::float) / (count(*)::float)) >=
0.1
), user_likes as (
/* 3. Таблица user_likes содержит информацию о пользователях, фильмах
и количестве просмотров фильмов пользователей с общими интересами,
включая самого пользователя */
select users.user1_id, likes.movie_id, count(likes.user_id) as like_count
from users
left join likes on users.user2_id = likes.user_idgroup by users.user1_id, likes.movie_id
)
/* 4. Таблица user_likes фильтруется таким образом, чтобы
не рекомендовать пользователю уже просмотренные им фильмы.
Выводится результат рекомендации для каждого пользователя в финальном виде.
**Значения отсортированы по убыванию id пользователя */**
select
user_likes.user1_id as "Пользователь",
user_likes.movie_id as "Рекомендованный фильм",
user_likes.like_count as "Рейтинг рекомендации"
from user_likes
left join likes
on user_likes.user1_id = likes.user_id and user_likes.movie_id =
likes.movie_id
where likes.movie_id is null
order by user_likes.user1_id, user_likes.like_count desc
Результат
| **Пользователь ** | **Рекомендованный фильм ** | **Рейтинг рекомендации ** |
|---|---|---|
| 1 | 3 | 2 |
| 1 | 7 | 2 |
| 1 | 2 | 1 |
| 1 | 11 | 1 |
| 2 | 8 | 2 |
| 3 | 1 | 2 |
| 3 | 7 | 2 |
| 4 | 2 | 3 |
| 4 | 6 | 2 |
| 4 | 4 | 1 |
| 4 | 9 | 1 |
| 4 | 12 | 1 |
| 4 | 13 | 1 |
| 5 | 11 | 3 |
| 5 | 6 | 2 |
| 5 | 9 | 1 |
| 5 | 4 | 1 |
| 5 | 12 | 1 |
| 5 | 13 | 1 |
Решение на SQL Fiddle: