Страницы

Поиск по вопросам

вторник, 9 июля 2019 г.

Сравнение и подсчет процентного совпадения по интересам у пользователей

Столкнулся с серьёзной проблемой в разработке веб-сайта знакомств для курсовой. Создал всю внешнюю оболочку, обмен сообщениями и т.д., но остался сложный подбор людей по интересам.
Мне нужно, чтобы у пользователей хранились их процентные совпадения по интересам с другими пользователями, чтобы потом я мог находить им друзей. Но сейчас мне нужно конкретно находить процент совпадений.
Ниже прикладываю схему, логику действий, которую я не могу реализовать, и MySQL код таблиц.
Я уже посмотрел все схожие темы но не нашёл ничего столь запутанного, как моё задание. Прошу вашей помощи, вопрос жизни и смерти! Этот "цикл" будет отдельно для каждого Id_music, id_book и id_film, потому в описании они идут через /
При добавлении поля Id_music/id_book/id_film в таблицы Watched,Read,Listened создавать цикл, равный количеству пользователей минус 1. ID нашего пользователя заносится каждый раз на позицию user_one в таблицу Stats, а id того, с кем сравниваем - в user_two. Считать количество записей по id в таблице Listened/Watched/Read, чтобы узнать общее количество интересов в этой категории у нашего пользователя. Тоже самое делаем для пользователя в цикле
3.Поочередно сравнивать поле ID_Music/ID_Book/ID_Film в таблице Read/Listened/Watched нашего пользователя на совпадение с теми же полями у другого пользователя, что сейчас в цикле. В итоге мы получим число совпадений.
4.Сравниваем это число с количеством записей этих пользователей из пункта 2. К примеру, если у нашего пользователя всего 4 записи , а у второго 8, и совпадений 2, мы заносим в таблицу stats 50(%) в поле bks_prcnt/msc_prcnt/flm_prcnt и 25(%) в поле bks_prcnt_rev/msc_prcnt_rev/flm_prcnt_rev, потому что у второго пользователя больше интересов.
5.?цикл заканчивается. Если поля существовали ранее - обновляем, а не создаём их.
CREATE TABLE `users` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `username` text NOT NULL, `password` text NOT NULL, 'about' text(500), PRIMARY KEY (`id`) ) ENGINE=InnoDB CHARACTER SET=UTF8;
CREATE TABLE `stats` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `user_one` INT(11) NOT NULL, `user_two` INT(11) NOT NULL, `bks_prcnt` INT(4) NOT NULL , `bks_prcnt_rev` INT(4) NOT NULL, `flm_prcnt` INT(4) NOT NULL, `flm_prcnt_rev` INT(4) NOT NULL, `msc_prcnt` INT(4) NOT NULL, `msc_prcnt_rev` INT(4) NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (user_one) REFERENCES users(id), FOREIGN KEY (user_two) REFERENCES users(id) ) ENGINE=InnoDB CHARACTER SET=UTF8;
CREATE TABLE `genres` ( `genre_id` INT(11) NOT NULL AUTO_INCREMENT, `genre_name` text NOT NULL, PRIMARY KEY (`genre_id`) ) ENGINE=InnoDB CHARACTER SET=UTF8;
CREATE TABLE `music` ( `music_id` INT(11) NOT NULL AUTO_INCREMENT, `title` text NOT NULL, `id_genre` INT(11) NOT NULL, `Compositor` text NOT NULL, PRIMARY KEY (`music_id`) , FOREIGN KEY (id_genre) REFERENCES genres(genre_id) ) ENGINE=InnoDB CHARACTER SET=UTF8;
CREATE TABLE `book` ( `book_id` INT(11) NOT NULL AUTO_INCREMENT, `title` text NOT NULL, `id_genre` INT(11) NOT NULL, `Author` text NOT NULL, PRIMARY KEY (`book_id`) , FOREIGN KEY (id_genre) REFERENCES genres(genre_id) ) ENGINE=InnoDB CHARACTER SET=UTF8;
CREATE TABLE `film` ( `film_id` INT(11) NOT NULL AUTO_INCREMENT, `title` text NOT NULL, `id_genre` INT(11) NOT NULL, `Producer` text NOT NULL, PRIMARY KEY (`film_id`), FOREIGN KEY (id_genre) REFERENCES genres(genre_id) ) ENGINE=InnoDB CHARACTER SET=UTF8;
CREATE TABLE `Listened` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `id_user` INT(11) NOT NULL, `id_music` INT(11) NOT NULL, PRIMARY KEY (`id`) , FOREIGN KEY (id_user) REFERENCES users(id), FOREIGN KEY (id_music) REFERENCES music(id) ) ENGINE=InnoDB CHARACTER SET=UTF8;
CREATE TABLE `Watched` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `id_user` INT(11) NOT NULL, `id_film` INT(11) NOT NULL, PRIMARY KEY (`id`) , FOREIGN KEY (id_user) REFERENCES users(id), FOREIGN KEY (id_film) REFERENCES film(id) ) ENGINE=InnoDB CHARACTER SET=UTF8;
CREATE TABLE `Read` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `id_user` INT(11) NOT NULL, `id_book` INT(11) NOT NULL, PRIMARY KEY (`id`) , FOREIGN KEY (id_user) REFERENCES users(id), FOREIGN KEY (id_book) REFERENCES book(id) ) ENGINE=InnoDB CHARACTER SET=UTF8;
и вот несколько Insert-ов (не обращайте внимание на схожесть жанров музыки, книг и фильмов)
INSERT INTO `Users`(`id`, `username`, `password`, `about`) VALUES ('','User1','213','something') INSERT INTO `Users`(`id`, `username`, `password`, `about`) VALUES ('','User2','313','something') INSERT INTO `genres`(`genre_id`, `genre_name`) VALUES ('','Genre1') INSERT INTO `genres`(`genre_id`, `genre_name`) VALUES ('','Genre2') INSERT INTO `genres`(`genre_id`, `genre_name`) VALUES ('','Genre3') INSERT INTO `book`(`book_id`, `title`,'id_genre','Author') VALUES ('','Book1','1','Author1') INSERT INTO `book`(`book_id`, `title`,'id_genre','Author') VALUES ('','Book2','2','Author2') INSERT INTO `music`(`music_id`, `title`,'id_genre','Compositor') VALUES ('','Song1','1','Compositor1') INSERT INTO `music`(`music_id`, `title`,'id_genre','Compositor') VALUES ('','Song2','2','Compositor2') INSERT INTO `film`(`film_id`, `title`,'id_genre','Producer') VALUES ('','Film1','1','Producer1') INSERT INTO `film`(`film_id`, `title`,'id_genre','Producer') VALUES ('','Film2','2','Producer2') INSERT INTO `Read`(`id`, `id_user,'id_book') VALUES ('','1','1') INSERT INTO `Read`(`id`, `id_user,'id_book') VALUES ('','1','2') INSERT INTO `Read`(`id`, `id_user,'id_book') VALUES ('','2','1') INSERT INTO `Listened`(`id`, `id_user,'id_music') VALUES ('','1','1') INSERT INTO `Listened`(`id`, `id_user,'id_music') VALUES ('','1','2') INSERT INTO `Listened`(`id`, `id_user,'id_music') VALUES ('','2','1') INSERT INTO `Watched`(`id`, `id_user,'id_film') VALUES ('','1','1') INSERT INTO `Watched`(`id`, `id_user,'id_film') VALUES ('','1','2') INSERT INTO `Watched`(`id`, `id_user,'id_film') VALUES ('','2','1')


Ответ

Вы работаете с SQL базой данных. SQL позволяет получить любые данные в любом нужном виде и в 99% случаев это делается одним запросом. Если при работе с SQL вам приходится использовать "цикл" и этот цикл предназначен не для вывода готовых данных из БД на клиента - то скорее всего вы что то делаете не так. Полное содержимое для таблицы stats можно получить следующим запросом:
select u1, u2, max(prcnt*(source='Read')) as bks_prcnt, max(prcnt_rev*(source='Read')) as bks_prcnt_rev, max(prcnt*(source='List')) as flm_prcnt, max(prcnt_rev*(source='List')) as flm_prcnt_rev, max(prcnt*(source='Watch')) as msc_prcnt, max(prcnt_rev*(source='Watch')) as msc_prcnt_rev from ( select * from ( select least(u1,u2) u1, greatest(u1,u2) u2, 'Read' as source, max((u1u2)*prcnt) prcnt_rev from ( select R1.id_user u1, R2.id_user u2, round(count(1)*100/R.all_cnt) prcnt from `Read` R1, `Read` R2, ( select id_user u, count(1) all_cnt from `Read` group by id_user ) R where R1.id_book=R2.id_book and R1.id_user!=R2.id_user and R.u=R1.id_user group by R1.id_user, R2.id_user ) A group by least(u1,u2), greatest(u1,u2) ) A UNION ALL select * from ( select least(u1,u2) u1, greatest(u1,u2) u2, 'List' as source, max((u1u2)*prcnt) prcnt_rev from ( select R1.id_user u1, R2.id_user u2, round(count(1)*100/R.all_cnt) prcnt from `Listened` R1, `Listened` R2, ( select id_user u, count(1) all_cnt from `Listened` group by id_user ) R where R1.id_music=R2.id_music and R1.id_user!=R2.id_user and R.u=R1.id_user group by R1.id_user, R2.id_user ) A group by least(u1,u2), greatest(u1,u2) ) A UNION ALL select * from ( select least(u1,u2) u1, greatest(u1,u2) u2, 'Watch' as source, max((u1u2)*prcnt) prcnt_rev from ( select R1.id_user u1, R2.id_user u2, round(count(1)*100/R.all_cnt) prcnt from `Watched` R1, `Watched` R2, ( select id_user u, count(1) all_cnt from `Watched` group by id_user ) R where R1.id_film=R2.id_film and R1.id_user!=R2.id_user and R.u=R1.id_user group by R1.id_user, R2.id_user ) A group by least(u1,u2), greatest(u1,u2) ) B ) C
Если записей в БД не много - то можно всегда получать нужные данные на лету. Если тормозит - то можно их, конечно, кешировать в таблице stats. Запрос легко переделывается для получения похожих пользователей относительно конкретного. Для этого надо добавить условия с ID нужного пользователя во все 3 подзапроса для алиасов R1, а так же в самый глубокий подзапрос получающий all_cnt.
Вообще запрос мог бы быть гораздо проще и короче, если бы вы не захотели в одной записи видеть как прямой так и обратный процент. Из за него приходится сначала получать эти 2 процента отдельными строками, а потом затягивать в одну строку группировкой по least(), greatest() (которая оставляет только строки где первый ID меньше второго).
Так же данный запрос был бы в 3 раза короче (и заодно упростилась бы работа с БД практически везде) если бы вы свели таблицы Book/Music/Film в одну, просто добавив в запись поле "тип контента" и сведя равнозначные поля к одному. После этого таблицы Read/Listed/Watched так же сводятся в одну таблицу и мы можем посчитать как общий процент так и проценты в разрезе типов контента управляя фразой group by в одном коротком запросе.
А что до таблицы stats, то если она понадобится, из нее следует удалить поле id, первичный ключ сделать PRIMARY KEY (user_one, user_two). После этого писать/обновлять данные в ней можно будет как:
insert into stats select ВОТ_ТОТ_БОЛЬШОЙ_ЗАПРОС on duplicate key update bks_prcnt=values(bks_prcnt), bks_prcnt_rev=values(bks_prcnt_rev), ...
Итого: Подучите SQL, он на самом деле довольно простой, несколько базовых элементов можно вкладывать друг в друга сколько угодно глубоко и описывать любой разрез данных. Изучать можно как раз по запросу вверху, берите из него небольшие куски, отдельно их пробуйте, смотрите что они дают, экспериментируйте. И запомните, в SQL нет вопроса "можно ли это сделать", есть только вопрос "как это сделать".
P.S. Многих наверняка напряжет "странная" запись max((u1

Комментариев нет:

Отправить комментарий