En nybörjarguide till den sanna ordningen för SQL-operationer

SQL-språket är mycket intuitivt. Tills det inte är det.

Under årens lopp har många människor kritiserat SQL-språket av olika anledningar. Till exempel: IDE kan inte enkelt gissa vilka alternativ för automatisk komplettering som ska erbjudas, för så länge du inte anger FROM-klausulen finns det inga tabeller inom räckvidden (ännu):

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

De här sakerna är konstiga, eftersom den lexikala ordningen för operationer inte stämmer överens med den logiska ordningen för operationer. Vi människor kan ibland (ofta) intuitivt förstå denna skillnad i ordningsföljd. Vi vet t.ex. att vi ska välja från kundtabellen. Men IDE vet inte detta.

GROUP BY bidrar till mest förvirring

När en juniorutvecklare/ SQL-nybörjare börjar arbeta med SQL kommer de ganska snabbt att få reda på vad aggregering och GROUP BY innebär. Och de kommer snabbt att skriva saker som:

SELECT count(*)FROM customer

Yay, we have 200 customers!

Och sedan:

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

Wow, 90 av dem heter Steve! Intressant. Låt oss ta reda på hur många vi har per namn…

SELECT first_name, count(*)FROM customerGROUP BY first_name

Ahaa!

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

Väldigt bra. Men är de alla likadana? Låt oss kolla efternamnet också

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

Oops!

ORA-00979: not a GROUP BY expression

Jeez, vad betyder det? (OBS, tyvärr kommer MySQL-användare som inte använder STRICT-läget fortfarande att få ett resultat här med godtyckliga efternamn!, så en ny MySQL-användare kommer inte att förstå deras misstag)

Hur förklarar du enkelt detta för en SQL-nybörjare? Det verkar uppenbart för ”proffs”, men är det verkligen uppenbart? Är det tillräckligt uppenbart för att man enkelt ska kunna förklara det för en nybörjare? Tänk på det. Varför är vart och ett av dessa uttalanden semantiskt korrekt eller fel?

Problemet är syntaxrelaterat

SQL-syntaxen fungerar på ett liknande sätt som det engelska språket. Det är ett kommando. Vi börjar kommandon med verb. Verbet är SELECT (eller INSERT, UPDATE, DELETE, CREATE, DROP, etc. etc.)

Tyvärr är det mänskliga språket otroligt dåligt lämpat för den mycket mer formella världen av programmering. Även om det erbjuder en viss tröst för nya användare (eventuellt icke-programmerare) som är absoluta nybörjare, gör det bara saker och ting svåra för alla andra. Alla de olika SQL-klausulerna har extremt komplexa inbördes beroenden. Till exempel:

  • I närvaro av en GROUP BY-klausul kan endast uttryck som är uppbyggda av GROUP BY-uttryck (eller funktionella beroenden av dessa) eller aggregerade funktioner användas i HAVING-, SELECT– och ORDER BY-klausulerna.
  • För enkelhetens skull ska vi inte ens prata om GROUPING SETS
  • Faktiskt finns det till och med ett fåtal fall där GROUP BY är implicit. T.ex. om du skriver en ”naken” HAVING-klausul
  • En enda aggregeringsfunktion i SELECT-klausulen (i avsaknad av GROUP BY) kommer att tvinga fram aggregering till en enda rad
  • I själva verket kan detta också vara implicit genom att sätta den aggregeringsfunktionen i ORDER BY (oavsett anledning)
  • Du kan ORDER BY en hel del uttryck som refererar till någon kolumn från FROM-klausulen utan att SELECTsätta dem. Men det gäller inte längre om du skriver SELECT DISTINCT

Listan är oändlig. Om du är intresserad kan du läsa SQL-standarddokumenten och kontrollera hur många konstiga och komplicerade inbördes beroenden som finns mellan de många klausulerna i SELECT-angivelsen.

Kan detta någonsin förstås?

Tyvärr, ja! Det finns ett enkelt knep, som jag alltid förklarar för de delegater som besöker min SQL Masterclass. Den lexikala (syntaktiska) ordningen för SQL-operationer (klausuler) motsvarar inte alls den logiska ordningen för operationer (även om de ibland gör det av en slump). Tack vare moderna optimerare motsvarar ordningen inte heller den faktiska operationsordningen, så vi har egentligen: syntaktisk -> logisk -> faktisk ordning, men låt oss lämna det åt sidan för tillfället.

Den logiska operationsordningen är följande (för ”enkelhetens skull” utelämnar jag leverantörsspecifika saker som CONNECT BY, MODEL, MATCH_RECOGNIZE, PIVOT, UNPIVOT och alla andra):

  • FROM: Detta är faktiskt det första som händer, logiskt sett. Innan något annat laddar vi alla rader från alla tabeller och sammanfogar dem. Innan du skriker och blir arg: Återigen, detta är vad som händer först logiskt, inte faktiskt. Optimeraren kommer med stor sannolikhet inte att göra den här operationen först, det vore dumt, utan kommer att få tillgång till något index baserat på WHERE-klausulen. Men återigen, logiskt sett är det detta som händer först. Dessutom är alla JOIN-klausuler faktiskt en del av denna FROM-klausul. JOIN är en operatör i relationsalgebra. Precis som + och - är operatörer i aritmetik. Det är inte en oberoende klausul, som SELECT eller FROM
  • WHERE: När vi har laddat alla rader från tabellerna ovan kan vi nu slänga dem igen med hjälp av WHERE
  • GROUP BY: Om du vill kan du ta de rader som återstår efter WHERE och placera dem i grupper eller hinkar, där varje grupp innehåller samma värde för GROUP BY-uttrycket (och alla andra rader placeras i en lista för den gruppen). I Java skulle du få något liknande: Map<String, List<Row>>. Om du anger en GROUP BY-klausul innehåller dina faktiska rader endast gruppkolumnerna, inte längre de återstående kolumnerna, som nu finns i den listan. Kolumnerna i listan är endast synliga för aggregeringsfunktioner som kan bearbeta listan. Se nedan.
  • aggregationer: Detta är viktigt att förstå. Oavsett var du placerar din aggregeringsfunktion syntaktiskt (dvs. i SELECT-klausulen eller i ORDER BY-klausulen) är detta här det steg där aggregeringsfunktionerna beräknas. Precis efter GROUP BY. (Kom ihåg: logiskt. Smarta databaser kan faktiskt ha beräknat dem tidigare). Detta förklarar varför du inte kan sätta en aggregerad funktion i WHERE-klausulen, eftersom dess värde inte kan nås ännu. WHERE-klausulen sker logiskt sett före aggregeringssteget. Aggregeringsfunktioner kan få tillgång till kolumner som du har lagt in i ”denna lista” för varje grupp, ovan. Efter aggregeringen kommer ”denna lista” att försvinna och inte längre vara tillgänglig. Om du inte har en GROUP BY-klausul kommer det bara att finnas en stor grupp utan nyckel som innehåller alla rader.
  • HAVING: … men nu kan du få tillgång till värden för aggregeringsfunktioner. Du kan till exempel kontrollera att count(*) > 1 i HAVING-klausulen. Eftersom HAVING ligger efter GROUP BY (eller innebär GROUP BY) kan vi inte längre komma åt kolumner eller uttryck som inte var GROUP BY-kolumner.
  • WINDOW: Om du använder den fantastiska fönsterfunktionsfunktionen är detta steget där alla beräknas. Endast nu. Och det häftiga är att eftersom vi redan har beräknat (logiskt!) alla aggregerade funktioner kan vi bädda in aggregerade funktioner i fönsterfunktioner. Det är alltså helt okej att skriva saker som sum(count(*)) OVER () eller row_number() OVER (ORDER BY count(*)). Att fönsterfunktioner beräknas logiskt först nu förklarar också varför man bara kan placera dem i klausulerna SELECT eller ORDER BY. De är inte tillgängliga för WHERE-klausulen, vilket skedde tidigare. Observera att PostgreSQL och Sybase SQL Anywhere har en riktig WINDOW-klausul!
  • SELECT: Äntligen. Vi kan nu använda alla rader som produceras från ovanstående klausuler och skapa nya rader/tupler från dem med hjälp av SELECT. Vi kan få tillgång till alla fönsterfunktioner som vi har beräknat, alla aggregeringsfunktioner som vi har beräknat, alla grupperingskolumner som vi har angett, eller om vi inte har grupperat/aggregerat kan vi använda alla kolumner från vår FROM-klausul. Kom ihåg: Även om det ser ut som om vi aggregerar saker inuti SELECT har detta skett för länge sedan, och den söta söta count(*)-funktionen är inget annat än en referens till resultatet.
  • DISTINCT: Ja! DISTINCT händer efter SELECT, även om den är placerad före din SELECT-kolumnlista, syntaxmässigt. Men tänk på det. Det är helt logiskt. Hur kan vi annars ta bort distinkta rader om vi inte känner till alla rader (och deras kolumner) ännu?
  • UNION, INTERSECT, EXCEPT: Detta är en självklarhet. En UNION är en operatör som förbinder två underfrågor. Allt vi har talat om hittills har varit en subquery. Resultatet av en union är en ny fråga som innehåller samma radtyper (dvs. samma kolumner) som den första delfrågan. Vanligtvis. För i det knasiga Oracle är den näst sista underfrågorna den rätta för att definiera kolumnnamnet. Oracle database, the syntactic troll 😉
  • ORDER BY: Det är helt logiskt att skjuta upp beslutet om att ordna ett resultat till slutet, eftersom alla andra operationer kan använda hashmaps, internt, så varje mellanliggande ordning kan gå förlorad igen. Så vi kan nu beställa resultatet. Normalt kan du få tillgång till många rader från ORDER BY-klausulen, inklusive rader (eller uttryck) som du inte SELECT. Men när du specificerade DISTINCT, tidigare, kan du inte längre beställa efter rader/uttryck som inte valdes. Varför inte? Därför att ordningen skulle bli helt odefinierad.
  • OFFSET: Använd inte offset
  • LIMIT, FETCH, TOP: Nu placerar vettiga databaser LIMIT (MySQL, PostgreSQL) eller FETCH (DB2, Oracle 12c, SQL Server 2012) klausulen i slutet, syntaktiskt sett. Förr i tiden tyckte Sybase och SQL Server att det var en bra idé att ha TOP som nyckelord i SELECT. Som om den korrekta ordningen i SELECT DISTINCT inte redan var tillräckligt förvirrande.

Där har vi det. Det är helt logiskt. Och om du någonsin vill göra något som inte är i ”rätt ordning” är det enklaste knepet alltid att ta till en härledd tabell. T.ex. när du vill gruppera på en fönsterfunktion:

Varför fungerar det? Därför att:

  • I den härledda tabellen sker FROM först, sedan beräknas WINDOW och därefter SELECThinken.
  • Den yttre SELECT kan nu behandla resultatet av denna fönsterfunktionsberäkning som vilken vanlig tabell som helst i FROM-klausulen, sedan GROUP BY en vanlig kolumn, sedan aggregera, sedan SELECT

Låt oss gå igenom våra ursprungliga exempel med en förklaring till varför de fungerar eller inte.

Tänk alltid på den logiska ordningen på operationerna

Om du inte ofta skriver SQL kan syntaxen verkligen vara förvirrande. Särskilt GROUP BY och aggregeringar ”smittar” resten av hela SELECTklausulen, och saker och ting blir riktigt konstiga. När vi konfronteras med denna konstighet har vi två alternativ:

  • Var arg och skrik åt SQL-språkets konstruktörer
  • Acceptera vårt öde, blunda, glöm snytax och kom ihåg den logiska operationsordningen

Jag rekommenderar i allmänhet det sistnämnda, för då börjar saker och ting bli mycket vettigare, bland annat den vackra beräkningen av den kumulativa dagliga intäkten nedan, där den dagliga intäkten (SUM(amount) aggregeringsfunktion) nästlas in i den kumulativa intäkten (SUM(...) OVER (...) fönsterfunktion):

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

… eftersom aggregeringar logiskt sett sker före fönsterfunktioner.

Klausul: ORDER BY-klausul

Det finns några förbehåll kring ORDER BY-klausulen, som kan bidra till ytterligare förvirring. Som standard fortsätter man att utgå från att den logiska operationsordningen är korrekt. Men sedan finns det några specialfall, särskilt:

  • I avsaknad av en DISTINCT-klausul
  • I avsaknad av mängdoperationer som UNION

Du kan referera till uttryck i ORDER BY, som inte projiceras av SELECT. Följande fråga fungerar utmärkt i de flesta databaser:

SELECT first_name, last_nameFROM actorORDER BY actor_id

Det finns en ”virtuell” / implicit ACTOR_ID-projektion, som om vi hade skrivit:

SELECT first_name, last_name, actor_idFROM actorORDER BY actor_id

Men sedan tagit bort ACTOR_ID-kolumnen igen från resultatet. Detta är mycket praktiskt, även om det kan leda till viss förvirring om semantiken och operationsordningen. Specifikt kan du inte använda t.ex. DISTINCT i en sådan situation. Följande fråga är ogiltig:

SELECT DISTINCT first_name, last_nameFROM actorORDER BY actor_id -- Oops

Till följd att, vad händer om det finns två aktörer med samma namn men med mycket olika ID? Ordningen skulle nu vara odefinierad.

Med setoperationer är det ännu tydligare varför detta inte är tillåtet:

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

I det här fallet finns inte kolumnen ACTOR_ID i tabellen CUSTOMER, så frågan är meningslös överhuvudtaget.

Vidare läsning

Vill du veta mer? Vi har även dessa artiklar som du kan läsa:

  • SQL GROUP BY och funktionella beroenden: En mycket användbar funktion
  • Hur SQL GROUP BY borde ha utformats – som Neo4js implicita GROUP BY
  • Hur man översätter SQL GROUP BY och aggregeringar till Java 8
  • Förstår du verkligen SQLs GROUP BY- och HAVING-klausuler?
  • GROUP BY ROLLUP / CUBE

Lämna ett svar

Din e-postadress kommer inte publiceras.