Spajanje podataka po zajednickom atributu

Tuesday, 16.10.2007 – Srdjan

MiŇ°a, jedan moj kolega s posla, je juńće naiŇ°ao na problem prilikom pravljenja nekog izveŇ°taja. Za potrebe tog izveŇ°taja mora da prikupi podatke iz ńćetiri razlińćita izvora, a onda da ih objedini u jedinstven izveŇ°taj.

MiŇ°a je napravio strukturu od ńćetiri tabele, i u svaku od tabela je importovao podatke iz po jednog izvora. Svi prikupljeni podaci se odnose¬†na¬†odnose¬†sa¬†poslovnim¬†partnerima¬†(bilo¬†da¬†se¬†radi¬†o¬†kupcima ili dobavljańćima). Ono Ň°to je zajednińćko svim podacima je da svi izvori koriste zajednińćki nańćin ‘Ň°ifriranja’ partnera.

PoŇ°to nemaju svi izvori odnose sa svim partnerima, ni jedan izvor nema kompletnu listu partnera. Problem je nastao kako¬†se od ńćetiri nepotpune liste partnera moŇĺe napraviti jedna jedinstvena lista. Po dobijanju jedinstvene liste partnera ostaje rutinski posao spajanja tabela.

MiŇ°a¬†mi¬†se¬†obratio¬†za¬†pomońᬆbaŇ°¬†za¬†kreiranje¬†ove¬†jedinstvene liste partnera, jer je problem dodatno komplikovala ńćinjenica da su razlińćiti izvori davali razlińćita imena partnerima.

Da¬†bih¬†ilustrovao¬†problem,¬†dańáu¬†sledeńáu¬†strukturu¬†tabela:

CREATE TABLE podaci_prodaje ( 
  sifra_partnera INTEGER NOT NULL PRIMARY KEY, 
  ime_partnera VARCHAR(50) NOT NULL, 
  broj_faktura INTEGER NOT NULL, 
  prodajna_vrednost NUMERIC(12, 2) NOT NULL);         

CREATE TABLE podaci_nabavke ( 
  sifra_partnera INTEGER NOT NULL PRIMARY KEY, 
  ime_partnera VARCHAR(50) NOT NULL, 
  datum_poslednje_nabavke DATE NOT NULL, 
  nabavna_vrednost NUMERIC(12, 2) NOT NULL);              

CREATE TABLE podaci_marketinga ( 
  sifra_partnera INTEGER NOT NULL PRIMARY KEY, 
  ime_partnera VARCHAR(50) NOT NULL, 
  broj_promocija INTEGER NOT NULL);

Ovu strukturu ńáu popuniti test podacima:

INSERT INTO podaci_prodaje (sifra_partnera, ime_partnera, broj_faktura, prodajna_vrednost) 
  VALUES (1, 'Mika str', 2, 15000.00); 
INSERT INTO podaci_prodaje (sifra_partnera, ime_partnera, broj_faktura, prodajna_vrednost) 
  VALUES (2, 'Pera doo', 5, 46500.00); 
INSERT INTO podaci_prodaje (sifra_partnera, ime_partnera, broj_faktura, prodajna_vrednost) 
  VALUES (4, 'Joca doo', 4, 75000.00);         

INSERT INTO podaci_nabavke (sifra_partnera, ime_partnera, datum_poslednje_nabavke, nabavna_vrednost) 
  VALUES (4, 'D.O.O. JOCA', '2007-03-01', 22000.00); 
INSERT INTO podaci_nabavke (sifra_partnera, ime_partnera, datum_poslednje_nabavke, nabavna_vrednost) 
  VALUES (5, 'Doo ZIKA i SONS', '2007-05-15', 15000.00);          

INSERT INTO podaci_marketinga (sifra_partnera, ime_partnera, broj_promocija) 
  VALUES (1, 'MIKA', 1); 
INSERT INTO podaci_marketinga (sifra_partnera, ime_partnera, broj_promocija) 
  VALUES (3, 'MELANIJA', 2); 
INSERT INTO podaci_marketinga (sifra_partnera, ime_partnera, broj_promocija) 
  VALUES (4, 'JOCA', 5);

Napomena: Spomenuo sam da je MiŇ°a napravio ńćetiri tabele, a ja ovde radim¬†sa¬†tri tabele jer to ne utińće na princip reŇ°avanja problema.

Zahtev je da se od datih podataka napravi izveŇ°taj sa sledeńáim kolonama:¬†ime¬†partnera, broj faktura, prodajna vrednost, datum poslednje nabavke, nabavna vrednost i kolona sa brojem promocija. TakońĎe, svi podaci o jednom partneru se moraju pojaviti u jednom redu.

U¬†testnim¬†podacima¬†treba¬†uońćiti¬†da¬†ni¬†jedna¬†od¬†sluŇĺbi¬†(prodaje, nabavke, marketinga) nema u svojim podacima sve partnere. Druga stvar koju treba uońćiti je neznatna razlika u imenovanju partnera u pojedinim sluŇĺbama. Tako sluŇĺba prodaje partnera sa Ň°ifrom 1 imenuje ‘Mika¬†str’, dok tog istog partnera¬†marketing¬†imenuje¬†‘MIKA’.

Ova¬†ńćinjenica¬†o¬†imenovanju¬†partnera¬†komplikuje¬†stvari jer se objedinjena lista partnera nemoŇĺe dobiti prostim spajanjem (UNION) podataka raznih sluŇĺbi.

SELECT sifra_partnera, ime_partnera FROM podaci_prodaje 
 UNION ALL 
SELECT sifra_partnera, ime_partnera FROM podaci_nabavke 
 UNION ALL 
SELECT sifra_partnera, ime_partnera FROM podaci_marketinga;

Prikazan upit ne spaja podatke o partnerima na ispravan nańćin jer duplira partnere koji su razlińćito imenovani od strane razlińćitih sluŇĺbi. Ovde nebi pomogla ni upotreba ńćistog UNION umesto UNION ALL.

Treba¬†nam¬†suptilniji¬†nańćin¬†za¬†spajanje¬†podataka¬†o¬†partnerima¬†i¬†to¬†se postiŇĺe pomońáu sledeńáeg pogleda:

CREATE VIEW partneri (sifra_partnera, ime_partnera) 
AS 
SELECT s.sifra_partnera, 
       CASE WHEN p.ime_partnera IS NOT NULL THEN p.ime_partnera 
            WHEN n.ime_partnera IS NOT NULL THEN n.ime_partnera 
            WHEN m.ime_partnera IS NOT NULL THEN m.ime_partnera 
       END 
  FROM (SELECT sifra_partnera FROM podaci_prodaje 
         UNION 
        SELECT sifra_partnera FROM podaci_nabavke 
         UNION 
        SELECT sifra_partnera FROM podaci_marketinga 
       ) AS s 
       LEFT OUTER JOIN 
       podaci_prodaje AS p 
         ON s.sifra_partnera = p.sifra_partnera 
       LEFT OUTER JOIN 
       podaci_nabavke AS n 
         ON s.sifra_partnera = n.sifra_partnera 
       LEFT OUTER JOIN 
       podaci_marketinga AS m 
         ON s.sifra_partnera = m.sifra_partnera;

U gornjem pogledu je prvo upotrebljen ńćist UNION za dobijanje svih (unikatnih) Ň°ifara, a onda su te Ň°ifre spojene sa odgovarajuńáim imenima partnera. Prost SELECT nad ovim pogledom vrańáa kompletnu listu jedinstvenih partnera:

sifra_partnera  ime_partnera 
--------------  --------------- 
             1  Mika str 
             2  Pera doo 
             3  MELANIJA 
             4  Joca doo 
             5  Doo ZIKA i SONS

Sada¬†kada¬†imamo¬†jedinstvenu¬†listu¬†partnera¬†uopŇ°te¬†nije¬†teŇ°ko¬†dońái do sledeńáeg upita:

SELECT s.ime_partnera, 
       p.broj_faktura, p.prodajna_vrednost, 
       n.datum_poslednje_nabavke, n.nabavna_vrednost, 
       m.broj_promocija 
  FROM partneri AS s 
       LEFT OUTER JOIN 
       podaci_prodaje AS p 
         ON s.sifra_partnera = p.sifra_partnera 
       LEFT OUTER JOIN 
       podaci_nabavke AS n 
         ON s.sifra_partnera = n.sifra_partnera 
       LEFT OUTER JOIN 
       podaci_marketinga AS m 
         ON s.sifra_partnera = m.sifra_partnera;

Ovaj upit nam daje traŇĺeni rezultat:

ime_partnera     broj_faktura  prodajna_vrednost  datum_poslednje_nabavke  nabavna_vrednost  broj_promocija 
---------------  ------------  -----------------  -----------------------  ----------------  -------------- 
Mika str                    2           15000.00                                                          1 
Pera doo                    5           46500.00 
MELANIJA                                                                                                  2 
Joca doo                    4           75000.00               2007-03-01          22000.00               5 
Doo ZIKA i SONS                                                2007-05-15          15000.00
  1. 3 Responses to “Spajanje podataka po zajednickom atributu”

  2. Fino, fino, samo verujem da bi bilo korisno naglasiti da se suŇ°tina postiŇĺe upotrebom ‚ÄěLEFT OUTER JOIN‚Äú koji ‚Äěspaja‚Äú i kada je poreńĎena kolona NULL.

    By –Ē–į–Ĺ–ł–Ľ–ĺ on Oct 17, 2007

  3. Predlozio bih sledece resenje:

    Select partneri.sifra_partnera, MAX(partneri.ime_partnera)
    from
    (SELECT podaci_prodaje.sifra_partnera, podaci_prodaje.ime_partnera FROM podaci_prodaje
    UNION
    SELECT podaci_nabavke.sifra_partnera, podaci_nabavke.ime_partnera FROM podaci_nabavke
    UNION
    SELECT podaci_marketinga.sifra_partnera, podaci_marketinga.ime_partnera FROM podaci_marketinga
    ) partneri
    GROUP BY partneri.sifra_partnera;

    Pretpostavimo da se u ove tri tabele nalaze desetine hiljada podataka, i to vrlo verovatno da nekoliko hiljada zapisa nema u jednoj a ima u drugoj tabeli itd. Prema prethodnom resenju usled LEFT OUTER JOIN-a spojilo bi se, bespotrebno, jako mnogo podataka, sa null-ovima, tj. bilo bi daleko vise zapisa, kroz koje bi upit prosao, kako bi CASE izdvojio koje “ime_partnera” da ispise, sto bi znacajno uticalo na performanse.
    U ovom predlogu resenja, funkcija MAX(partneri.ime_partnera) (ili min) uz pretpostavku da nije bitno iz koje ce se tabele uzimati “ime_partnera”, poredi samo ono sto vec imamo, znaci maksimalno po 3 zapisa.
    Mislim da ovo resenje ima prednost u pogledu brzine izvrsavanja, kada je u pitanju velika kolicina podataka (sto je najcesce slucaj)

    By Andrija on Nov 9, 2007

  4. Tako je, a moŇĺe da se upotrebi i UNION ALL.

    Treba napomenuti da ova dva upita ipak nisu ekvivalentna u sluńćaju kad se u ulaznim podacima pojavljuju duplirani redovi.

    By Srdjan on Nov 9, 2007

Post a Comment