SQL dotazy pre analýzu dát

V predchádzajúcom článku SQL a základy práce s dátami sme si vysvetlili základné príkazy ako SELECT, WHERE, GROUP BY a ORDER BY. Dnes nadviažeme na tieto základy a posunieme sa k pokročilejšej úrovni práce s databázami.

Tento článok je druhou časťou série o SQL pre dátových analytikov a zameriava sa na techniky, ktoré sa používajú v reálnom analytickom prostredí. Príklady, ktoré uvádzam v článku sú ilustračné, no vychádzajú z typických situácií, s ktorými sa analytik stretáva pri práci s relačnými databázami.

Prečo samotné základy SQL nestačia?

Základné príkazy sú nevyhnutné, ale profesionálna analytická práca začína tam, kde analytik spája viac tabuliek, počíta trendy v čase, analyzuje správanie zákazníkov, pripravuje dátové sady pre Python alebo BI nástroje a optimalizuje výkon dotazov nad miliónmi riadkov.

Dôležité je teda uvedomiť si, že analytik málokedy pracuje s jednou tabuľkou. Dáta bývajú rozdelené do viacerých častí (zákazníci, objednávky, produkty, platby) a ich správne prepojenie je základom korektnej analýzy.

1/ Spájanie tabuliek cez JOIN (JOIN = INNER JOIN)

INNER JOIN je v dátovej analytike najpoužívanejší typ spojenia, pretože pracuje iba s relevantnými a úplnými záznamami. V praxi to znamená, že sa analyzujú len tie objednávky, ktoré majú existujúceho zákazníka, produkt alebo inú nadväzujúcu položku. To je dôležité najmä pri výpočtoch tržieb, počte objednávok alebo pri modelovaní správania zákazníkov, kde chýbajúce väzby môžu skresliť výsledky.

INNER JOIN nie je síce vždy ten najrýchlejší typ JOINu, ale najčastejšie sa používa, pretože pracuje len s relevantnými záznamami. Výkon závisí najmä od indexov a veľkosti dát.

Kedy INNER JOIN nepoužiť?

Nemal by sa použiť v prípade, keď treba vidieť aj neúplné alebo chýbajúce údaje. Typickým príkladom je analýza zákazníkov, ktorí ešte neurobili žiadnu objednávku, alebo produktov, ktoré sa ešte nepredávali. V takýchto prípadoch je vhodnejšie použiť LEFT JOIN, ktorý zachová všetky záznamy z hlavnej tabuľky a umožní identifikovať chýbajúce dáta (NULL hodnoty).

Aké sú najčastejšie chyby začiatočníkov pri použití JOINu?

Jednou z najčastejších chýb je nepochopenie typu vzťahov medzi tabuľkami (1:1, 1:N, N:M). Ak sa spoja tabuľky bez znalosti týchto vzťahov, môže dôjsť k násobeniu riadkov, čo spôsobí nesprávne súčty a skreslené výsledky. Ďalšou častou chybou je spájanie tabuliek na nesprávne stĺpce, absencia indexov pri veľkých tabuľkách alebo používanie JOINu bez kontroly, prípadne ignorovanie duplicít v tabuľkách.

Preto je vždy dobrým zvykom pred samotným JOINom skontrolovať počet záznamov a unikátnosť kľúčov.

Príklad tabuliek:

Pre spojenie tabuliek sa použije INNER JOIN.
SELECT c.name, o.price, o.date
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

Pre zobrazenie aj zákazníkov, ale bez objednávok by to zase vyzeralo takto.
SELECT c.name, o.price
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

2/ Analýza trendov v čase

Časová analýza patrí medzi časté úlohy dátového analytika a zápis kódu by vyzeral nasledovne.
SELECT date, SUM(price) AS trzby
FROM orders
GROUP BY date
ORDER BY date;

3/ Analytické funkcie

V tomto prípade ide o funkcie, ktoré sú extrémne dôležité pre analytikov. Ako príklad by som uviedol zápis pre kumulatívne tržby a následne zápis pre zobrazenie poradia objednávok.

Rast tržieb v čase
SELECT
date,
SUM(price) OVER (ORDER BY date) AS kumulativne_trzby
FROM orders;

Poradie nákupov každého zákazníka podľa ceny
SELECT
customer_id,
price,
RANK() OVER (PARTITION BY customer_id ORDER BY price DESC) AS poradie
FROM orders;

Týmto spôsobom je možné sledovať kumulatívne tržby denne alebo poradie nákupov zákazníkov a to je celkom užitočné pri identifikácii top zákazníkov alebo najpredávanejších produktov.

4/ Poddotazy (angl. subqueries)

V jazyku SQL ide o vnorené dotazy, ktoré sú vložené do iného SQL dotazu a používajú sa na to, aby jeden dotaz pomohol s výsledkom iného dotazu. Napríklad ak by sme chceli nájsť objednávky, ktoré majú cenu vyššiu než je priemer, potom zápis kódu by bol:
SELECT *
FROM orders
WHERE price > (
SELECT AVG(price)
FROM orders
);

5/ Príprava dátovej sady pre analýzu

Tu je zase príklad na to, ako vzniká dátová sada pripravená na ďalšie spracovanie s použitím jazyku Python alebo nástroja Power BI.

WITH data AS (
SELECT
c.name,
o.date,
o.price
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
)
SELECT
name,
SUM(price) AS total_spent
FROM data
GROUP BY name
ORDER BY total_spent DESC;

 

Štandardne sa často možno stretnúť s tým, že analytik exportuje príliš veľké množstvo dát do Excelu alebo Pythonu, hoci väčšinu výpočtov je možné efektívne spraviť priamo v databáze. Správne napísaný SQL dotaz dokáže z miliónov riadkov vytvoriť kompaktnú dátovú sadu pripravenú na ďalšiu analýzu bez zbytočného zaťaženia systému.

Prečo je pokročilé SQL dôležité?

Pokročilé SQL dotazy umožňujú redukovať množstvo dát ešte pred ich spracovaním v jazyku Python, minimalizovať prenos dát medzi databázou a analytickým nástrojom, zvýšiť výkon analýzy a zabezpečiť presnosť výpočtov.

Pri práci s veľkými dátami sa často mnohé analýzy vykonajú priamo v databáze. Databázové servery sú optimalizované na prácu s veľkými objemami dát a správne napísaný SQL dotaz dokáže výrazne zrýchliť celý analytický proces.

 

Pokročilé SQL dotazy predstavujú teda nevyhnutný krok pre každého, kto chce začať pracovať s dátami profesionálne. JOIN, analytické funkcie, alebo poddotazy umožnia spracovať komplexné dátové modely a pripravovať kvalitné vstupy pre ďalšiu analýzu.

Tento článok nadväzuje na úvod do SQL a je súčasťou série zameranej na praktické využitie SQL v rámci dátovej analytiky. V ďalšej časti sa budeme zaoberať optimalizáciou výkonu dotazov a práci s indexami podrobnejšie.

---

Zaujal Vás článok?  

Zvážte možnosť jeho zdieľania na sociálnych sieťach alebo sa podeľte o svoj názor a pripomienky k článku a to odoslaním správy na e-mailovú adresu info@dataspark.sk.