CTE - Relacionamentos Diretos e Indiretos de população

Question

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_list

Este é 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

Answer 1

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]

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

Qual é o ponto de start ?

Perceba que nesta resposta eu considerei como start aqueles membros cujo ninguem os conhece.

---

[]'s | Rodrigo Ribeiro Gomes | MCTS/MCITP Dev/DBA

Answer 6

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