Relacje międzyludzkie

Odpowiedz Nowy wątek
2011-09-04 16:18
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;

Pozostało 580 znaków

2011-09-04 16:36
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ć *.

Pozostało 580 znaków

2011-09-04 16:47
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?

Pozostało 580 znaków

2011-09-04 20:22
1
( CAST(@p1 AS VARCHAR)+CAST(@p2 AS VARCHAR) IN (SELECT CAST(p1 AS VARCHAR)+CAST(p2 AS VARCHAR) FROM irelations))

Pozostało 580 znaków

2011-09-05 11:01
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.

Szczerze, to nie chce mi się analizować tego. Poza tym bez bardzo dobrej znajomości bazy i danych chyba bym nie wysunął trafnych wniosków. Kursory zawsze będą się wykonywać długo - tego nie zmienisz. Spróbuj to zrobić w pure sql. - Marcin.Miga 2011-09-05 11:42

Pozostało 580 znaków

2011-09-05 21:53
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ć?

Pozostało 580 znaków

2011-09-06 10:49
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.

Pozostało 580 znaków

2011-09-06 11:09
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

edytowany 1x, ostatnio: AdamPL, 2011-09-06 11:13

Pozostało 580 znaków

2011-09-06 11:28
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.

edytowany 1x, ostatnio: marek_w, 2011-09-06 11:29

Pozostało 580 znaków

2011-09-08 09:02
0

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

Dziękuję za pomoc.

Pozostało 580 znaków

Odpowiedz
Liczba odpowiedzi na stronę

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