Форум пользователей MySQL

lokiby · 12.05.2008 11:53:46

Помогите решить проблему

есть таблица в которой ведется список всех покупок всех пользователей, нужно предложить пользователю продукт который купил другой пользователь, но при этом учитывать схожесть списков
user_id | prod_id
1 | 222
1 | 333
1 | 444
2 | 222
2 | 111
3 | 555
3 | 111
3 | 222

нужно сделать такой запрос чтобы при поиске user_id=2 и file_id=222 он выдал
user_id | prod_id
3 | 555
1 | 333
1 | 444

т.к. у user_id 2 есть prod_id=222 и prod_id=111 что на 2/3 совпадает со списком user_id=3 и только на 1/3 со списком user_id=1

LazY · 12.05.2008 12:19:46

Если я правильно понял задачу, то мне видится в такой задаче два этапа:
1. Получение весовых соотношений совпадения для каждого пользователя
2. Выборка продуктов в соответсвующих весовых соотношениях от каждого пользователя

Загвоздка в том, что п.2 реализуется через запрос определенного количества строк (скорее всего, через LIMIT это будет), что есть всегда величина целая. Поэтому весовые соотношения, полученные из п.1, нужно будет округлять до целого.

Одним запросом такое не сделать, поэтому я думаю, что нужно будет писать хранимую процедуру (если у Вас MySQL не ниже 5)

у user_id 2 есть prod_id=222 и prod_id=111 что на 2/3 совпадает со списком user_id=3 и только на 1/3 со списком user_id=1

А почему тогда от 1 в два раза больше записей, чем от 3, хотя соотношение совпадения списков обратное?

lokiby · 12.05.2008 12:55:01

соотношение совпадения списков определяет порядок, и по большому щету мне нужны только user_id и только по одному разу, но в порядке убывания совпадения списков
user_id
3
1

и mysql у меня 4.1.22

LazY · 12.05.2008 14:47:08

по большому щету мне нужны только user_id и только по одному разу, но в порядке убывания совпадения списков

Такой список получить просто - нужно выбрать всех пользователей, у которых есть товары, присутствующие у пользователя с user_id=2 (кроме самого этого пользователя, иначе будет дублирование) и упорядочить их по количеству совпавших товаров:

SELECT user_id FROM tbl 

WHERE prod_id IN -- вот условие, задающее совпадение товаров

  (SELECT prod_id FROM tbl WHERE user_id = 2) -- подзапрос, определяющий набор товаров для совпадения

AND user_id != 2 -- кроме самого пользователя, для которого делаем выборку

GROUP BY user_id -- товары нужно сгруппировать по пользователям, чтобы посчитать

ORDER BY COUNT(*) DESC; -- сортируем по количеству товаров

Следующий шаг - выборка вместе с этими пользователями товаров. Для этого результат предыдущего запроса нужно через JOIN прилепить к выборке перечня товаров и пользвователей:

SELECT t.user_id, t.prod_id

FROM tbl t

INNER JOIN 

-- соединяем через INNER JOIN, иначе из таблицы полезут и те пользователи, у которых не совпало  

-- ни одного товара; хотя они и будут в конце, нам они вообще не нужны

  (

  SELECT COUNT(*) AS numprod, user_id

  -- нам понадобился новый столбец - количество продукци; хотя он в запросах нигде не фигурирует,

  -- он нужен, чтобы обратиться к нему потом при сортировке - см. ниже

  FROM tbl WHERE prod_id IN

    (SELECT prod_id FROM tbl WHERE user_id = 2)

  AND user_id != 2

  GROUP BY user_id

  ) AS t2 -- чтобы подзапрос использовать как таблицу, ему нужно присвоить псевдоним, что и делаем словом AS

  ON t2.user_id = t.user_id -- работаем как при обычном JOIN'е - соединяем 

WHERE prod_id != 222 

ORDER BY t2.numprod DESC -- это чтобы сначала шли пользователи с более полным совпадением

Что-то, гляжу, многовато комментариев насовал. Не буду на всякий случай убирать - вдруг помогут.

Вообще такого вида запрос всегда весьма тяжёл для сервера.
Если окажется, что это узкое место производительности, то тогда придется делать в два-три захода запросами из клиента
(была бы у Вас MySQL 5 - можно было бы написать процедуру...)

lokiby · 13.05.2008 12:34:25

большое спасибо ))

EugeneTM · 13.05.2008 17:47:35

С учетом потенциальной проблемы с подзапросом
http://sqlinfo.ru/forum/viewtopic.php?id=559

Возможно лучше будет

SELECT t.user_id FROM tbl t
WHERE EXISTS(
SELECT tbl.prod_id
FROM tbl
WHERE tbl.user_id = 2
AND t.prod_id = tbl.prod_id)
AND user_id != 2
GROUP BY t.user_id
ORDER BY COUNT(*) DESC;

Ну и соответственно переделать второй шаг.

Форум пользователей MySQL

#1 12.05.2008 11:53:46

выборка по совпадению множества записей

#2 12.05.2008 12:19:46

Re: выборка по совпадению множества записей

#3 12.05.2008 12:55:01

Re: выборка по совпадению множества записей

#4 12.05.2008 14:47:08

Re: выборка по совпадению множества записей

#5 13.05.2008 12:34:25

Re: выборка по совпадению множества записей

#6 13.05.2008 17:47:35

Re: выборка по совпадению множества записей

Board footer