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

Откуда: канализация
Сообщений: 6615
Здравствуйте,
большая просьба помочь хотя бы советом
есть задача по слиянию семей из множества CONV_PC_ID . A_PC - ссылка на человека. A_ID просто какой то идентификатор(у человека их может быть несколько), A_CONV_NAME - конвертация, A_OUID идентификатор записи в CONV_PC_ID.
Задача из двух этапов:
1) Связать в семьи людей в разрезе конвертации
2) Связать в семьи людей без учета конвертации

По первому этапу: в семьи люди попадают если у них совпадают A_ID. Если у человека несколько A_ID, то в семью попадут все люди с такими же A_ID и так далее рекурсивно. Ну и все это происходит в разрезе конвертации.

По второму этапу: получив семьи на первом этапе, надо слить их через конвертации. Сливать по такому правилу: если в двух семьях есть хоть один одинаковый человек то семьи сливаются. Вот тестовые данные

declare @WM_PERSONAL_CARD table (OUID int , FIO varchar(255))
insert into @WM_PERSONAL_CARD(OUID,FIO)
values (345,  'Иванов Иван Иванович')
  ,(346,  'Петров Петр Петрович')
  ,(347,  'Сергеев Сергей Сергеевич')
  ,(348,  'Васильев Василий Васильевич')
  ,(450, 'Майя Исааковна Пельман')
  ,(349, 'Абдулла Ибн Махмуд')
  ,(500, 'Тульский пряник')
  ,(501, 'Тульская баба')
  
declare @CONV_PC_ID table (A_OUID int ,  A_CONV_NAME varchar(50), A_PC int, A_ID int)
insert into @CONV_PC_ID(A_OUID,A_CONV_NAME,A_PC,A_ID)
values (10, 'ufa_child', 345, 555)
  ,(11, 'tula', 345, 5555)
  ,(12, 'ufa_child', 346, 555)
  ,(13, 'ufa_child', 346, 999)
  ,(17,'ufa_child', 346, 1000)
  ,(14, 'ufa_child', 347, 777)
  ,(15, 'ufa_child', 348, 998)
  ,(19,'ufa_child', 348, 1001)
  ,(18,'ufa_child', 450, 1001)
  ,(16,'ufa_child',349,555)
  ,(20,'tula', 500, 5555)
  ,(21,'tula', 501, 5555)


вот результаты по первому этапу:
convName                                           family      A_PC
-------------------------------------------------- ----------- -----------
ufa_child                                          10          345
ufa_child                                          10          346
ufa_child                                          10          349
tula                                               11          345
tula                                               11          500
tula                                               11          501
ufa_child                                          14          347
ufa_child                                          15          348
ufa_child                                          15          450


По второму:
family      OUID        FIO
----------- ----------- 
10          345         Иванов Иван Иванович
10          346         Петров Петр Петрович
10          349         Абдулла Ибн Махмуд
10          500         Тульский пряник
10          501         Тульская баба
14          347         Сергеев Сергей Сергеевич
15          348         Васильев Василий Васильевич
15          450         Майя Исааковна Пельман


Вообще запрос то я написал, но к сожалению на требуемых обьемах он не работает. Планируется таблица CONV_PC_ID на миллион записей.
Заранее всем большое спасибо.
20 апр 12, 12:46    [12444978]     Ответить | Цитировать Сообщить модератору
 Re: зарпрос на выделение подмножеств из множества  [new]
aleks2
Guest
Треба розжувати
>>Если у человека несколько A_ID
Что идентифицирует "человека"?
20 апр 12, 13:04    [12445114]     Ответить | Цитировать Сообщить модератору
 Re: зарпрос на выделение подмножеств из множества  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
aleks2
Треба розжувати
>>Если у человека несколько A_ID
Что идентифицирует "человека"?

A_PC . A_ID никакого значения к идентификатору не имеет. В рамках данной задачи целое положительное число. В CONV_PC_ID может быть несколько записей с одинаковым A_PC(и это ссылка на @WM_PERSONAL_CARD), но разными A_ID
20 апр 12, 13:14    [12445186]     Ответить | Цитировать Сообщить модератору
 Re: зарпрос на выделение подмножеств из множества  [new]
aleks2
Guest
declare @WM_PERSONAL_CARD table (OUID int , FIO varchar(255))
insert into @WM_PERSONAL_CARD(OUID,FIO)
values (345,  'Иванов Иван Иванович')
  ,(346,  'Петров Петр Петрович')
  ,(347,  'Сергеев Сергей Сергеевич')
  ,(348,  'Васильев Василий Васильевич')
  ,(450, 'Майя Исааковна Пельман')
  ,(349, 'Абдулла Ибн Махмуд')
  ,(500, 'Тульский пряник')
  ,(501, 'Тульская баба')
  
declare @CONV_PC_ID table (A_OUID int ,  A_CONV_NAME varchar(50), A_PC int, A_ID int)
insert into @CONV_PC_ID(A_OUID,A_CONV_NAME,A_PC,A_ID)
values (10, 'ufa_child', 345, 555)
  ,(11, 'tula', 345, 5555)
  ,(12, 'ufa_child', 346, 555)
  ,(13, 'ufa_child', 346, 999)
  ,(17,'ufa_child', 346, 1000)
  ,(14, 'ufa_child', 347, 777)
  ,(15, 'ufa_child', 348, 998)
  ,(19,'ufa_child', 348, 1001)
  ,(18,'ufa_child', 450, 1001)
  ,(16,'ufa_child',349,555)
  ,(20,'tula', 500, 5555)
  ,(21,'tula', 501, 5555)
  
  declare @t table (A_PC int, A_ID int
  , family int -- признак семейства
  )

  insert @t( A_PC, A_ID, family )
  select A_PC, A_ID,  A_PC 
  from @CONV_PC_ID

  declare @rc int, @l int
  
  set @rc=1

while @rc>0 begin

  update T SET family=T1.family
  FROM @t T inner join @t T1 on T1.family<T.family AND T.A_ID=T1.A_ID

  set @rc=@@ROWCOUNT 

end
  
select * from  @t
20 апр 12, 14:38    [12445973]     Ответить | Цитировать Сообщить модератору
 Re: зарпрос на выделение подмножеств из множества  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
aleks2,
спасибо неплохо, но не то.
Если малость поменять исходные данные (каюсь, упрощенный вариант дал),

declare @WM_PERSONAL_CARD table (OUID int , FIO varchar(255))
insert into @WM_PERSONAL_CARD(OUID,FIO)
values (345,  'Иванов Иван Иванович')
  ,(346,  'Петров Петр Петрович')
  ,(347,  'Сергеев Сергей Сергеевич')
  ,(348,  'Васильев Василий Васильевич')
  ,(450, 'Майя Исааковна Пельман')
  ,(349, 'Абдулла Ибн Махмуд')
  ,(500, 'Тульский пряник')
  ,(501, 'Тульская баба')
  
declare @CONV_PC_ID table (A_OUID int ,  A_CONV_NAME varchar(50), A_PC int, A_ID int)
insert into @CONV_PC_ID(A_OUID,A_CONV_NAME,A_PC,A_ID)
values (10, 'ufa_child', 345, 555)
  ,(11, 'tula', 345, 5555)
  ,(12, 'ufa_child', 346, 555)
  ,(13, 'ufa_child', 346, 999)
  ,(17,'ufa_child', 346, 1000)
  ,(14, 'ufa_child', 347, 777)
  ,(15, 'ufa_child', 348, 999)
  ,(19,'ufa_child', 348, 1001)
  ,(18,'ufa_child', 450, 1001)
  ,(16,'ufa_child',349,555)
  ,(20,'tula', 500, 5555)
  ,(21,'tula', 501, 5555)



то хотелось бы увидеть по первому этапу(слияние семей в разрезе A_CONV_NAME)
convName                                           family      A_PC
-------------------------------------------------- ----------- -----------
ufa_child                                          10          345
ufa_child                                          10          346
ufa_child                                          10          348
ufa_child                                          10          349
ufa_child                                          10          450
tula                                               11          345
tula                                               11          500
tula                                               11          501
ufa_child                                          14          347


по второму этапу
family      OUID        FIO
----------- ----------- 
10          345         Иванов Иван Иванович
10          346         Петров Петр Петрович
10          348         Васильев Василий Васильевич
10          349         Абдулла Ибн Махмуд
10          450         Майя Исааковна Пельман
10          500         Тульский пряник
10          501         Тульская баба
14          347         Сергеев Сергей Сергеевич


то есть 3 семьи и 2 семьи соответственно . В предложенном вами варианте - 4 .
Но все равно большое спасибо, попробую написать с помощью цикла, а не с помощью рекурсивного CTE - может быть будет быстрее.
20 апр 12, 14:58    [12446195]     Ответить | Цитировать Сообщить модератору
 Re: зарпрос на выделение подмножеств из множества  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
aleks2,
еще раз огромное спасибо. Развил вашу идею до нужного мне результата - все работает правильно и достаточно быстро

+

declare @WM_PERSONAL_CARD table (OUID int , FIO varchar(255))
insert into @WM_PERSONAL_CARD(OUID,FIO)
values (345,  'Иванов Иван Иванович')
  ,(346,  'Петров Петр Петрович')
  ,(347,  'Сергеев Сергей Сергеевич')
  ,(348,  'Васильев Василий Васильевич')
  ,(450, 'Майя Исааковна Пельман')
  ,(349, 'Абдулла Ибн Махмуд')
  ,(500, 'Тульский пряник')
  ,(501, 'Тульская баба')
  
declare @CONV_PC_ID table (A_OUID int ,  A_CONV_NAME varchar(50), A_PC int, A_ID int)
insert into @CONV_PC_ID(A_OUID,A_CONV_NAME,A_PC,A_ID)
values (10, 'ufa_child', 345, 555)
  ,(11, 'tula', 345, 5555)
  ,(12, 'ufa_child', 346, 555)
  ,(13, 'ufa_child', 346, 999)
  ,(17,'ufa_child', 346, 1000)
  ,(14, 'ufa_child', 347, 777)
  ,(15, 'ufa_child', 348, 999)
  ,(19,'ufa_child', 348, 1001)
  ,(18,'ufa_child', 450, 1002)
  ,(16,'ufa_child',349,1001)
  ,(22,'ufa_child',349,1002)
  ,(20,'tula', 500, 5555)
  ,(21,'tula', 501, 5555)
  

if OBJECT_ID('tempdb..#t') is not null
begin
	DROP TABLE #t
end	  
  
  create table #t  (
	A_PC int
	,A_ID varchar(500)
	
	,family int -- признак семейства
	,A_CONV_NAME varchar(50)
	primary key clustered(A_CONV_NAME,A_PC,A_ID)
  )
  

  insert #t( A_PC, A_ID, family,A_CONV_NAME )
  select distinct 
		A_PC
		,isNull(cast(A_ID as varchar(32)),'')--+isNull(A_ID2,'')+ISNULL(A_ID3,'')
		,A_PC
		,A_CONV_NAME
  from @CONV_PC_ID
 --where A_CONV_NAME = 'tula'

declare @rc int, @l int
set @rc=1
	

while @rc>0 begin

  update T SET family=T1.family
  FROM #t T inner join #t T1 
			on	T1.family>T.family AND T.A_ID=T1.A_ID
				and T1.A_CONV_NAME = T.A_CONV_NAME

  set @rc=@@ROWCOUNT 

end

set @l=1
while @l>0 begin		
	;with uf(family1,family2) as (
	select  t.family,  max(t1.family)
	from	#t t
			inner join #t t1
			on	
				
				 t1.A_PC = t.A_PC
				and t1.family<t.family
	group by t.family			
	)
	update T SET family=uf.family2
	FROM #t T inner join uf  
			on	uf.family1 = T.family
	set @l=@@ROWCOUNT
end



 
select distinct 
 t.family
 ,t.A_PC
from  #t t

20 апр 12, 17:10    [12447391]     Ответить | Цитировать Сообщить модератору
 Re: зарпрос на выделение подмножеств из множества  [new]
aleks2
Guest
Ну... работать надо было - я отвлекся. Делофф то

  
declare @CONV_PC_ID table (A_OUID int ,  A_CONV_NAME varchar(50), A_PC int, A_ID int)
insert into @CONV_PC_ID(A_OUID,A_CONV_NAME,A_PC,A_ID)
values (10, 'ufa_child', 345, 555)
  ,(11, 'tula', 345, 5555)
  ,(12, 'ufa_child', 346, 555)
  ,(13, 'ufa_child', 346, 999)
  ,(17,'ufa_child', 346, 1000)
  ,(14, 'ufa_child', 347, 777)
  ,(15, 'ufa_child', 348, 999)
  ,(19,'ufa_child', 348, 1001)
  ,(18,'ufa_child', 450, 1001)
  ,(16,'ufa_child',349,555)
  ,(20,'tula', 500, 5555)
  ,(21,'tula', 501, 5555)



  declare @t table (A_PC int, A_ID int
  , family int -- признак семейства
  )

  insert @t( A_PC, A_ID, family )
  select A_PC, A_ID,  A_PC 
  from @CONV_PC_ID

  declare @rc int, @l int
  
  set @rc=1

while @rc>0 begin

  update T SET family=T1.family
  FROM @t T inner join @t T1 on T1.family<T.family AND T.A_ID=T1.A_ID

  set @rc=@@ROWCOUNT 
  
  update T SET family=T1.family
  FROM @t T inner join @t T1 on T1.family<T.family AND T.A_PC=T1.A_PC
  
  set @rc=@rc+@@ROWCOUNT 

end
  
select * from  @t

select DISTINCT family from  @t


Тока хде ты там три клана углядел?
20 апр 12, 17:12    [12447406]     Ответить | Цитировать Сообщить модератору
 Re: зарпрос на выделение подмножеств из множества  [new]
aleks2
Guest
Мистер Хенки
aleks2,
все работает правильно и достаточно быстро

Хе-хе? Это бред низзя называть "быстрым".
20 апр 12, 17:15    [12447432]     Ответить | Цитировать Сообщить модератору
 Re: зарпрос на выделение подмножеств из множества  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
aleks2
Мистер Хенки
aleks2,
все работает правильно и достаточно быстро

Хе-хе? Это бред низзя называть "быстрым".

ну очень будет здорово если предложите способ побыстрее.
20 апр 12, 17:17    [12447451]     Ответить | Цитировать Сообщить модератору
 Re: зарпрос на выделение подмножеств из множества  [new]
aleks2
Guest
Мистер Хенки
aleks2
пропущено...

Хе-хе? Это бред низзя называть "быстрым".

ну очень будет здорово если предложите способ побыстрее.

Дык, я вроде предложил?

Для вящей скорострельности надо два индекса

  declare @t table (A_PC int, A_ID int
  , family int -- признак семейства
  , unique clustered (A_PC, A_ID)
  , unique ( A_ID, A_PC, family)
  )
20 апр 12, 17:23    [12447503]     Ответить | Цитировать Сообщить модератору
 Re: зарпрос на выделение подмножеств из множества  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
aleks2
Ну... работать надо было - я отвлекся. Делофф то



Тока хде ты там три клана углядел?

да способ неплох тоже, но немного не то. Надо обязательно кланы(семьи) сначала собрать в разрезе A_CONV_NAME, а потом только сливать их по A_PC по всем A_CONV_NAME.


ну в итоге должно получится 2 клана, я немного поменял исходные данные( запись в CONV_PC_ID c A_OUID=15) и соответственно требуемый результат. Выше в постах есть.
20 апр 12, 17:25    [12447519]     Ответить | Цитировать Сообщить модератору
 Re: зарпрос на выделение подмножеств из множества  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
aleks2
Мистер Хенки
aleks2,
все работает правильно и достаточно быстро

Хе-хе? Это бред низзя называть "быстрым".

рекурсия cte можно сказать только на тестовых данных и работала
20 апр 12, 17:27    [12447549]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить