Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Выделить группы в таблице  [new]
puhh
Member

Откуда:
Сообщений: 512
Помогите придумать быстрый запрос.

Есть большая табличка, допустим, 100 млн строк. В ней 2 столбца, text_id и word_id. В среднем, в каждом text_id примерно 3-6 word_id. Пара text_id - word_id уникальна.

Нужно сгруппировать все text_id, которые состоят из одинаковых word_id.
Т.е. нужен примерно такой результат:
text_id --- group_id
1 --- group_A
2 --- group_A
4 --- group_A
5 --- group_B
7 --- group_B
....


Первая идея - для каждого text_id делать строку из слов, типа 13,256,290,1001 и сравнивать строки. Идея не нравится отсутствием масштабируемости (у каждого слова есть вес, который бы хотелось в дальнейшем учитывать).

Вторая идея - обрабатывать text_id курсом попарно. Ну допустим выбирать для каждого текста таблицы с word_id и сравнивать их. Не проверяла, но думаю будет убийственно долго.


ЧТо еще можно придумать? Вопрос производительности критичен.
16 май 11, 09:59    [10656360]     Ответить | Цитировать Сообщить модератору
 Re: Выделить группы в таблице  [new]
iljy
Member

Откуда:
Сообщений: 8711
puhh,

100 млн будет долго в любом случае, потому что вам фактически предстоит проанализировать декартово произведение. А так - была тема Пересечение множеств.
16 май 11, 10:17    [10656472]     Ответить | Цитировать Сообщить модератору
 Re: Выделить группы в таблице  [new]
puhh
Member

Откуда:
Сообщений: 512
Ну "долго" это я плохо выразилась, слишком общее слово.
Несколько часов не устроит, 15 минут, вполне :)

Спасибо за ссылку, похоже на то, что нужно. Искала сама, но видимо не по тем ключевым словам.
16 май 11, 10:29    [10656547]     Ответить | Цитировать Сообщить модератору
 Re: Выделить группы в таблице  [new]
aleks2
Guest
declare @t table(text_id int, word_id int)

insert @t
select 1, 11 union all
select 1, 12 union all
select 1, 13 union all
select 2, 11 union all
select 2, 12 union all
select 2, 13 union all
select 3, 11 union all
select 3, 12 union all
select 3, 13 union all
select 3, 14 union all
select 7, 12 union all
select 7, 14 union all
select 4, 11 union all
select 4, 12 union all
select 4, 13 

select DISTINCT T.text_id
, ISNULL((select min(X.text_id) FROM @t X 
	    WHERE X.text_id<T.text_id 
        	  AND 
		  exists(select * FROM @t Y WHERE Y.text_id=X.text_id AND Y.word_id=X.word_id)
		  AND
		  not exists(select * FROM 
					(select * FROM @t WHERE text_id=X.text_id) Z1 
					FULL OUTER JOIN
					(select * FROM @t WHERE text_id=T.text_id) Z2
					ON Z1.word_id=Z2.word_id
					WHERE Z1.text_id is null or Z2.text_id is null 
        	            )

	  ),
  T.text_id)  as group_id
FROM @t T
16 май 11, 10:39    [10656622]     Ответить | Цитировать Сообщить модератору
 Re: Выделить группы в таблице  [new]
puhh
Member

Откуда:
Сообщений: 512
aleks2 , спасибо, на небольшом куске данных работает верно, но уже на 4 млн - висит уже 10 мин ...

Зато меня удивляет, с какой скоростью работает for xml path('') !
16 май 11, 11:09    [10656840]     Ответить | Цитировать Сообщить модератору
 Re: Выделить группы в таблице  [new]
Владимир СА
Member

Откуда:
Сообщений: 7915
А индексы есть?
16 май 11, 11:12    [10656864]     Ответить | Цитировать Сообщить модератору
 Re: Выделить группы в таблице  [new]
aleks2
Guest
Владимир СА
А индексы есть?

1. Это не будет быстро работать даж с индексами - всеж коррелированные подзапросы. Это классика - она нетленна.
2. for xml path - это идеологически неверный подход. Ибо забивать гвозди можна и компутером, но удобнее молотком.
3. Если нада быстро - городим временные таблицы.
16 май 11, 11:27    [10656996]     Ответить | Цитировать Сообщить модератору
 Re: Выделить группы в таблице  [new]
puhh
Member

Откуда:
Сообщений: 512
Индексы есть, но как я уже писала, несколько часов ожидать нет возможности.


aleks2, расскажите, пожалуйста (или киньте ссылкой), что не так с for xml path. А то мне, честно говоря, очень понравилось.
16 май 11, 11:44    [10657174]     Ответить | Цитировать Сообщить модератору
 Re: Выделить группы в таблице  [new]
Начинающий SQL 2008
Member

Откуда:
Сообщений: 438
puhh,

???

declare @t table(text_id int, word_id int)
insert @t select 1, 11 union all select 4, 120 union all select 2, 13 union all select 1, 12 union all select 1, 13 union all 
select 7, 12 union all select 3, 110 union all select 3, 120 union all select 4, 130 union all select 3, 140 union all 
select 2, 11 union all select 2, 12 union all select 7, 14 union all select 3, 130 union all select 4, 110 union all select 4, 140 

/* Для проверки */
--select * from @t order by 1,2

select text_id, dense_rank() over (order by gr) [group_id]
from (
 select t1.text_id, (select cast(word_id as varchar)+',' from @t t2 where t2.text_id = t1.text_id order by word_id for xml path('')) [gr]
 from @t t1
 group by t1.text_id
) t
16 май 11, 12:59    [10657765]     Ответить | Цитировать Сообщить модератору
 Re: Выделить группы в таблице  [new]
puhh
Member

Откуда:
Сообщений: 512
Угу, такой запрос я и соорудила. Как я писала в первом посте, мне не нравится в этом отсутствие масштабируемости. Хотя сейчас склоняюсь к мысли, что учитывая высокую скорость работы, можно делать несколько "столбцов" - первый - это слова с высоким весом, которые обязательно должны быть в тексте, и несколько стобцов со словами, которые могут быть, и которых может не быть, но тексты все равно будут идентичными с некоторой погрешностью.
17 май 11, 03:31    [10662299]     Ответить | Цитировать Сообщить модератору
 Re: Выделить группы в таблице  [new]
iljy
Member

Откуда:
Сообщений: 8711
puhh,

выложите дамп своих данных, тогда можно будет пооптимизировать.
17 май 11, 08:49    [10662482]     Ответить | Цитировать Сообщить модератору
 Re: Выделить группы в таблице  [new]
puhh
Member

Откуда:
Сообщений: 512
Да в общем и так все отлично, на 25 млн строк скрипт типа приведенного выше отрабатывает секунд за 20.

А идея с весами слов еще до конца не продумана, так что пусть пока будет так, как есть.

Всем спасибо.
17 май 11, 09:15    [10662580]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить