Inquiridor
CTE - Relacionamentos Diretos e Indiretos de população

Pergunta
-
Olá pessoal, estou a algum tempo investindo tempo em uma solução de agrupar pessoas que se conhecem diretamente ou indiretamente por grandes grupos. existe no meu banco um cadastro de pessoas, e uma tabela com os vinculos entre elas.
Eu cheguei a achar um código utilizando CTE, mas mas pesquisas nao consigo localizar algo proximo com que eu preciso.
Vejam este código e imaginem que os "test" podem ser os codigos das pessoas, e vejam que cada linha mostra uma relacao entre pessoas, ou seja o 1 conhece o 2 e se o 2 conhece o 3 estao no mesmo grupo, desta forma o 3 conhece o 4, entao também estao no mesmo grupo..somente o 20 que faz parte de outro grupo, pois ninguem antes os conhecem (20,11,15)...apos o codigo continuo
declare @tbl as table (col1 varchar(20), col2 varchar(20))
insert into @tbl values
('test1', 'test2')
,('test2', 'test3')
,('test3', 'test4')
,('test3', 'test9')
,('test4', 'test5')
,('test5', 'test3')
,('test9', 'test8')
,('test20', 'test11')
,('test20', 'test15')
;with sample_data (Base, Parent) as (
select col1, col2 from @tbl
),
nt_list (Base, Ancestor, [level], [path]) as (
select Base,
Parent Ancestor,
1 [level],
'/' + convert(varchar(max), rtrim(Base)) + '/' [path]
from sample_data where Base = 'test1'
union all
select ntl.Base,
nt.Parent,
ntl.[level] + 1 [level],
ntl.[path] + rtrim(nt.Base) + '/'
from nt_list ntl
join sample_data nt on ntl.Ancestor = nt.Base
where ntl.path not like '%/' + rtrim(nt.Parent) + '/%'
)
select distinct Base, Ancestor from nt_listEste é o resultado.
mas veja que eu tive que incluir um Where ( where Base = 'test1' ) que me removeu o grupo do test20, que deveria ficar deste formato. (isso eu já nao consegui fazer)
Base Ancestor
Test1 Test2
Test1 Test3
Test1 Test4
Test1 Test5
Test1 Test8
Test1 Test9
Test20 Test11
Test20 Test15
A Coluna Base serve somente para eu agrupar os conhecidos por alguma coisa, poderia ser um sequencia. a outra informacao importante, veja que existem 2 grupos, se o 15 (por exemplo) se relacionar com o 3, isso aglutina tudo em um unico grupo. o resultado final que eu preciso é algo assim:
Juliano Segoa Projetos
Todas as Respostas
-
Juliano,
Acredito que para entendermos melhor ou tentar ajudar, seria o caso de analisar a Modelagem de Dados que você esta utilizando, como também o relacionamento entre suas tabelas.
A mesma Seq Grupo pode ter mais de uma base?
O Ancestor pertence somente a um grupo?
Existe algum tipo de relacionamento:
Base X SEQ Grupo
SEQ Grupo X Ancestor
Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]
-
Olá pedro, na tabela so existem 2 campos, conforme abaixo, esta tela é carregada, quando se entra no cadastro do contato e informa quais o mesmo "conhece", veja que o 33 conhece o 34, e o 7220 conhece o 34, desta forma eu preciso agrupar os que se conhecem de alguma forma. que pode ser um sequencial ou qualquer coisa, mas eu preciso "por exemplo" selecionar o 34 e saber todos que ele conhece mesmo que indiretamente por meio de outros. imagine que alguma posição desta tabela ainda o 720 conhece outros que conhece outros, todos estes precisar estar em um mesmo grupo.
Estou ainda focado no CTE para resolver mas não estou achando uma forma, a clausula do post acima, eu achei algo muito próximo, mas não me ajudou.
Obrigado Pedro.
Juliano Segoa Projetos
-
declare @tbl as table (col1 varchar(20), col2 varchar(20)) insert into @tbl values ('test1', 'test2') ,('test2', 'test3') ,('test3', 'test4') ,('test3', 'test9') ,('test4', 'test5') ,('test5', 'test3') ,('test9', 'test8') ,('test20', 'test11') ,('test20', 'test15') ;WITH Rede AS ( SELECT --> Aqui nós obtemos quem nao é conhecido por ninguém, ou seja, aquele que "inicia" um grupo. col1 as Membro ,col2 as Conhece ,CONVERT(VARCHAR(MAX),QUOTENAME(CONVERT(varchar(100),col1),'"')) as Anteriores --> Aqui vamos ir salvando quem a gente já listou. o QUOTENAME vai colocar o codigo entre "(aspas) para nao dar problema na pesquisa. ,CONVERT(bigint,1) as Nivel ,CONVERT(varchar(30),'Amigos do: '+col1) as Grupo FROM @tbl T WHERE NOT EXISTS (SELECT * FrOM @tbl t1 where t1.col2 = t.col1) UNION ALL SELECT T.col1 as Membro ,T.col2 as Conhece ,CONVERT(varchar(max),QUOTENAME(T.col1,'"')) +','+ Anteriores ,R.Nivel + 1 ,R.Grupo FROM @tbl T inner join Rede R ON R.Conhece = T.col1 --> A nossa condicao de match é encontrar aqueles que conhecidos por quem foi listado na iteracao anterior e que ja nao tenha sido listado. AND Anteriores not like '%'+QUOTENAME(T.col1,'"')+'%' escape '\' --> De novo, as " (aspas) impedem que a gente encontre parte de alguem. Ex.: 50 encontraria em "10,501". As aspas evitam isso. ) SELECT Membro ,Conhece ,Grupo FROM Rede ORDER BY Grupo OPTION(MAXRECURSION 0)
Seria isto ?[]'s | Rodrigo Ribeiro Gomes | MCTS/MCITP Dev/DBA
- Sugerido como Resposta Junior Galvão - MVPMVP quarta-feira, 3 de julho de 2013 19:11
-
Olá Rodrigo, primeiramente agradeço,
É exatamente isso, só que ele precisa fazer a volta, por exemplo, inclua mais uma condição no final do insert
,('test11', 'test3')
Esta condição une todos em um unico grupo (neste caso um membro de um grupo conhece um membro de outro grupo o que faz com que todos sejam do mesmo grupo.
abraço.
Juliano Segoa Projetos
-
-
Rodrigo,
Compartilhei um arquivo chamado CTE.sql, com um insert da base real, veja que nesta base não existe um ponto de partida definido, lá no final eu inclui o resultado em uma temporaria, apos eu seleciono todos os grupo que ele faz parte e ai sim pego todos os distintos. (teste com o 778)
https://docs.google.com/file/d/0B0UL0-6YseY8X3RzZ0RkSC13a0k/edit?usp=sharing
Juliano Segoa Projetos