En nybegynderguide til den rigtige rækkefølge af SQL-operationer

SQL-sproget er meget intuitivt. Indtil det ikke er det.

Igennem årene har mange mennesker kritiseret SQL-sproget af forskellige årsager. For eksempel: IDE’er kan ikke nemt gætte, hvilke auto completion-muligheder de skal tilbyde, for så længe du ikke angiver FROM-klausulen, er der (endnu) ingen tabeller i scope:

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

Disse ting er underlige, fordi den leksikalske rækkefølge af operationer ikke stemmer overens med den logiske rækkefølge af operationer. Vi mennesker kan nogle gange (ofte) intuitivt forstå denne forskel i rækkefølge. F.eks. ved vi, at vi er ved at vælge fra kundetabellen. Men IDE’en ved det ikke.

GROUP BY bidrager med den største forvirring

Når en juniorudvikler / SQL-begynder begynder at arbejde med SQL, vil de ret hurtigt finde ud af aggregering og GROUP BY. Og de vil hurtigt skrive ting som:

SELECT count(*)FROM customer

Yay, vi har 200 kunder!

Og så:

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

Wow, 90 af dem hedder Steve! Interessant. Lad os finde ud af, hvor mange vi har pr. navn…

SELECT first_name, count(*)FROM customerGROUP BY first_name

Ahaa!

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

Smuk. Men er de alle ens? Lad os også tjekke efternavnet

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

Ops!

ORA-00979: not a GROUP BY expression

Jeez, hvad betyder det? (bemærk, desværre vil MySQL-brugere, der ikke bruger STRICT-mode, stadig få et resultat her med vilkårlige efternavne!, så en ny MySQL-bruger vil ikke forstå deres fejltagelse)

Hvordan forklarer man nemt dette til en SQL-nybegynder? Det virker indlysende for “professionelle”, men er det virkelig indlysende? Er det indlysende nok til at man nemt kan forklare det til en junior? Tænk over det. Hvorfor er hvert af disse udsagn semantisk korrekt eller forkert?

Problemet er syntaksrelateret

SQL-syntaksen fungerer på samme måde som det engelske sprog. Det er en kommando. Vi starter kommandoer med verber. Verbet er SELECT (eller INSERT, UPDATE, DELETE, CREATE, DROP osv. osv.)

Det menneskelige sprog er desværre utroligt dårligt egnet til den meget mere formelle verden af programmering. Selv om det giver en vis trøst til nye brugere (eventuelt ikke-programmører), der er absolutte nybegyndere, gør det bare tingene svære for alle andre. Alle de forskellige SQL-klausuler har ekstremt komplekse indbyrdes afhængigheder. For eksempel:

  • I tilstedeværelsen af en GROUP BY-klausul kan kun udtryk, der er bygget op af GROUP BY-udtryk (eller funktionelle afhængigheder heraf), eller aggregerede funktioner anvendes i HAVING-, SELECT– og ORDER BY-klausuler.
  • For enkelhedens skyld skal vi ikke engang tale om GROUPING SETS
  • Der er faktisk endda nogle få tilfælde, hvor GROUP BY er implicit. F.eks. hvis du skriver en “nøgen” HAVING-klausul
  • En enkelt aggregeringsfunktion i SELECT-klausulen (i mangel af GROUP BY) vil tvinge aggregering til en enkelt række
  • Faktisk kan dette også være implicit ved at sætte denne aggregeringsfunktion i ORDER BY (af en eller anden grund)
  • Du kan ORDER BY en hel del udtryk, der refererer til alle kolonner fra FROM-klausulen uden SELECT at SELECTgøre dem. Men det gælder ikke længere, hvis du skriver SELECT DISTINCT

Listen er uendelig lang. Hvis du er interesseret, kan du læse SQL-standarddokumenterne og se, hvor mange mærkelige og komplicerede indbyrdes afhængigheder der findes mellem de mange klausuler i SELECT-erklæringen.

Kan dette nogensinde forstås?

Glædeligt nok, ja! Der findes et simpelt trick, som jeg altid forklarer til de delegerede, der besøger min SQL Masterclass. Den leksikalske (syntaktiske) rækkefølge af SQL-operationer (klausuler) svarer overhovedet ikke til den logiske rækkefølge af operationer (selv om de nogle gange tilfældigvis gør det). Takket være moderne optimeringsværktøjer svarer rækkefølgen heller ikke til den faktiske rækkefølge af operationer, så vi har i virkeligheden: syntaktisk -> logisk -> faktisk rækkefølge, men lad os lade det ligge ude af betragtning for nu.

Den logiske rækkefølge af operationer er følgende (for “enkelhedens skyld” udelader jeg leverandørspecifikke ting som CONNECT BY, MODEL, MATCH_RECOGNIZE, PIVOT, UNPIVOT og alle de andre):

  • FROM: Dette er faktisk det første, der sker, logisk set. Før alt andet indlæser vi alle rækker fra alle tabellerne og sammenføjer dem. Inden du skriger og bliver sur: Igen: Dette er det, der sker først logisk, ikke faktisk. Optimiseren vil med stor sandsynlighed ikke foretage denne operation først, det ville være dumt, men tilgå et eller andet indeks baseret på WHERE-klausulen. Men igen, logisk set sker dette først. Desuden: Alle JOIN-klausuler er faktisk en del af denne FROM-klausul. JOIN er en operatør i relationel algebra. Ligesom + og - er operatorer i aritmetik. Det er ikke en selvstændig klausul, som SELECT eller FROM
  • WHERE: Når vi har indlæst alle rækker fra tabellerne ovenfor, kan vi nu smide dem væk igen ved hjælp af WHERE
  • GROUP BY: Hvis du vil, kan du tage de rækker, der er tilbage efter WHERE, og sætte dem i grupper eller spande, hvor hver gruppe indeholder den samme værdi for GROUP BY-udtrykket (og alle de andre rækker sættes i en liste for den pågældende gruppe). I Java ville du få noget i stil med: Map<String, List<Row>>. Hvis du angiver en GROUP BY-klausul, indeholder dine faktiske rækker kun gruppekolonnerne og ikke længere de resterende kolonner, som nu står på denne liste. Disse kolonner i listen er kun synlige for aggregeringsfunktioner, der kan operere på denne liste. Se nedenfor:
  • aggregeringer: Dette er vigtigt at forstå. Uanset hvor du placerer din aggregeringsfunktion syntaktisk (dvs. i SELECT-klausulen eller i ORDER BY-klausulen), er dette her det trin, hvor aggregeringsfunktionerne beregnes. Lige efter GROUP BY. (husk: logisk set. Smarte databaser kan have beregnet dem før, faktisk). Dette forklarer, hvorfor du ikke kan sætte en aggregeret funktion i WHERE-klausulen, fordi dens værdi ikke kan tilgås endnu. WHERE-klausulen sker logisk set før aggregeringstrinnet. Aggregeringsfunktioner kan få adgang til de kolonner, som du har sat i “denne liste” for hver gruppe, ovenfor. Efter aggregeringen vil “denne liste” forsvinde og ikke længere være tilgængelig. Hvis du ikke har en GROUP BY-klausul, vil der bare være én stor gruppe uden nøgle, som indeholder alle rækker.
  • HAVING: … men nu kan du få adgang til aggregationsfunktionens værdier. Du kan f.eks. kontrollere, at count(*) > 1 i HAVING-klausulen. Fordi HAVING er efter GROUP BY (eller indebærer GROUP BY), kan vi ikke længere få adgang til kolonner eller udtryk, der ikke var GROUP BY-kolonner.
  • WINDOW: Hvis du bruger den fantastiske vinduesfunktionsfunktion, er dette det trin, hvor de alle beregnes. Kun nu. Og det fede er, at fordi vi allerede har beregnet (logisk!) alle aggregatfunktionerne, kan vi indlejre aggregatfunktioner i vinduesfunktioner. Det er altså helt fint at skrive ting som sum(count(*)) OVER () eller row_number() OVER (ORDER BY count(*)). At vinduesfunktioner først beregnes logisk nu, forklarer også, hvorfor man kun kan sætte dem i klausulerne SELECT eller ORDER BY. De er ikke tilgængelige for WHERE-klausulen, hvilket var tilfældet før. Bemærk, at PostgreSQL og Sybase SQL Anywhere har en egentlig WINDOW-klausul!
  • SELECT: Endelig. Vi kan nu bruge alle de rækker, der er produceret fra ovenstående klausuler, og oprette nye rækker / tupler ud fra dem ved hjælp af SELECT. Vi kan få adgang til alle de vinduesfunktioner, som vi har beregnet, alle de aggregerede funktioner, som vi har beregnet, alle de grupperingskolonner, som vi har angivet, eller hvis vi ikke har grupperet/aggregeret, kan vi bruge alle kolonnerne fra vores FROM-klausul. Husk: Selv om det ser ud som om vi aggregerer ting inde i SELECT, er det sket for længe siden, og den søde søde søde count(*)-funktion er ikke andet end en henvisning til resultatet.
  • DISTINCT: Ja! DISTINCT sker efter SELECT, selv om den er sat før din SELECT-kolonneliste syntaksmæssigt. Men tænk over det. Det giver perfekt mening. Hvordan kan vi ellers fjerne særskilte rækker, hvis vi ikke kender alle rækker (og deres kolonner) endnu?
  • UNION, INTERSECT, EXCEPT: Dette er en no-brainer. En UNION er en operatør, der forbinder to underafspørgsler. Alt det, vi har talt om indtil nu, var et subquery. Resultatet af en union er en ny forespørgsel, der indeholder de samme rækketyper (dvs. de samme kolonner) som den første underafspørgsel. Som regel. Fordi i wacko Oracle er det næstsidste subquery det rigtige til at definere kolonnenavnet. Oracle database, den syntaktiske trold 😉
  • ORDER BY: Det giver fuldstændig mening at udskyde beslutningen om at ordne et resultat til sidst, fordi alle andre operationer kan bruge hashmaps, internt, så enhver mellemliggende orden kan gå tabt igen. Så vi kan nu bestille resultatet. Normalt kan du få adgang til en masse rækker fra ORDER BY-klausulen, herunder rækker (eller udtryk), som du ikke SELECT. Men når du har angivet DISTINCT, før, kan du ikke længere bestille efter rækker/udtryk, der ikke blev valgt. Hvorfor? Fordi rækkefølgen ville være helt udefineret.
  • OFFSET: Nu sætter fornuftige databaser LIMIT (MySQL, PostgreSQL) eller FETCH (DB2, Oracle 12c, SQL Server 2012) klausulen helt til sidst, syntaktisk set. I gamle dage mente Sybase og SQL Server, at det ville være en god idé at have TOP som et nøgleord i SELECT. Som om den korrekte rækkefølge af SELECT DISTINCT ikke allerede var forvirrende nok.

Der har vi det. Det giver fuldstændig mening. Og hvis du nogensinde ønsker at gøre noget, der ikke er i den “rigtige rækkefølge”, er det enkleste trick altid at ty til en afledt tabel. F.eks. når du ønsker at gruppere på en vinduesfunktion:

Hvorfor virker det? Fordi:

  • I den afledte tabel sker FROM først, og derefter beregnes WINDOW, hvorefter spanden SELECTed.
  • Den ydre SELECT kan nu behandle resultatet af denne vinduesfunktionsberegning som enhver almindelig tabel i FROM-klausulen, derefter GROUP BY en almindelig kolonne, derefter aggregeres, derefter SELECT

Lad os gennemgå vores oprindelige eksempler med en forklaring på, hvorfor de virker, eller hvorfor de ikke gør det.

Tænk altid på den logiske rækkefølge af operationer

Hvis du ikke er en hyppig SQL-skriver, kan syntaksen faktisk være forvirrende. Især GROUP BY og aggregeringer “smitter” resten af hele SELECT klausulen, og tingene bliver virkelig underlige. Når vi konfronteres med denne mærkelighed, har vi to muligheder:

  • Gå amok og skrig på SQL-sprogets designere
  • Acceptere vores skæbne, lukke øjnene, glemme snytaksen og huske den logiske operationsrækkefølge

Jeg anbefaler generelt det sidste, for så begynder tingene at give meget mere mening, herunder den smukke kumulative daglige indtægtsberegning nedenfor, som indlejrer den daglige indtægt (SUM(amount) aggregatfunktion) inde i den kumulative indtægt (SUM(...) OVER (...) vinduesfunktion):

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

… fordi aggregeringer logisk set sker før vinduesfunktioner.

Caveat: ORDER BY-klausul

Der er nogle forbehold omkring ORDER BY-klausulen, som kan være med til at skabe yderligere forvirring. Som standard fortsætter man med at antage, at den logiske rækkefølge af operationer er korrekt. Men så er der nogle særlige tilfælde, især:

  • I mangel af en DISTINCT-klausul
  • I mangel af sætoperationer som UNION

Du kan henvise til udtryk i ORDER BY, som ikke er projiceret af SELECT. Følgende forespørgsel er helt fint i de fleste databaser:

SELECT first_name, last_nameFROM actorORDER BY actor_id

Der er en “virtuel” / implicit ACTOR_ID-projektion, som om vi havde skrevet:

SELECT first_name, last_name, actor_idFROM actorORDER BY actor_id

Men derefter fjernet ACTOR_ID-kolonnen igen fra resultatet. Dette er meget praktisk, selv om det kan føre til en vis forvirring om semantikken og rækkefølgen af operationer. Specifikt kan du ikke bruge f.eks. DISTINCT i en sådan situation. Følgende forespørgsel er ugyldig:

SELECT DISTINCT first_name, last_nameFROM actorORDER BY actor_id -- Oops

For hvad nu, hvis der er to aktører med samme navn, men med meget forskellige ID’er? Ordningen ville nu være udefineret.

Med sætoperationer er det endnu tydeligere, hvorfor dette ikke er tilladt:

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

I dette tilfælde er kolonnen ACTOR_ID ikke til stede i tabellen CUSTOMER, så forespørgslen giver ingen mening overhovedet.

Videre læsning

Vil du lære mere? Vi har også disse artikler, som du kan læse:

  • SQL GROUP BY og funktionelle afhængigheder: En meget nyttig funktion
  • Sådan burde SQL GROUP BY have været designet – ligesom Neo4j’s Implicit GROUP BY
  • Sådan oversættes SQL GROUP BY og aggregeringer til Java 8
  • Forstår du virkelig SQL’s GROUP BY- og HAVING-klausuler?
  • GROUP BY ROLLUP / CUBE

Skriv et svar

Din e-mailadresse vil ikke blive publiceret.