Funkcje agregujące i klauzule GROUP BY oraz HAVING

Artykuł ten jest częścią serii Od 0 do TSQL którą znajdziesz tutaj.

W ramach współpracy międzyblogowej zostałem zaproszony do napisania wpisu na seequality.net za co serdecznie dziękuję Adrianowi i Sławkowi. Jako temat pozwoliłem sobie wybrać epizod w ramach serii Od 0 do 500 TSQL zatytułowany Funkcje agregujące i klauzula GROUP BY oraz HAVING.

Niejednokrotnie analitycy stają w obliczu zadania opracowania raportu w oparciu o dane przechowywane w bazie danych. Raporty bardzo często dotyczą pewnych podsumowań w obrębie różnorakich grup. Temat dzisiejszego wpisu to właśnie tworzenie zestawień w oparciu o dane zgrupowane.

W celu zgrupowania danych musimy posłużyć się klauzulą GROUP BY. Od strony faktycznej kolejności wykonywania operacji przez silnik bazodanowy SQL Server grupowanie odbywa się zaraz po wykonaniu wybrania miejsca składowania danych (FROM) i ich filtracji (WHERE). W przypadku kolejności syntaktyki SQL klauzula GROUP BY pojawia się przed klauzulą ORDER BY.

Dane można podsumować wykorzystując kilka tzw. funkcji agregujących, do których zaliczyć można:

  • zliczanie (COUNT() – zwraca int) oraz (COUNT_BIG() – zwraca bigint)
  • sumowanie (SUM())
  • obliczanie średniej (AVG())
  • obliczanie odchylenia standardowego w próbce (STDEV()) oraz w populacji (STDEVP())
  • obliczanie wariancji w próbce (VAR()) oraz w populacji (VARP())
  • wyznaczanie maksimum (MAX())
  • wyznaczanie minimum (MIN())
  • obliczanie sumy kontrolnej grupy (CHECKSUM_AGG() – ignorowanie wartości null)
  • wskazanie czy wyrażenie na liście w GROUP BY jest zagregowane czy nie (GROUPING)
  • obliczenie poziomu agregacji (GROUPING_ID)

Wyposażeni w dotychczasową wiedzę możemy np. chcieć podsumować poszczególne zamówienia pod względem ilości pozycji w bazie AdventureWorks:

1

Za pomocą powyższego zapytania zgrupowaliśmy pozycje zamówień względem numerów zamówień, zliczyliśmy pozycje zamówień w obrębie danej grupy i posortowaliśmy wyniki malejąco od zamówień z najwyższą liczbą pozycji. Zapytanie możemy rozszerzyć o dodatkowe statystyki:

2

Jak można zauważyć w tym przypadku uzyskujemy już bardziej interesujące zestawienie, które oblicza statystyki w obrębie danego zamówienia na podstawie jego pozycji. Wykorzystane funkcje grupujące są funkcjami skalarnymi, co oznacza, że zwracają jedną wartość zadanego typu. Funkcje grupujące nie wymagają użycia klauzuli GROUP BY, co prezentuje poniższy skrypt:

3

A jak zachowują się funkcje agregujące w przypadku wartości nieokreślonych? Sprawdźmy to na przykładzie zawartości tabeli SalesPerson:

4

Silnik bazodanowy obliczając sumę zignoruje wartości nieokreślone, czy też potraktuje je jako zera. Można także zadać sobie pytanie jak zachowa się funkcja COUNT w takim przypadku:

5

Ta własność dotyczy nie tylko funkcji sumy, ale wszystkich funkcji. Przykładowo, poniżej skrypt dotyczący średniej:

6

Przyjrzyjmy się jeszcze jak zachowa się funkcja SUM, gdy jej argumentami będzie różnica dwóch kolumn.Warto odnotować fakt, iż najpierw wykona się pożądane działanie (różnica), a potem dopiero suma z wyniku tego działania. Podejście może to być użyteczne w przypadku gdy obliczamy coś czego składniki nie do końca są sumowalne np. suma wartości sprzedaży na którą składa się iloczyn ilości oraz ceny – nie możemy tego działania wykonać jako suma ilości * suma ceny gdyż ten drugi składnik jest po prostu niesumowalny.

7

Należy zwrócić uwagę, że dokonanie operacji dodawania/odejmowania na poziomie wiersza, gdzie choć jeden z elementów (składnik, odjemna lub odjemnik) jest wartością nieokreśloną da nam w wyniku wartość nieokreśloną. Z kolei funkcja SUM, zgodnie z tym co zostało napisane wcześniej, obliczy całkowitą sumę z wierszy pomijając wartości nieokreślone.

We wcześniejszych wpisach w ramach serii poruszany był temat filtrowania rekordów z wykorzystaniem klauzuli WHERE. Gdybyśmy chcieli jednak ograniczyć wynik do zamówień, których liczba pozycji przekracza określoną wartość, to musielibyśmy się posłużyć klauzulą HAVING. Dlaczego nie WHERE? Klauzula WHERE filtruje wiersze, natomiast klauzula HAVING filtruje grupy. To bardzo ważne, by o tym pamiętać i nie mylić tych dwóch sposobów filtrowania. Przykładowe zapytanie ograniczające zbiór wynikowy do zamówień z minimum 50 pozycjami:

8

Warto tutaj nadmienić że różnica pomiędzy klauzulą WHERE a HAVING dotyczy także kolejności przetwarzania przez silnik Microsoft SQL Server. Klauzula WHERE filtruje wiersze zaraz po dokonaniu operacji FROM. Filtrowanie grup za pomocą klauzuli HAVING odbywa się przed operacją SELECT, tuż po fazie grupowania (GROUP BY). Uproszczona kolejność działania silnika bazodanowego prezentuje się następująco:

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

Nie można zatem używać aliasów wykorzystywać w klauzuli GROUP BY, ponieważ najpierw wykonywana jest faza grupowania wierszy, a wykonanie SELECT przez silnik bazodanowy następuje dopiero później.

Kolejną kwestia, którą warto poruszyć to grupowanie może dotyczyć więcej niż jednej kolumny. Przykładowo zgrupowanie pozycji zamówień względem identyfikatorów klienta i numerów zamówień:

9

Ostatnia ciekawa własność to możliwość wymiennego stosowania DISTINCT i klauzuli GROUP BY, co prezentuje poniższy skrypt:

10

Można używać zatem GROUP BY bez wykorzystania funkcji agregującej.

Poza kolumnami można grupować rekordy względem wyrażeń, np. względem roku zamówienia (użycie funkcji YEAR):

11

Nadmienię tylko, że GROUP BY to tylko jeden ze sposobów grupowania. Można do tego wykorzystać także polecenia ROLLUP, CUBE oraz GROUPING SETS. Jednak jest to temat na zupełnie inny wpis.

Polecane źródła:

https://msdn.microsoft.com/en-us/library/ms173454.aspx

https://edux.pjwstk.edu.pl/mat/118/lec/w6.html

http://www.centrumxp.pl/dotNet/347,12-Funkcje-agregujace.aspx

Piotr Ziuziański

Piotr Ziuziański

Business Intelligence Specialist at JCommerce
MS BI & SQL Server fun

Interesuje się technologiami Business Intelligence Microsoftu od etapu projektowania i modelowania hurtowni danych, projektowania i wdrażania procesów ETL, po implementację kostek analitycznych i wdrożenie wizualnej warstwy raportowej. Autor bloga techniczno-teoretycznego MSBIFUN.PL poświęconego tematyce rozwiązań klasy Business Intelligence ze szczególnym uwzględnieniem platformy danych Microsoft SQL Server. Autor licznych polsko i anglojęzycznych publikacji naukowych z obszaru e-zdrowia i wizualizacji danych.
Piotr Ziuziański

Latest posts by Piotr Ziuziański (see all)

2 Comments

  1. Adrian ChodkowskiAdrian Chodkowski

    Dzięki Piotr za wpis! Artykuł fajnie wpasowuje się w naszą serię 0 to TSQL 🙂

    Reply
    1. Piotr Ziuziański

      Adrian, cała przyjemność po mojej stronie 🙂
      Polecam się na przyszłość, zapraszam wszystkich zainteresowanych do lektury 🙂

      Reply

Leave a Comment

Your email address will not be published. Required fields are marked *