Сравнение полей по условию

[Stalone]

Сравнение полей по условию

Всем здрасьте. очень туплю над запросом( может кто подскажет

визуально:

$myuserid=1;

user_id,  category_id,  subcat_id
1	  7		5
1	  1		8
1	  1		3

5	  7		5
3	  4		8
3	  1		3

как мне выбрать всех пользователей, у которых category_id и/или subcat_id,
максимально совпадают с $myuserid (user_id=1),
- и отсортировать их в порядке максимально совпавших с user_id=1?

т.е. в данном случае, если $myuserid=1, то на выходе должно получиться:

1. user_id = 3
2. user_id = 5

 

[dr-sm]

ну по идее inner join + union distinct из 3х запросов

 

[Gas]

вариант 1:

сначала вытянуть список категорий и подкатегорий

SELECT GROUP_CONCAT(DISTINCT category_id) AS cat, GROUP_CONCAT(DISTINCT subcat_id) 
FROM test WHERE user_id=1;

потом подставить их в запрос

SELECT user_id, SUM(IF(FIND_IN_SET(category_id, '7,1')>0,1,0)+IF(FIND_IN_SET(subcat_id, '5,8,3')>0,1,0)) AS cnt
FROM test WHERE user_id != 1 AND (category_id IN (7,1) OR subcat_id IN (5,8,3))
GROUP BY user_id
ORDER BY cnt DESC

вариант 2:
хоть и один запрос, но за счёт subquery в in () скорее всего будет тормознее первого. Но если стоят индексы на category_id/subcat_id, в IN подставлять значения полученные первым запросом в топике и количество записей подпадающих под условие в несколько раз меньше чем в таблице - может быть быстрее )

SELECT user_id, COUNT(*) AS cnt 
FROM (
  SELECT DISTINCT user_id, category_id AS cnt 
  FROM test WHERE category_id IN (SELECT category_id FROM test WHERE user_id=1)
  UNION ALL
  SELECT DISTINCT user_id, subcat_id AS cnt 
  FROM test WHERE subcat_id IN (SELECT subcat_id FROM test WHERE user_id=1)
) AS t 
GROUP BY user_id
ORDER BY cnt DESC

-~{}~ 26.03.08 20:54:

вариант 3:

доработаный вариант 1, но без необходимости вытягивания данных 1-го запроса на application или SP уровень и формирования строк для второго запроса.
Просто подряд выполнить 2 запроса

SELECT @cat:=GROUP_CONCAT(DISTINCT category_id), @subcat:=GROUP_CONCAT(DISTINCT subcat_id) 
FROM test WHERE user_id=1;

SELECT user_id, SUM(IF(FIND_IN_SET(category_id, @cat)>0,1,0)+IF(FIND_IN_SET(subcat_id, @subcat)>0,1,0)) AS cnt
FROM test WHERE user_id != 1 AND (FIND_IN_SET(category_id, @cat) OR FIND_IN_SET(subcat_id, @subcat))
GROUP BY user_id
ORDER BY cnt DESC

 

[berkut]

Gas а как отсюда-же вытащить наиболее похожий ряд по параметрам cat, subcat?

SELECT MIN(ABS(t1.cat_id - t2.cat_id) + ABS(t1.subcat_id - t2.subcat_id))
    , t2.*
FROM test AS t1
    INNER JOIN test AS t2 ON t1.user_id != t2.user_id
WHERE t1.user_id = 1
GROUP BY t2.user_id

т.е. по ряду на каждлгл юзера у которых максимально похожи cat, sub_cat на cat & subcat первого юзера - т.е. типо друзья по интересам.
в груп бай нужно выбираемое поле указывать, но нужно выбрать, упростим, уникальный id ряда, а групировать по user_id

 

[Gas]

а как отсюда-же вытащить наиболее похожий ряд

дык вроде такого и не просили

 

[berkut]

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

 

[Stalone]

Gas
это невероятно круто!!! спасибо огромное!
я вот разбираю, но пока не доходит, буду втыкать и читать мануал.

 

[Gas]

berkut
если допустим что в таблице появилось уникальное поле id и нужно вытянуть для каждого пользователя id строки, максимально близкой к данным заданного пользователя, то у меня получился такой запрос (покурив вот это)

SELECT user_id, SUBSTRING(MIN(delta),12) AS id FROM
(SELECT t1.user_id, 
   (SELECT concat(LPAD(MIN(ABS(t1.category_id - t2.category_id) + ABS(t1.subcat_id - t2.subcat_id)),10,'0'), '_', t1.id) 
    FROM test AS t2 WHERE t2.user_id=1
    ) AS delta
FROM test AS t1
WHERE t1.user_id != 1) as t
GROUP BY user_id

производительности тут нет, но похоже что работает

 

[Stalone]

Gas,
не могу понять, как в представленном тобой третьем варианте,
может тут?:

... SUM(IF(FIND_IN_SET(category_id, @cat)>0,1,0) ...

считать (группировать?) одинаковые категории за одну?

т.е. в одной категории может быть хоть 100 других, не тех подкатегорий, которые у USERID=1, но в итоге такой пользователь будет выше, т.к. любая категория при подсчёте подкатегории, тоже считается +

например:

user_id,        cat_id,     subcat_id
1                   2             88
1                   1             55

5                   2             144
5                   2             11
5                   2             100

3                   2             88
3                   1             55

тут user_id-5 будет выше, хотя должен быть выше user_id-3
т.к. у user_id-3 максимально совпадают с user_id-1

 

[Gas]

для приведённых данных user_id=3 будет на 1-м месте, а если добавить ещё пару строк user_id=5, cat_id=2 - то уже 5-ы станет первым.
В общем мой 3-ий вариант не подходит, бери 2-ой. Можно его ещё потюнить, если на полях category_id и subcat_id нет индексов, то in (subquery) можно заменить на find_in_set как в 3-м. Если индексы есть и хочется большей скорости, сначала вытягиваем список category_id, subcat_id для нужно юзера в php и во второй запрос в IN подставляем конкретные значения - 'cat1,cat2,...catN'

 

[Stalone]

спасибо! всё понял