12. Funkcje agregujące

12. Funkcje agregujące

Autor: Grzegorz Chuchra

Opublikowano: 3/15/2006, 12:00 AM

Liczba odsłon: 94845

Temat artykułu jest dość ważnym zagadnieniem w teorii baz danych i na pierwszy rzut oka dość zagmatwanym. Zrozumienie agregatów i sposobu ich działania jest zasadniczą kwestią, dlatego mam nadzieję, że wszyscy bez problemów opanują poniższy materiał.
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.