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

SQL-kieli on hyvin intuitiivinen. Kunnes se ei ole.

Vuosien mittaan monet ihmiset ovat kritisoineet SQL-kieltä monista eri syistä. Esim: IDE-ohjelmat eivät voi helposti arvata, mitä automaattisia täydennysvaihtoehtoja tarjota, koska niin kauan kuin et määritä FROM-lauseketta, soveltamisalassa ei ole taulukoita (vielä):

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

Nämä asiat ovat outoja, koska operaatioiden leksikaalinen järjestys ei vastaa operaatioiden loogista järjestystä. Me ihmiset voimme joskus (usein) intuitiivisesti ymmärtää tämän järjestyseron. Esim. tiedämme, että olemme valitsemassa asiakastaulusta. Mutta IDE ei tiedä tätä.

GROUP BY aiheuttaa eniten sekaannusta

Kun nuorempi kehittäjä / SQL-aloittaja alkaa työskennellä SQL:n parissa, melko nopeasti hän saa selville aggregoinnin ja GROUP BY. Ja he kirjoittavat nopeasti sellaisia asioita kuin:

SELECT count(*)FROM customer

Jee, meillä on 200 asiakasta!

Ja sitten:

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

Vau, 90 niistä on nimeltään Steve! Mielenkiintoista. Otetaanpa selvää, kuinka monta meillä on per nimi…

SELECT first_name, count(*)FROM customerGROUP BY first_name

Ahaa!

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

Erittäin hienoa. Mutta ovatko ne kaikki samanlaisia? Tarkistetaan myös sukunimi

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

Oops!

ORA-00979: not a GROUP BY expression

Jeez, mitä se tarkoittaa? (huom, valitettavasti MySQL-käyttäjät, jotka eivät käytä STRICT-tilaa, saavat tässä edelleen tuloksen, jossa on mielivaltaisia sukunimiä!, joten uusi MySQL-käyttäjä ei ymmärrä heidän virhettään)

Miten selitätte tämän helposti SQL-newbie:lle? ”Ammattilaisille” se tuntuu itsestäänselvyydeltä, mutta onko se oikeasti itsestäänselvyys? Onko se tarpeeksi ilmeinen, että sen voi selittää helposti aloittelijalle? Ajattele asiaa. Miksi kukin näistä lausekkeista on semanttisesti oikein tai väärin?

Ongelma liittyy syntaksiin

SQL:n syntaksi toimii samalla tavalla kuin englannin kieli. Se on komento. Aloitamme komennot verbeillä. Verbi on SELECT (tai INSERT, UPDATE, DELETE, CREATE, DROP, jne. jne.)

Epäonnekseen ihmiskieli soveltuu uskomattoman huonosti paljon muodollisempaan ohjelmoinnin maailmaan. Vaikka se tarjoaa jonkinlaista lohtua uusille käyttäjille (mahdollisesti ei-ohjelmoijille), jotka ovat absoluuttisia aloittelijoita, se vain tekee asioista vaikeita kaikille muille. Kaikilla SQL:n eri lausekkeilla on erittäin monimutkaisia keskinäisiä riippuvuuksia. Esimerkiksi:

  • Lausekkeen GROUP BY ollessa läsnä vain GROUP BY-lausekkeista (tai niiden funktionaalisista riippuvuuksista) rakennettuja lausekkeita tai aggregaattifunktioita voidaan käyttää lausekkeissa HAVING, SELECT ja ORDER BY.
  • Yksinkertaisuuden vuoksi ei edes puhuta lausekkeesta GROUPING SETS
  • Tosiasiassa on jopa muutama sellainen tapaus, jossa GROUP BY-lauseke on implisiittinen. Esim. jos kirjoitat ”paljaan” HAVING-lausekkeen
  • Yksittäinen aggregaattifunktio SELECT-lausekkeessa (ilman GROUP BY) pakottaa aggregaation yhdelle riville
  • Se voidaan itse asiassa implisiittisesti tehdä myös laittamalla kyseinen aggregaattifunktio ORDER BY-lausekkeeseen (jostain syystä)
  • Voit ORDER BY tehdä melko monta lauseketta, jotka viittaavat mihin tahansa FROM-lausekkeen sarakkeisiin ilman SELECT-lauseketta. Mutta se ei enää pidä paikkaansa, jos kirjoitat SELECT DISTINCT

Lista on loputon. Jos sinua kiinnostaa, voit lukea SQL-standardidokumentteja ja tarkistaa, kuinka monta outoa ja monimutkaista keskinäistä riippuvuutta SELECT-lauseen monien lausekkeiden välillä on.

Voidaanko tätä koskaan ymmärtää?

Onneksi kyllä! On olemassa yksinkertainen temppu, jonka selitän aina SQL-mestarikurssillani vieraileville delegaateille. SQL-operaatioiden (lausekkeiden) leksikaalinen (syntaktinen) järjestys ei vastaa lainkaan operaatioiden loogista järjestystä (vaikka joskus ne sattumalta vastaavat). Nykyaikaisten optimoijien ansiosta järjestys ei myöskään vastaa operaatioiden todellista järjestystä, joten meillä on oikeastaan: syntaktinen -> looginen -> todellinen järjestys, mutta jätetään tämä toistaiseksi sivuun.

Looginen operaatiojärjestys on seuraava (jätän ”yksinkertaisuuden” vuoksi pois sellaiset myyjäkohtaiset asiat kuin CONNECT BY, MODEL, MATCH_RECOGNIZE, PIVOT, UNPIVOT ja kaikki muut):

  • FROM: Tämä on itse asiassa ensimmäinen asia, joka tapahtuu loogisesti. Ennen kaikkea muuta lataamme kaikki rivit kaikista taulukoista ja yhdistämme ne. Ennen kuin huudat ja suutut: Tämäkin tapahtuu ensin loogisesti, ei oikeasti. Optimoija ei hyvin todennäköisesti tee tätä operaatiota ensin, se olisi typerää, vaan käyttää jotain WHERE-lausekkeeseen perustuvaa indeksiä. Mutta jälleen, loogisesti tämä tapahtuu ensin. Lisäksi: kaikki JOIN-lausekkeet ovat itse asiassa osa tätä FROM-lauseketta. JOIN on relaatioalgebran operaattori. Aivan kuten + ja - ovat operaattoreita aritmetiikassa. Se ei ole itsenäinen lauseke, kuten SELECT tai FROM
  • WHERE: Kun olemme ladanneet kaikki rivit edellä mainituista taulukoista, voimme nyt heittää ne taas pois käyttämällä WHERE
  • GROUP BY: Halutessasi voit ottaa WHERE:n jälkeen jäljelle jääneet rivit ja sijoittaa ne ryhmiin tai ämpäreihin, joissa kussakin ryhmässä on sama arvo lausekkeelle GROUP BY (ja kaikki muut rivit sijoitetaan kyseisen ryhmän luetteloon). Java-kielellä saataisiin jotain sellaista kuin: Map<String, List<Row>>. Jos määrität GROUP BY-lausekkeen, varsinaiset rivit sisältävät vain ryhmän sarakkeet, eivät enää muita sarakkeita, jotka ovat nyt tuossa luettelossa. Nämä luettelossa olevat sarakkeet näkyvät vain aggregaattifunktioille, jotka voivat toimia kyseisellä luettelolla. Katso alla.
  • aggregaatiot: Tämä on tärkeää ymmärtää. Riippumatta siitä, mihin laitat aggregaattifunktion syntaktisesti (eli SELECT-lausekkeeseen tai ORDER BY-lausekkeeseen), tässä on vaihe, jossa aggregaattifunktiot lasketaan. Heti GROUP BY:n jälkeen. (muista: loogisesti. Fiksut tietokannat ovat itse asiassa saattaneet laskea ne jo aiemmin). Tämä selittää, miksi WHERE-lausekkeeseen ei voi laittaa aggregaattifunktiota, koska sen arvoa ei voida vielä käyttää. WHERE-lauseke tapahtuu loogisesti ennen aggregointivaihetta. Aggregaattifunktiot voivat käyttää sarakkeita, jotka olet laittanut ”tähän luetteloon” kullekin ryhmälle edellä. Aggregoinnin jälkeen ”this list” katoaa eikä ole enää käytettävissä. Jos sinulla ei ole GROUP BY-lauseketta, on vain yksi suuri ryhmä ilman mitään avainta, joka sisältää kaikki rivit.
  • HAVING: … mutta nyt voit käyttää aggregaattifunktion arvoja. Voit esimerkiksi tarkistaa, että count(*) > 1 HAVING-lausekkeessa HAVING. Koska HAVING on GROUP BY:n jälkeen (tai implikoi GROUP BY:n), emme voi enää käyttää sarakkeita tai lausekkeita, jotka eivät olleet GROUP BY:n sarakkeita.
  • WINDOW: Jos käytät mahtavaa ikkunafunktio-ominaisuutta, tässä vaiheessa ne kaikki lasketaan. Vain nyt. Ja siistiä on se, että koska olemme jo laskeneet (loogisesti!) kaikki aggregaattifunktiot, voimme sijoittaa aggregaattifunktiot ikkunafunktioihin. On siis täysin mahdollista kirjoittaa esimerkiksi sum(count(*)) OVER () tai row_number() OVER (ORDER BY count(*)). Se, että ikkunafunktiot lasketaan loogisesti vasta nyt, selittää myös sen, miksi niitä voi laittaa vain SELECT– tai ORDER BY-lausekkeisiin. Ne eivät ole käytettävissä WHERE-lausekkeessa, mikä tapahtui aiemmin. Huomaa, että PostgreSQL:ssä ja Sybase SQL Anywhere:ssa on varsinainen WINDOW-lauseke!
  • SELECT: Vihdoinkin. Voimme nyt käyttää kaikkia edellä mainittujen lausekkeiden tuottamia rivejä ja luoda niistä uusia rivejä / tupleja käyttämällä SELECT. Voimme käyttää kaikkia laskemiamme ikkunafunktioita, kaikkia laskemiamme aggregaattifunktioita, kaikkia määrittämiämme ryhmittelysarakkeita, tai jos emme ole ryhmitelleet/aggregoineet, voimme käyttää kaikkia FROM-lausekkeemme sarakkeita. Muista: Vaikka näyttääkin siltä, että aggregoimme asioita SELECT:n sisällä, tämä on tapahtunut kauan sitten, ja suloinen suloinen count(*)-funktio ei ole muuta kuin viittaus tulokseen.
  • DISTINCT: Kyllä! DISTINCT tapahtuu SELECT:n jälkeen, vaikka se laitetaan ennen SELECT:n sarakeluetteloa syntaktisesti. Mutta ajattele asiaa. Se on täysin järkevää. Miten muuten voimme poistaa erillisiä rivejä, jos emme vielä tiedä kaikkia rivejä (ja niiden sarakkeita)?
  • UNION, INTERSECT, EXCEPT: Tämä ei ole mikään ihme. UNION on operaattori, joka yhdistää kaksi alakyselyä. Kaikki, mistä olemme tähän mennessä puhuneet, oli alakyselyjä. Unionin tuloksena on uusi kysely, joka sisältää samat rivityypit (eli samat sarakkeet) kuin ensimmäinen alakysely. Yleensä. Koska hassussa Oraclessa toiseksi viimeinen alakysely on oikea sarakkeen nimen määrittelyyn. Oracle-tietokanta, syntaktinen peikko 😉
  • ORDER BY: On täysin järkevää lykätä päätös tuloksen järjestämisestä loppuun, koska kaikki muut operaatiot saattavat käyttää hashmapeja, sisäisesti, joten mahdollinen välijärjestys saattaa hävitä taas. Voimme siis nyt järjestää tuloksen. Normaalisti ORDER BY-lausekkeesta pääsee käsiksi moniin riveihin, myös sellaisiin riveihin (tai lausekkeisiin), joita ei SELECT. Mutta kun määrittelit DISTINCT, aiemmin, et voi enää järjestellä rivejä / lausekkeita, joita ei valittu. Miksi? Koska järjestys olisi aivan määrittelemätön.
  • OFFSET:
  • LIMIT, FETCH, TOP: Nyt järkevät tietokannat laittavat LIMIT (MySQL, PostgreSQL) tai FETCH (DB2, Oracle 12c, SQL Server 2012) lausekkeen aivan loppuun, syntaktisesti. Ennen vanhaan Sybase ja SQL Server ajattelivat, että SELECT:n avainsanana olisi hyvä olla TOP. Ikään kuin SELECT DISTINCT:n oikea järjestys ei olisi jo tarpeeksi hämmentävä.

Niin, siinä se on. Siinä on täysi järki. Ja jos joskus haluat tehdä jotain, joka ei ole ”oikeassa järjestyksessä”, yksinkertaisin temppu on aina turvautua johdettuun taulukkoon. Esim. kun haluat ryhmitellä ikkunafunktion:

Miksi se toimii? Koska:

  • Johdetussa taulukossa FROM tapahtuu ensin, ja sitten lasketaan WINDOW, jonka jälkeen ämpäri on SELECTed.
  • Ulkoinen SELECT voi nyt käsitellä tämän ikkunafunktion laskennan tulosta kuin mitä tahansa tavallista taulukkoa FROM-lausekkeessa, sitten GROUP BY tavallinen sarake, sitten aggregaatti, sitten SELECT

Katsotaan vielä kerran alkuperäisiä esimerkkejämme selittämällä, miksi ne toimivat tai miksi ne eivät toimi.

Pohdi aina operaatioiden loogista järjestystä

Jos et ole usein SQL-kirjoittaja, syntaksi voi todellakin olla hämmentävä. Erityisesti GROUP BY ja aggregaatiot ”tartuttavat” koko muun SELECT lausekkeen, ja asiat muuttuvat todella oudoiksi. Kun kohtaamme tämän outouden, meillä on kaksi vaihtoehtoa:

  • Suuttua ja huutaa SQL-kielen suunnittelijoille
  • Hyväksyä kohtalomme, sulkea silmät, unohtaa snytax ja muistaa loogisten operaatioiden järjestys

Suosittelen yleensä jälkimmäistä vaihtoehtoa, koska silloin asiat alkavat olla paljon järkevämpiä, mukaan lukien alla oleva kaunis kumulatiivinen päiväkohtainen liikevaihtolaskelma, jossa päiväkohtaiset liikevaihtolaskennat (SUM(amount) aggregaattifunktio) on sijoitettu kasaan kumulatiivisten liikevaihtolaskentojen sisälle (SUM(...) OVER (...) ikkunatoiminto):

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

… koska aggregaatiot tapahtuvat loogisesti ennen ikkunafunktioita.

Varoitus: ORDER BY -lauseke

Lausekkeen ORDER BY ympärillä on joitakin varoituksia, jotka saattavat aiheuttaa lisää sekaannusta. Oletusarvoisesti jatketaan olettamalla, että looginen järjestys on oikea. Mutta sitten on joitakin erikoistapauksia, erityisesti:

  • Jos DISTINCT-lauseketta ei ole
  • Jos joukko-operaatioita ei ole, kuten UNION

Voit viitata ORDER BY-lausekkeessa oleviin lausekkeisiin, joita SELECT ei projisoi. Seuraava kysely on täysin kunnossa useimmissa tietokannoissa:

SELECT first_name, last_nameFROM actorORDER BY actor_id

Tässä on ”virtuaalinen” / implisiittinen ACTOR_ID-projektio, ikään kuin olisimme kirjoittaneet:

SELECT first_name, last_name, actor_idFROM actorORDER BY actor_id

Mutta sitten poistaneet ACTOR_ID-sarakkeen uudelleen tuloksesta. Tämä on erittäin kätevää, vaikkakin se saattaa aiheuttaa sekaannusta semantiikan ja operaatioiden järjestyksen suhteen. Erityisesti et voi käyttää esim. DISTINCT tällaisessa tilanteessa. Seuraava kysely on virheellinen:

SELECT DISTINCT first_name, last_nameFROM actorORDER BY actor_id -- Oops

Miksi, entä jos on kaksi toimijaa, joilla on sama nimi mutta hyvin erilaiset tunnukset? Järjestys olisi nyt määrittelemätön.

Sarjaoperaatioiden kohdalla on vielä selvempää, miksi tämä ei ole sallittua:

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

Tässä tapauksessa ACTOR_ID-saraketta ei ole CUSTOMER-taulussa, joten kyselyssä ei ole mitään järkeä.

Lisälukemisto

Haluatko oppia lisää? Meillä on myös nämä artikkelit luettavana:

  • SQL GROUP BY ja toiminnalliset riippuvuudet: Erittäin hyödyllinen ominaisuus
  • Miten SQL GROUP BY olisi pitänyt suunnitella – kuten Neo4j:n implisiittinen GROUP BY
  • Miten SQL GROUP BY ja aggregaatiot käännetään Java 8:aan
  • Ymmärrätkö todella SQL:n GROUP BY- ja HAVING-lausekkeet?
  • GROUP BY:n ROLLUP / CUBE

Vastaa

Sähköpostiosoitettasi ei julkaista.