Столкнулся с серьёзной проблемой в разработке веб-сайта знакомств для курсовой. Создал всю внешнюю оболочку, обмен сообщениями и т.д., но остался сложный подбор людей по интересам.
Мне нужно, чтобы у пользователей хранились их процентные совпадения по интересам с другими пользователями, чтобы потом я мог находить им друзей. Но сейчас мне нужно конкретно находить процент совпадений.
Ниже прикладываю схему, логику действий, которую я не могу реализовать, и 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((u1
Если записей в БД не много - то можно всегда получать нужные данные на лету. Если тормозит - то можно их, конечно, кешировать в таблице 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
Комментариев нет:
Отправить комментарий