Průvodce začátečníka skutečným pořadím operací jazyka SQL

Jazyk SQL je velmi intuitivní. Dokud není.

V průběhu let mnoho lidí kritizovalo jazyk SQL z různých důvodů. Např: IDE nemohou snadno odhadnout, jaké možnosti automatického doplňování nabídnout, protože dokud nezadáte klauzuli FROM, nejsou v oboru (zatím) žádné tabulky:

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

Tyto věci jsou divné, protože lexikální pořadí operací neodpovídá logickému pořadí operací. My lidé můžeme někdy (často) intuitivně chápat tento rozdíl v pořadí. Např. víme, že se chystáme vybrat z tabulky zákazníků. Ale IDE to neví.

GROUP BY přispívá k největšímu zmatku

Když juniorní vývojář/začátečník v SQL začne pracovat s SQL, poměrně rychle se dozví o agregaci a GROUP BY. A rychle budou psát věci jako:

SELECT count(*)FROM customer

Jé, máme 200 zákazníků!

A pak:

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

Páni, 90 z nich se jmenuje Steve! Zajímavé. Zjistíme, kolik jich máme na jedno jméno…

SELECT first_name, count(*)FROM customerGROUP BY first_name

Aha!“

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

Velmi pěkné. Ale jsou všechny stejné? Podívejme se také na příjmení

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

Oops!

ORA-00979: not a GROUP BY expression

Jéžiš, co to znamená? (pozn. uživatelé MySQL, kteří nepoužívají režim STRICT, zde bohužel stále dostanou výsledek s libovolným příjmením!, takže nový uživatel MySQL jejich chybu nepochopí)

Jak to jednoduše vysvětlit nováčkovi v SQL? „Profíkům“ to připadá samozřejmé, ale je to opravdu samozřejmé? Je to natolik zřejmé, abyste to mohli snadno vysvětlit juniorovi? Přemýšlejte o tom. Proč jsou jednotlivé příkazy sémanticky správné nebo špatné?“

Problém souvisí se syntaxí

Syntaxe jazyka SQL funguje podobně jako angličtina. Jedná se o příkazy. Příkazy začínáme slovesy. Sloveso je SELECT (nebo INSERT, UPDATE, DELETE, CREATE, DROP, atd. atd.)

Naneštěstí je lidský jazyk neuvěřitelně nevhodný pro mnohem formálnější svět programování. Nabízí sice určitou útěchu novým uživatelům (případně neprogramátorům), kteří jsou naprostými začátečníky, ale všem ostatním věci jen ztěžuje. Všechny různé klauzule jazyka SQL mají nesmírně složité vzájemné závislosti. Například:

  • Při přítomnosti klauzule GROUP BY lze v klauzulích HAVING, SELECT a ORDER BY použít pouze výrazy sestavené z výrazů GROUP BY (nebo jejich funkční závislosti) nebo agregační funkce.
  • Pro zjednodušení se o GROUPING SETS
  • V podstatě existuje dokonce několik případů, kdy je GROUP BY implicitní. Např. pokud napíšete „holou“ HAVING klauzuli
  • Jediná agregační funkce v SELECT klauzuli (při absenci GROUP BY) si vynutí agregaci do jediného řádku
  • Ve skutečnosti to lze implikovat i tak, že tuto agregační funkci vložíte do ORDER BY (ať už z jakéhokoli důvodu)
  • Můžete ORDER BY docela dost výrazů, které se odkazují na libovolné sloupce z FROM klauzule, aniž byste je SELECT uvedli. To už ale neplatí, pokud napíšete SELECT DISTINCT

Seznam je nekonečný. Pokud vás to zajímá, můžete si přečíst standardní dokumenty SQL a zjistit, kolik podivných a komplikovaných vzájemných závislostí existuje mezi mnoha klauzulemi příkazu SELECT.

Dá se to vůbec pochopit?

Naštěstí ano! Existuje jednoduchý trik, který vždy vysvětluji delegátům, kteří navštěvují mé mistrovské kurzy SQL. Lexikální (syntaktické) pořadí operací (klauzulí) jazyka SQL vůbec neodpovídá logickému pořadí operací (i když někdy shodou okolností ano). Díky moderním optimalizátorům pořadí také neodpovídá skutečnému pořadí operací, takže ve skutečnosti máme: syntaktické -> logické -> skutečné pořadí, ale to teď nechme stranou.

Logické pořadí operací je následující (pro „jednoduchost“ vynechávám věci specifické pro vendor jako CONNECT BY, MODEL, MATCH_RECOGNIZE, PIVOT, UNPIVOT a všechny ostatní):

  • FROM: To je vlastně první věc, která se logicky stane. Před čímkoli dalším načteme všechny řádky ze všech tabulek a spojíme je. Než začnete křičet a vztekat se: Opět, toto je to, co se děje jako první logicky, ne ve skutečnosti. Optimalizátor velmi pravděpodobně neprovede tuto operaci jako první, to by byla hloupost, ale přistoupí k nějakému indexu na základě klauzule WHERE. Ale opět, logicky se to stane jako první. A také: všechny klauzule JOIN jsou ve skutečnosti součástí této klauzule FROM. JOIN je operátor v relační algebře. Stejně jako + a - jsou operátory v aritmetice. Není to samostatná klauzule jako SELECT nebo FROM
  • WHERE: Jakmile jsme načetli všechny řádky z výše uvedených tabulek, můžeme je nyní opět vyhodit pomocí WHERE
  • GROUP BY: Pokud chcete, můžete vzít řádky, které zbyly po WHERE, a zařadit je do skupin nebo kbelíků, přičemž každá skupina obsahuje stejnou hodnotu pro výraz GROUP BY (a všechny ostatní řádky jsou zařazeny do seznamu pro tuto skupinu). V jazyce Java byste dostali něco takového: Map<String, List<Row>>. Pokud zadáte klauzuli GROUP BY, pak vaše skutečné řádky obsahují pouze sloupce skupiny, nikoli již ostatní sloupce, které jsou nyní v tomto seznamu. Tyto sloupce v seznamu jsou viditelné pouze pro agregační funkce, které mohou s tímto seznamem pracovat. Viz níže.
  • agregace: Toto je důležité pochopit. Bez ohledu na to, kam svou agregační funkci syntakticky umístíte (tj. do klauzule SELECT nebo do klauzule ORDER BY), je toto zde krok, ve kterém se počítají agregační funkce. Hned za GROUP BY. (Nezapomeňte: logicky. Chytré databáze je možná počítaly už předtím, ve skutečnosti). To vysvětluje, proč nelze agregační funkci umístit do klauzule WHERE, protože k její hodnotě ještě nelze přistupovat. Klauzule WHERE se logicky vyskytuje před agregačním krokem. Agregační funkce mohou přistupovat ke sloupcům, které jste vložili do „tohoto seznamu“ pro každou skupinu výše. Po agregaci „tento seznam“ zmizí a nebude již dostupný. Pokud nemáte klauzuli GROUP BY, bude existovat jen jedna velká skupina bez klíče, která bude obsahovat všechny řádky.
  • HAVING: … ale nyní můžete přistupovat k hodnotám agregačních funkcí. Můžete například zkontrolovat, že count(*) > 1 v klauzuli HAVING. Protože HAVING je za GROUP BY (nebo implikuje GROUP BY), nemůžeme již přistupovat ke sloupcům nebo výrazům, které nebyly sloupci GROUP BY.
  • WINDOW: Pokud používáte úžasnou funkci okenních funkcí, v tomto kroku se všechny vypočítají. Teprve nyní. A skvělé je, že protože jsme již vypočítali (logicky!) všechny agregační funkce, můžeme agregační funkce vnořit do okenních funkcí. Je tedy naprosto v pořádku psát věci jako sum(count(*)) OVER () nebo row_number() OVER (ORDER BY count(*)). To, že se okenní funkce logicky počítají až nyní, také vysvětluje, proč je můžete umístit pouze do klauzulí SELECT nebo ORDER BY. Nejsou dostupné klauzuli WHERE, což se dělo dříve. Všimněte si, že PostgreSQL a Sybase SQL Anywhere mají skutečnou klauzuli WINDOW!
  • SELECT: Konečně. Nyní můžeme použít všechny řádky vzniklé z výše uvedených klauzulí a vytvořit z nich nové řádky / tuply pomocí SELECT. Můžeme přistupovat ke všem okénkovým funkcím, které jsme vypočítali, ke všem agregačním funkcím, které jsme vypočítali, ke všem seskupovacím sloupcům, které jsme zadali, nebo pokud jsme neseskupovali/neagregovali, můžeme použít všechny sloupce z naší klauzule FROM. Nezapomeňte:
  • DISTINCT: Ano! DISTINCT se děje za SELECT, i když je syntakticky umístěn před vaším seznamem sloupců SELECT. Ale zamyslete se nad tím. Dává to dokonalý smysl. Jak jinak můžeme odstranit odlišné řádky, když ještě neznáme všechny řádky (a jejich sloupce)?“
  • UNION, INTERSECT, EXCEPT: To je přece nesmysl. A UNION je operátor, který spojuje dva poddotazy. Vše, o čem jsme dosud mluvili, byl poddotaz. Výstupem sjednocení je nový dotaz obsahující stejné typy řádků (tj. stejné sloupce) jako první poddotaz. Obvykle se jedná o tzv. Protože v bláznivém Oracle je předposlední poddotaz tím správným, který definuje název sloupce. Databáze Oracle, syntaktický troll 😉
  • ORDER BY: Celkem dává smysl odložit rozhodnutí o řazení výsledku až na konec, protože všechny další operace mohou používat hashmapy, interně, takže by se případné mezipořadí mohlo zase ztratit. Nyní tedy můžeme výsledek uspořádat. Za normálních okolností můžete z klauzule ORDER BY přistupovat k mnoha řádkům, včetně řádků (nebo výrazů), které jste SELECT neuspořádali. Když jste ale zadali DISTINCT, předtím už nemůžete řadit podle řádků/výrazů, které nebyly vybrány. Proč? Protože řazení by bylo zcela nedefinované.
  • OFFSET:
  • LIMIT, FETCH, TOP: Nyní rozumné databáze dávají klauzuli LIMIT (MySQL, PostgreSQL) nebo FETCH (DB2, Oracle 12c, SQL Server 2012) syntakticky úplně na konec. Za starých časů si Sybase a SQL Server myslely, že by bylo dobré mít TOP jako klíčové slovo v SELECT. Jako by správné řazení SELECT DISTINCT nebylo už tak dost matoucí.

Tady to máme. Dává to naprostý smysl. A pokud někdy budete chtít udělat něco, co není ve „správném pořadí“, nejjednodušším trikem je vždy uchýlit se k odvozené tabulce. Např. když chcete seskupit na funkci okna:

Proč to funguje? Protože:

  • V odvozené tabulce se nejprve stane FROM, pak se vypočítá WINDOW a teprve potom se SELECTvyplní kbelík.
  • Vnější SELECT nyní může s výsledkem výpočtu této okenní funkce zacházet jako s jakoukoli běžnou tabulkou v klauzuli FROM, pak GROUP BY běžný sloupec, pak agregace, pak SELECT

Přehlédněme si naše původní příklady s vysvětlením, proč fungují nebo proč nefungují.

Vždy přemýšlejte o logickém pořadí operací

Pokud nepíšete často SQL, může být syntaxe skutečně matoucí. Zejména GROUP BY a agregace „nakazí“ zbytek celé SELECT klauzule a věci se stanou opravdu podivnými. Když se s touto podivností setkáme, máme dvě možnosti:

  • Naštvat se a křičet na tvůrce jazyka SQL
  • Smířit se s osudem, zavřít oči, zapomenout na snytax a vzpomenout si na pořadí logických operací

Obecně doporučuji druhou možnost, protože pak věci začnou dávat mnohem větší smysl, včetně níže uvedeného krásného výpočtu kumulativních denních příjmů, který vnořuje denní příjmy (SUM(amount) agregační funkce) do kumulativních příjmů (SUM(...) OVER (...) okenní funkce):

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

… protože agregace logicky probíhají před okenními funkcemi.

Caveat: klauzule ORDER BY

Okolí klauzule ORDER BY obsahuje několik výhrad, které mohou přispívat k dalším nejasnostem. Standardně se nadále předpokládá, že logické pořadí operací je správné. Pak ale existují některé speciální případy, zejména:

  • Při absenci klauzule DISTINCT
  • Při absenci množinových operací jako UNION

Můžete se odkazovat na výrazy v ORDER BY, které nejsou promítnuty SELECT. Následující dotaz je ve většině databází naprosto v pořádku:

SELECT first_name, last_nameFROM actorORDER BY actor_id

Je zde „virtuální“ / implicitní projekce ACTOR_ID, jako kdybychom napsali:

SELECT first_name, last_name, actor_idFROM actorORDER BY actor_id

Ale pak z výsledku opět odstranili sloupec ACTOR_ID. To je velmi pohodlné, i když to může vést k určitým nejasnostem ohledně sémantiky a pořadí operací. Konkrétně nelze v takové situaci použít například DISTINCT. Následující dotaz je neplatný:

SELECT DISTINCT first_name, last_nameFROM actorORDER BY actor_id -- Oops

Protože, co když existují dva aktéři stejného jména, ale s velmi odlišnými ID? Pořadí by nyní bylo nedefinované.

Při množinových operacích je ještě jasnější, proč to není dovoleno:

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

V tomto případě se sloupec ACTOR_ID v tabulce CUSTOMER nevyskytuje, takže dotaz nemá vůbec žádný smysl.

Další čtení

Chcete se dozvědět více? Máme pro vás také tyto články:

  • SQL GROUP BY a funkční závislosti:
  • Jak měly být navrženy SQL GROUP BY – jako implicitní GROUP BY v Neo4j
  • Jak převést SQL GROUP BY a agregace do Javy 8
  • Rozumíte opravdu SQL klauzulím GROUP BY a HAVING?
  • GROUP BY ROLLUP / CUBE

Napsat komentář

Vaše e-mailová adresa nebude zveřejněna.