SQL-lekérdezést írnál, de nem tudod, hogyan kezdj neki?

Ma már elképzelhetetlen bármilyen programozási feladat az SQL nyelv alapjainak ismerete nélkül. Számtalanszor szükség van arra, hogy különféle, nagy mennyiségű adatot tároljunk, kezeljünk. Mielőtt azonban elmerülnénk a kódírásban, van néhány lépés, amivel időt és energiát spórolhatunk magunknak.

Adatbázis-kapcsolatok meghatározása

Az adatbázisok különbözőképpen épülnek fel:

• a struktúra,
• a táblák, adatok, oszlopok száma,
• a kapcsolatok és kulcsok,
• és a benne tárolt információk relevanciája alapján.

Mielőtt nekiállnánk SQL-lekérdezéseket írni és elmerülnénk az eredmények elemzésében, először meg kell ismernünk és értenünk az adatokat és a sémákat. Van, hogy ez az infó rendelkezésre áll, és van, hogy nekünk kell meghatározni. 

# Vizsgáljuk meg, mely táblák relevánsak. 

Nagyon gyakran előfordulhat, hogy az adatbázis történeti adatokat, archívumokat és helyi másolatokat is tartalmaz. Ezekre az információkra általában nincs szükség, ezért érdemes rögtön az elején kiemelni, hogy később se zavarjon az elemzésben.

# Különítsük el az adatszótárakat. 

A szótárak általában 2-4 mezőt tartalmaznak a megfelelő kulcsokkal. Ezek ugyan a teljes adatbázishoz képest kis táblázatok, azonban nagyon sok lehet belőlük. Ha a szótárakat egy külön poolba különítjük, azzal időt spórolunk, hiszen lecsökkentjük az elemzésbe bevont táblák számát.

# Keressen olyan táblákat, amelyek entitásokat vagy naplókat írnak le (pl.: felhasználók és viselkedésük). 

Ez lesz a fő adatállomány. Az ilyen táblákban rengeteg információ és oszlop található, az adatok pedig folyamatosan frissülnek.

A fenti három lépés után megmaradt táblák azok, amelyek tényleges rendszer- vagy műszaki adatokat tartalmaznak, vagy ahol az adatrögzítés logikája nem világos. Ezekre valószínűleg ritkán lesz szükségünk, ezért nem szükséges az elemzés elején részletesen foglalkozni velük. 

Tipp! Minden következtetést, szűrést, eredményt írjunk bele a dokumentációba. Amikor ténylegesen elkezdünk dolgozni az adatbázissal, már csak a táblázatok egy kis halmazát kell átnéznünk.

Miután meghatároztuk a táblázatok és adatok fő halmazát, elkezdhetünk dolgozni velük. Egy ilyen halmaz általában az alap adatbázis körülbelül 50%-át teszi ki. Az egyes táblázatokat érdemes külön-külön átnézni, különböző lekérdezéseket futtatni rajtuk, hogy lássuk, milyen adatokat tartalmaznak. 

Képzések ebben a témában:

SQL-programozás az alapoktól

Ákos Németh

Data Intelligence Solution Architect @ 1stQuad Solutions

A képzésről

Keressünk választ az alábbi kérdésekre:

• Milyen időszak adatait tartalmazza a táblázat?
• Milyen azonosítókkal rendelkezik (ids mezők)?
• Milyen eseményeket tartalmaz a táblázat?
• Vannak-e benne entitások*? Ha igen, próbáljuk meg összekapcsolni őket az attribútumaik szerint (pl.: ellenőrzött és nem ellenőrzött felhasználók, törölt és nem törölt felhasználók).

*Az entitások összetartozó elemi adatokat fognak össze. Például egy karakterlánc önmagában nem entitás, de a gépkocsi, amelynek márkája, típusa és rendszáma van, már igen.

Ha sok azonosítót (ids) találunk, próbáljunk meg adatszótárt találni hozzájuk. Érdemes megnézni az azonosító előtti mező nevét, és keresni egy hasonló nevű táblát. Ha mondjuk egy táblában a user_id kulcs szerepel, próbáljunk meg olyan táblákat keresni, amelyek címében a „users” megtalálható.

Azt is érdemes feljegyezni, ha egy táblában nem minden oszlop van kitöltve. Ilyen esetben ezekre az oszlopokra nem kell hivatkozni, hisz a keresett információ nincs ott. A lekérdezés előtt ellenőrizni kell, hogy van-e átirat az adatszótárban.

Amennyiben megvagyunk az adatok ellenőrzésével, nekiállhatunk az SQL-lekérdezések létrehozásának. Legtöbbször az elemzéshez nincs szükség az összes lekérdezendő adat listájára, mivel azonban az SQL-lekérdezés egy sor mérőszámot és mutatót fog kidobni, ezért van néhány dolog, amit előzetesen át kell gondolni.

• Határozzuk meg az oszlopok listáját, a kérdés megválaszolását segítő metrikákat és entitásokat. Arra most nem kell figyelni, hogy ezek az adatok rendelkezésre állnak-e és az sem baj, ha csak számításokkal kapjuk meg. Viszont, ha a metrika kiszámításához szükséges adat nem áll rendelkezésre az adatbázisban, akkor azt ne vegyük fel az eredményhalmazba. 
• A metrika neve utáni oszlopba írjuk be a kiszámításához szükséges képletet is. Ennek az adatbázis tényleges oszlopain és adatain kell alapulnia.
• Adjuk meg a képlet kiszámításához szükséges táblázatok listáját. Ha az oszlop egy entitás, adjunk meg egy táblázatot, ahonnan az adatokat venni kell.
• A séma tetején írjuk le a mintára vonatkozó feltételeket: időtartomány, ország, eszköztípus vagy operációs rendszer szerinti korlátozások. Ezzel zárjuk ki a lekérdezés megírásakor az elemzés szempontjából érdektelen adatokat.

Ha a séma készen van, tegyük fel magunknak az alábbi kérdéseket:

• Hány táblát érint a lekérdezés?
• Lehetséges-e, hogy duplikálódnak az adatok a táblák összekapcsolásakor?
• Elkerülhető a duplikáció distinct operátor használatával, vagy allekérdezéseket/CTE-ket (Common Table Expressions) kell használni?
• Mennyi információt kérünk le az adatbázisból? Csökkenthető a mennyiség? Vannak extra korlátozások?
• Mely adatok legyenek lekérdezhetők? A szótárban szereplő összes entitást figyelembe kell venni, vagy éppen ellenkezőleg, az összes tevékenységre vonatkozó adatokat akarjuk lekérdezni (még akkor is, ha azok átiratai nem szerepelnek a szótárakban)? 

Nézzük meg, hogy is néznek ki a lekérdezés létrehozása előtti jegyzetek egy új adatbázis esetén:

SQL-lekérdezés írása és tesztelése

Az SQL-lekérdezések esetén az eredményeket mindenképp ellenőrizzük szúrópróbaszerűen egy teszteseten. Előfordulhatnak olyan hibák, amelyek csak így lesznek felfedezhetők. Vegyünk tehát egyetlen felhasználót vagy eseményt, és ellenőrizzük az eredményt egy egyszerű SELECT + WHERE szekvenciális halmazzal. Az ellenőrzéskor ne használjunk összetett számításokat, ahol esetleg szintén hibát véthetünk (ablakfüggvények, having, korreláló alkérdések). Az egyszerű select szűrő használata tökéletesen elég. Ha ezen végigmentünk, és ugyanazt az eredményt kaptuk, mint a megírt lekérdezéssel, akkor helyesen írtuk meg. 

Ha az ellenőrzés után az eredmények viszont nem egyeznek, kezdjük el keresni a hibát a megírt lekérdezésben. Ezt akár fokozatosan is csinálhatjuk, minden egyes lépést tesztelhetünk egy tesztadathalmazon, egy mesterséges szűrőn, amely eredményül 1-2 sort ad. Ha megvan a hiba, írjuk újra, majd végezzünk újabb tesztet, amíg a tesztadatok és a lekérdezés eredménye meg nem egyezik.

Ezután érdemes elmenteni a lekérdezést, egy az általa megoldott problémára mutató hivatkozással. Beilleszthetünk egy megjegyzést, vagy csatolhatjuk egy sémához, így legközelebb, amikor szükségünk lesz rá, nem kell újra megírni.

Így kezeljük az összetett SQL-lekérdezéseket

• Részenként ellenőrizzük, hogy minden helyesen működik – sokkal könnyebb szakaszonként észrevenni és javítani a hibát, mint a folyamat legvégén.
• Ne írjunk túl sok egymásba ágyazott allekérdezést. Az ilyen kódot néhány nap múlva még mi magunk is nehezen tudjuk majd értelmezni. A CTE-ben igyekezzünk a logikai részeket hangsúlyozni.
• Lehetőleg ne keverjük a CTE-t és háromnál több egymásba ágyazott elemből álló allekérdezést. Egyszerre csak egy dolgot használjunk a kódban, tegyük az összetett részeket egy CTE-be vagy írjunk nagy beágyazottságú allekérdezéseket.
• Használjuk szűrőket már a kezdetektől. Minél szűrtebb a minta, annál kevesebb adattal kell dolgozni a későbbi műveletek során.
• Csak jó okkal nevezzünk át oszlopokat vagy táblákat. Nem éri meg átnevezni egy táblát csak azért, hogy 2 karakterrel rövidebb legyen. Szükség esetén használjunk alias elnevezéseket, ha egy táblázat neve túl hosszú. Fontos viszont, hogy a tábla első betűjével írt alias például akkor alkalmazható, ha mindenki tudja, hogy az S az értékesítési táblát jelenti. Ha vonatkozhat a sales_profit, sales_roi vagy sales_partners táblákra is, akkor jobb, ha nem nyúlunk az elnevezéshez.
• Használjunk megjegyzéseket.
• Ne feledkezzünk meg a formázásról. Enélkül olvashatatlan lesz a szkriptünk, és félreértésekhez is vezethet a csapaton belül. Használjunk tabulátort, behúzásokat és ne írjunk mindent egybefüggő szövegként.
• Különítsük el az ismétlődő részeket, így azok többször is használhatók lesznek anélkül, hogy újra kellene írni őket. 

Végezetül, mint sok már területen, itt is érdemes az eredményeket egészséges szkepticizmussal kezelni. Úgy tűnik, minden adat elérhető? Nincsenek rendellenességek? Csupán a működő lekérdezés nem garantálja, hogy az adatokban nincs hiba – ezek az utólagos elemzéssel szűrhetők ki.