Funkcje agregujące zwracają podsumowania dla całej tabeli lub grupy wierszy w tabeli. Podsumowaniem może być suma, średnia, ilość wierszy czy nawet wariancja z tabeli lub określonej grupy rekordów.
AVG(nazwa_kolumny) |
Zwraca wartość średnią |
COUNT(*) |
Zwraca liczbę wybranych wierszy |
COUNT(nazwa_kolumny) |
Zwraca liczbe wierszy w zbiorze |
MAX(column_name) |
Zwraca największą wartość w zbiorze |
MIN(column_name) |
Zwraca najmniejszą wartość w zbiorze |
SUM(column_name) |
Zwraca sumę kolumn ze zbioru wierszy |
SELECT COUNT(*) AS
'ContactsCount'
FROM [Person].[Contact]
Ta najprostsza z funkcji agregujących zlicza ilość wierszy w zbiorze wynikowym. Jeśli jednak dodalibyśmy klauzule ograniczającą WHERE, zbiór wynikowy uległby zmianie i podsumowanie również miałoby inną wartość.
SELECT
COUNT(*) AS 'AContactsCount'FROM [Person].[Contact]
WHERE
[FirstName] LIKE 'A%'
Jak widać liczba osób, których imię zaczyna się na literę A stanowi ok. 10% wszystkich osób w tabeli Contact.
Przykłady te wprowadziły nas w obszar funkcji agregujących i
ich natury działania na zbiorach danych. Przejdźmy teraz do ciekawszego
przykładu. Przypuśćmy, że chcemy znaleźć w naszej bazie danych listę dziesięciu
najbardziej popularnych nazwisk. Przeprowadźmy najpierw mały proces myślowy, mający
na celu wyszczególnić kroki, jakie są niezbędne aby otrzymać wynik. Na początku
wypadałoby wylistować wszystkie nazwiska, jakie mamy w bazie danych. Następnie
zliczyć wystąpienia każdego z tych nazwisk. Gdy już będziemy posiadać wyniki
tych operacji, posortujemy je w kolejności malejącej i odetniemy te, które nie
mieszczą się w pierwszej dziesiątce. Przystąpmy więc do zadania.
Aby wylistować wszystkie grupy nazwisk w bazie danych musimy użyć specjalnej
klauzuli GROUP BY. Klauzula ta grupuje dane podsumowania, które
spełniają warunki zawarte w klauzuli WHERE (lub z całej tabeli,
jeśli nie posiadamy klauzuli WHERE), aby zostały zwrócone w
postaci pojedynczego wiersza. GROUP BY zbiera w jeden
wiersz zbiór spełniający wymagania.
SELECT
[LastName]
FROM [Person].[Contact]
GROUP BY [LastName]
Jak łatwo zobaczyć, liczba wierszy wynikowych po dodaniu GROUP BY uległa znacznemu zmniejszeniu. Jest to spowodowane tym, że każda z pozycji, które do tej pory występowały wielokrotnie, ograniczona została do jednego rekordu. Ale co to nam daje? Ano to, iż teraz wystarczy tylko dodać kolejną funkcję agregującą COUNT i mamy już prawie to, co chcieliśmy uzyskać.
SELECT COUNT([LastName]), [LastName]
FROM [Person].[Contact]
GROUP BY [LastName]
Funkcja COUNT użyta w powyższym
zapytaniu, pomimo iż wygląda podobnie, różni się od tej użytej w pierwszym
skrypcie. COUNT(*) zlicza wystąpienie wybranych wierszy, gdy
COUNT(nazwa_kolumny)
zwraca liczbę wartości w wyrażeniu, z wszystkich kolumn lub z różnych wartości
kolumn.
Teraz do naszego zapytania wystarczy zastosować tylko parę zabiegów
kosmetycznych i mamy już gotowy wynik.
SELECT TOP 10 COUNT([LastName]),
[LastName]
FROM [Person].[Contact]
GROUP BY [LastName]
ORDER BY 1 DESC
W celach wyjaśnienia: opcja TOP 10 przy klauzuli SELECT ogranicza zbiór wynikowy do dziesięciu pierwszych wierszy. @STRONA@ Jeśli chcielibyśmy ograniczyć wyniki do trzech wystarczyłoby w miejsce 10 wpisać 3 i już mamy gotowy wynik. Dodatkową klauzulą, z którą do tej pory nie mieliśmy kontaktu, jest ORDER BY. Nie robi ona nic specjalnie ciekawego, za wyjątkiem tego, że porządkuje zapytanie w kolejności rosnącej (domyślnie, jeśli nie podamy parametru) ASC lub malejącej DESC (tak jak jest to w naszym przypadku). Dodatkowym parametrem jest kolumna, po której będzie odbywało się sortowanie. W naszym przypadku chcemy, aby sortowanie odbyło się po ilości wystąpień nazwiska, czyli po pierwszej kolumnie. Jeśli jednak chcielibyśmy posortować wynik w kolejności alfabetycznej, musielibyśmy zmienić kolumnę sortowania na 2, jednakże wynik naszego zapytania uległby zniekształceniu. Jak rozwiązać ten problem, dowiemy się w jednym z następnych artykułów.
Jeśli już umiemy obliczać ilość poszczególnych wystąpień w zbiorze wynikowym oraz rozumiemy, na jakiej podstawie grupowane są dane, przejdźmy do operacji troszeczkę innego typu. Za pomocą funkcji agregujących możemy bez problemu obliczyć średnią wagę produktów dostępnych w bazie danych Adventureworks, lub średni czas jaki produkt jest dostępny w sklepie. Skrypt, który to obliczy wygląda następująco
SELECT AVG([Weight]) * 0.453592
AS 'AverageWeight'
FROM [Production].[Product]
WHERE [Weight] IS NOT NULL
Ponieważ wartość wagi w bazie danych jest zapisana w funtach użyliśmy stałej 0.453592 aby przeskalować wartość na kilogramy.
Teraz przejdźmy do skryptu podsumowującego nasze zmagania z funkcjami agregującymi. Pozwoliłem sobie jeszcze nieco uszczegółowić treść zapytania, które przedstawiłem przed chwilą. Treść brzmi następująco: „wypisz dwa kolory produktów, które znajdowały się najdłużej w sprzedaży wraz ze średnią ilością dni w sprzedaży”. Zapytanie brzmi dość skomplikowanie i troszeczkę niejasno. Chodzi w nim mniej więcej o to, by wyszukać wszystkie produkty określonego koloru i policzyć dni przez ile były dostępne. Następnie ze zbioru wybieramy produkty takiego koloru, który średnio utrzymał się najdłużej w sprzedaży. Zapytanie wygląda w sposób następujący.
SELECT TOP 2
[Color], AVG(DATEDIFF(dd, [SellStartDate], [SellEndDate])) AS
'AverageDaysInSales'
FROM [Production].[Product]
WHERE [SellEndDate] IS NOT NULL AND
[SellStartDate] IS NOT NULL AND [Color] IS NOT NULL
GROUP BY [Color]
Teraz, kiedy już widzimy rozwiązanie, pora przyjrzeć się mu dokładnie. Jedyną nową rzeczą, z którą się tu można spotkać jest systemowa funkcja serwera obliczająca różnicę pomiędzy datami.
DATEDIFF(typ_czasu, data_początkowa, data_końcowa)
Oprócz dat pobiera ona dodatkowo jako argumenty typ czasu w jakim chcemy otrzymać wynik. A jaśniej mówiąc to czy chcemy mieć różnice obliczoną w dniach (dd), miesiącach(MM) czy latach (yyyy). Ponieważ w treści zadania jest napisane, że dodatkowo chcemy wyświetlić ilość dni, przyjmujemy dzień jako przedział do naliczania różnicy. Następnie wyliczamy średnią ilość dni i zadanie zostało rozwiązane.
Przejdźmy teraz do ostatniej
modyfikacji naszego przykładu. Przypuśćmy, że interesują nas tylko te produkty,
których średnia ilość dni w sprzedaży jest większa niż 400. Do rozwiązanie tego
zadania potrzeba nam będzie jeszcze jedna bardzo ważna klauzula HAVING.
Klauzula ta determinuje, które wiersze zostaną zwrócone przez klauzulę GROUP
BY. Ważne jest, aby dobrze zrozumieć zasadę współpracy między funkcjami,
a klauzulami SQL'a WHERE oraz HAVING.
Podstawową różnicą między WHERE i HAVING jest to,
że WHERE filtruje wiersze przed grupowaniem i obliczeniami
(decyduje, które wiersze wejdą do obliczeń funkcji agregujących), podczas gdy
HAVING
selekcjonuje wiersze już pogrupowane, po wykonaniu obliczeń. Dlatego klauzula
WHERE
nie może zawierać funkcji agregujących, ponieważ w gruncie rzeczy nie ma to sensu.
Z drugiej strony, klauzula HAVING zawsze zawiera funkcje
agregujące. Mówiąc wprost, możesz zastosować klauzulę HAVING bez
funkcji agregujących, ale jest wtedy mniej efektywna niż WHERE z
tym samym warunkiem.
Zastosowanie funkcji agregujących jest efektywniejsze, niż dodanie warunku do
HAVING,
ponieważ grupowanie i obliczenia dokonywane są dla wierszy, które przeszły
przez sprawdzenie warunku w WHERE.
SELECT [Color], AVG(DATEDIFF(dd,
[SellStartDate], [SellEndDate])) AS 'AverageDaysInSales'
FROM [Production].[Product]
WHERE [SellEndDate] IS NOT NULL AND
[SellStartDate] IS NOT NULL AND [Color] IS NOT NULL
GROUP BY [Color]
HAVING AVG(DATEDIFF(dd, [SellStartDate],
[SellEndDate])) > 400
ORDER BY 2 DESC
Podsumowanie
Funkcje agregujące są potężnym narzędziem w relacyjnych bazach danych, bez którego nie można wyobrazić sobie pracy. Poprawne zrozumienie i świadome korzystanie z agregatów jest podstawą w pracy zarówno w mały bazach danych, jak i w dużych systemach bazodanowych, gdzie skomplikowane raportowanie jest jedną z najważniejszych ról serwera bazodanowego.