SELECT Explanation, Example FROM Pro.Knowledge
FacebookRSS

Logiczne przetwarzanie zapytań SQL

Naukę http://www.dreamstime.com/stock-image-handshake-abstract-cogwheels-blackboard-green-blackboard-hand-image44667161języka SQL w zakresie pisania zapytań, powinieneś rozpocząć właśnie od tego rozdziału. Zrozumienie procesu przetwarzania kwerendy jest niezbędne, aby w pełni świadomie tworzyć polecenia SQL i móc wykorzystać ich możliwości.

W artykule tym, znajdziesz informacje o kolejności kroków przetwarzania zapytań.

Analizując ten proces, nie skupiaj się proszę na szczegółach i możliwościach konkretnych poleceń. Chciałbym abyś uchwycił tu ogólne zasady, w jaki sposób wykonywane są kwerendy. Temat ten, z uwagi na zakres materiału podzieliłem na dwie części. W tej, omawiam pełen proces na prostym przykładzie zapytania do pojedynczej tabeli.

Druga część wymaga znajomości zasad łączenia tabel i umieściłem ją zaraz po rozdziale opisującym te zagadnienia. Wyjaśniam w niej aspekty kolejności łączenia tabel.


Kolejność kroków przetwarzania zapytań

Wykonywanie każdej kwerendy przez silnik bazodanowy odbywa się krokowo, w ściśle określonej kolejności. Reguły te są spójne dla wszystkich relacyjnych baz danych.

W konstrukcji zapytań możemy wyszczególnić 6 głównych bloków logicznych.

(Step 5)   SELECT   -- określanie kształtu wyniku, selekcja pionowa (kolumn)
(Step 1)   FROM     -- określenie źródła (źródeł) i relacji między nimi
(Step 2)   WHERE    -- filtracja rekordów
(Step 3)   GROUP BY -- grupowanie rekordów
(Step 4)   HAVING   -- filtrowanie grup
(Step 6)   ORDER BY -- sortowanie wyniku

W ramach każdego z nich, możemy umieszczać całkiem złożone konstrukcje. Podzapytania, zapytania skorelowane, dodatkowe polecenia, takie jak operator DISTINCT czy TOP w SELECT. Poszczególne klauzule i zakres ich możliwości omawiam szczegółowo w kolejnych rozdziałach tego kursu.

Najprostsze polecenie SQL może składać się z samego SELECTa np. :

SELECT 'Hello World!'

Takie „zapytania” rzadko kiedy nas interesują i tak naprawdę trudno nawet nazwać je zapytaniami. Zazwyczaj chcemy pobierać dane z jakiegoś źródła np. tabeli czy widoku. Do ich określenia, służy klauzula FROM. Ustalmy więc, że kwerenda to konstrukcja składająca się przynajmniej z bloków SELECT oraz FROM.

Poza nimi, wszystkie pozostałe są opcjonalne. Warto jednak zauważyć, że stosowanie HAVING bez GROUP BY również za bardzo nie będzie miało sensu. Z punktu widzenia samej konstrukcji jest jednak możliwe.

Wynikiem przetwarzania każdego z kroków jest tabela wirtualna (VT) , będąca jednocześnie obiektem wejściowym kolejnego etapu. Do tabel wirtualnych, czyli produktów pośrednich kroków przetwarzania, nie mamy dostępu z zewnątrz. Są one logicznymi strukturami, które rozpatrujemy tylko w kontekście analizy teoretycznej.

Trzeba podkreślić, że faktyczny sposób realizacji zapytań, jest wykonywany za pomocą efektywnych i zoptymalizowanych przez silnik bazodanowy metod. W efekcie końcowym, pozwalają one na osiągnięcie dokładnie tego samego wyniku co w analizie teoretycznej. Podkreślam to, bo szczególnie opis iloczynów kartezjańskich czy konieczność czytania całej zawartości tabel może słusznie budzić w Tobie niedowierzanie czy wewnętrzny sprzeciw :)

W analizie teoretycznej tak właśnie się dzieje, aby osiągnąć określony wynik. W praktyce, silnik bazodanowy zna skróty, które doprowadzają dokładnie do tego samego celu – znacznie szybciej.


Analiza przetwarzania kwerendy na przykładzie

Cały proces najlepiej prześledzić na przykładzie. Na początek coś prostego – zapytanie pobierające dane tylko z jednej tabeli dbo.Pracownicy. Zawartość tego zbioru jest następująca :

IdPrac Nazwisko        Placa                 Zespol     Dzial
------ --------------- --------------------- ---------- ----------
1      Kasprzak        3000,00               DBA        IT
2      Norwid          2200,00               DBA        IT
3      Walewska        4000,00               Security   IT
4      Nowak           1300,00               Detal      Sprzedaz
5      Piotrowska      2300,00               Hurt       Sprzedaz
6      Lewandowski     3300,00               DBA        IT
7      Tusk            1100,00               HelpDesk   IT
8      Podemski        9500,00               Hurt       Sprzedaz
9      Gmoch           1750,00               Security   IT
10     Walendziak      1750,00               NULL       NULL

(10 row(s) affected)

W naszym scenariuszu, będziemy chcieli wyświetlić informacje o liczbie pracowników, pracujących w zespołach wieloosobowych (więcej niż jeden pracownik) w ramach Działu IT. Końcowy wynik posortujemy rosnąco po liczbie pracowników.

Kwerenda realizująca to zadanie będzie wyglądała następująco :

SELECT Zespol , COUNT( IdPrac ) AS LiczbaPracowników
FROM dbo.Pracownicy 
WHERE Dzial = 'IT'
GROUP BY Zespol
HAVING COUNT ( IdPrac ) > 1 
ORDER BY LiczbaPracowników

Zapytanie to wykorzystuje wszystkie bloki logiczne z jakich może składać się dowolna kwerenda. Na razie celowo nie omawiam logiki łączenia tabel (pobieramy dane tylko z jednego zbioru), aby lepiej przedstawić generalne zasady.

Krok 1 : FROM – określenie źródła (źródeł) i relacji między nimi

Na początku trzeba określić skąd będziemy czerpać dane. Dlatego pierwszym krokiem jest klauzula FROM. W naszym przykładzie zbiorem wejściowym jest jedna tabela – dbo.Pracownicy. Wynikiem przetwarzania pierwszego kroku będzie zatem tabela wirtualna VT1, zawierającą całą (tak, CAŁĄ !) zawartość tego zbioru.

W tym momencie powinna zapalić się u Ciebie lampka ostrzegawcza. Jak to? Czy faktycznie tak jest, że jak odpytuję tabelę zawierającą 100 milionów rekordów to wszystkie te elementy są czytane i przekazywane do jakiejś VT1? Teoretycznie, zgodnie z zasadami logicznego przetwarzania – tak właśnie się dzieje.

Tylko teoretycznie. Jak już wspominałem, w praktyce silnik serwera zna efektywne sposoby dostępu do danych. Z pewnością jeśli nie są potrzebne wszystkie dane tabeli w kolejnych krokach, to nie będzie do nich sięgał.

Zawartość tabeli VT1 będzie więc wyglądała identycznie jak tabeli dbo.Pracownicy :

SELECT Zespol , COUNT( IdPrac ) AS LiczbaPracowników
FROM dbo.Pracownicy                                    -- wynik VT1WHERE Dzial = 'IT'
GROUP BY Zespol
HAVING COUNT ( IdPrac ) > 1 
ORDER BY LiczbaPracowników
VT1

VT1

Krok 2 : WHERE – filtrowanie rekordów

Krok ten, działa na wyniku poprzedniego. Czyli w naszym wypadku będzie operował na tabeli VT1. Może to się wydawać oczywiste, ale świadomie tu podkreślę, że filtrować (wyszukiwać) możemy rekordy tylko takie, które są obecne w tabeli VT1.

W WHERE, dla każdego wiersza wyznaczany jest wynik logiczny zastosowanych wyrażeń (warunków). W naszym przykładzie stawiamy tylko jeden warunek. Interesują nas elementy zbioru VT1, dla których wartość kolumny Dział jest równa ciągowi znaków ‚IT’.

SELECT Zespol , COUNT( IdPrac ) AS LiczbaPracowników
FROM dbo.Pracownicy 
WHERE Dzial = 'IT'                                     -- wynik VT2GROUP BY Zespol
HAVING COUNT ( IdPrac ) > 1 
ORDER BY LiczbaPracowników

Do tabeli wynikowej VT2 – czyli rezultatu przetwarzania kroku WHERE, zostaną wstawione tylko takie rekordy, dla których wynik postawionych warunków (wyrażeń) będzie TRUE.

Przyjrzyjmy się zatem, jakie wyniki logiczne naszego wyrażenia, przyjmą kolejne rekordy.

Wynik logiczny wyrażeń w WHERE

Wynik logiczny wyrażeń określonych w WHERE

Relacyjne systemy baz danych, w tym także SQL Server, bazują na logice trójwartościowej. Jak sama nazwa wskazuje, wynik porównań może przyjmować trzy wartości – TRUE, FALSE lub UNKNOWN. TRUE oraz FALSE nie trzeba tłumaczyć – albo jest coś równe czemuś, albo nie.

Wartość UNKNKOWN, czyli nieznana została wyznaczona dla ostatniego (10) rekordu. Jest ona bezpośrednio związana z koncepcją wartości nieokreślonych – NULL. Jakiekolwiek porównanie, czy operacje z NULLem, skutkują zawsze wartością nieznaną – UNKNOWN. Szczegółowe wyjaśnienie tego zagadnienia, znajdziesz w artykule poświęconym NULL.

Podsumowując, do kolejnego kroku, zostanie przekazana tabela VT2. Zawierać będzie tylko 6 rekordów – te dla których wynik wyrażeń wynosi TRUE.

VT2 - wynik filtrowania WHERE

VT2 – wynik filtrowania WHERE

Krok 3 – GROUP BY – grupowanie rekordów

Podobnie jak filtrowanie, GROUP BY jest blokiem opcjonalnym. W tym kroku tworzone są grupy rekordów. Definicję grupy tworzą kolumny (atrybuty) grupujące, wyszczególnione w GROUP BY.

W naszym prostym przykładzie, potrzebujemy znać informacje o liczbie pracowników w ramach zespołów. Grupować będziemy po kolumnie Zespol.

SELECT Zespol , COUNT( IdPrac ) AS LiczbaPracowników
FROM dbo.Pracownicy 
WHERE Dzial = 'IT'
GROUP BY Zespol                                        -- wynik VT3HAVING COUNT ( IdPrac ) > 1 
ORDER BY LiczbaPracowników

Każdy element tabeli wejściowej, czyli wynik pochodzący z kroku WHERE – VT2, może znaleźć się tylko w jednej grupie. Utworzonych zostanie tyle grup, ile jest unikalnych wartości w kombinacjach wszystkich kolumn grupujących. W naszym przypadku utworzone zostaną 3 grupy rekordów, reprezentujące zespoły DBA, HelpDesk oraz Security :

GROUP BY - tworzenie grup rekordów

GROUP BY – tworzenie grup rekordów

Ten krok przetwarzania jest szczególny, gdyż jego istnienie wprowadza bardzo ważne konsekwencje we wszystkich kolejnych krokach. Każda grupa, reprezentowana będzie przez jeden rekord tabeli wynikowej VT3, określony wartościami kolumn grupujących.

Sednem działania tego kroku, jest utworzone dwóch sekcji danych. Sekcji grupującej – opisanej przez kolumny, według których grupujemy oraz sekcji danych surowych. Ta z kolei, zawiera wszystkie pozostałe atrybuty (kolumny) nie występujące w klauzuli GROUP BY.

GROUP BY - sekcja grupująca i danych surowych

GROUP BY – sekcja grupująca i danych surowych

Od tego momentu, bezpośrednio odwoływać się można tylko do kolumn sekcji grupującej. To one tworzą elementy tabeli VT3 – są po prostu ich atrybutami.

Tabela VT3 zawierać będzie 3 elementy (rekordy), opisane atrybutem Zespol. Z każdym z tych rekordów będzie skojarzona sekcja danych surowych, tworzona przez wszystkie pozostałe kolumny. Do kolumn sekcji danych surowych, możemy odwoływać się w kolejnych krokach, tylko poprzez funkcje agregujące – np. COUNT().

Jest to całkiem logiczne. Zastanów się, jaką wartość kolumny Nazwisko, silnik bazodanowy miałby zwrócić w reprezentacji relacyjnej dla rekordu grupy DBA. Pierwsze? Ostatnie? A może losowe? Nic z tych rzeczy. SQL bazuje na matematycznej teorii zbiorów i nie ma w niej miejsca na przypadek. Wszystko musi odbywać się wedle ściśle określonych zasad.

W wyniku działania GROUP BY, otrzymamy tabelę VT3 zawierającą 3 rekordy. Każdy z nich, reprezentuje grupę. Z każdą z grup, skojarzona jest sekcja surowa. Celem lepszego zobrazowania, poniżej przedstawiam zawartość tabeli VT3. Dodatkowe trzy kolumny, pokazują wynik działania funkcji agregujących.

COUNT(idPrac) – zlicza wystąpienia wartości idPrac, wszystkich rekordów w ramach sekcji danych surowych. MAX i MIN zwracają największą i najmniejszą wartość w kolumnie Nazwisko.

SELECT Zespol , COUNT( IdPrac ) as COUNT_Raw_Data_Records, 
	MIN( Nazwisko ) MIN_Nazwisko, MAX( Nazwisko ) MAX_Nazwisko 
FROM dbo.Pracownicy
WHERE Dzial='IT'
GROUP BY Zespol
VT3 + dodatkowe kolumny - wynik funkcji agregujących

VT3 + dodatkowe kolumny – wynik funkcji agregujących

Krok 4 – HAVING – filtrowanie grup

HAVING jest bezpośrednio związany z operacjami na grupach. Jest więc logicznym, że ten krok przetwarzania nastąpi po etapie tworzenia grup czyli po GROUP BY. Ogólne zasady filtrowania są identyczne jak w WHERE. Do tabeli wynikowej VT4, trafią tylko takie rekordy (grupy), dla których wynik wyrażeń logicznych będzie TRUE.

Budując warunki filtracji grup rekordów, trzeba pamiętać o konsekwencji grupowania. Bezpośrednio odwoływać możemy się tylko do kolumn sekcji grupującej, do pozostałych za pośrednictwem funkcji agregujących.

W naszym scenariuszu, interesuje nas liczba elementów w sekcji danych surowych w obrębie grupy. Czyli z ilu pracowników składa się każda grupa. Wynik logiczny określony w HAVING, spełniają dwa rekordy i tylko te dwie grupy znajdą się w tabeli wynikowej VT4.

SELECT Zespol , COUNT( IdPrac ) AS LiczbaPracowników
FROM dbo.Pracownicy 
WHERE Dzial = 'IT'
GROUP BY Zespol
HAVING COUNT ( IdPrac ) > 1                            -- wynik VT4ORDER BY LiczbaPracowników
HAVING - filtrowanie grup

HAVING – filtrowanie grup

Krok 5 – SELECT – selekcja pionowa i kształtowanie wyniku

SELECT odpowiada za ostateczny kształt zbioru wynikowego czyli prezentowanie wyników. Możemy dokonać wyboru interesujących nas kolumn (selekcja pionowa), przekształceń (np. dodać do siebie wartości dwóch kolumn) czy wywoływać funkcje skalarne.

Pełne możliwości tego kroku opisuje w rozdziale poświęconym SELECT. Warto wspomnieć o dwóch dodatkowych operatorach, które mogą się w SELECT pojawić. DISTINCT – czyli usuwanie duplikatów oraz TOP – ograniczenie wyników.

Z punktu widzenia kolejności wykonywanych kroków, najpierw wykonywane są wszelkie działania mające na celu ostateczne określenie wartości kolumn (np. złączenia stringów czy obliczenia matematyczne). Następnie, jeśli jest stosowane polecenie DISTINCT – usuwane są wszystkie duplikaty. Polecenie TOP – wykonywane jest na samym końcu przetwarzania kwerendy, czyli po sortowaniu.

Wynik działania naszej kwerendy – kroku SELECT – czyli zawartość VT5 będzie następująca :

SELECT Zespol , COUNT( IdPrac ) AS LiczbaPracowników   -- wynik VT5FROM dbo.Pracownicy 
WHERE Dzial = 'IT'
GROUP BY Zespol
HAVING COUNT ( IdPrac ) > 1 
ORDER BY LiczbaPracowników
VT5 - wynik SELECT

VT5 – wynik SELECT

Jeśli nadajesz tutaj aliasy (alternatywne nazwy kolumn), widoczne one będą tylko w ostatnim kroku – ORDER BY. Przecież te nazwy, to nic innego jak atrybuty tabeli VT5 – czyli produktu przetwarzania SELECT. Taki zapis będzie więc nieprawidłowy :

-- alias LiczbaPracownikow, znany będzie dopiero w kroku
-- następujących po SELECT czyli w ORDER BY
SELECT z.Nazwa as Zespol , COUNT(*) as LiczbaPracownikow,  LiczbaPracownikow + 1
......

Krok 6 : ORDER BY – sortowanie wyniku

Sortujemy oczywiście efekt kroku SELECT, czyli tabelę VT5. Tylko tutaj w kwerendzie, możemy odwoływać się do zastosowanych w SELECT aliasów nazw kolumn.

SELECT Zespol , COUNT( IdPrac ) AS LiczbaPracowników
FROM dbo.Pracownicy 
WHERE Dzial = 'IT'
GROUP BY Zespol
HAVING COUNT ( IdPrac ) > 1 
ORDER BY LiczbaPracowników

W tym kroku otrzymujemy finalny rezultat przetwarzania kwerendy – posortowany w określony sposób zbiór elementów (domyślnie rosnąco ASCending).

Finalny rezultat kwerendy

Finalny rezultat kwerendy

Wyjątkiem jest stosowanie w SELECT polecenia TOP – ograniczające ilość zwracanych rekordów. Wtedy, wykonywana jest dodatkowa filtracja ze względu na istnienie tego polecenia.

Jednym z ciekawszych możliwości ORDER BY, jest wprowadzona w dialekt T-SQL (SQL Server 2012) możliwość stronnicowania wyników – opisuję to w artykule poświęconym ORDER BY.


Podsumowanie

W SQL Server dostępnych jest szereg rozszerzeń funkcjonalnych pisania kwerend. Na przykład elementy składniowe stosowane do tworzenia i przeszukiwania dokumentów XML (FOR XML, XQuery, OPENXML) czy tabel przestawnych (PIVOT, UNPIVOT).

Nie stanowią one jednak fundamentu logiki przetwarzania zapytań.

Opisany powyżej proces, ma zastosowanie do wszystkich typowych zapytań. Oczywiście można je mocno skomplikować, stosując np. zapytania skorelowane czy podzapytania. Jednak ich przetwarzanie i tak odbywa się zgodnie z opisanym powyżej sposobem.

Kolejnym krokiem w poznawaniu SQL, jest wiedza na temat wymienionych tu bloków funkcjonalnych oraz zasad łączenia tabel. Opisuję te zagadnienia w kolejnych rozdziałach tego kursu.

HAVING – filtrowanie grup

Jest to trzeci i ostatni krok, w którym możemy filtrować elementy zbioru wynikowego. Różni się zasadniczo od poznanych do tej pory, związanych z selekcją wierszy we FROM (gdzie filtrem są warunki złączeń z innymi tabelami) oraz WHERE.

(5)     SELECT
(1)	FROM
(2)	WHERE
(3)	GROUP BY
(4)     HAVING(6)     ORDER BY

Operacja grupowania opisana w artykule na temat GROUP BY, wprowadza pewne ograniczenia. W każdym kolejnym kroku po GROUP BY, odwoływać się możemy bezpośrednio tylko do atrybutów (kolumn) sekcji grupującej. Do pozostałych kolumn (sekcji danych surowych) tylko za pośrednictwem funkcji agregujących.

HAVING jest kolejnym krokiem po GROUP BY – działamy zatem na całych grupach wierszy. Jest to tak zwana selekcja pozioma grup wierszy. Warunki określone w WHERE, traktujemy jako selekcję poziomą pojedynczych rekordów.


Sposób działania HAVING

Najlepiej pokazać to na przykładzie. W naszym scenariuszu, będziemy działać na bazie Northwind. Załóżmy że potrzebujemy wyciągnąć informacje o miastach w Brazylii, w których mamy więcej niż jednego Klienta (z tabeli dbo.Customers).

Pierwszym krokiem, będzie selekcja pozioma wierszy (WHERE) do odfiltrowania wszystkich Klientów z Brazylii. Działanie tego filtra jest proste, wybieramy precyzyjnie tylko te pojedyncze rekordy, których Country = ’Brazil’. Fragment działania filtra w WHERE obrazuje poniższy zrzut :
Having_01
Otrzymamy wyselekcjonowane wiersze zgodnie z definicją filtra w WHERE.

select Country, CIty, CustomerID , ContactName, CompanyName  
from dbo.Customers
WHERE Country = 'Brazil'

Having_02
Kolejnym krokiem analizy naszych danych, niech będzie wyciągnięcie informacji o liczbie Klientów z danego miasta w Brazylii.

select CIty, COUNT(CustomerID) as CustQty
from dbo.Customers
WHERE Country = 'Brazil'
GROUP BY City

Having_03
Filtrowanie w HAVING, polega na filtrowaniu całych grup rekordów. Zgodnie z zasadą opisaną na początku artykułu, możemy filtrować po kolumnach grupujących lub pozostałych, za pośrednictwem funkcji agregujących. W tym momencie, chcemy właśnie filtrować grupy rekordów, ze względu na ilość elementów (liczby klientów) w ich ramach.

Cel ten zrealizuje filtrowanie za pomocą HAVING. Filtrem będzie wynik funkcją agregującej COUNT(), wybierający tylko te grupy, dla których ilość wierszy (Klientów) będzie większa od 1.

select City, COUNT(CustomerID) as CustQty
from dbo.Customers
WHERE Country = 'Brazil'
GROUP BY City
HAVING COUNT(CustomerID)>1

Having_04
Tworzenia filtrów w HAVING podobnie jak w WHERE, umożliwia łączenie wielu warunków ze sobą, za pomocą operatorów logicznych AND i OR. Powyższe zapytanie, moglibyśmy również zapisać w ten sposób :

select Country,City, COUNT(CustomerID) as CustQty
from dbo.Customers
GROUP BY Country, City
HAVING Country = 'Brazil' AND COUNT(CustomerID) >1

Wynik działania będzie identyczny. Zauważ jednak, że istnieje różnica logiczna w jego przetworzeniu. Przynajmniej teoretycznie, w tym przypadku, całość filtracji odbędzie się tylko w kroku HAVING.
W praktyce optymalizator i tak zastosuje filtrację w pierwszym kroku przetwarzania, minimalizując liczbę rekordów, które będzie przetwarzał w kolejnych etapach. Zobaczyć można to na planie wykonania. Obydwa zapytania posiadają identyczny plan.
Having_06
Having_05


Podsumowanie

Ważne jest abyś dobrze zrozumiał, w jaki sposób wykonywane są zapytania. Pozwala to zapobiec popełnianiu, najtrudniej wykrywalnych błędów logicznych.

Pamiętać należy również, że funkcje agregujące pomijają w kalkulacjach wartości null. Więcej na temat grupowania, having oraz stosowania funkcji agregujacych znajdziesz w rozdziale opisującym szerzej, praktyczne aspekty pisania zapytań.

GROUP BY – grupowanie danych

Grupowanie danych polega na tworzeniu grup rekordów w oparciu o definicję grupowania (klauzulę GROUP BY). Krok ten, wykonywany jest jako kolejny po filtrowaniu rekordów, zgodnie z warunkami określonymi w WHERE (o ile w ogóle cokolwiek filtrujemy), lub bezpośrednio po FROM jeśli nie korzystamy z selekcji wierszy.

(5)     SELECT
(1)	FROM
(2)	WHERE
(3)	GROUP BY(4)     HAVING
(6)     ORDER BY

Aby dobrze zrozumieć możliwości jakie daje nam grupowanie rekordów, trzeba poznać sposób, w jaki jest ono realizowane przez silnik relacyjny.

W przypadku stosowania GROUP BY, w trakcie przetwarzania zapytania, tworzone są dwie sekcje danych.

Sekcja grupująca składa się z atrybutów tworzących definicję grupowania (są to kolumny wyszczególnione w GROUP BY). Grupy tworzone są przez wiersze, które mają takie same wartości w ramach tej sekcji. Grupa może być tworzona przez jeden (jeśli jest unikalny w ramach definicji grupy) lub wiele wierszy, w przypadku gdy posiadają te same wartości w kolumnach po których grupujemy.

Sekcja danych surowych (raw data section) zawierają wszystkie pozostałe dane, które mogą być już unikalne dla każdego wiersza w ramach sekcji grupującej. Sekcja danych surowych jest zawsze rozpatrywana w kontekście grupy.


Przykład grupowania rekordów

Najłatwiej zaprezentować ten podział na konkretnym przypadku. Spójrz na fragment danych w tabeli dbo.Employees, przez pryzmat przyszłej operacji grupowania. Będziemy chcieli, pogrupować pracowników po ich stanowisku pracy (title) oraz miejscu zatrudnienia (country). Firma posiada dwie placówki – jedna w USA, druga w UK.

USE Northwind
GO
 
select Title, Country, LastName, FirstName, 
	cast(BirthDate as date) BirthDate, cast(HireDate as date) HireDate
from dbo.Employees
ORDER BY Country,Title;

Group_01
Grupując dane po kolumnach Title i Country, zgodnie z definicją mechanizmów grupujących, utworzone zostaną dwie sekcje.

Sekcja grupująca – będzie składała się z elementów unikalnych (tworzących faktyczne grupy), opisana za pomocą dwóch atrybutów grupujących Title i Country. Takich grup będziemy mieli 5. Dwie, które zawierają po trzy elementy (ponieważ są ciekawsze, zaznaczyłem je ramkami czerwonymi) oraz 3 jednoelementowe (np. Sales Manager z UK).

W skład sekcji danych surowych, wchodzą wszystkie pozostałe kolumny. Każda grupa sekcji grupującej jest bezpośrednio powiązana z właściwą sobie – grupą danych surowych (zaznaczyłem dwie ciekawsze na niebiesko – widać że zawierają unikalne w ramach grupy, wartości).

Grupowanie rekordów w praktyce

Wiemy już na jakich danych będziemy działać i jak w teorii będą wyznaczone sekcje. Napiszmy więc zapytanie, które pogrupuje rekordy ze względu na stanowisko i miejsce zatrudnienia.

select Title, Country, COUNT(*) as EmQty
from dbo.Employees
GROUP BY Country,Title

Group_02
Korzystając z możliwości grupowania, musimy być świadomi pewnej logicznej konsekwencji. We wszystkich kolejnych krokach przetwarzania zapytania następujących po klauzuli GROUP BY (czyli w HAVING, SELECT, ORDER BY), będziemy mogli bezpośrednio wybierać tylko dane z kolumn sekcji grupującej. Wszystkie pozostałe kolumny, zawarte w sekcji surowej, mogą być wyciągane i przetwarzane tylko za pośrednictwem funkcji agregujących.

W naszym przykładzie, w SELECT możemy odwołać się bezpośrednio tylko do kolumn Title i Country.

Zwróć uwagę na to, że w wyniku grupowania, otrzymaliśmy zbiór 5 elementów. Zasada dostępu bezpośredniego tylko do danych z atrybutów grupujących jest moim zdaniem intuicyjna. W kontekście np. elementu Sales Representative z UK – w skład tej grupy wchodzą 3 wiersze. Pokazałem to za pomocą funkcji COUNT(), użytej w tym zapytaniu. Jest to także widoczne w poprzednim przykładzie, gdzie prezentowałem wszystkie dane na których będę działał (ramka niebieska – właściwa dla tej grupy rekordów).

Próbując wyciągnąć w SELECT bezpośrednio informację z kolumny HireDate, skąd silnik relacyjny miałby wiedzieć, o którą wartość nam chodzi. W ramach grupy są trzy rekordy i każdy z nich posiada inną wartość ‘1993-10-17’, ‘1994-01-02’ oraz ‘1994-11-15’.

Język SQL jest oparty na matematycznej teorii zbiorów w której (prawie) nie ma przypadków. Jest ściśle określony. Dlatego w każdym kroku po GROUP BY, bezpośrednio mamy dostęp tylko do kolumn tworzących grupę (wyszczególnionych w GROUP BY), a do pozostałych kolumn, możemy odwoływać się tylko poprzez funkcje agregujące.

Pomimo tego „ograniczenia”, mamy możliwość wykonywania dowolnych przekształceń i działań na danych surowych, przed wykonaniem agregacji.

W kolejnym przykładzie, obliczam średni wiek (AVG) pracownika w ramach grupy, a także pokazuje informację o najstarszym (MIN) i najmłodszym z nich (MAX). Korzystam tu także z dwóch skalarnych funkcji wbudowanych – YEAR(), która wyciąga rok z daty oraz GETDATE() – zwracająca aktualny czas systemowy.

SELECT Title, Country, COUNT(*) as EmQty,
	AVG( YEAR(Getdate()) - YEAR(BirthDate) ) as AvgAge,
	MAX( YEAR(Getdate()) - YEAR(BirthDate) ) as MaxAge, 
	MIN( YEAR(Getdate()) - YEAR(BirthDate) ) as MinAge 
from dbo.Employees
GROUP BY Country,Title
ORDER BY AvgAge

Group_03
Zauważ, że w ORDER BY, odwołuje się do aliasu wyniku działania funkcji AVG(). Jest to niejako potwierdzenie, że krok ORDER BY wykonywany jest jako ostatni – po SELECT.


Podsumowanie

Zaprezentowane powyżej sposób działania GROUP BY jest esencją grupowania rekordów. Pamiętać należy, że tworzone są sekcje atrybutów grupujących i danych surowych. Nie ma znaczenia czy grupujemy najpierw po kolumnie ‚x’ a potem po ‚y’. W teorii zbiorów, kolejność elementów oraz atrybutów z definicji nie ma znaczenia.

W każdym kolejnym kroku przetwarzania zapytania po GROUP BY, bezpośredni dostęp mamy do kolumn grupujących (wymienionych w klauzuli GROUP BY). Na pozostałych działamy za pomocą funkcji agregujących, które szerzej opisuję w artykule im poświęconym.

SQL Server 2008 R2 oferuje też inne sposoby grupowania rekordów. Bazują one wszystkie na zasadzie przedstawionej powyżej i są tylko rozszerzeniem mechanizmu grupowania. Ułatwiają i optymalizują zadania wielokrotnego grupowania (GROUPING SETS, CUBE, ROLLUP).

WHERE – filtrowanie rekordów

Tworzenie warunków filtracji

Drugim etapem przetwarzania zapytań, po określeniu zbiorów źródłowych i relacji między nimi w klauzuli FROM, jest możliwość filtracji (wyszukiwania) konkretnych elementów.

(5)     SELECT
(1)	FROM
(2)	WHERE(3)	GROUP BY
(4)     HAVING
(6)     ORDER BY

Wymagania określane w WHERE, są również drugim miejscem w którym możemy dokonywać selekcji wierszy. Dla przypomnienia, pierwszym jest klauzula FROM wraz z określonymi warunkami łączenia tabel.

Prześledźmy możliwości WHERE, zaczynając od najprostszych warunków wyszukiwania rekordów.


Operatory porównania

Stosowanie WHERE jest intuicyjne. Najczęściej używana filtracja, czy też wyszukiwanie określonych rekordów, bazuje na podstawowych, znakowych operatorach porównania (=,>,<, <>, >=,<=). Czasem stosuje się ich aliasy np. symbol „różny od” != oraz <> są synonimami, podobnie jak !> i <= (nie większy niż i mniejszy lub równy). Rzadziej stosowanymi są operatory negacji ~, czy bitowe & AND, | OR oraz ^ XOR. Warunek selekcji określamy za pomocą wyrażenia, wiążącego zazwyczaj wartości kolumny z wartością porównywaną.

select Name, Color, ListPrice
from Production.Product
where Color = 'White'

WHERE_01
Filtrację tworzy się w prosty sposób, określając jeden lub wiele warunków dla danych atrybutów jakie mają zostać spełnione.

Każdy z nich, musi stanowić logicznie spójną, niezależną strukturę porównywania wartości w określony sposób. Jeśli chcesz określić ich kilka, trzeba połączyć je za pomocą operatorów logicznych AND – jeśli wszystkie, lub OR, jeśli przynajmniej jeden z nich ma być spełniony.

select Name, Color, ListPrice
from Production.Product
-- niepoprawne byłoby określenie np. Color = 'WHITE' OR 'Grey'
-- zawsze muszą to być niezależne w pełni poprawne warunki, stąd :
WHERE Color = 'White' OR Color = 'Grey'

WHERE_02
Tworzenie ciągu logicznych warunków, może wymagać stosowania nawiasów. Szczególnie, jeśli chcesz aby konkretne wymagania były spełnione opcjonalnie (OR) czy obligatoryjnie (AND).

Napiszmy więc zapytanie, które zwróci nam wszystkie produkty kolorów białego lub szarego i ceną, każdego z tych produktów większą niż 9. Na początek bez nawiasów :

select Name, Color, ListPrice
from Production.Product
WHERE Color = 'White' OR Color = 'Grey' and ListPrice > 9

WHERE_02
Powyższe zapytanie, zwróci wszystko co jest białe. Dodatkowo, operator OR umożliwi także wybranie wszystkich elementów szarych, ale z ceną większą niż 9. Ponieważ, nie określiliśmy jawnie w jaki sposób mają być traktowane poszczególne warunki, wszystkie wymagania które połączone są operatorem AND zostaną potraktowane razem (obligatoryjnie). Pozostałe, zostaną rozpatrzone osobno.

Jest to powód, przez który do zbioru wynikowego dostały się elementy, których cena jest mniejsza niż 9. Silnik bazodanowy przetwarzający zapytanie, potraktował warunki AND łącznie, czyli poprzedni zapis bez nawiasów będzie równoznaczny z poniższym :

WHERE Color = 'White' OR ( Color = 'Grey' and ListPrice > 9 )

Tłumacząc na polski – wybierz wszystkie elementy (koloru białego) lub (koloru szarego i ceną większą niż 9).

Właściwie zapisany warunek filtracji w przypadku naszego scenariusza, powinien wyglądać następująco :

select Name, Color, ListPrice
from Production.Product
WHERE ( Color = 'White' OR Color = 'Grey' ) and ListPrice > 9

WHERE_03
Brak, lub niepoprawne stosowanie nawiasów, jest prawdopodobnie jednym z najczęstszych błędów logicznych w pisaniu zapytań, popełnianych przez początkujacych użytkowników.

Operacje porównania, muszą być spójne w zakresie kompatybilności typów danych. Logicznym jest, że nie ma za bardzo sensu porównywanie np. wartości liczbowych przechowywanych w kolumnie typu int z ciągami znakowymi o ile nie jest możliwa konwersja niejawna (automatyczna).

Warunki filtracji, mogą być budowane za pomocą wyrażeń, jak również, możemy tu stosować dowolne funkcje skalarne. Należy jednak pamiętać, że stosowanie ich, może znacząco wpłynąć na wydajność wykonywanych zapytań. Aspekty związane z optymalizacją w tym zakresie i dobrymi praktykami, opisuję w ostatnim rozdziale tego kursu.

select Name, Color,  ListPrice,  StandardCost ,  ListPrice - StandardCost as CostDiff
from Production.Product
where ListPrice > StandardCost + 1000

WHERE_04

Filtrowanie wartości znakowych (stringów)

Oprócz typowej selekcji rekordów, których kolumny zawierają wartości tekstowe bazującej na operatorze równości (=) , możemy stosować również operatory porównania – ><, > lub <. Ciekawsze możliwości oferuje filtrowanie stringów za pomocą wzorców (maski) określanych w LIKE.

Maskę dopasowania, tworzymy za pomocą prostych konstrukcji. Prześledźmy kilka przykładów :

  • symbol „%” – zastępuje dowolną liczbę znaków.
    -- wszystkie rekordy, dla których wartość kolumny City zaczyna się od „par”
    select distinct city
    from Person.Address
    where City LIKE 'par%'

    WHERE_LIKE_01

  • Inny przykład, ze stosowaniem % – znajdź wszystkie rekordy, dla których wartość kolumny City zwiera ciąg znaków „par” (plus dowolna liczba znaków przed i po)
    select distinct city
    from Person.Address
    where City LIKE '%par%'

    WHERE_LIKE_02

  • symbol „_” – zstępuje jeden dowolny znak
    -- wszystkie miasta które zaczynają się od dowolnego znaku,
    -- zawierają na pozycji 2-4 ciąg „par” 
    select distinct city
    from Person.Address
    where City LIKE '_par%'

    WHERE_LIKE_03

  • Określenie zakresu znaków na wskazanej pozycji w nawiasach klamrowych []. Zakres może być ciągły [a-z] lub stanowić listę możliwych znaków [adg]. Może też określać, wykluczenie konkretnych znaków – stosujemy wtedy symbol ^. Wszystkie rekordy zawierające w kolumnie City ciąg znaków „ringt” lub „ringd”.
    select distinct city
    from Person.Address
    where City LIKE '%ring[td]%'

    WHERE_LIKE_04

  • a teraz z wykluczeniem tych ciągów znaków, czyli wszystko co zawiera ring i kolejnym znakiem nie jest t lub d.

    select distinct city
    from Person.Address
    where City LIKE '%ring[^td]%'

    WHERE_LIKE_05

  • Określanie zakresu może być wykonane dla kilku znaków. Na przykład, poniższe zapytanie zwraca wszystkie elementy zaczynające się na a lub b, i mające drugą literę z przedziału n – p.

    select distinct city
    from Person.Address
    where City LIKE '[ab][n-p]%'

    WHERE_LIKE_06

Porówywanie wartości tekstowych odbywa się zawsze w określony sposób – zgodny z regułami określonego alfabetu. Ponieważ istenieje wiele reguł (języków), mamy możliwość wyboru konkretnej z nich. Garść informacji na temat COLLATION (czyli właśnie sposobu porównywania znaków) znajdziesz w artykule dotyczącym sortowania wyników.

Filtrowanie zakresów za pomocą BETWEEN … AND

Przeszukiwania wartości liczbowych czy dat, wykonywane jest często w ramach zakresów. Dwa poniższe zapisy są równoważne :

SELECT SalesOrderNumber, OrderDate, TotalDue
FROM Sales.SalesOrderHeader
WHERE TotalDue >= 870 AND TotalDue  <= 1000
And  OrderDate >= '2002-01-01' AND OrderDate <= '2002-06-30'
 
SELECT SalesOrderNumber, OrderDate, TotalDue
FROM Sales.SalesOrderHeader
WHERE TotalDue BETWEEN 870 AND 1000
and OrderDate BETWEEN '2002-01-01' AND'2002-06-30'

WHERE_BETWEEN_01
Operator „BETWEEN AND” określa przedział obustronnie domknięty dla którego chcemy akceptować wiersze w zbiorze wynikowym. Może odnosić się do różnych typów danych – liczb, dat czy stringów.

Filtrowanie według wartości z listy – IN, ANY (SOME), ALL

Kolejnym sposobem filtrowania wartości jest odniesienie do listy wartości za pomocą operatorów IN, ANY, ALL.

IN oraz ANY (SOME) – logiczny ciąg warunków OR

Sposób ten, jest tłumaczony na szereg operacji logicznych z zastosowaniem OR.

select FirstName, LastName
from Person.Person
where FirstName in('Joan', 'John' ,'Joanna','Johnny')
 
-- powyższe jest równoznaczne z 
select FirstName, LastName
from Person.Person
where FirstName = 'Joan' or FirstName =  'John' or FirstName = 'Joanna' 
or FirstName = 'Johnny'

WHERE_IN_01
Elementy listy, mogą być podane jawnie, tak jak powyżej, lub mogą być wektorem zwracanym przez podzapytanie. Musi to być wektor, czyli zbiór elementów, opisanych jednym atrybutem (kolumną).
Napiszmy więc zapytanie, które zwróci nam tylko te rekordy, które później będziemy chcieli użyć na liście IN :

	select distinct FirstName
	from Person.Person
	where FirstName like 'Jo[ah]%' and LEN(FirstName)<7

WHERE_IN_02
Pełne zapytanie, korzystające z tego wektora wartości, określającego akceptowalną listę, będzie wyglądało następująco (wynik będzie dokładnie taki sam jak poprzednio) :

select FirstName, LastName
from Person.Person
-- Podzapytanie zwraca 4 elementy 
-- zatem będzie to równoważne z where FirstName IN ('Joan', 'John' ,'Joanna','Johnny')
where FirstName IN
(
	select distinct FirstName
	from Person.Person
	where FirstName like 'Jo[ah]%' and LEN(FirstName)<7 
)

WHERE_IN_01
W tym przykładzie, użyłem dodatkowo funkcji LEN(), która zwracająca długość ciągu tekstowego. Filtruję tu wszystkie imiona krótsze niż 7 znaków, zaczynające się od ‘Joa’ lub ‘Joh’. Distinct, usuwa ze zbioru wynikowego wszystkie duplikaty.
Co istotne, operator IN tłumaczony jest tylko i wyłącznie na operator ‘=’ równości. Żeby skorzystać z innych (np. > lub <) należy użyć operatora ANY (ewentualnie jego synonimu SOME – który działa dokładnie tak samo). Przykład użycia ANY – wybieramy rekordy, które są większe lub równe od przynajmniej jednej wartości zwracanej przez podzapytanie :

SELECT Name,ListPrice
FROM Production.Product
WHERE ListPrice >= ANY
    (
     SELECT MAX (ListPrice)
     FROM Production.Product
     GROUP BY ProductSubcategoryID)

WHERE_ANY_01

ALL – logiczny ciąg AND

Ostatnim z operatorów działających na listach, jest ALL, który tłumaczony jest na szereg warunków AND. Zatem porównanie w filtrze z uzyciem ALL, zakłada, że dana wartość jest np. większa bądź równa, od każdego elementu listy zwracanej przez podzapytanie :

SELECT Name, ListPrice
FROM Production.Product
WHERE ListPrice >= ALL
    (
      SELECT MAX (ListPrice)
      FROM Production.Product
      GROUP BY ProductSubcategoryID
     )

WHERE_ALL_01
Jeśli na liście jest więcej niż jeden element, stosowanie = ALL, jest błędem logicznym, bo żadna z wartości nie spełni takiego ciągu warunków.

Podzapytania, funkcje skalarne w WHERE

Jak już zdążyłeś pewnie zauważyć, możliwe jest stosowanie podzapytań w klauzuli WHERE.
Jest to generalna zasada elastyczności języka SQL. Jeśli zadbamy aby w określonym miejscu, pojawiły się wartości określonego (oczekiwanego) typu np. wartości skalarne to wszystko będzie zgodne z jego strukturą. Pisząc więc warunek :

WHERE kolumna = 'wartosc'

Mamy do czynienia z porównaniem wartości skalarnej, przechowywanych w określonej kolumnie z inną wartością skalarną. Zaznaczam, że nie musi to być wartość stała. Może to być zmienna (np. w skrypcie, czy definicji procedury składowanej)

WHERE kolumna = @zmienna

lub wartość zwracana przez podzapytanie czy też funkcję.

-- podzapytanie zwracające wartość skalarną
SELECT ListPrice
FROM Production.Product
WHERE Name = 'Women''s Mountain Shorts, S'
 
-- filtrowanie w WHERE za pomocą subquery (skalar)
SELECT Name, ListPrice
FROM Production.Product
WHERE ListPrice =
    (
		SELECT ListPrice
		FROM Production.Product
		WHERE Name = 'Women''s Mountain Shorts, S'
    )
Zapytanie :
WHERE_SUB_01
Podzapytanie :
WHERE_SUB_02

Jeśli dane podzapytanie, w miejscu oczekiwanej wartości skalarnej, zwróci więcej niż jeden element – otrzymasz komunikat o błędzie :

SELECT Name, ListPrice
FROM Production.Product
WHERE ListPrice =
    (
		SELECT ListPrice
		FROM Production.Product
 
    )
Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Podobnie jeśli element zwrócony przez podzapytanie, okaże się wartością opisaną przez więcej niż jeden atrybut (nie będzie już skalarem), również otrzymamy błąd:

SELECT Name, ListPrice
FROM Production.Product
WHERE ListPrice =
    (
		SELECT *
		FROM Production.Product
		WHERE Name = 'Women''s Mountain Shorts, S'
    )
Msg 116, Level 16, State 1, Line 7
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Istotne jest więc zrozumienie ogólnych reguł struktur języka SQL, aby w ich ramach czuć się swobodnie w stosowaniu pełnych możliwości jakie oferuje.


Podsumowanie

Etap filtrowania rekordów, operuje na elementach tabeli wirtualnej, powstałej w pierwszym kroku przetwarzania zapytania czyli po klauzuli FROM. Jeśli łączymy wewnętrznie (INNER JOIN) np. dwie tabele, Klienci i Zamówienia, dostępne będą tutaj tylko te rekordy z tabel źródłowych, dla których wynik operacji złączenia zostanie spełniony – będzie równy TRUE. Wyszukiwanie wśród rekordów „odsianych” w kroku łączenia tabel (np. Klientów bez zamówień) w tej sytuacji nie powiedzie się. Musielibyśmy inaczej skonstruować zasady relacji (połączenie zewnętrzne OUTER JOIN), aby na etapie WHERE, mieć dostęp do wszystkich elementów zbioru Klienci.

Tworząc warunki filtracji, mamy do dyspozycji szeroki zakres możliwości oferowanych przez język SQL. Możemy odwoływać się tu do wszystkich kolumn, tabel źródłowych, wyszczególnionych we FROM. Na wartościach które zawierają, dokonywać dowolnych przekształceń i tak otrzymane dane, porównywać z innymi kolumnami lub wyrażeniami. Do przekształceń danych, posłużyć nam mogą wbudowane lub własne funkcje skalarne. Warunki filtracji, mogą być również definiowane w oparciu o podzapytania.

Sortowanie wyniku ORDER BY

Ostatnim krokiem logicznego przetwarzania zapytań jest operacja sortowania, którą możemy określić właśnie w ORDER BY.

(5)     SELECT
(1)	FROM
(2)	WHERE
(3)	GROUP BY
(4)     HAVING
(6)     ORDER BY

W tym miejscu, silnik relacyjny, ma już w pełni uformowany zbiór wynikowy. Pozostało tylko posortowanie go zgodnie z określonymi w ORDER BY wymogami.


Po czym możemy sortować

Zazwyczaj sortujemy zbiór wynikowy według określonych atrybutów (kolumn) elementów, znajdujących się w klauzuli SELECT. Ponieważ jest to kolejny krok po SELECT, silnik relacyjny wykonujący zapytaniem zna także nazwy aliasów które tam mogliśmy użyć.

Możemy zatem odwoływać się zarówno do nazw kolumn, tabel (zbiorów) z których pobieramy dane (wyszczególnione w klauzuli FROM) jak również aliasów które nadaliśmy w SELECT.

USE AdventureWorks2008
GO
-- sortowanie po aliasie i nazwie kolumny źródłowej
select FirstName as Imie, 
	   LastName as Nazwisko,
	   PersonType
from Person.Person
order by Nazwisko, FirstName

ORDER_BY_01
Kolejną możliwością jest sortowanie po numerach kolumn tabeli wynikowej. Poniższy zapis jest tożsamy z poprzednim przykładem – zwróci identyczny wynik.

select FirstName as Imie, 
	   LastName as Nazwisko,
	   PersonType
from Person.Person
order by 2, 1

Nie jest to jednak zalecany sposób z uwagi na możliwość zmiany kolejności kolumn, zatem ich numerów w zbiorze wynikowym. Wystarczy że dopiszemy dodatkową kolumnę, lub zamienimy kolejność w SELECT i wynik sortowania będzie niezgodny z pierwotnym zamierzeniem. Sposób wygodny, ale polecany tylko do szybkich kwerend adhoc, w których nie chce nam się przepisywać nazw kolumn.

W standardzie ANSI SQL:1999 wprowadzono możliwość sortowania nie tylko po kolumnach, które określamy w SELECT. Możemy sortować po dowolnych atrybutach tabel (zbiorów) do których odwołujemy się w klauzuli FROM zapytania, pod warunkiem, że nie używamy DISTINCTa.

-- zauważ, że BusinessEntityID nie występuje w SELECT
select FirstName as Imie, 
	   LastName as Nazwisko,
	   PersonType
from Person.Person
order by Nazwisko, FirstName, BusinessEntityID

ORDER_BY_03
Zauważ, że kolejność rekordów dla osób nazywających się Kim Abercombie, różni się od poprzedniego przykładu – widać to po kolumnie PersonType. Jest zgodna z sortowaniem wewnątrz tej grupy po BusinessEntityID, której w tym zapytaniu nie wyciągam w SELECT.

Pewnym wyjątkiem od tej zasady (oprócz sytuacji z DISINCT), są zapytania w których grupujemy dane. W kwerendach grupujących w wyrażeniu SELECT jak i ORDER BY, możemy odwoływać się tylko do kolumn lub ich aliasów, tworzących grupy oraz do pozostałych kolumn ale tylko poprzez funkcje agregujące. Nie można odwoływać się bezpośrednio do części surowej (RAW data) w ramach grupy – jest to szerzej wyjaśnione w rozdziale tego kursu, dotyczącym grupowania danych.

Podsumowując, możemy sortować po

  • dowolnych kolumnach tabel źródłowych użytych we FROM (pod warunkiem że nie korzystamy z DISTINCT lub w przypadku grupowania – sortujemy po kolumnach tworzących grupy)
  • wyrażeniach tworzących nowe kolumny zbioru wynikowego
  • aliasach użytych w SELECT
  • numerach kolumn zbioru wynikowego

Sortowanie po wartościach losowych

Składnia klauzuli ORDER BY pozwala nadać losowości elementom zbioru. Trochę to może być mylące, ponieważ by definition, każdy zbiór, który nie jest jawnie posortowany jest losowy. Jest to podstawa rozważań teorii zbiorów i tak powinniśmy w ogólności do tematu podchodzić. Jednak owa losowość – jest zawsze w pewien sposób uwarunkowana np. fizycznym składowaniem rekordów (lub sposobem przechowywania danych w pamięci podręcznej). Pamiętajmy, że jeśli tabela posiada indeks klastrowy – jej elementy będą już zawsze fizycznie posortowane według tego indeksu.

Dlatego czasem potrzebujemy nadać dodatkowej losowości np. nowym produktom wyświetlanym na stronie głównej sklepu (żeby przy każdej wizycie był to jakiś losowy podzbiór).

Nadanie losowości elementom w zbiorze możemy wykonać np. z zastosowaniem funkcji NEWID(). Obydwa poniższe zapytania są równoważne w sensie wydajnościowym (plany wykonania będą identyczne) i obrazują one skrót, jaki możemy wykonać sortując wyniki po wartości losowej :

select NEWID() as RandomValue ,FirstName as Imie, 
	   LastName as Nazwisko,
	   PersonType
from Person.Person
order by 1
 
-- obydwa zapytania zwracają ten sam rezultat. W drugim sortujemy po NEWID()
select FirstName as Imie, 
	   LastName as Nazwisko,
	   PersonType
from Person.Person
order by NEWID()

ORDER_BY_04

Sposób sortowania

Operacja sortowania odbywa się zawsze w określony sposób – malejąco lub rosnąco. Możemy sortować po jednej lub kilku kolumnach. Operacja taka wykonywana jest zawsze od lewej do prawej. W naszym przypadku było to najpierw sortowanie po Nazwisku, następnie w ramach podzbiorów (gdzie wartość nazwiska jest taka sama) – sortowanie po kolumnie FirstName.
Domyślnie, sortowanie odbywa się w sposób rosnący (ASC ang. ascending). Dla każdej z kolumn możemy określić właściwy kierunek sortowania rekordów :

  • rosnąco ASC ascending – jest to domyślny sposób i nie trzeba go jawnie określać.
  • malejąco DESC descending – konieczne jest jawne określenie przy atrybucie, po którym chcemy w ten sposób sortować.
-- sortujemy najpierw po nazwisku (rosnąco), następnie po imieniu (malejąco)
select FirstName as Imie, 
	   LastName as Nazwisko,
	   PersonType
from Person.Person
where LastName like 'SUL%'
order by Nazwisko, FirstName DESC

ORDER_BY_05


COLLATION – czyli sposób porównania wartości

Każde sortowanie sprowadza się do wykonania operacji porównania wartości. Porównywanie dwóch liczb jest zawsze jednoznaczne – matematyka nie daje nam w tej materii żadnej interpretacji. Albo coś jest większe, równe ew. mniejsze.

Inaczej sprawa ma się z wartościami tekstowymi – stringami. Istnieją przecież różne alfabety i dialekty, w których panują różne reguły. SQL Server dostarcza możliwości ustawienia reguł porównywania wartości tekstowych na różnych poziomach – np. bazy danych, tabeli czy nawet konkretnej kolumny.

W skryptach, zapytaniach SQL, wszędzie tam gdzie wykonujemy operacje porównywania wartości znakowych, możemy jawnie określić sposób w jaki chcemy to czynić. Do tego celu stosujemy słowo kluczowe COLLATE wraz z nazwą reguły według której będą wykonywane porównania.

Porównywanie wartości tekstowych sprowadza się do zasad ogólnych panujących w danym języku (alfabecie) – a także do określenia w jaki sposób traktowane będą małe / wielkie litery CS lub CI (case sensitivity) oraz czy będziemy rozróżnili znaki akcentowane występujące w niektórych językach AS / AI (accent sensitivity). Istnieją jeszcze dwie inne, ale rzadko stosowane w praktyce własności – KS / KI (kana sensitivity) dotyczy tylko języka japońskiego oraz WS / WI (width sensitivity) – porównywanie tych samych znaków kodowanych na jednym lub dwóch bajtach (UNICODE).

Funkcja systemowa fn_helpcollations(),pokaże nam informacje o wszystkich dostępnych sposobach porównywania (collations) ciągów znakowych.

SELECT Name, Description FROM fn_helpcollations()
where name like '%pol%'

ORDER_BY_06
Sprawdźmy zatem wynik porównywania stringów na przykładzie zawierającym polskie znaki. W tym celu posłużymy stwórzmy tabelkę testową :

create table dbo.collate_test
(
	id int identity(1,1),
	opis nvarchar(10)
)
 
insert into dbo.collate_test(opis)
values (N'Pączek'), (N'Paczek'), (N'pączek'),
(N'paczek'), (N'paCzek'), (N'PACZEK'), (N'PĄCZEK')

Nowo utworzona tabela posiada collation dziedziczone po ustawieniach bazy. Aby sprawdzić jakie faktycznie ma collation, zerknijmy do jej definicji (poprzez widok systemowy sys.columns) :

-- wynik zależny od ustawień bazy danych w której utworzyliśmy tabelę
SELECT c.name, c.collation_name 
FROM sys.columns c 
where OBJECT_ID = OBJECT_ID('dbo.collate_test')
and c.name = 'opis'

ORDER_BY_07

Przykład zastosowania COLLATION – polski alfabet, z uwzględnieniem małych i wielkich liter (a<A) oraz bez wrażliwości na akcent (w polskim języku i tak nie ma znaczenia).
Sortowanie ze wskazanym collation :

select * from collate_test
order by opis collate Polish_CS_AI

ORDER_BY_08
Zauważ, że pomimo wybrania kolacji z accent insensitive, wciąż są rozróżniane polskie znaczki (w sortowaniu a < ą). Nadal działamy w ramach polskiego alfabetu – litery ą, ę itd. nie są akcentami tylko są regularnymi znakami, stąd ustawienia AI/AS nie ma znaczenia. Jeśli chcesz spłaszczyć polski język – pozbyć się ogonków, trzeba użyć specjalnej kolacji Azerskiej, która jest idealna do tego zadania :

select * from collate_test
order by opis collate Azeri_Cyrillic_90_CI_AI

ORDER_BY_08c
Wszystkie rekordy traktowane są płasko jako ‘paczek’ – tak więc zwrócone są w kolejności odpowiadającej fizycznemu zapisowi w tabeli (widać to po ID).

Sprawdźmy szerzej, co wyprawia collation azerskie z polskim alfabetem :

declare @alfabet varchar(100) = 'ĄąĆćĘꣳŃńÓ󌜯żŹź'
 
select @alfabet as Polskie,
@alfabet COLLATE Azeri_Cyrillic_90_CI_AI as Azerskie

POL_AZER


Rozszerzone możliwości ORDER BY

Implementacja ORDER BY w T-SQL do wersji 10.5 (SQL Server 2008R2), obejmuje w zasadzie tylko określenie kolejności w jakiej mają być posortowane kolumny + sposób porównywania (collation). W SQL Server 2012 klauzula ta jest rozszerzona o obsługę stronnicowania wyników – operatory OFFSET i FETCH. W celach demonstracyjnych użyłem funkcji ROW_NUMBER do ponumerowanie rekordów, aby pokazać, które faktycznie ze zbioru wynikowe są zwracane.

OFFSET – czyli przesunięcie o n rekordów – zwraca zbiór wynikowy z pominięciem pierwszych n wierszy :

-- zwracamy wiersze, startując od pozycji 1001 (1000 pierwszych pomijamy zgodnie z zastosowanym
-- sposobem sortowania - po RN)
select ROW_NUMBER() OVER(order by BusinessEntityId) as RN, * 
from [Person].[Person]
ORDER BY RN OFFSET 1000 ROWS

ORDER_BY_11

OFFSET + FETCH – czyli stronnicowanie wyniku. Podajemy ile rekordów pomijamy za pomocą OFFSET a w FETCH określamy rozmiar strony. Chcemy np. wyświetlić czwartą stronę wyników, w ramach której pokazujemy 10 rekordów :

select ROW_NUMBER() OVER(order by BusinessEntityId) as RN, * from [Person].[Person]
 
ORDER BY RN OFFSET 30 ROWS
    --FETCH FIRST 10 ROWS ONLY -– FIRST/NEXT oraz ROW/ROWS
                               -- są synonimami zgodnymi ze standardem ANSI
	                       -- możemy je stosować wymiennie
     FETCH NEXT 10 ROWS ONLY

ORDER_BY_12