Moderný reporting pracujeme s MySQL (spájanie pomocou JOIN)

 

Väčšina z PC friendly čitateľov určite vrámci databáz počula pojem SELECT-FROM-WHERE-JOIN – GROUP BY spojenie. Vzhľadom k predchádzajúcim článkom si v podstate všetko okrem klauzuly JOIN a GROUP BY dokážete dať do súvislostí.

Tak teda čo znamená pojem JOIN v SQL jazyku:

Podľa Wiki je to klauzula, založená na vymedzení referenčných polí, ktorá kombinuje záznamy medzi tabuľkami. Ľudskou rečou povedané, ak mám napríklad dve tabuľky tak pomocou JOIN spolu s vymedzaním relevantných stĺpcov (Foerign key stĺpec v primárnej tabuľke =  Primary key stĺpec v referenčnej)  program vie, ako má dané záznamy spojiť.

Ak si spomeniete na predchádzajúci článok, kde som spájal dve tabuľky takto:

SELECT dealer.Predajca, cost.* FROM cost, dealer WHERE Cost.Predajca=Dealer.ID;

Tak v novom príkaze vypustíme klauzulu WHERE a pridáme JOIN (typ spojenia). Tu sa dostávame pre mnohých do komplikovaného rozhodnutia typu: Aký typ JOIN klauzuly použiť?

V tomto bode si dovolím inšpirovať sa knihou Mistrovství v MySQL od M. Koflera, kde na str. 237 má prehľad typov JOIN klauzúl:

Nepodmienené typy vrátia všetky možné kombinácie vrámci záznamov, odlišný je však STRAIGHT_JOIN, ktorý neoptimalizuje poradie výberu dat. Zameriame sa preto na použitie podmienených typov:

V podmienených typoch záleží na poradí tabuliek. Pre pochopenie som vytvoril nasledujúce tabuľky:

Tabuľka Dealer:

Tabuľka Dovolenka:

Všimnite si, že Tabuľky Dovolenka sa odkazuje na zoznam pracovníkov z tabuľky Dealer a zároveň fakt, že Helena zatiaľ nečerpala žiadnu dovolenku a v Dovolenke máme dva neplatné záznamy s ID zamestnanca 5 a 7.

Ak vezmeme v úvahu LEFT JOIN (a zároveň NATURAL JOIN) vrámci tabuliek dealer a dovolenka, nasledujúci skript:

select dealer.Predajca as 'Zamestnanec', sum(dovolenka.Hodiny) as 'Dovolenka Celkom' from dovolenka left join  dealer on dovolenka.Zamestnanec = dealer.ID group by dealer.predajca;

Vráti všetky záznamy z tabuľky Dovolenka, ku ktorým doplní relevantné mená z tabuľky Dealer. V prípade, ak by sme v evidencii Dovolenka mali ID > 4, znamenalo by to, že tam máme zle vloženú evidenciu alebo ID zamestnanca, ktorý ešte nie je evidovaný v systéme (tabuľke Dealer).

To znamená, ak by kontent tabuľky Dovolenka vyzeral takto:

Predchádzajúci skript by vyhodil nasledujúci prehľad:

To znamená, že vrátil všetky dovolenkové záznamy, vrátane tých, ku ktorým v tabuľke Dealer nenašiel Meno (súčet hodín s prívlastkom NULL).

Čo by sa stalo, ak by sme zamenili LEFT za RIGHT JOIN:

select dealer.Predajca as 'Zamestnanec', sum(dovolenka.Hodiny) as 'Dovolenka Celkom' from dovolenka right join  dealer on dovolenka.Zamestnanec = dealer.ID group by dealer.predajca;

Tento skript by zamenil tabuľky. To znamená, ku každému Menu v tabuľke Dealer by priradil súčty hodín vrámci dovolenky:

Všimnite si, že nám vrátil naviac prehľad o Helene, ktorá ešte dovolenku nečerpala a zároveň sa zbavil neidentifikovaných 13 hodín voľna. Čo sa stane ak použijem pôvodný select typ z predcházajúceho článku alebo INNER JOIN? INNER JOIN v podstate definuje niečo ako skalárny súčin údajov, medzi dvoma tabuľkami.

Znamená to, že ak niektorá z tabuliek obsahuje vrámci referencií hodnoty NULL (Helena v tabuľke Dealer a záznam 4 a 5 z tabuľky Dovolenka), tak tieto hodnoty budú ignorované pri výstupe:

select dealer.Predajca as 'Zamestnanec', sum(dovolenka.Hodiny) as 'Dovolenka Celkom' from dovolenka inner join  dealer on dovolenka.Zamestnanec = dealer.ID group by dealer.predajca;

alebo:

select dealer.Predajca as 'Zamestnanec', sum(dovolenka.Hodiny) as 'Dovolenka Celkom' from dovolenka, dealer where dovolenka.Zamestnanec = dealer.ID group by dealer.predajca;

Vráti nasledujúci prehľad:

Ak by sme tam chceli zanechať tých 13 hodín, museli by sme im priradiť správne ID alebo definotať ich ako nových zamestnancov. Helena sa tam objaví v prípade, že niekedy načerpá dovolenku.

Všimnite si časť GROUP BY: Tá je potrebná, aby aplikácia vedela, podľa ktorých parametrov má urobiť prehľad. Inak povedané, predchádzajúce kódy vypočítali súčet hodín z dovoleniek a rozdelili ich podľa hodnôt zo stĺpca predajca v tabuľke dealer.

Použijeme prvý skript ale bez group by:

select dealer.Predajca as 'Zamestnanec', sum(dovolenka.Hodiny) as 'Dovolenka Celkom' from dovolenka left join  dealer on dovolenka.Zamestnanec = dealer.ID;

Sami vidíme, že výsledok čo sa týka počtu hodín je rovnaký. Akurát problém je v tom, že tu aplikácia zobrala prvé ID = 1, ktoré má hodnotu Peter a k nemu priradila celkový súčet hodín. Príkaz GROUP BY vytvorí prehľad všetkých Mien a k nim nasledne dohodí súčty, ktoré celkovo dajú hodnotu 82 hodin.

Poučenie: snažte sa čo najlepšie poznať svoju databázu a taktiež si vždy presne vymedzte, čo je cieľom selektu.

Tým pádom môžem konštatovať, že sme pripravení si v ďalších článkoch vytvárať selekty podľa daných požiadaviek. Taktiež sa začneme hrať (kresliť si) so schémami dashboardov.

V prípade dotazov sa neváhajte pýtať – alebo kliknite na FB odkaz dole.

Funkcia–OverStlpec (Zistí, či sú v stĺpci rovnaké hodnoty)

 

Dnešok môžem označiť za “excelovský” a to z dôvodu, že som sa po dlhej dobe celý deň hral s datami v Exceli. Ako to už býva u mňa zvykom – priniesol som celkom jednoduché, pre mňa dnes užitočné riešenie na otázku:

Sú v danom stĺpci rovnaké hodnoty?

Samozrejme, spôsoby ako si poradiť sú rôzne. Avšak ak máte povedzme 100 stĺpcov, sú filtre alebo pivotky časovo náročná záležitosť.  Čo by ste povedali na nasledujúce riešenie:

Postup je naozaj jednoduchý: Alt + F11 – otvoríte VBA editor. Kliknutím na Insert – New Module vložíte nový modul, do ktorého stačí skopírovať nasledujúci kód:

Function OverStlpec(Oblast As Range)
 
Dim i As Integer
 
UB = Oblast.Rows.Count
 
 
For i = 2 To UB
 
    If Oblast(i) <> Oblast(i - 1) Then
        OverStlpec = "Nezhoda"
        Exit Function
    End If
Next i
  
OverStlpec = "Zhoda"
 
End Function

Na záver môžete túto funkciu hravo využívať, pričom jej tvorba zabrala maximálne 20 sekúnd. Celý súbor vrátane príkladu si môžete stiahnuť kliknutím na Download obrázok. Jednoduché, prístupné aj pre VBA laika – tak snáď Vám to niekedy pomôže.

             

Moderný reporting pracujeme s MySQL (Prvé selekty)

 

Vzhľadom k tomu, že som postup prípravy databázy spolu s jej monžosťou downloadu popísal v predchádzajúcich článkoch, nemôžem nezdieľať svoj enthusiasmus, čo sa týka manipulácie s datami.

Túto časť považujem za najviac zaujímavú z hľadiska možnej špekulácie. Inak povedané, v tomto štádiu môžete využiť svoju kreativitu a tzv. otázky na papieri premeniť v skutočné dotazy s ešte zaujímavejšími faktami. Spomeňme si na niekoľko bodov, z úvodného sylabu:

1) Koľko ktorý predajca predal áut v danom regióne, pričom platí, že predajcovia kolujú medzi predajňami.

2) Aké auto predal - každý model má inú cenu/maržu a Škoda auto momentálne disponuje modelmi: Citigo, Fabia, Roomster, Rapid, Yeti, Octavia a ich vlajková loď Superb. Tým pádom je jasné, že sa nám bude lepšie dariť, ak predáme 10 Superb-ov, nadopovaných nie skromnou výbavou oproti 10 Citigo vozíkom, určeným na nákupy a ku kaderníkovi.

3) Náklady spojené s predajom alebo odborne Cost of Sales. Pod nimi si môžeme pre jednoduchosť predstaviť rôzne promo akcie, výstavy v supermarketoch, víkendové dni a iné. Ďalšou súčasťou Cost of Sales sú zľavy na automobil.

4) Mzdové hodnotenie dealerov spolu s bonusovým systémom, aby boli dostatočne motivovaní predávať a predávať.

Na základe nich budeme formovať dotazy tak, aby sme dostali relevantné podklady. Predtým si však musíme osvojiť niektoré základné funckie.

SELECT niečo FROM niečo WHERE niečo;

Tento stýl uvažovania sa používa prakticky neustále. Ako ste si už všimli, SELECT * FROM názov tabuľky vráti vždy všetky záznamy z konkrétnej tabuľky. Napríklady SELECT * FROM cost vráti hodnoty v nasledujúcom obrázku:

Čo jesíce pekné, ale nepoznáme kto sa skrýva pod číslom 1 v stĺpci Predajca. Dnes si preto ukážeme základný postup spájania tabuliek (aby ste pochopili logiku, prepojíme tabuľku cost s tabuľkou dealer tak, aby sme pri selekte miesto čísel mali Mená) – viď obrázok:

SELECT dealer.Predajca, cost.* FROM cost, dealer WHERE Cost.Predajca=Dealer.ID;

Tento skript pracuje s označením názov tabuľky.názov stĺpca – dealer.Predajca, v ktorom sa nachádzajú skutočné mená, cost.* znamená vráť všetko z tabuľky cost. FROM cost, dealer – zoznam tabuliek. Za príkazom WHERE spájame tabuľky tak, že definujeme, ktoré stĺpce medzi tabuľkami su relevantné. Teda  čísla v Cost.Predajca sú zo zoznamu čísel v Dealer.ID.

Po exekúcii skriptu si môžeme overiť, že prepojenie vyšlo tak ako malo:

V uvedenom obrázku platí, že pod číslom 1 je zakódovaný predajca Peter. Pod číslom 2 je už Martin. Takto pripravený skript nám už síce dáva pridanú hodnotu, avšak aby sme z neho dostali zrozumiteľný prehľad, chcelo by to veľa práce (napr. v Exceli). Povezme, že chceme prehľad ročných miezd jednotlivých Predajcov.

SELECT dealer.Predajca, cost.Rok, sum(cost.Mzda) as 'Mzdy' FROM cost, dealer WHERE Cost.Predajca=Dealer.ID GROUP BY dealer.Predajca, cost.Rok;

V ňom sme poupravili názov sumácie ako Mzdy a pridali parameter GROUP BY, v ktorom definujeme stĺpce, podľa ktorých chceme vytvoriť prehľad vo forme sumácie – inak povedané, chceme súčet mzdových nákladov podľa dealera a konkrétneho roku.

Výsledok vyzerá nasledovne:

Nabudúce si rozšírime problematiku spájania tabuliek pomocou JOIN klauzuly, ktorá sa používa omnoho viac frekventovane, než spôsob ktorý som dnes opísal.

 

Moderný reporting pracujeme s MySQL (prepájame tabuľky pomocou referencií)

 

Máme pripravené tabuľky spolu s datami. Čaká nás však veľmi dôležitá udalosť – tieto tabuľky musíme prepojiť tak, aby sme zabezpečitli tzv. referenčnú integritu. Inak povedané, aby niekde v systéme nenastala situácia, že užívateľ priradí zákazke zľavu číslo 6, ak ich reálne má k dispozícii iba 5. Poprípade, ak by sa tak stalo musel by zľavu riadne nadefinovať, resp. nechať si ju schváliť vedením.

Veľmi pekne je táto vlastnosť popísaná v nasledujúcom odkaze od W3schools. Pokúsim sa ju načrtnúť na našom príklade:

Pri query typu: select*from sales.sales limit 5; nám program vráti nasledujúcu tabuľku:

Na prvý pohľad môžeme konštatovať, že stĺpce ako Naklady, Predajca, Mesto apod. nemajú zmysel bez toho aby sme vedeli, že pod predajcom 1 sa ukrýva Peter atď. 

V tomto prípade môžeme povedať, že: Tabuľka Sales v stĺpci Predajca neobsahuje primárne hodnoty, ale tzv. foreign_key hodnoty, ktoré sa rovnajú ID hodnotám v tabuľke Dealer.

Všeobecne sa jedná o nadefinovanie Primary a Foreign Key medzi tabuľkami. Primary key som už spomínal pri tvorbe tabuliek. Prejdime na príkaz Foreign Key:

Pri tvorbe tabuliek ho môžeme zakomponovať takto:

CREATE TABLE Tabulka
(Tab_ID int NOT NULL, Tab_Stlpec Varchar(50) NOT NULL,
Zakodovana_Premenna int NOT NULL,
PRIMARY KEY (Tab_Id), FOREIGN KEY (Zakodovana_Premenna) REFERENCES Dealer(ID));

Tento príkaz je skoro totožný s klasickým CREATE TABLE, avšak na konci sme okrem Primary Key definovali Foreign key (Stĺpec, kde namiesto textov bude odkaz na ID v referenčnej tabuľke) a referenciu (tabuľka Dealer, resp. jej stĺpec ID).

Obecne platí, že hodnoty vo foreign key v istej tabuľke sú rovné primary key hodnotám v referenčnej tabuľke.

Čo je ďalej potrebné dodať? Okrem definovania aj správanie sa pri uploadovaní a vymazávaní záznamov:

Jedná sa o možnosti ON UPDATE a ON DELETE. Pre lepšiu manipuláciu s tabuľkami však nebudeme používať možnosť FOREIGN KEY ale CONSTRAINT – dôvod? – lepšia manipulácia pri mazaní / zmenách referencií.

CREATE TABLE Tabulka
(Tab_ID int NOT NULL, Tab_Stlpec Varchar(50) NOT NULL,
Zakodovana_Premenna int NOT NULL,
PRIMARY KEY (Tab_Id), CONSTRAINT Prepojenie1
    FOREIGN KEY (Zakodovana_Premenna ) REFERENCES Dealer (ID) ON DELETE CASCADE ON UPDATE CASCADE);

Malá zmena kódu, kde sme použili pre obe možnosti úpravy CASCADE. ON DELETE znamená, udalosť, keď sa zmaže záznam v referenčnej tabuľke (zmažem konkrétneho dealera, na ktorého sa odkazuje niektorá z iných tabuliek). ON UPDATE čo sa stane s odkazujúcimi sa tabuľkami pri zmene záznamu v referenčnej tabuľke (Zmením Priezvisko dealera, ktorý bol už použitý v iných tabuľkách – teda niečo predal alebo dostal výplatu).

Tieto udalosti môžeme definovať nasledujúcimi možnosťami:

Set NULL : Ak zmažem záznam, v ostatných tabuľkách sa namiesto čísel vo foreign key objavia hodnoty null.

CASCADE : Aplikuje zmenu v odkazujúcich sa tabuľkách.

RESTRICT : Zabráni vymazaniu referenčnej hodnoty v prípade, že sa používa v iných tabuľkách.

NO ACTION :V podstate podobná ako RESTRICT, akurát systém nerobí kontrolu pred jeho exekúciou (nebudeme používať, teda ani zaoberať sa).

Obvykle sa používa nasledujúca kombinácia:

ON DELETE RESTRICT – zabráni zmazaniu používaných referencií

ON UPDATE CASCADE – použije zmenu referenčnej hodnoty v odkazujúcich sa tabuľkách

Ak by sme pripravovali tabuľku Cost odznova vrátane referencií, použijeme nasledujúci kód:

CREATE TABLE cost (ID int(11) NOT NULL AUTO_INCREMENT, Predajca int(11) NOT NULL,
   Mesiac int(11) NOT NULL, Rok int(11) NOT NULL, Mzda decimal(10,2) NOT NULL,
   Sprava decimal(10,2) NOT NULL, Bonus decimal(10,2) DEFAULT NULL, PRIMARY KEY (ID),
   KEY Dealer_Typ (Predajca), CONSTRAINT Dealer_Typ FOREIGN KEY (Predajca) REFERENCES dealer (ID) ON DELETE RESTRICT ON UPDATE CASCADE);

Nás však zaujíma úprava už vytvorených tabuliek. Takže budeme pridávať CONSTRAINTS pre tabuľky sales a cost:

-- Foreign Keys - Sales
ALTER TABLE sales add
   CONSTRAINT Auto_Typ FOREIGN KEY (Auto) REFERENCES car (ID)ON DELETE RESTRICT ON UPDATE CASCADE,
Diskont_Typ FOREIGN KEY (Diskont) REFERENCES discount (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
   add CONSTRAINT Mesto_Typ FOREIGN KEY (Mesto) REFERENCES city (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
   add CONSTRAINT Naklady_Typ FOREIGN KEY (Naklady) REFERENCES cost (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
   add CONSTRAINT Predajca_Typ FOREIGN KEY (Predajca) REFERENCES dealer (ID) ON DELETE RESTRICT ON UPDATE CASCADE;

-- Foreign Keys - Cost
ALTER TABLE cost ADD CONSTRAINT Dealer_Typ FOREIGN KEY (Predajca) REFERENCES dealer (ID) ON DELETE RESTRICT ON UPDATE CASCADE;

Pre ilustráciu ak datáte príkaz show create table sales.sales; SQL Vám vráti celý proces tvorby existujúcej tabuľky:

CREATE TABLE `sales` (   `ID` int(11) NOT NULL AUTO_INCREMENT,   `Naklady` int(11) NOT NULL,   `Predajca` int(11) NOT NULL,   `Mesto` int(11) NOT NULL,   `Auto` int(11) NOT NULL,   `Diskont` int(11) NOT NULL,   `Predaj` int(11) DEFAULT NULL,   `Mesiac` int(11) NOT NULL,   `Rok` int(11) NOT NULL,   PRIMARY KEY (`ID`),   KEY `Naklady_Typ` (`Naklady`),   KEY `Mesto_Typ` (`Mesto`),   KEY `Auto_Typ` (`Auto`),   KEY `Diskont_Typ` (`Diskont`),   KEY `Predajca_Typ` (`Predajca`),   CONSTRAINT `Auto_Typ` FOREIGN KEY (`Auto`) REFERENCES `car` (`ID`) ON DELETE NO ACTION ON UPDATE CASCADE,   CONSTRAINT `Diskont_Typ` FOREIGN KEY (`Diskont`) REFERENCES `discount` (`ID`) ON DELETE NO ACTION ON UPDATE CASCADE,   CONSTRAINT `Mesto_Typ` FOREIGN KEY (`Mesto`) REFERENCES `city` (`ID`) ON DELETE NO ACTION ON UPDATE CASCADE,   CONSTRAINT `Naklady_Typ` FOREIGN KEY (`Naklady`) REFERENCES `cost` (`ID`) ON DELETE NO ACTION ON UPDATE CASCADE,   CONSTRAINT `Predajca_Typ` FOREIGN KEY (`Predajca`) REFERENCES `dealer` (`ID`) ON DELETE NO ACTION ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=17473 DEFAULT CHARSET=utf8

Všimnite si, že mám neošetrenú udalosť ON DELETE. Ak by som potreboval jednotlivé kľúče zmeniť, jednoducho ich vymažem pomocou  nasledujúceho kódu (nezabudnite vypnúť kontrolu kľúčov (neskôr opäť zapnúť) - SET FOREIGN_KEY_CHECKS=0; potom to isté ale s 1)´:

alter table sales.sales drop foreign key Predajca_Typ;

Zistíme, že tabuľka Sales už neobsahuje CONSTRAINT Predajca_Typ. Tým pádom nám stačí si upraviť tabuľku tak, že jednoducho pridáme opäť ten istý kľúč, ale už so správnou definíciou ON DELETE:

ALTER TABLE sales
   add CONSTRAINT Predajca__Typ FOREIGN KEY (Predajca) REFERENCES dealer (ID) ON DELETE RESTRICT ON UPDATE CASCADE;

A opäť sa pozrieme na SHOW CREATE TABLE, zistíme že zmena bola prevedená. POZNÁMKA: Tieto zmeny sa snažte robiť v command line – nie vo Workbench, aby ste predišli komplikáciám.

ERR diagram vo finálnej fáze (po refreshnutí) vyzerá nasledovne:

Tým pádom môžem konštatovať, že sme si pripravili databázku, s ktorou môžeme pracovať. Tí, ktorí ste mali komplikácie – pokúste sa ju stiahnuť a vložiť folder do zložky C:\MySQL- Data\data