Za svoje doterajšie pôsobenie v Analytickom svete som budoval niekoľko teamov na zelenej lúke. Vo všeobecnosti to nie je žiaden „med lízať“. Musíte hľadať nových ľudí, zladiť ich skúsenosti s úlohami, ktoré stoja pred teamom. Na jednu vec je to však dobré. Vidíte jednoznačnejšie, čo sú chyby (či zlozvyky), ktoré si ľudia so sebou prinesú.
Hoci väčšina ľudí vám povie, že chcú robiť na Deep learningu (prinajhoršom aspoň Machine learningu – pre vysvetlenie rozdielu pozri sem), neuvedomujú si, že ak aj nejaký ML budú robiť, zdroje dát pre modely budú väčšinou pochádzať z relačných databáz. Ono sa odchodovosť klienta alebo náchylnosť ku kúpe nejakého produktu ťažko modeluje z fotiek klienta. (aj keď cesta by tu nepochybne bola pre niektoré produkty). Hoci pre niektorých to znie ako volanie minulosti, tak pre prácu s dátami je stále potrebné (nadpriemerne) dobe ovládať prácu s SQL. Túto skutočnosť potvrdil aj veľký Kaggle prieskum o tom, čo by mal dátový analytik vedieť, ak sa chce uplatniť.
Áno, SQLko nemá nič, čo by sa nedalo urobiť v Pythone (alebo Rku), ale veľmi často je SQL jediná forma, akou vám na primárne dáta umožnia siahnuť. Len málo databáz má natívne nadstavby pre jazyky ako Python a spoliehať sa na to, že si najprv dáta potiahnete ako celok do dataframu, aby ste potom nad nimi robili Python “čary“ je často (z hľadiska disk space alebo bezpečnosti dát) nemysliteľné. A tak, hoci sa na to niektorí moji mladší kolegovia pozerajú s dešpektom, je treba uchovávať s SQLkom istú formu „priateľstva.“
O to viac ma prekvapuje, keď ľudia, ktorí nastupujú do práce analytikov, majú len povrchné znalosti SQLka (a aj v tých nachádzam bodku pred groupby klauzulami 😊). Zo všetkých úloh, ktoré sú súčasťou prijímacieho konania do nášho teamu kandidáti dosiahli najhoršie výsledky práve v SQL časti, hoci testujeme len strednú úroveň odbornosti na túto zručnosť. Rád by som sa s Vami podelil o 3 SQL oblasti, ktoré ľudia vstupujúci do data science oblasti najčastejšie „dopackali“. Snáď vám poslúžia ako verifikácia, či by ste prešli podobnými pohovormi.
Proletári všetkých tabuliek spojte sa …
V transakčných dátach sa často objavujú subjekty na každej zo strán transakcie. Ak máte napríklad dáta banky alebo telco operátora, A môže poslať peniaze (alebo zavolať) B a rovnako aj naopak B môže poslať (volať) A. Ak teda máte transakčnú tabuľku, kde je zapísané, že A volalo B, subjekt A bude v jednom stĺpci tabuľky a stĺpec B v inom stĺpci. S veľkou pravdepodobnosťou však nájde aj riadok, kde je to naopak, kde B je v prvom stĺpci a A v tom druhom. Preto keď potrebujete vyfiltrovať všetky interakcie daného subjektu potrebujete zohľadniť oba možné smery.
Univerzálnosť SQLka ponúka niekoľko spôsobov ako vyhľadať všetky vzájomne vzťahy, suverénne najrýchlejším však je UNION alebo UNION ALL. Keďže väčšina čitateľov asi vie, čo tieto príkazy realizujú, preskočím ich popis (ak treba pozrite si definície cez priložené linky) . V čom však má jasno iba minimum uchádzačov je, keby je výhodnejšie použiť UNION a kedy UNION ALL. Pri práci s malými databázami ich rozdiel môže prísť banálny, ale ak robíte a tabuľkami o veľkosti desiatok GB či dokonca TB, chybné rozhodnutie už výrazne zabolí.
Ak by sme prirovnali UNION/UNION ALL k upratovaniu, tak UNION ALL je bordelár a UNION čistomilný pedant. UNION ALL totiž iba nahádže na hromadu jednotlivé kúsky (dielčie subquery), zakiaľ UNION sa snaží hromadu poukladať tak aby v nej neboli duplicity. Z tohto podstatného rozdielu vyplýva aj to, kedy dáva zmysel jednotlivé varianty použiť. Ak “hromada“, ktorú treba upratať, je v ráde tisícov, či státisícov riadkov, UNION stále uprace v relatívne rozumnom čase. Ak však hromada, ktorú treba pretriediť má milióny, či nebodaj ešte viac riadkov, UNION začína byť výraznou brzdou. Prvky, treba totiž medzi sebou vzájomne porovnať, aby ste zistili kto je komu duplicita a aj tie najrýchlejšie sortovacie algoritmy dosahujú zložitosť N*log N. Ak navyše k tomu pridáte fakt, že vzájomné porovnanie musí prebehnúť na viacerých stĺpcoch UNION príkazu, zavarili ste si neraz viac ako kvadratickú zložitosť a to pri desiatkach miliónov položiek naozaj zabolí. Hoci mnohé ženy sa budú hnevať, ale pri veľkých dátach je bordelársky UNION ALL tou správnou voľbou. Ak aj potrebujete upratať je vhodnejšie použiť ďalšiu úroveň pomocou konštrukcie:
SELECT xxx,yyy FROM (SELECT xxx, yyy FROM A_table UNION ALL SELECT yyy, xxx FROM A_table ) GROUP BY xxx,yyy.
Aj vľavo, nielen vo vnútri …
Aj tí najväčší SQL začiatočníci vedia, že pri spájaní tabuliek v rámci SQL je možné použiť nielen INNER JOIN , ale aj LEFT JOIN alebo RIGHT JOIN (o používaní FULL OUTER JOINu sa už väčšinou nezmienia, ale to teraz dajme bokom). Čo však z rukáva už nevytiahne tak veľa ľudí, je úloha LEFT/RIGHT JOINu pri filtrácii údajov.
Predstavte si príklad, kde v Tabuľke CCC máte určitú podmnožinu klientov (napr. z vášho rodného mesta) a v Tabuľke OOO máte všetky objednávky realizované klientmi za celú históriu. Vašou úlohou je dodať zoznam klientov, ktorí si niečo objednali za posledné dva týždne. Väčšina ľudí by k riešeniu pristúpila tak, že by si najoinovala obe tabuľky (teda CCC INNER JOIN OOO on CCC.client = 000.client) a potom filtrovala pomocou WHERE polia s dátumom objednávky tak, aby boli len za posledné dva týždne. Na prvý pohľad správne riešenie. Čo ak však zoznam klientov v CCC tabuľke je len veľmi malou podmnožinou všetkých klientov, čo si niečo objednali? A čo ak časový interval, ktorý vás zaujíma (2 týždne) je len zlomok histórie zaznamenanej v tabuľke OOO?
WHERE je dobrý “kamarát“ len v situácií, kde očakávate približne rovnaké početnosti na oboch stranách. Pokiaľ už pri písaní query je jasné, že jedna zo strán bude rádovo menšia ako druhá, je výhodnejšie používať na filtrovanie priamo JOIN. Pokiaľ viem, ktorá tabuľka je z hľadiska distinct hodnôt zaručene menšia oplatí sa LEFT JOINovať na túto tabuľku. Aj navyše viete, že z niektorej tabuľky použijete len zlomok z jej celkových dát, oplatí sa toto vymedzenie zapísať priamo do ON časti joinu. V našom prípade teda:
CCC LEFT JOIN OOO on CCC.client = OOO.client AND OOO.datum_objednavky >= CURRENT_DATE – 14.
Tým podstatným rozdielom oproti INNER JOIN + WHERE riešeniu je, že zaprasíte výrazne menej operačnej pamäte a tým umožníte rýchlejší beh (nielen) vašim výpočtom. Pokročilejší užívatelia by mohli namietať, že ak použijem dodatočnú podmienku v ON časti, tak INNER JOIN môže byť rovnako rýchly ako LEFT JOIN. Áno, niektoré implementácie SQLka skutočne testujú vzájomné veľkosti tabuliek ešte pred JOINom a snažia sa správať LEFT JOINovo voči malej tabuľke aj pri INNER JOINe. Toto čaro sa však vytratí, keď potrebujete takto spojiť 3, 4 či dokonca viac tabuliek, kde vzájomné kombinácie relatívnych dĺžok tabuliek musia byť otestované n*(n-1)/2 porovnaniami a neraz sú joiny skladané sekvenčne ((A INNER JOIN B) INNER JOIN C) INNER JOIN D … ). Preto ak viete query pomôcť s performance treba sa vedieť pozerať aj doľava (LEFT), nielen dovnútra (INNER).
Občas treba mať okno…
V prítmí bežných funkcií SQL, stojí pre mnohých málo známa oblasť WINDOW functions. Ide o agregácie podobné tým, aké dokážete urobiť pomocou GROUP BY, ale bez potreby zgrupenia riadkov. V tomto ohľade to pripomína .groupby metódu z Python Pandas syntaxu. Použitie GROUP BY je však v takýchto prípadoch závažnou (minimálne performance) chybou.
Zásadnou výhodou WINDOW functions je, že dokážete v tej istej query zgrupiť dáta aj podľa rozličných dimenzií súčasne. Užitočnou stránkou WINDOW functions je, že dokážete počítať priamo aj kumulatívny súčet alebo vývoj aritmetického priemeru s pribúdajúcimi dátami. (v takom prípade sa používa klauzula OVER () iba s ORDER BY, bez použitia PARTIOTION BY. Niektoré dialekty SQL dokonca pomocou WINDOW functions umožňujú aj dávať do súvisu dva rozličné riadky, pomocou klauzúl LAG alebo LEAD (ich použitie vysvetlené tu), čo je špeciálne praktické pre výpočet kĺzavého priemeru alebo výpočet ročnej, či medzimesačnej zmeny určitých hodnôt. Samostatnou kapitolou WINDOW functions je spriahnutie OVER() s funkciou ROW_NUMBER(), čo umožňuje vytvárať si ľubovoľné vlastné označovanie riadkov. V zložitejších analýzach sa bez window funkcií neobídete, ich alternatívy cez kombinácie rôznych GROUP BY sú výpočtovo neúnosné. Preto sa celkom oplatí mať občas okno. Aspoň v SQLku …
Čítaj ďalej aj o:
Čistenie email adries v SQL
Kam ďalej po SQLku?
Autodetekcia anomálií vo Vašich tabuľkách
Publikované dňa 17. 11. 2018.
Späť na domovskú stránku
