none
CTE - Relacionamentos Diretos e Indiretos de população RRS feed

  • 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_list

    Este é o resultado.

    Resultado1

    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:

    Resultado Esperado (FINAL)


    Juliano Segoa Projetos

    quarta-feira, 26 de junho de 2013 12:36

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]

    sexta-feira, 28 de junho de 2013 16:48
  • 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

    sexta-feira, 28 de junho de 2013 18:08
  • 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

    segunda-feira, 1 de julho de 2013 13:52
  • 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

    segunda-feira, 1 de julho de 2013 14:35
  • 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

    segunda-feira, 1 de julho de 2013 17:16
  • 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

    segunda-feira, 1 de julho de 2013 18:01