[es] - SQL query za presjek skupova

sovabass
Banja Luka

Član broj: 96338
Poruke: 43
*.stat.customer.blic.net.

Profil

^{17.02.2012. u 15:15 - pre 148 meseci}

Pozdrav svima,

problem podvrste skolski je sledeci:potrebno je napraviti sp koja ce traziti da li postoje korisnici koji su posjecivali iste servere/ipadrese,procedura mora da podrzava unos 8 korisnika.
Ipadrese korisnika i servera su smjestene u tabeli Ipnimber (ipadresa_klijent,ipadresa_adresa). Evo primjera kako sam uradio za dva korisnika. Problem se svodi se na trazenje presjeka skupova
izmedju 2 tabele. Pokusao sam ovom metodom uraditi query za 8 klijenata medjutim, dobija se ogromna i kabasta skripta,pa mi treba elegantnije rjesenja. Ako neko ima kakav prijedlog,neka se javi,zahvaljujem.

Code:
/*tabela sa ipadresama*/

create table ipnumber
(
ipadresa_klijent varchar (32),
ipadresa_adresa varchar (32)
)

INSERT INTO ipnumber values (22222,33333)
INSERT INTO ipnumber values (55555,12345)
INSERT INTO ipnumber values (22222,54321)
INSERT INTO ipnumber values (11111,88888)
INSERT INTO ipnumber values (99999,88888)
INSERT INTO ipnumber values (12345,22222)

/*Primjer za dva korisnika,korisnik1 ima adresu 22222,korisnik2 ima adresu 55555*/

create table test1
(
broj varchar (32)
)

create table test2
(
broj varchar (32)
)

insert into test1
select ipadresa_klijent from ipnumber where ipadresa_adresa like '%55555'
union all
select ipadresa_adresa from ipnumber where ipadresa_klijent like '%55555'

insert into test2
select ipadresa_klijent from ipnumber where ipadresa_adresa like '%22222'
union all
select ipadresa_adresa from ipnumber where ipadresa_klijent like '%22222'

select distinct (broj) from test1 t1
where exists
(select broj from test2 where broj= t1.broj)

/*Kao rezultat dobijemo:
broj
12345

sto je zajednicka ipadresa za 22222 i 55555*/

Odgovor na temu

djoka_l
Beograd

Član broj: 56075
Poruke: 3453

Jabber: djoka_l

+1462 Profil

Re: SQL query za presjek skupova

^{17.02.2012. u 15:48 - pre 148 meseci}

Tvoj upit ne valja, ili ja nisam razumeo šta želiš da dobiješ.
Evo kako bih ja napisao upit:

Code (sql):

SELECT k1.ipadresa_klijent, k2.ipadresa_klijent, k1.ipadresa_adresa

FROM ipnumber k1, ipnumber k2

WHERE k1.ipadresa_klijent < k2.ipadresa_klijent

AND k1.ipadresa_adresa = k2.ipadresa_adresa

Odgovor na temu

sovabass
Banja Luka

Član broj: 96338
Poruke: 43
*.stat.customer.blic.net.

Profil

Re: SQL query za presjek skupova

^{17.02.2012. u 16:11 - pre 148 meseci}

Moja greska,izvinjavam se. Klijent moze biti u obe kolone, greska je sto sam nazvao ipadresa_klijent. Tvoja skripta je ok ako pretopstavimo da su klijenti samo u ipadresa_klijent. Znaci, zelim izaberem bilo koje dve ipadrese provjerim da li su posjecivali iste adrese.

Odgovor na temu

HladankaoLed
Database Consultant
Vienna, Austria

Član broj: 228937
Poruke: 36
*.5.14.univie.teleweb.at.

Sajt: milossql.wordpress.com

+1 Profil

Re: SQL query za presjek skupova

^{17.02.2012. u 16:16 - pre 148 meseci}

Hajde da definisemo zadatak najpre, to je ovde veca muka nego resiti ga. Rekao si da ti treba SP koja treba da ima takve parametere da podrzava unos 8 brojeva tipa varchar(32). Jesam li dobro razumeo?
Definisimo najpre ulazne parametre, pa onda sta treba da se uradi u proceduri, tj. sta ista treba da vrati (koju kolonu, kolone, pod kojim uslovima etc.)

Poz.
M.

Sad šta je tu je. A možda će da dođe i ova tvoja tetka iz Bosanske Krupe.

Odgovor na temu

sovabass
Banja Luka

Član broj: 96338
Poruke: 43
*.stat.customer.blic.net.

Profil

Re: SQL query za presjek skupova

^{17.02.2012. u 16:47 - pre 148 meseci}

Sto se tice ulaznih parametara,dobro si razumeo.Procedura treba da uradi sledece,odaberem 8 poznatih ipadresa iz iplog ( nebitno dali u ipadresa_klijent ili ipadresa_adresa) i vraca mi one ipadrese kojima su pristupali svih 8 korisnika.vracena vrijednost je u formatu varchar (32).

Korisnik je pristupao adresi ako je u istom redu,npr,''INSERT INTO ipnumber values (22222,33333)" znaci da je '22222' pristupio '33333' ili obrnuto.

U svom primjeru sam izabrao dve adrese '55555' i '22222' i iz iplog trazio da li postoji adresa kojoj su oba pristupali (u sp to ce biti @trazenaIPadresa1 i @trazenaIPadresa2)i dobio '12345' kao adresu kojoj su obe zadate ipadrese pristupale.

Odgovor na temu

HladankaoLed
Database Consultant
Vienna, Austria

Član broj: 228937
Poruke: 36
*.5.14.univie.teleweb.at.

Sajt: milossql.wordpress.com

+1 Profil

Re: SQL query za presjek skupova

^{17.02.2012. u 17:43 - pre 148 meseci}

Evo resenja - nisam se bas natestirao, tako da moras detaljno da proveris da li je sve OK.

Elem, u ovom slucaju pozeljno je da koristis table varijablu kao ulazni parametar za proceduru. Nadam se da koristis SQL Server 2008, ako ne onda moramo neki workaround da pravimo sa XML-om.
Table varijabla je fleksibilna, mozes da koristis proizvoljan broj adresa i eliminise potrebu za comma separated listama parametara koje su dobra meta za SQL injection napade. Evo kako sve to izgleda. Ako nesto ne radi kako treba, javljaj da popravimo.

Code:
--Najpre treba da se napravi korisnicki table-tip
--kako bi SQL Server razumeo strukturu ulaznog parametra za stored proceduru
--u ovom slucaju to je lista razlicitih ip adresa
--da bi se osiguralo da su adrese razlicite stavljamo PRIMARY KEY
CREATE TYPE IPAdresa AS TABLE
(
    ip_adresa VARCHAR(32) NOT NULL PRIMARY KEY
);
GO

--sada pravimo proceduru u kojoj je table varijabla ulazni parametar
--mozes da koristis proizvoljno mnogo ip adresa, READONLY mora da stoji,
--table parametar je samo ulazni, ne moze da se manipulise njime
--u ovom slucaju i ne treba
CREATE PROCEDURE dbo.NadjiZajednickeAdrese
(@IPAdrese IPAdresa READONLY)
AS
BEGIN
    SET NOCOUNT ON;
    WITH cte (ipadresa_adresa,ipadresa_klijent) AS
    (
        SELECT ipadresa_adresa, ipadresa_klijent
        FROM ipnumber
        UNION ALL
        SELECT ipadresa_klijent, ipadresa_adresa
        FROM ipnumber
    )
    SELECT ipadresa_adresa
    FROM cte
    WHERE ipadresa_klijent IN (SELECT ip_adresa FROM @IPAdrese)
    GROUP BY ipadresa_adresa
    HAVING COUNT(DISTINCT ipadresa_klijent) = (SELECT COUNT(*) FROM @IPAdrese);
END;
GO

----------------------------------------------
--Poziv procedure
---------------------------------------------
--Punjenje ulaznog parametra
DECLARE @Ulaz AS IPAdresa;
INSERT INTO @Ulaz (ip_adresa) VALUES(22222),(55555);

--Poziv procedure
EXEC dbo.NadjiZajednickeAdrese @Ulaz;

I, naravno, ako nije 2008+ onda moramo drugacije da implemntiramo ulazni parametar.
Poz,
M.

Sad šta je tu je. A možda će da dođe i ova tvoja tetka iz Bosanske Krupe.

Odgovor na temu

sovabass
Banja Luka

Član broj: 96338
Poruke: 43
..able.dyn.broadband.blic.net.

Profil

Re: SQL query za presjek skupova

^{18.02.2012. u 13:50 - pre 148 meseci}

Pozdrav,

ideja sa tabelom kao ulaznim parametrom je odlicna, testirao sam sp na MSSQL 2008-ci,sve je ok,radi posao.
Medjutim,DB na kojem bi trebao koristiti sp je na MSSQL2000,tako da bi proceduru trebalo prilagoditi za MSSQL2000 ( ne dozvoljava definisanje ulaznog parametra ni kreiranje procedure),pa ako te ne mrzi..... :)

U svakom slucaju,hvala ti.

Odgovor na temu

HladankaoLed
Database Consultant
Vienna, Austria

Član broj: 228937
Poruke: 36
*.5.14.univie.teleweb.at.

Sajt: milossql.wordpress.com

+1 Profil

Re: SQL query za presjek skupova

^{18.02.2012. u 20:57 - pre 148 meseci}

Alternativa za TVP je XML, ali SQL Server raspolaze tim tipom tek od verzije 2005. Jedino sto za SQL Server 2000 preostaje je da se proceduri preda comma separated list IP adresa i da se iste unutar tela procedure importuju u temporary tabelu. Ovo bi trebalo da radi na 2K, ja sam ga probao na 2008 R2 i 2012 RC0 verzijama.

Code:
CREATE PROCEDURE [dbo].[NadjiZajednickeAdrese2000]
(@Input varchar(300))
AS
BEGIN
    CREATE TABLE #@IPAdrese
    (
        ip_adresa varchar(32)  NOT NULL   PRIMARY KEY
    )

    DECLARE @IP varchar(32), @Pos int

    SET @Input = LTRIM(RTRIM(@Input))+ ','
    SET @Pos = CHARINDEX(',', @Input, 1)

    IF REPLACE(@Input, ',', '') <> ''
    BEGIN
        WHILE @Pos > 0
        BEGIN
            SET @IP = LTRIM(RTRIM(LEFT(@Input, @Pos - 1)))
            IF @IP <> '' INSERT INTO #@IPAdrese (ip_adresa) VALUES (@IP)
            SET @Input = RIGHT(@Input, LEN(@Input) - @Pos)
            SET @Pos = CHARINDEX(',', @Input, 1)
        END
    END

    SELECT ipadresa_adresa
    FROM
    (
        SELECT ipadresa_adresa, ipadresa_klijent
        FROM ipnumber
        UNION ALL
        SELECT ipadresa_klijent ipadresa_adresa, ipadresa_adresa  ipadresa_klijent
        FROM ipnumber
    ) cte
    WHERE ipadresa_klijent IN (SELECT ip_adresa FROM #@IPAdrese)
    GROUP BY ipadresa_adresa
    HAVING COUNT(DISTINCT ipadresa_klijent) = (SELECT COUNT(*) FROM #@IPAdrese)
END

Poziv procedure:

Code:
DECLARE @input varchar(300)='22222,55555'
EXEC dbo.NadjiZajednickeAdrese2000 @input

Poz,
M.

Sad šta je tu je. A možda će da dođe i ova tvoja tetka iz Bosanske Krupe.

Odgovor na temu

sovabass
Banja Luka

Član broj: 96338
Poruke: 43
*.stat.customer.blic.net.

Profil

Re: SQL query za presjek skupova

^{20.02.2012. u 15:04 - pre 148 meseci}

Pozdrav,

probao sam i izbacuje:

Code:

Server: Msg 139, Level 15, State 1, Line 1
Cannot assign a default value to a local variable.
Server: Msg 137, Level 15, State 1, Line 2
Must declare the variable '@input'.

Odgovor na temu

HladankaoLed
Database Consultant
Vienna, Austria

Član broj: 228937
Poruke: 36
*.wk.or.at.

Sajt: milossql.wordpress.com

+1 Profil

Re: SQL query za presjek skupova

^{20.02.2012. u 15:08 - pre 148 meseci}

To je zbog inicijalizacije varijable. To ne radi pre 2008-ce...

Code:
DECLARE @input varchar(300)
SET @input ='22222,55555'
EXEC dbo.NadjiZajednickeAdrese2000 @input

Sad bi trebalo da radi...

Sad šta je tu je. A možda će da dođe i ova tvoja tetka iz Bosanske Krupe.

Odgovor na temu