Baze podataka – MySQL, Oracle, PostgreSQL, MS SQL Server, Access, modelovanje podataka …

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