A Beginner’s Guide to the True Order of SQL Operations

Az SQL nyelv nagyon intuitív. Egészen addig, amíg nem az.

Az évek során sokan kritizálták az SQL nyelvet különböző okokból. Például: Az IDE-k nem tudják könnyen kitalálni, hogy milyen automatikus kitöltési lehetőségeket kínáljanak, mert amíg nem adjuk meg a FROM záradékot, addig (még) nincsenek táblázatok a hatókörben:

-- Don't you wish this would be completed to first_name?SELECT first_na...-- Aaah, now it works:SELECT first_na...FROM customer

Ezek a dolgok azért furcsák, mert a műveletek lexikai sorrendje nem egyezik a műveletek logikai sorrendjével. Mi emberek néha (gyakran) intuitíve megérthetjük ezt a sorrendi különbséget. Pl. tudjuk, hogy az ügyfél táblából akarunk szelektálni. De az IDE ezt nem tudja.

GROUP BY járul hozzá a legnagyobb zűrzavarhoz

Amikor egy junior fejlesztő / SQL kezdő elkezd SQL-el dolgozni, elég hamar rájön az aggregációra és GROUP BY. És gyorsan írni fognak olyan dolgokat, mint:

SELECT count(*)FROM customer

Jó, 200 ügyfelünk van!

És aztán:

SELECT count(*)FROM customerWHERE first_name = 'Steve'

Váó, közülük 90-et Steve-nek hívnak! Érdekes. Nézzük meg, hányan vannak egy névre vetítve…

SELECT first_name, count(*)FROM customerGROUP BY first_name

Ahaa!

FIRST_NAME COUNT------------------Steve 90Jane 80Joe 20Janet 10

Nagyon szép. De vajon mind egyforma? Nézzük meg a vezetéknevet is

SELECT first_name, last_name, count(*)FROM customerGROUP BY first_name

Oops!

ORA-00979: not a GROUP BY expression

Jej, mit jelent ez? (megjegyzés, sajnos a MySQL felhasználók, akik nem használják a STRICT módot, itt is tetszőleges vezetéknevekkel kapnak eredményt!, tehát egy új MySQL felhasználó nem fogja érteni a hibájukat)

Hogyan lehet ezt egy SQL kezdőnek könnyen elmagyarázni? A “profik” számára egyértelműnek tűnik, de tényleg egyértelmű? Elég nyilvánvaló ahhoz, hogy egy kezdőnek könnyen el tudd magyarázni? Gondolj csak bele. Miért szemantikailag helyes vagy helytelen az egyes kijelentések mindegyike?

A probléma a szintaxissal kapcsolatos

Az SQL szintaxis hasonlóan működik, mint az angol nyelv. Ez egy parancs. A parancsokat igékkel kezdjük. Az ige SELECT (vagy INSERT, UPDATE, DELETE, CREATE, DROP, stb. stb. stb.)

Az emberi nyelv sajnos hihetetlenül alkalmatlan a programozás sokkal formálisabb világára. Bár némi vigaszt nyújt az új felhasználóknak (esetleg nem programozóknak), akik abszolút kezdők, de mindenki másnak csak megnehezíti a dolgát. A különböző SQL-klauzulák rendkívül összetett függőségi viszonyban állnak egymással. Például:

• A GROUP BY záradék jelenlétében a HAVING, SELECT és ORDER BY záradékokban csak GROUP BY kifejezésekből (vagy azok funkcionális függőségeiből) felépített kifejezések, illetve aggregált függvények használhatók.
• Az egyszerűség kedvéért ne is beszéljünk a GROUPING SETS
• Sőt, van néhány olyan eset is, amikor a GROUP BY implikált. Pl. ha “csupasz” HAVING záradékot írunk
• Egyetlen aggregáló függvény a SELECT záradékban (GROUP BY hiányában) egyetlen sorba való aggregálást kényszerít ki
• Sőt, ezt implikálhatjuk azzal is, ha ezt az aggregáló függvényt ORDER BY-ba tesszük (bármilyen okból)
• Meglehet ORDER BY elég sok olyan kifejezés, amely SELECT nélkül hivatkozik a FROM záradék bármely oszlopára. De ez már nem igaz, ha azt írja SELECT DISTINCT
• …

A lista végtelen. Ha érdekel, elolvashatod az SQL szabványdokumentumokat, és megnézheted, mennyi furcsa és bonyolult kölcsönös függőség van a SELECT utasítás számos záradéka között.

Meg lehet ezt valaha is érteni?

Szerencsére igen! Van egy egyszerű trükk, amit mindig elmagyarázok az SQL mesterkurzusomat látogató küldötteknek. Az SQL műveletek (záradékok) lexikai (szintaktikai) sorrendje egyáltalán nem felel meg a műveletek logikai sorrendjének (bár néha véletlenül mégis). A modern optimalizálóknak köszönhetően a sorrend szintén nem felel meg a műveletek tényleges sorrendjének, így valójában: szintaktikai -> logikai -> tényleges sorrend, de ezt most hagyjuk félre.

A műveletek logikai sorrendje a következő (az “egyszerűség kedvéért” kihagyom az olyan beszállítóspecifikus dolgokat, mint CONNECT BY, MODEL, MATCH_RECOGNIZE, PIVOT, UNPIVOT és az összes többit):

• FROM: Ez valójában az első dolog, ami történik, logikailag. Minden más előtt betöltjük az összes sort az összes táblából, és összekapcsoljuk őket. Mielőtt sikítanál és dühbe gurulnál: Ismétlem, ez történik először logikailag, nem ténylegesen. Az optimalizáló nagyon valószínű, hogy nem ezt a műveletet fogja először elvégezni, az butaság lenne, hanem a WHERE záradék alapján hozzáfér valamilyen indexhez. De ismétlem, logikailag ez történik először. Továbbá: az összes JOIN záradék valójában ennek a FROM záradéknak a része. A JOIN egy operátor a relációs algebrában. Ugyanúgy, mint ahogy az + és - operátorok az aritmetikában. Ez nem egy önálló záradék, mint a SELECT vagy a FROM
• WHERE: Miután betöltöttük az összes sort a fenti táblázatokból, most újra kidobhatjuk őket a WHERE
• GROUP BY használatával: Ha akarjuk, akkor a WHERE után megmaradt sorokat foghatjuk, és csoportokba vagy vödrökbe rakhatjuk őket, ahol minden csoport tartalmazza a GROUP BY kifejezés azonos értékét (és az összes többi sor az adott csoporthoz tartozó listába kerül). Java nyelven valami ilyesmit kapnánk: Map<String, List<Row>>. Ha mégis megadja a GROUP BY záradékot, akkor a tényleges sorok csak a csoport oszlopait tartalmazzák, a többi oszlopot már nem, amelyek most már ebben a listában vannak. A listában lévő oszlopokat csak azok az aggregáló függvények láthatják, amelyek képesek a listával operálni. Lásd alább:
• aggregációk: Ezt fontos megérteni. Nem számít, hogy szintaktikailag hová helyezi az aggregáló függvényt (azaz a SELECT záradékba vagy a ORDER BY záradékba), ez itt az a lépés, ahol az aggregáló függvények kiszámításra kerülnek. Közvetlenül a GROUP BY után. (ne feledjük: logikailag. Az okos adatbázisok valójában már korábban is kiszámíthatták őket). Ez megmagyarázza, hogy miért nem lehet az WHERE záradékba aggregált függvényt tenni, mert annak értékéhez még nem lehet hozzáférni. A WHERE záradék logikailag az aggregációs lépés előtt történik. Az aggregáló függvények elérhetik azokat az oszlopokat, amelyeket a fentiekben az egyes csoportok “this list”-jába tettél. Az aggregálás után a “this list” eltűnik, és többé nem lesz elérhető. Ha nincs GROUP BY záradék, akkor csak egy nagy csoport marad kulcs nélkül, amely az összes sort tartalmazza.
• HAVING: … de most már hozzáférhet az aggregációs függvények értékeihez. Például ellenőrizheti, hogy count(*) > 1 a HAVING záradékban HAVING. Mivel a HAVING a GROUP BY után van (vagy feltételezi a GROUP BY-ot), már nem tudunk hozzáférni olyan oszlopokhoz vagy kifejezésekhez, amelyek nem voltak GROUP BY oszlopok.
• WINDOW: Ha a fantasztikus ablakfüggvény funkciót használja, ez az a lépés, ahol az összes kiszámításra kerül. Csak most. És az a menő, hogy mivel már kiszámítottuk (logikailag!) az összes aggregált függvényt, az aggregált függvényeket ablakfüggvényekbe fészkelhetjük. Így teljesen rendben van, ha olyan dolgokat írunk, mint sum(count(*)) OVER () vagy row_number() OVER (ORDER BY count(*)). Az, hogy az ablakfüggvények csak most számítódnak ki logikailag, megmagyarázza azt is, hogy miért csak a SELECT vagy ORDER BY záradékokba tehetjük őket. A WHERE záradékhoz nem állnak rendelkezésre, ami korábban megtörtént. Vegyük észre, hogy a PostgreSQL és a Sybase SQL Anywhere rendelkezik tényleges WINDOW záradékkal!
• SELECT: Végre. Most már felhasználhatjuk a fenti záradékok által előállított összes sort, és a SELECT használatával új sorokat/tuplikat hozhatunk létre belőlük. Hozzáférhetünk az összes kiszámított ablakfüggvényhez, az összes kiszámított aggregált függvényhez, az összes megadott csoportosító oszlophoz, vagy ha nem csoportosítottunk/aggregáltunk, akkor használhatjuk az összes oszlopot a FROM záradékunkból. Ne feledje! Még ha úgy is tűnik, mintha a SELECT belsejében aggregálnánk dolgokat, ez már régen megtörtént, és az édes-édes count(*) függvény nem más, mint egy hivatkozás az eredményre.
• DISTINCT: Igen! A DISTINCT a SELECT után történik, még akkor is, ha szintaxisilag a SELECT oszloplista elé kerül. De gondolj bele! Ennek tökéletes értelme van. Hogyan tudnánk különálló sorokat eltávolítani, ha még nem ismerjük az összes sort (és azok oszlopait)?
• UNION, INTERSECT, EXCEPT: Ez nem is kérdéses. A UNION egy olyan operátor, amely két részkérdést kapcsol össze. Minden, amiről eddig beszéltünk, alkérdés volt. Az egyesítés kimenete egy új lekérdezés, amely ugyanazokat a sortípusokat (azaz ugyanazokat az oszlopokat) tartalmazza, mint az első alkérdés. Általában. Mert a wacko Oracle-ben az utolsó előtti alkérdés a megfelelő az oszlopnév meghatározására. Oracle adatbázis, a szintaktikai troll 😉
• ORDER BY: Teljesen logikus az eredmény rendezésének eldöntését a végére halasztani, mert minden más művelet használhat hashmapset, belsőleg, így minden közbenső rendezés újra elveszhet. Tehát most már rendezhetjük az eredményt. Normális esetben a ORDER BY záradékból rengeteg sorhoz hozzáférhetünk, köztük olyan sorokhoz (vagy kifejezésekhez) is, amelyeket nem SELECT. De ha korábban DISTINCT-t adtunk meg, akkor már nem tudunk rendezni olyan sorok/kifejezések szerint, amelyek nem voltak kijelölve. Miért? Mert a sorrend eléggé meghatározhatatlan lenne.
• OFFSET:
• LIMIT, FETCH, TOP: Most az épeszű adatbázisok a LIMIT (MySQL, PostgreSQL) vagy FETCH (DB2, Oracle 12c, SQL Server 2012) záradékot a legvégére teszik, szintaktikailag. A régi időkben a Sybase és az SQL Server úgy gondolta, hogy a SELECT kulcsszóként TOP. Mintha a SELECT DISTINCT helyes sorrendje nem lenne már így is elég zavaró.

Tessék, megvan. Teljesen érthető. És ha valaha is olyasmit akarsz csinálni, ami nem a “helyes sorrendben” van, a legegyszerűbb trükk mindig az, hogy származtatott táblázathoz folyamodsz. Pl. amikor egy ablakfüggvényre akarsz csoportosítani:

Miért működik? Mert:

• A származtatott táblázatban először a FROM történik, majd a WINDOW kerül kiszámításra, utána pedig a vödör SELECT.
• A külső SELECT most már úgy kezelheti ennek az ablakfüggvény-számításnak az eredményét, mint bármelyik közönséges táblázatot a FROM záradékban, majd GROUP BY egy közönséges oszlopot, majd aggregál, majd SELECT

Mondjuk újra az eredeti példáinkat azzal, hogy miért működnek vagy miért nem működnek.

Mindig gondoljunk a műveletek logikai sorrendjére

Ha nem gyakran írunk SQL-t, a szintaxis valóban zavaró lehet. Különösen a GROUP BY és az aggregációk “fertőzik” az egész SELECT záradék többi részét, és a dolgok nagyon furcsává válnak. Amikor ezzel a furcsasággal szembesülünk, két lehetőségünk van:

• Dühbe gurulni és üvöltözni az SQL nyelv tervezőivel
• Elvállalni a sorsunkat, becsukni a szemünket, elfelejteni a snytaxot és emlékezni a logikai műveletek sorrendjére

Általában az utóbbit javaslom, mert akkor a dolgoknak sokkal több értelme lesz, beleértve az alábbi gyönyörű kumulatív napi bevétel számítást, amely a napi bevételt (SUM(amount) aggregátum függvény) a kumulatív bevételbe (SUM(...) OVER (...) ablak függvény) fészkeli be:

SELECT payment_date, SUM(SUM(amount)) OVER (ORDER BY payment_date) AS revenueFROM paymentGROUP BY payment_date

… mert az aggregációk logikailag az ablakfüggvények előtt történnek.

Caveat: ORDER BY záradék

A ORDER BY záradék körül van néhány caveat, ami további zavart okozhat. Alapértelmezés szerint továbbra is feltételezi, hogy a műveletek logikai sorrendje helyes. De aztán van néhány speciális eset, különösen:

• A DISTINCT záradék hiányában
• A halmazműveletek hiányában, mint UNION

A ORDER BY-ban hivatkozhatunk olyan kifejezésekre, amelyeket a SELECT nem vetít előre. A következő lekérdezés tökéletesen megfelel a legtöbb adatbázisban:

SELECT first_name, last_nameFROM actorORDER BY actor_id

Ez egy “virtuális” / implicit ACTOR_ID vetítés, mintha azt írtuk volna:

SELECT first_name, last_name, actor_idFROM actorORDER BY actor_id

De aztán a ACTOR_ID oszlopot ismét eltávolítottuk volna az eredményből. Ez nagyon kényelmes, bár némi zavarhoz vezethet a szemantikát és a műveletek sorrendjét illetően. Konkrétan nem használhatod pl. a DISTINCT-t ilyen helyzetben. A következő lekérdezés érvénytelen:

SELECT DISTINCT first_name, last_nameFROM actorORDER BY actor_id -- Oops

Mert mi van akkor, ha két azonos nevű, de nagyon különböző azonosítóval rendelkező szereplő van? A sorrend ekkor meghatározhatatlan lenne.

A halmazműveleteknél még világosabb, hogy miért nem megengedett:

SELECT first_name, last_nameFROM actorUNIONSELECT first_name, last_nameFROM customerORDER BY actor_id -- Oops

Ez esetben a ACTOR_ID oszlop nincs jelen a CUSTOMER táblában, így a lekérdezésnek egyáltalán nincs értelme.

További olvasmány

Még többet szeretne megtudni? Ezeket a cikkeket is elolvashatja:

• SQL GROUP BY és funkcionális függőségek: Egy nagyon hasznos funkció
• Hogyan kellett volna megtervezni az SQL GROUP BY-t – mint a Neo4j implicit GROUP BY-jét
• Hogyan fordítsuk le az SQL GROUP BY-t és az aggregációkat Java 8-ra
• Tényleg érti az SQL GROUP BY és HAVING záradékokat?
• GROUP BY ROLLUP / CUBE