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

Limbajul SQL este foarte intuitiv. Până când nu mai este.

De-a lungul anilor, o mulțime de oameni au criticat limbajul SQL dintr-o varietate de motive. De exemplu: IDE-urile nu pot ghici cu ușurință ce opțiuni de autocompletare să ofere, deoarece atâta timp cât nu specificați clauza FROM, nu există (încă) tabele în domeniul de aplicare:

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

Aceste lucruri sunt ciudate, deoarece ordinea lexicală a operațiilor nu se potrivește cu ordinea logică a operațiilor. Noi, oamenii, putem înțelege uneori (adesea) intuitiv această diferență de ordonare. De exemplu, știm că suntem pe cale să selectăm din tabelul de clienți. Dar IDE-ul nu știe acest lucru.

GROUP BY contribuie la cea mai mare confuzie

Când un dezvoltator junior / începător SQL începe să lucreze cu SQL, destul de repede, va afla despre agregare și GROUP BY. Și vor scrie rapid lucruri de genul:

SELECT count(*)FROM customer

Yay, avem 200 de clienți!

Și apoi:

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

Wow, 90 dintre ei se numesc Steve! Interesant. Hai să aflăm câți avem pe nume…

SELECT first_name, count(*)FROM customerGROUP BY first_name

Ahaa!

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

Frumos. Dar sunt toate la fel? Să verificăm și numele de familie

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

Oops!

ORA-00979: not a GROUP BY expression

Doamne, ce înseamnă asta? (notă, din păcate, utilizatorii MySQL care nu folosesc modul STRICT vor obține în continuare un rezultat aici cu nume de familie arbitrare!, așa că un utilizator nou de MySQL nu va înțelege greșeala lor)

Cum explicați cu ușurință acest lucru unui începător SQL? Pare evident pentru „profesioniști”, dar este cu adevărat evident? Este suficient de evident încât să-l poți explica cu ușurință unui începător? Gândiți-vă la asta. De ce fiecare dintre aceste afirmații sunt corecte sau greșite din punct de vedere semantic?

Problema este legată de sintaxă

Sintaxa SQL funcționează într-un mod similar cu limba engleză. Este o comandă. Noi începem comenzile cu verbe. Verbul este SELECT (sau INSERT, UPDATE, DELETE, CREATE, DROP, etc. etc.)

Din păcate, limbajul uman este incredibil de nepotrivit pentru lumea mult mai formală a programării. Deși oferă o oarecare consolare noilor utilizatori (eventual neprogramatori) care sunt începători absoluți, nu face decât să îngreuneze lucrurile pentru toți ceilalți. Toate diferitele clauze SQL au interdependențe extrem de complexe. De exemplu:

• În prezența unei clauze GROUP BY, numai expresiile construite din expresii GROUP BY (sau dependențe funcționale ale acestora), sau funcțiile agregate pot fi folosite în clauzele HAVING, SELECT și ORDER BY.
• Din motive de simplitate, să nu vorbim nici măcar despre GROUPING SETS
• De fapt, există chiar câteva cazuri în care GROUP BY este implicită. De ex. dacă scrieți o clauză HAVING „goală”
• O singură funcție de agregare în clauza SELECT (în absența lui GROUP BY) va forța agregarea într-un singur rând
• De fapt, acest lucru poate fi implicat și prin plasarea acelei funcții de agregare în ORDER BY (indiferent de motiv)
• Puteți ORDER BY destule expresii care fac referire la orice coloane din clauza FROM fără a le SELECT. Dar acest lucru nu mai este valabil dacă scrieți SELECT DISTINCT
• …

Lista este nesfârșită. Dacă vă interesează, puteți citi documentele standard SQL și să verificați câte interdependențe ciudate și complicate există între numeroasele clauze ale instrucțiunii SELECT.

Poate fi aceasta înțeleasă vreodată?

Din fericire, da! Există un truc simplu, pe care îl explic mereu delegaților care îmi vizitează Masterclass-ul SQL. Ordinea lexicală (sintactică) a operațiilor (clauzelor) SQL nu corespunde deloc cu ordinea logică a operațiilor (deși, uneori, ele coincid întâmplător). Mulțumită optimizatorilor moderni, ordinea nu corespunde nici cu ordinea reală a operațiilor, așa că avem de fapt: ordine sintactică -> logică -> ordine reală, dar să lăsăm asta deoparte pentru moment.

Ordinea logică a operațiilor este următoarea (pentru „simplitate” las la o parte lucrurile specifice furnizorilor, cum ar fi CONNECT BY, MODEL, MATCH_RECOGNIZE, PIVOT, UNPIVOT și toate celelalte):

• FROM: Acesta este de fapt primul lucru care se întâmplă, din punct de vedere logic. Înainte de orice altceva, încărcăm toate rândurile din toate tabelele și le unim. Înainte să țipați și să vă enervați: Din nou, acesta este primul lucru care se întâmplă logic, nu de fapt. Foarte probabil că optimizatorul nu va face mai întâi această operațiune, ar fi o prostie, ci va accesa un index bazat pe clauza WHERE. Dar, din nou, din punct de vedere logic, acest lucru se întâmplă primul. De asemenea: toate clauzele JOIN fac de fapt parte din această clauză FROM. JOIN este un operator în algebra relațională. La fel cum + și - sunt operatori în aritmetică. Nu este o clauză independentă, precum SELECT sau FROM
• WHERE: După ce am încărcat toate rândurile din tabelele de mai sus, acum le putem arunca din nou folosind WHERE
• GROUP BY: Dacă doriți, puteți lua rândurile care rămân după WHERE și să le puneți în grupuri sau găleți, unde fiecare grup conține aceeași valoare pentru expresia GROUP BY (și toate celelalte rânduri sunt puse într-o listă pentru acel grup). În Java, ați obține ceva de genul: Map<String, List<Row>>. Dacă specificați o clauză GROUP BY, atunci rândurile efective conțin doar coloanele grupului, nu și celelalte coloane, care sunt acum în acea listă. Acele coloane din listă sunt vizibile doar pentru funcțiile de agregare care pot opera pe acea listă. A se vedea mai jos.
• agregări: Acest lucru este important de înțeles. Indiferent unde ați plasat funcția de agregare din punct de vedere sintactic (de exemplu, în clauza SELECT sau în clauza ORDER BY), acesta este pasul în care se calculează funcțiile de agregare. Imediat după GROUP BY. (țineți minte: din punct de vedere logic. Este posibil ca bazele de date inteligente să le fi calculat înainte, de fapt). Așa se explică de ce nu puteți pune o funcție agregată în clauza WHERE, pentru că valoarea ei nu poate fi accesată încă. Clauza WHERE are loc, în mod logic, înainte de etapa de agregare. Funcțiile de agregare pot accesa coloanele pe care le-ați pus în „această listă” pentru fiecare grup, mai sus. După agregare, „această listă” va dispărea și nu va mai fi disponibilă. Dacă nu aveți o clauză GROUP BY, va exista doar un singur grup mare, fără nicio cheie, care va conține toate rândurile.
• HAVING: … dar acum puteți accesa valorile funcției de agregare. De exemplu, puteți verifica că count(*) > 1 în clauza HAVING. Deoarece HAVING este după GROUP BY (sau implică GROUP BY), nu mai putem accesa coloane sau expresii care nu au fost coloane GROUP BY.
• WINDOW: Dacă folosiți minunata funcție de funcție de fereastră, acesta este pasul în care sunt calculate toate. Numai acum. Și ceea ce este grozav este că, deoarece am calculat deja (logic!) toate funcțiile agregate, putem anina funcții agregate în funcții fereastră. Astfel, este perfect în regulă să scriem lucruri precum sum(count(*)) OVER () sau row_number() OVER (ORDER BY count(*)). Faptul că funcțiile de fereastră sunt calculate logic abia acum explică și de ce le puteți pune doar în clauzele SELECT sau ORDER BY. Ele nu sunt disponibile pentru clauza WHERE, ceea ce se întâmpla înainte. Rețineți că PostgreSQL și Sybase SQL Anywhere au o clauză WINDOW reală!
• SELECT: În cele din urmă. Acum putem utiliza toate rândurile care sunt produse de clauzele de mai sus și putem crea noi rânduri / tuple din ele folosind SELECT. Putem accesa toate funcțiile de fereastră pe care le-am calculat, toate funcțiile de agregare pe care le-am calculat, toate coloanele de grupare pe care le-am specificat sau, dacă nu am grupat/agregat, putem utiliza toate coloanele din clauza noastră FROM. Rețineți: Chiar dacă pare că agregăm lucruri în interiorul lui SELECT, acest lucru s-a întâmplat cu mult timp în urmă, iar funcția dulce dulce count(*) nu este nimic mai mult decât o referință la rezultat.
• DISTINCT: Da! DISTINCT se întâmplă după SELECT, chiar dacă este pusă înaintea listei de coloane SELECT, din punct de vedere sintactic. Dar gândiți-vă la asta. Este perfect logic. Cum altfel putem elimina rândurile distincte, dacă nu cunoaștem încă toate rândurile (și coloanele lor)?
• UNION, INTERSECT, EXCEPT: Acest lucru este de la sine înțeles. Un UNION este un operator care conectează două subinterogări. Tot ceea ce am discutat până acum a fost o subîntrebare. Rezultatul unei uniuni este o nouă interogare care conține aceleași tipuri de rânduri (adică aceleași coloane) ca și prima subinterogare. De obicei. Pentru că, în Oracle, penultima subinterogare este cea corectă pentru a defini numele coloanei. Baza de date Oracle, trolul sintactic 😉
• ORDER BY: Are sens total să se amâne decizia de ordonare a unui rezultat până la sfârșit, deoarece toate celelalte operații ar putea folosi hashmaps, la nivel intern, astfel încât orice ordine intermediară ar putea fi pierdută din nou. Așadar, acum putem ordona rezultatul. În mod normal, puteți accesa o mulțime de rânduri din clauza ORDER BY, inclusiv rânduri (sau expresii) pe care nu le-ați SELECT. Dar când ați specificat DISTINCT, înainte, nu mai puteți ordona după rândurile/expresiile care nu au fost selectate. De ce? Pentru că ordonarea ar fi destul de nedefinită.
• OFFSET: Nu folosiți offsetul
• LIMIT, FETCH, TOP: Acum, bazele de date sănătoase pun clauza LIMIT (MySQL, PostgreSQL) sau FETCH (DB2, Oracle 12c, SQL Server 2012) chiar la sfârșit, din punct de vedere sintactic. Pe vremuri, Sybase și SQL Server au crezut că ar fi o idee bună să aibă TOP ca un cuvânt cheie în SELECT. De parcă ordonarea corectă a SELECT DISTINCT nu era deja suficient de confuză.

Aici, am ajuns. Are sens în totalitate. Și dacă doriți vreodată să faceți ceva care nu este în „ordinea corectă”, cel mai simplu truc este întotdeauna să recurgeți la un tabel derivat. De exemplu, atunci când doriți să grupați pe o funcție de fereastră:

De ce funcționează? Pentru că:

• În tabelul derivat, FROM se întâmplă mai întâi FROM, apoi se calculează WINDOW, după care găleata este SELECTeditată.
• Cele exterioare SELECT pot trata acum rezultatul calculului acestei funcții de fereastră ca orice tabel obișnuit în clauza FROM, apoi GROUP BY o coloană obișnuită, apoi agregarea, apoi SELECT

Să trecem în revistă exemplele noastre originale cu o explicație de ce funcționează sau de ce nu.

Gândiți-vă întotdeauna la ordinea logică a operațiilor

Dacă nu sunteți un scriitor SQL frecvent, sintaxa poate fi într-adevăr confuză. În special GROUP BY și agregările GROUP BY și agregările „infectează” restul întregii clauze SELECT, iar lucrurile devin foarte ciudate. Atunci când ne confruntăm cu această ciudățenie, avem două opțiuni:

• Să ne enervăm și să țipăm la proiectanții limbajului SQL
• Să ne acceptăm soarta, să închidem ochii, să uităm de snytaxă și să ne amintim ordinea operațiilor logice

În general, recomand cea de-a doua variantă, pentru că atunci lucrurile încep să aibă mult mai mult sens, inclusiv frumosul calcul al veniturilor zilnice cumulate de mai jos, care înglobează veniturile zilnice (SUM(amount) funcția de agregare) în interiorul veniturilor cumulate (SUM(...) OVER (...) funcția fereastră):

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

… pentru că agregările au loc, în mod logic, înaintea funcțiilor de fereastră.

Caveat: clauza ORDER BY

Există câteva caveat-uri în jurul clauzei ORDER BY, care ar putea contribui la o confuzie suplimentară. În mod implicit, continuă să presupună că ordinea logică a operațiilor este corectă. Dar apoi, există câteva cazuri speciale, în special:

• În absența unei clauze DISTINCT
• În absența unor operații de set cum ar fi UNION

Puteți face referire la expresii în ORDER BY, care nu sunt proiectate de SELECT. Următoarea interogare este perfect corectă în majoritatea bazelor de date:

SELECT first_name, last_nameFROM actorORDER BY actor_id

Există o proiecție „virtuală” / implicită ACTOR_ID, ca și cum am fi scris:

SELECT first_name, last_name, actor_idFROM actorORDER BY actor_id

Dar apoi, am eliminat din nou coloana ACTOR_ID din rezultat. Acest lucru este foarte convenabil, deși ar putea duce la unele confuzii cu privire la semantica și ordinea operațiilor. Mai exact, nu puteți utiliza, de exemplu, DISTINCT într-o astfel de situație. Următoarea interogare nu este valabilă:

SELECT DISTINCT first_name, last_nameFROM actorORDER BY actor_id -- Oops

Pentru că, ce se întâmplă dacă există doi actori cu același nume, dar cu ID-uri foarte diferite? Ordinea ar fi acum nedefinită.

În cazul operațiilor cu seturi, este și mai clar de ce nu este permisă:

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

În acest caz, coloana ACTOR_ID nu este prezentă în tabelul CUSTOMER, așa că interogarea nu are niciun sens.

Citește mai departe

Vrei să afli mai multe? Avem, de asemenea, aceste articole pe care le puteți citi:

• SQL GROUP BY și dependențe funcționale: O caracteristică foarte utilă
• Cum ar fi trebuit să fie proiectat SQL GROUP BY – Ca și Neo4j’s Implicit GROUP BY
• Cum să traduci SQL GROUP BY și agregările în Java 8
• Înțelegi cu adevărat clauzele GROUP BY și HAVING din SQL?
• GROUP BY ROLLUP / CUBE

.