Przewodnik dla początkujących po prawdziwej kolejności operacji SQL

Język SQL jest bardzo intuicyjny. Dopóki nie jest.

Na przestrzeni lat wiele osób krytykowało język SQL z różnych powodów. Na przykład: IDE nie mogą łatwo odgadnąć, jakie opcje autouzupełniania zaoferować, ponieważ tak długo, jak nie określisz klauzuli FROM, nie ma tabel w zakresie (jeszcze):

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

Te rzeczy są dziwne, ponieważ kolejność leksykalna operacji nie pasuje do logicznej kolejności operacji. My, ludzie, możemy czasami (często) intuicyjnie zrozumieć tę różnicę w zamawianiu. Np. wiemy, że mamy zamiar wybrać z tabeli klientów. Ale IDE tego nie wie.

GROUP BY przyczynia się do największego zamieszania

Kiedy młodszy programista / początkujący SQL zaczyna pracę z SQL, dość szybko dowie się o agregacji i GROUP BY. I szybko napiszą rzeczy takie jak:

SELECT count(*)FROM customer

Tak, mamy 200 klientów!

A potem:

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

Wow, 90 z nich ma na imię Steve! Interesujące. Dowiedzmy się, ilu mamy ich na jedno imię…

SELECT first_name, count(*)FROM customerGROUP BY first_name

Ahaa!

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

Bardzo ładnie. Ale czy wszystkie są takie same? Sprawdźmy też nazwisko

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

Oops!

ORA-00979: not a GROUP BY expression

Jeezu, co to znaczy? (uwaga, niestety, użytkownicy MySQL, którzy nie używają trybu STRICT, nadal otrzymają tutaj wynik z arbitralnie wybranymi nazwiskami!, więc nowy użytkownik MySQL nie zrozumie ich błędu)

Jak łatwo wytłumaczyć to nowicjuszowi SQL? Wydaje się to oczywiste dla „profesjonalistów”, ale czy naprawdę jest to oczywiste? Czy jest to na tyle oczywiste, że możesz łatwo wyjaśnić to juniorowi? Zastanów się nad tym. Dlaczego każde z tych stwierdzeń jest semantycznie poprawne lub błędne?

Problem dotyczy składni

Składnia SQL działa w podobny sposób jak język angielski. Jest to polecenie. Polecenia rozpoczynamy od czasowników. Czasownik to SELECT (lub INSERT, UPDATE, DELETE, CREATE, DROP, itd. itd.)

Niestety, język ludzki jest niewiarygodnie nieprzystosowany do znacznie bardziej formalnego świata programowania. Chociaż oferuje to pewne pocieszenie dla nowych użytkowników (być może nieprogramistów), którzy są absolutnie początkujący, to po prostu utrudnia rzeczy dla wszystkich innych. Wszystkie różne klauzule SQL mają niezwykle złożone współzależności. Na przykład:

  • W obecności klauzuli GROUP BY w klauzulach HAVING, SELECT i ORDER BY można używać tylko wyrażeń zbudowanych z wyrażeń GROUP BY (lub ich zależności funkcjonalnych) lub funkcji agregujących.
  • Dla uproszczenia nie mówmy nawet o GROUPING SETS
  • W rzeczywistości istnieje nawet kilka przypadków, w których GROUP BY jest implikowane. Np. jeśli napiszesz „gołą” HAVING klauzulę
  • Pojedyncza funkcja agregująca w klauzuli SELECT (przy braku GROUP BY) wymusi agregację do pojedynczego wiersza
  • W rzeczywistości może to być również implikowane przez umieszczenie tej funkcji agregującej w ORDER BY (z jakiegokolwiek powodu)
  • Możesz ORDER BY całkiem sporo wyrażeń, które odwołują się do dowolnych kolumn z klauzuli FROM bez SELECT ich umieszczania. Ale to już nie jest prawdą, jeśli napiszesz SELECT DISTINCT

Lista jest nieskończona. Jeśli jesteś zainteresowany, możesz przeczytać dokumenty standardu SQL i sprawdzić, jak wiele dziwnych i skomplikowanych współzależności istnieje między wieloma klauzulami instrukcji SELECT.

Czy można to kiedykolwiek zrozumieć?

Na szczęście tak! Istnieje prosta sztuczka, którą zawsze wyjaśniam delegatom, którzy odwiedzają moje SQL Masterclass. Otóż kolejność leksykalna (składniowa) operacji (klauzul) SQL wcale nie odpowiada kolejności logicznej operacji (choć czasem przypadkowo tak się dzieje). Dzięki nowoczesnym optymalizatorom kolejność ta nie odpowiada również faktycznej kolejności operacji, więc tak naprawdę mamy: kolejność składniowa -> kolejność logiczna -> kolejność faktyczna, ale zostawmy to na razie na boku.

Logiczna kolejność operacji jest następująca (dla „uproszczenia” pomijam rzeczy specyficzne dla dostawcy, takie jak CONNECT BY, MODEL, MATCH_RECOGNIZE, PIVOT, UNPIVOT i wszystkie inne):

  • FROM: To jest właściwie pierwsza rzecz, która się dzieje, logicznie. Zanim cokolwiek innego, ładujemy wszystkie wiersze ze wszystkich tabel i łączymy je. Zanim zaczniesz krzyczeć i się wściekać: Ponownie, to jest to, co dzieje się najpierw logicznie, a nie faktycznie. Optymalizator najprawdopodobniej nie wykona tej operacji jako pierwszy, to byłoby głupie, ale uzyska dostęp do jakiegoś indeksu na podstawie klauzuli WHERE. Ale znowu, logicznie, to dzieje się najpierw. Ponadto: wszystkie klauzule JOIN są w rzeczywistości częścią tej klauzuli FROM. JOIN jest operatorem w algebrze relacyjnej. Tak jak + i - są operatorami w arytmetyce. Nie jest to niezależna klauzula, jak SELECT lub FROM
  • WHERE: Po załadowaniu wszystkich wierszy z powyższych tabel, możemy je teraz ponownie wyrzucić, używając WHERE
  • GROUP BY: Jeśli chcesz, możesz wziąć wiersze, które pozostały po WHERE i umieścić je w grupach lub wiadrach, gdzie każda grupa zawiera tę samą wartość dla wyrażenia GROUP BY (a wszystkie pozostałe wiersze są umieszczane na liście dla tej grupy). W Javie otrzymałbyś coś takiego jak: Map<String, List<Row>>. Jeśli określisz klauzulę GROUP BY, wtedy twoje rzeczywiste wiersze zawierają tylko kolumny grupy, a nie pozostałe kolumny, które są teraz na liście. Te kolumny na liście są widoczne tylko dla funkcji agregujących, które mogą operować na tej liście. Zobacz poniżej.
  • agregacje: To jest ważne, aby zrozumieć. Bez względu na to, gdzie umieścisz swoją funkcję agregującą składniowo (tj. w klauzuli SELECT lub w klauzuli ORDER BY), to tutaj jest krok, w którym funkcje agregujące są obliczane. Zaraz po GROUP BY. (pamiętaj: logicznie. Sprytne bazy danych mogły je obliczyć wcześniej, w rzeczywistości). To wyjaśnia, dlaczego nie można umieścić funkcji agregującej w klauzuli WHERE, ponieważ jej wartość nie może być jeszcze dostępna. Klauzula WHERE logicznie dzieje się przed krokiem agregacji. Funkcje agregujące mogą uzyskać dostęp do kolumn, które umieściłeś w „tej liście” dla każdej grupy, powyżej. Po agregacji, „ta lista” zniknie i nie będzie już dostępna. Jeśli nie masz klauzuli GROUP BY, będzie po prostu jedna duża grupa bez żadnego klucza, zawierająca wszystkie wiersze.
  • HAVING: … ale teraz możesz uzyskać dostęp do wartości funkcji agregujących. Na przykład, możesz sprawdzić, że count(*) > 1 w klauzuli HAVING. Ponieważ HAVING jest po GROUP BY (lub implikuje GROUP BY), nie możemy już uzyskać dostępu do kolumn lub wyrażeń, które nie były kolumnami GROUP BY.
  • WINDOW: Jeśli używasz niesamowitej funkcji okna, jest to krok, w którym wszystkie są obliczane. Tylko teraz. A fajną rzeczą jest to, że ponieważ mamy już obliczone (logicznie!) wszystkie funkcje agregujące, możemy zagnieżdżać funkcje agregujące w funkcjach okna. Można więc pisać takie rzeczy jak sum(count(*)) OVER () czy row_number() OVER (ORDER BY count(*)). Funkcje okna, które są logicznie obliczane dopiero teraz, wyjaśniają również, dlaczego można je umieścić tylko w klauzulach SELECT lub ORDER BY. Nie są one dostępne dla klauzuli WHERE, co miało miejsce wcześniej. Zauważ, że PostgreSQL i Sybase SQL Anywhere mają rzeczywistą klauzulę WINDOW!
  • SELECT: Wreszcie. Możemy teraz użyć wszystkich wierszy, które są produkowane z powyższych klauzul i tworzyć nowe wiersze / krotki z nich za pomocą SELECT. Możemy uzyskać dostęp do wszystkich funkcji okna, które obliczyliśmy, wszystkich funkcji agregujących, które obliczyliśmy, wszystkich kolumn grupujących, które określiliśmy, lub jeśli nie grupowaliśmy/agregowaliśmy, możemy użyć wszystkich kolumn z naszej klauzuli FROM. Pamiętaj: Nawet jeśli wygląda na to, że agregujemy rzeczy wewnątrz SELECT, stało się to już dawno temu, a słodka słodka funkcja count(*) jest niczym więcej niż odwołaniem do wyniku.
  • DISTINCT: Tak! DISTINCT dzieje się po SELECT, nawet jeśli jest umieszczony przed twoją listą kolumn SELECT, składniowo. Ale pomyśl o tym. To ma doskonały sens. Jak inaczej możemy usunąć odrębne wiersze, jeśli nie znamy jeszcze wszystkich wierszy (i ich kolumn)?
  • UNION, INTERSECT, EXCEPT: To nie jest oczywistość. A UNION jest operatorem, który łączy dwa podzapytania. Wszystko, o czym mówiliśmy do tej pory, było podzapytaniem. Wynikiem unii jest nowe zapytanie zawierające te same typy wierszy (tj. te same kolumny) co pierwsze podzapytanie. Zazwyczaj. Ponieważ w wacko Oracle, przedostatnie podzapytanie jest tym właściwym, które definiuje nazwę kolumny. Baza Oracle, składniowy troll 😉
  • ORDER BY: Odkładanie decyzji o zamówieniu wyniku do końca ma sens, ponieważ wszystkie inne operacje mogą używać hashmaps, wewnętrznie, więc każdy pośredni porządek może zostać ponownie utracony. Możemy więc teraz zamówić wynik. Normalnie, możesz uzyskać dostęp do wielu wierszy z klauzuli ORDER BY, w tym wierszy (lub wyrażeń), których nie SELECT. Ale kiedy określiłeś DISTINCT, wcześniej, nie możesz już zamówić wierszy / wyrażeń, które nie zostały wybrane. Dlaczego? Ponieważ zamawianie byłoby całkiem niezdefiniowane.
  • OFFSET: Nie używaj offsetu
  • LIMIT, FETCH, TOP: Teraz rozsądne bazy danych umieszczają klauzulę LIMIT (MySQL, PostgreSQL) lub FETCH (DB2, Oracle 12c, SQL Server 2012) na samym końcu, składniowo. W dawnych czasach Sybase i SQL Server uważały, że dobrym pomysłem jest posiadanie TOP jako słowa kluczowego w SELECT. Jakby prawidłowe uporządkowanie SELECT DISTINCT nie było już wystarczająco mylące.

Tutaj, mamy to. To ma całkowity sens. A jeśli kiedykolwiek chcesz zrobić coś, co nie jest we „właściwej kolejności”, najprostszą sztuczką jest zawsze uciekać się do tabeli pochodnej. Np. gdy chcemy zgrupować się na funkcji okna:

Dlaczego to działa? Ponieważ:

  • W tabeli pochodnej najpierw zachodzi FROM, a potem obliczane jest WINDOW, następnie wiadro jest SELECTed.
  • Zewnętrzny SELECT może teraz traktować wynik tego obliczenia funkcji okna jak każdą zwykłą tabelę w klauzuli FROM, a następnie GROUP BY zwykłą kolumnę, a następnie agregować, a następnie SELECT

Przejrzyjrzyjmy nasze oryginalne przykłady z wyjaśnieniem, dlaczego działają lub dlaczego nie działają.

Zawsze myśl o logicznej kolejności operacji

Jeśli nie jesteś częstym autorem SQL, składnia rzeczywiście może być myląca. Szczególnie GROUP BY i agregacje „zarażają” resztę całej SELECT klauzuli, a rzeczy stają się naprawdę dziwne. W konfrontacji z tym dziwactwem, mamy dwie opcje:

  • Wścieknąć się i krzyczeć na projektantów języka SQL
  • Pogodzić się z losem, zamknąć oczy, zapomnieć o snytaxie i zapamiętać porządek operacji logicznych

Zazwyczaj polecam to drugie rozwiązanie, ponieważ wtedy rzeczy zaczynają mieć dużo więcej sensu, w tym piękne skumulowane dzienne obliczenie przychodów poniżej, które zagnieżdża dzienne przychody (SUM(amount) funkcja agregująca) wewnątrz skumulowanych przychodów (SUM(...) OVER (...) funkcja okna):

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

… ponieważ agregacje logicznie występują przed funkcjami okna.

Caveat: klauzula ORDER BY

Istnieją pewne zastrzeżenia wokół klauzuli ORDER BY, które mogą przyczyniać się do dalszego zamieszania. Domyślnie, kontynuuj zakładając, że logiczna kolejność operacji jest poprawna. Ale wtedy, istnieją pewne specjalne przypadki, w szczególności:

  • W przypadku braku klauzuli DISTINCT
  • W przypadku braku operacji zestawu, takich jak UNION

Możesz odwoływać się do wyrażeń w ORDER BY, które nie są rzutowane przez SELECT. Następujące zapytanie jest całkowicie w porządku w większości baz danych:

SELECT first_name, last_nameFROM actorORDER BY actor_id

Jest „wirtualna” / niejawna projekcja ACTOR_ID, tak jakbyśmy napisali:

SELECT first_name, last_name, actor_idFROM actorORDER BY actor_id

Ale następnie ponownie usunęli kolumnę ACTOR_ID z wyniku. Jest to bardzo wygodne, choć może prowadzić do pewnych nieporozumień związanych z semantyką i kolejnością operacji. W szczególności, nie można w takiej sytuacji użyć np. DISTINCT. Następujące zapytanie jest niepoprawne:

SELECT DISTINCT first_name, last_nameFROM actorORDER BY actor_id -- Oops

Ponieważ, co by było, gdyby istniały dwa podmioty o tej samej nazwie, ale o bardzo różnych identyfikatorach? Porządkowanie byłoby teraz niezdefiniowane.

W przypadku operacji na zbiorach jest jeszcze bardziej jasne, dlaczego jest to niedozwolone:

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

W tym przypadku kolumna ACTOR_ID nie jest obecna w tabeli CUSTOMER, więc zapytanie nie ma żadnego sensu.

Dalsza lektura

Chcesz dowiedzieć się więcej? Mamy dla Ciebie również następujące artykuły do przeczytania:

  • SQL GROUP BY i zależności funkcjonalne: Bardzo przydatna funkcja
  • Jak powinna być zaprojektowana SQL GROUP BY – jak Implicit GROUP BY w Neo4j
  • Jak przetłumaczyć SQL GROUP BY i agregacje na Javę 8
  • Czy naprawdę rozumiesz klauzule SQL GROUP BY i HAVING?
  • GROUP BY ROLLUP / CUBE

.

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany.