Relacje międzyludzkie

0

Chcę znaleźć sposób na wykrywanie bliższych relacji pomiędzy ludźmi z różnych środowisk opierając się na tabeli: "A zna B" etc. Zaczynam od wyszukania osób skupiających największą liczbę innych, na podstawie powiązań. Dalej przyporządkowuję im tych, którzy mają z nimi jakiś związek tworząc grupę o nazwie tego, kto jest jej centrum. Z tych danych chcę wyciągnąć tylko tych powiązanych z centrum, którzy mają również powiązanie z innymi w swojej grupie. Kolejnym krokiem będzie rozszerzenie grupy na znajomych znajomych, a raczej na grupy, do których należą znajomi z aktualnie przeglądanej. Napisałem funkcję, ale spotkałem się z błędem w warunku if'a trzeciego, najbardziej zagnieżdżonego kursora cr_pairs.

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Nie ma problemów z czytaniem, więcej z doświadczeniem. Oprócz pytania o to, jak najlepiej poprawić ten warunek będę wdzięczny za ogólną opinię nt. podejścia do sprawy. Poniżej przykładowe dane testowe w skróconej objętościowo wersji:

drop table irelations
GO
create table irelations
(
	relID int identity(1, 1) primary key,
	p1 varchar(1) not null,
	p2 varchar(1) not null
);
GO
insert into irelations values ('A', 'B');
insert into irelations values ('B', 'C');
insert into irelations values ('C', 'D');
insert into irelations values ('C', 'M');
insert into irelations values ('D', 'E');
insert into irelations values ('D', 'M');
insert into irelations values ('E', 'G');
insert into irelations values ('E', 'F');
insert into irelations values ('F', 'G');
insert into irelations values ('F', 'Z');
insert into irelations values ('G', 'L');
insert into irelations values ('G', 'H');
insert into irelations values ('H', 'L');
insert into irelations values ('H', 'I');
insert into irelations values ('I', 'L');
insert into irelations values ('I', 'K');
insert into irelations values ('I', 'J');
insert into irelations values ('J', 'K');

i oczywiście sama funkcja:

create function sn (@tableName varchar)
returns @resultTable table (groupName varchar(1), groupMember varchar(1))
as
begin
	declare @t1Table table (groupCenter varchar(1), groupMembers varchar(1))
	insert into @t1Table
		select group_centers.Os as Grupa, friends.p1 as Znajomi
			from
			(
					select connections.Osoba as "Os", connections.CN as PDegree
					from
					(
						select p1 as Osoba, SUM(ile) as CN
						from
						(
						  select p1, COUNT(*) as ile from irelations group by p1
						  union all
						  select p2, COUNT(*) as ile from irelations group by p2
						) as ilosc_relacji
						group by ilosc_relacji.p1
					) as connections
					where connections.CN >= 4
			) as group_centers
			join
			(
				select tt.p1 from
				(
					select p1 from irelations
					union
					select p2 from irelations
				) as tt
			) as friends
			on ((((cast(group_centers.Os as varchar)+cast(friends.p1 as varchar)) in (select cast(p1 as varchar)+cast(p2 as varchar) from irelations))))
			or (((cast(friends.p1 as varchar))+(cast(group_centers.Os as varchar)) in (select cast(p1 as varchar)+cast(p2 as varchar) from irelations)))
	declare cr_sieve1 cursor for
		select groupCenter, groupMembers from @t1Table
			declare @gcenter varchar(1) -- group center: PreviousGroupName
			declare @gmember varchar(1) -- group member inside cursor
			declare @pgn varchar(1) = '#' -- PreviousGroupName
			declare @tt table (m varchar(1)) -- TempTable of m-embers
	open cr_sieve1
			fetch next from cr_sieve1 into @gcenter, @gmember -- pobierz nazwe potencjalnej grupy
			set @pgn = @gcenter -- NazwaPoprzedniejGrupy
			insert into @tt select @gmember -- add group member to the table
			while @@FETCH_STATUS = 0 -- do konca tabeli
			begin
				-- set @ttn = @gcenter -- group center is a name of temp table
				fetch next from cr_sieve1 into @gcenter, @gmember
				if @gcenter <> @pgn
				begin
					declare cr_mi cursor for select m from @tt
					declare @member varchar(1)
					declare @tempPairs table (tp1 varchar(1), tp2 varchar(1)) -- tabela par punktow grupy
					insert into @tempPairs	-- wpisanie unikalnych par punktow danej grupy
						select t1.m as m1, t2.m as m2 from @tt as t1, @tt as t2
						where t1.m < t2.m
						order by t1.m
					open cr_mi
						fetch next from cr_mi into @member
						while @@FETCH_STATUS = 0
						begin -- dla kazdego punktu z tabeli znajomych grupy wchodzimy do tabeli unikalnych par znajomych tej grupy
						declare cr_pairs cursor for select tp1, tp2 from @tempPairs
						declare @p1 varchar(1), @p2 varchar(1)
						open cr_pairs
							fetch next from cr_pairs into @p1, @p2
							if
							( (@member = @p1) or (@member = @p2)  -- jezeli aktualny znajomy jest w parze
							and
							  ( -- ktora zawarla ze soba znajomosc
								( CAST(@p1 as varchar)+CAST(@p2 as varchar) in (select * from irelations))
								or ( CAST(@p2 as varchar)+CAST(@p1 as varchar) in (select * from irelations))
							   ) )
							begin
								insert into @resultTable select @gcenter, @p1
								insert into @resultTable select @gcenter, @p2
							end
						close cr_pairs
						deallocate cr_pairs
						end
					close cr_mi
					deallocate cr_mi
					set @pgn = @gcenter
				end
				else
					insert into @tt select @gmember
			end
	close cr_sieve1
	deallocate cr_sieve1
	return
end;
1
IN (SELECT * FROM irelations)

jest źle.
Powinno być

IN (SELECT p1 FROM irelations)

lub oczywiście p2 (albo coś innego). Nie możesz przy IN podawać *.

0

Zgadza się, ale nie mogę też podać dwóch pól, a tego właśnie potrzebuję do porównania par z tabeli wszystkich z lokalną tabelą par w wyodrębnionych grupach. Przy okazji: czy zmienna tabelaryczna @TT będzie niszczona za każdym razem po znalezieniu innej od zapisanej w poprzedniej grupie (@pgn) nazwy czy zadeklarowałem ją w złym miejscu pod względem zasięgu?

1
( CAST(@p1 AS VARCHAR)+CAST(@p2 AS VARCHAR) IN (SELECT CAST(p1 AS VARCHAR)+CAST(p2 AS VARCHAR) FROM irelations))
0

Dzięki, tego właśnie brakowało mi w warunku. Niestety, teraz pojawił się problem innej natury, mianowicie zapytanie wykonuje się w minutach... Wstępna selekcja grup działa poprawnie, zwraca tabelę wynikową bez problemów, ale po wejściu do kursora sieve1 i zagnieżdzonych w nim dwu innych ma za wiele do zrobienia i po select * from dbo.sn() executuje się 'długo'. Proszę o spojrzenie z zewnątrz na te kursory, może ma to związek ze zmienną tabelaryczną @TT, do której chcę ładować członków pojedynczej podgrupy a potem członków innej nie jest 'opróżniana' i wciąż się powiększa? Muszę przyznać, że nie potrafię znaleźć przyczyny.

0

Spróbowałem to przepisać od początku kombinacji z kursorami, kontrolując wyniki i mam pytanie co do zmiennej @tmtable.

	declare cr_sieve1 cursor for select groupCenter, groupMembers from @t1Table
		declare @gcenter varchar(1)
		declare @gmember varchar(1)
		declare @pgn varchar(1) = '#'
		declare @tmtable table(member varchar(1))
	open cr_sieve1
		fetch next from cr_sieve1 into @gcenter, @gmember -- pobierz centrumGrupy i czlonka
		set @pgn = @gcenter -- ustaw jako aktualne centrum pobrane centrumGrupy
		insert into @tmtable select @gmember
		while @@FETCH_STATUS = 0
		begin
			if @pgn <> @gcenter
			begin
			
					insert into @resultTable	-- wpisanie unikalnych par punktow danej grupy
						select distinct t1.member as m1, t2.member as m2 from @tmtable as t1, @tmtable as t2
						where t1.member < t2.member
						order by t1.member

				delete @tmtable
			end
			else
			begin
				insert into @tmtable select @gmember
			end
			fetch next from cr_sieve1 into @gcenter, @gmember 
		end
	close cr_sieve1
	deallocate cr_sieve1

Chciałem usuwać tę tymczasową tabelę z parami membersów za każdym razem, gdy napotkam nowego (innego od poprzedniego - przechowywanego w zmiennej @pgn) wodza grupy. Wpisałem

delete @tmtable
gdyż nie mogę użyć drop. Nie stosując tej linijki (z delete) otrzymuję 102 rekordy, które są 16krotnie powtórzonym zestawem prawidłowym dla pierwszego centrum/wodza grup, a więc bez delete dostaję 17 razy wynik dla tylko pierwszego centrum grupy. Moim celem było uzyskanie zestawu par dla każdego z centrów grup, po to wymyśliłem delete, ale najwidoczniej nie zadziała to tak jak sobie wyobrażałem - chciałbym usunąć poprzedni zestaw par i rozpocząć zbieranie nowego gdy @pgn <> @gcenter. Jak mogę to zrealizować?

0

Czy jest możliwość wykonania operacji równoważnej do truncate table na zmiennej tabelarycznej wewnątrz funkcji? Nie mogę skorzystać z zewnętrznej tabeli, bo SQL nie pozwala mi używać insert do niej.

1
marek_w napisał(a)

Czy jest możliwość wykonania operacji równoważnej do truncate table na zmiennej tabelarycznej wewnątrz funkcji? Nie mogę skorzystać z zewnętrznej tabeli, bo SQL nie pozwala mi używać insert do niej.

Wystarczy

DELETE @tabvar;

//poprawka w funkcji nie można wykonywać operacji insert, delete, update

0

Tworzenie tylu tymczasowych tabel/zmiennych, ile będzie centrów grup nie ma pewnie większego sensu, ale czy istnieje inna możliwość kiedy nie mogę 'wyczyścić' tej jednej?
Edit: mimo wszystko po delete @tmtable nie dostaje 102, a 6 prawidłowych dla pierwszego centrum rekordów.

0

A jednak delete @tab działało prawidłowo, zabrakło m.in. aktualizacji zmiennej @pgn, problem rozwiązany.

Dziękuję za pomoc.

1 użytkowników online, w tym zalogowanych: 0, gości: 1