SQLinfo.ru - Все о MySQL

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

Задавайте вопросы, мы ответим

Вы не зашли.

#1 12.05.2008 11:53:46

lokiby
Участник
Зарегистрирован: 12.05.2008
Сообщений: 6

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

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

есть таблица в которой ведется список всех покупок всех пользователей, нужно предложить пользователю продукт который купил другой пользователь, но при этом учитывать схожесть списков
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

Неактивен

 

#2 12.05.2008 12:19:46

LazY
_cмельчак
MySQL Authorized Developer and DBA
Зарегистрирован: 02.04.2007
Сообщений: 845

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

Если я правильно понял задачу, то мне видится в такой задаче два этапа:
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, хотя соотношение совпадения списков обратное?

Неактивен

 

#3 12.05.2008 12:55:01

lokiby
Участник
Зарегистрирован: 12.05.2008
Сообщений: 6

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

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

и mysql у меня 4.1.22

Неактивен

 

#4 12.05.2008 14:47:08

LazY
_cмельчак
MySQL Authorized Developer and DBA
Зарегистрирован: 02.04.2007
Сообщений: 845

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

по большому щету мне нужны только 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 - можно было бы написать процедуру...)

Неактивен

 

#5 13.05.2008 12:34:25

lokiby
Участник
Зарегистрирован: 12.05.2008
Сообщений: 6

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

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

Неактивен

 

#6 13.05.2008 17:47:35

EugeneTM
Гуру
Зарегистрирован: 11.04.2008
Сообщений: 89

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

С учетом потенциальной проблемы с подзапросом
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;
 

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

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson