SQL+

My, czyli Ci którzy nie myślimy o SQL jak o czymś co jest stare, słabe i powolne, jakże cieszymy się kiedy nasz ukochany król języków programowania się rozwija i staje się coraz lepszy. A początki wyglądały tak:

• w 1970 roku pracownik firmy IBM (Edgar Frank Codd) opublikował pracę „A Relational Model of Data for Large Shared Data Banks” w której po raz pierwszy opisał zależności jakie mogą występować pomiędzy danymi oraz założenia dotyczące modelu relacyjnego i jego przeszukiwania

• na początku lat siedemdziesiątych dwóch pracowników firmy IBM (Raymond F.Boyce oraz Donald Chamberline) opracowali język SEQUEL (Structured English Query Language), przemianowany w niedługim czasie na SQL (Structured Query Language)

• w 1973 roku firma IBM udostępniła SYSTEM R pierwszy silnik bazodanowy oparty o model relacyjny z interfejsem SQL

• w tym samym czasie, a może nawet trochę wcześniej, Lawrence Ellison z wspólnikami pracując na zlecenie FBI nad tajnym projektem „Oracle” uznali że będzie on oparty o założenia Codd’a i język SQL i już w 1979 jako pierwsi komercyjnie wdrożyli relacyjną bazę danych z SQL

Mamy zatem do czynienia z wysoce dojrzałym i uznanym standardem, który przez lata ewoluował, do dzisiaj stanowiąc fundament wielu systemów informatycznych. Pomimo pozornej, jak się okazało, ofensywy baz obiektowych i już mniej pozornej ofensywy systemów opartych o ORM czy rozwiązań klasy „NoSQL”, SQL ma się dobrze i będzie miał się dobrze jeszcze długo.

Jak zatem wygląda teraźniejszość i przyszłość, co nowego w SQL, czy nasz „ulubiony” język może nas czymkolwiek zaskoczyć? Omówię kilka nowych „ficzerów”, które nie były od początku składnikami języka i jak mniemam nie są znane wszystkim SQL’owiczom. Ograniczę przykłady do tych działających w silniku Postgres w wersji 9.6.

Lateral – Nowy rodzaj złączeń

Słowo kluczowe LATERAL powoduje że klasyczne typy złączeń INNER, OUTER, FULL, czy CROSS zmieniają swój sposób działania. W pierwszej kolejności pobieramy jest wiersz z tabeli głównej i do takiego wiersza „dobierany” jest wiersz z tabeli dołączanej. Inaczej to ujmując takie złączenie dla każdego wiersza z głównej tabeli wykonuje podzapytanie przekazując do niego aktualny wiersz jako parametr.

Przed LATERAL, można często spotkać konstrukcje podobne do:

czyli wielokrotne złączenia w celu uzyskania wartości wielu kolumn. Wygodniej było by napisać, i wydajniej zrealizować to w postaci:

ale niestety dla takiego zapytania otrzymamy komunikat:

SQL Error [42P01]: ERROR: invalid reference to FROM-clause entry for table „a”

There is an entry for table „a”, but it cannot be referenced from this part of the query.

LATERAL rozwiązuje problem, serwer narzuca sobie inny sposób wykonania zapytania i zapytanie w postaci

wykona się z powodzeniem.

String_agg – agregacja wartości

Do kompletu klasycznych agregatów sum(), min(), max(), count(), w standardzie SQL dodano nowy agregat list_agg() który w Postgres przyjmuje postać string_agg(). Rolą tego agregatu jest „sklejanie” wartości w jeden długi ciąg. Zapytanie:

zwróci listę gdzie dla każdego operatora prowadzącego otrzymamy dwie kolumny, jedną z ilością kontrahentów których on prowadzi, a drugą z listą id tych kontrahentów oddzielonych przecinkami.

Tablesample – próbka danych z tabeli

W sytuacji gdy chcemy mieć dostęp do losowo wybranych danych z tabeli, w celu np. szybkiego obliczenia średniej wartości jakiś kolumn bazując na 5% danych w tabeli, tak aby nie tracić czasu na przeglądanie całej tabeli, decydując się na typowy błąd statystyczny, możemy użyć słowa kluczowego TABLESAMPLE:

co spowoduje policzenie średniej wartości na próbce 5% losowo wybranych danych, algorytmem losowania typu „system”, który losuje strony bazy danych z wierszami tabeli, zwracając mniej więcej zadaną procentowo liczbę wierszy.

Grouping sets – rozszerzenia dla grupowania

Klasyczna klauzula GROUP BY wymaga od nas podania jawnie i statycznie listy kolumn które mają zostać zgrupowane, a co a tym idzie dla nich zostaną policzone agregaty. Typowy problem z którym się spotykamy, jak policzyć podsumowania grup i jednocześnie podać sumę całkowitą w jednym zapytaniu rozwiązują nowe rozszerzenia tej klauzuli. Można się „męczyć” UNION’em ale Słowo kluczowe GROUPING SETS pozwala na podanie więcej niż jednej listy kolumn do grupowania, np.:

zwróci zarówno count() dla dla poszczególnych operatorów prowadzących jak i count() ogólny. A zapytanie:

zwróci liczby dla poszczególnych operatorów prowadzących osobno dla tych kontrahentów co mają uwagi, jak i tych, który nie mają oraz całkowitą liczbę ogólną. Kolejnym rozszerzeniem jest klauzula ROLLUP która wykonuje za nas mozolną pracę przy wypisywaniu zestawów kolumn grupujących. W zapytaniu:

otrzymamy wynik dokładnie taki sam jakby napisać

a więc mamy trzy poziomy grupowania, z dwóch kolumn, z jednej i ogólny. Natomiast klauzula CUBE powoduje powstanie wszystkich możliwych grupowań ze zbioru podanych kolumn, np.:

spowoduje powstanie czterech poziomów jednego z dwóch kolumn, dwóch złożonych z jednej kolumny i jednego ogólnego.

Filter – sprytniejsze agregaty

Idąc w kierunku osiągania celu jednym prostszym zapytaniem warto rozważyć kolejne rozszerzenie SQL , klauzulę FILTER która bardzo elegancko współpracuje z agregatami, np.:

pozwala w jednym zapytaniu pobrać wiele zagregowanych informacji na temat danych w tabelach, z określeniem dla każdego z nich specyficznego warunku WHERE.

With – tymczasowe tabele na czas wykonania zapytania

Klauzula WITH znacząco ułatwia tworzenie złożonych zapytań, dzięki WITH możemy na czas trwania zapytania stworzyć wiele wirtualnych tabel/widoków które mają własne zapytania SQL i wszystkie te tabele łączyć w zapytaniu głównym. Bez WITH było naprawdę ciężko (a tak kiedyś było), wielu zapytań nie dało się osiągnąć w sensowny i szybki sposób. WITH jest emanacją techniki CTE (Common Table Expression) która obecnie jest wspierana przez większość silników SQL. Np.:

 zwraca towary które są z rzadkich jednostek miary i produkują je producenci od których mamy dużo towarów.

Over – funkcje okna

Słowo kluczowe OVER otwiera wachlarz możliwości jakie dają funkcje okien danych. Okno danych rozumiane jest jako zbiór wierszy powiązany z aktualnym wierszem jakimś związkiem. W bardzo prostym przykładzie:

pobieramy listę pozycji dokumentów sprzedaży, z ilością dla każdej pozycji i z łączną sumą sprzedaży kartoteki której dotyczy ta pozycja. Związek wiersza z agregatem opisuje klauzula PARTITION BY, w przykładzie jest to związek według kolumny „id_good”. Co ważne poza typowymi agregatami mamy dostęp do wielu innych funkcji, np.:

           

zapytanie także jest listą pozycji dokumentów sprzedaży, w czwartej kolumnie zawiera miejsce w rankingu sprzedaży jaki zajmuje ta pozycja biorąc pod uwagę ilość sprzedaży w obrębie jej kartoteki. Warto zauważyć że użyliśmy nowej funkcji „rank()” oraz użyliśmy ORDER BY do uporządkowania wierszy (wg ilości malejąco) wewnątrz okna opartego na „id_good”. Z ważniejszych funkcji na uwagę zasługują:

• row_number() – zwraca kolejny numer wiersza w partycji zaczynając od 1

• rank() – zwraca numer w rankingu wartości, dla wartości powtarzających się, zwraca te same numery rankingowe, wartości kolejne po powtarzających się mają ranking tak jakby wartości powtarzających się nie było

• dense_rank() – zwraca numer w rankingu wartości, dla wartości powtarzających się, zwraca te same numery rankingowe, wartości kolejne po powtarzających się mają ranking wprost z kolejnymi liczbami

• percent_rank() – ranking w ujęciu procentowym w stosunku do ilości wierszy w partycji, pierwszy wiersz ma wynik bliski zera a ostatni równy 1

• first_value(value) – zwraca wartość „value” z pierwszego wiersza w partycji

• last_value(value) – zwraca wartość „value” z ostatniego wiersza w partycji

• nth_value(value, nrow) – zwraca wartość „value” z wiersza na pozycji „nrow” w partycji

Fanom SQL polecam śledzenie nowych elementów języka SQL, zdecydowanie warto poszerzać swój warsztat wykorzystując nowości pojawiające się zarówno w standardzie, jak i w implementacjach. Te kilka prostych przykładów pokazuje także, że SQL ma się dobrze, rozwija się i jako król dostępu do danych nie zamierza abdykować.