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 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:

SELECT 
  COUNT(*) as 'Liczba pozycji zamówienia', 
  SalesOrderID as 'Numer zamówienia'
FROM 
  [AdventureWorks2012].[Sales].[SalesOrderDetail]
GROUP BY 
   SalesOrderID
ORDER BY 
   'Liczba pozycji zamówienia' DESC

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:

SELECT
             SalesOrderID as 'Numer zamówienia',
             COUNT(*) as 'Liczba pozycji zamówienia',
             SUM(LineTotal) as 'Suma pozycji zamówienia',
             AVG(LineTotal) as 'Średnia z pozycji zamówienia',
             MAX(LineTotal) as 'Najdroższa pozycja zamówienia',
             MIN(LineTotal) as 'Najtańsza pozycja zamówienia',
             MAX(LineTotal) - MIN(LineTotal) as 'Rozstęp pozycji zamówienia',
             STDEV(LineTotal) as 'Odchylenie od średniej z pozycji zamówienia'
  FROM 
             [AdventureWorks2012].[Sales].[SalesOrderDetail] s
GROUP BY 
             SalesOrderID
ORDER BY 
             'Liczba pozycji zamówienia' DESC

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:

SELECT
             COUNT(*) as 'Liczba pozycji zamówień',
             SUM(LineTotal) as 'Suma pozycji zamówień',
             AVG(LineTotal) as 'Średnia z pozycji zamówień',
             MAX(LineTotal) as 'Najdroższa pozycja zamówień',
             MIN(LineTotal) as 'Najtańsza pozycja zamówień',
             MAX(LineTotal) - MIN(LineTotal) as 'Rozstęp pozycji zamówień',
             STDEV(LineTotal) as 'Odchylenie od średniej z pozycji zamówień'

FROM 
             [AdventureWorks2012].[Sales].[SalesOrderDetail] s
ORDER BY 
             'Liczba pozycji zamówień' DESC

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

SELECT 
      SUM(SalesQuota) AS Query1
FROM 
      [AdventureWorks2012].[Sales].[SalesPerson]

GO

SELECT 
       SUM(SalesQuota) AS Query2
FROM 
       [AdventureWorks2012].[Sales].[SalesPerson]
WHERE 
       SalesQuota IS NULL

GO

SELECT 
       SUM(SalesQuota) AS Query3
FROM 
       [AdventureWorks2012].[Sales].[SalesPerson]
WHERE 
       SalesQuota IS NOT NULL

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:

SELECT 
      COUNT(*) 'Liczba rekordów',
      COUNT(SalesQuota)  AS 'Liczba wszystkich niepustych wartości w kolumnie SalesQuota',
      COUNT(DISTINCT SalesQuota) 'Liczba wszystkich niepowtarzających niepustych wartości w kolumnie SalesQuota'
FROM 
      [AdventureWorks2012].[Sales].[SalesPerson]

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

SELECT 
      COUNT(*) AS Query1
FROM 
      [AdventureWorks2012].[Sales].[SalesPerson]
WHERE 
      SalesQuota IS NOT NULL

-----

SELECT 
       SUM(SalesQuota)/14, 
       AVG(SalesQuota)
FROM 
       [AdventureWorks2012].[Sales].[SalesPerson]

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.

SELECT 
      SUM(MaxQty - MinQty) AS Query1
FROM 
      [AdventureWorks2012].[Sales].[SpecialOffer]

SELECT 
       SUM(MaxQty - MinQty) AS Query2
FROM 
       [AdventureWorks2012].[Sales].[SpecialOffer]
WHERE 
        MaxQty IS NOT NULL OR MinQty IS NOT NULL


SELECT 
        SUM(MaxQty - MinQty) AS Query3
FROM 
        [AdventureWorks2012].[Sales].[SpecialOffer]
WHERE 
        MaxQty IS NULL OR MinQty IS NULL


SELECT 
       *
FROM 
       [AdventureWorks2012].[Sales].[SpecialOffer]
WHERE 
        MaxQty IS NULL 
        OR MinQty IS NULL

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:

SELECT
             SalesOrderID as 'Numer zamówienia',
             COUNT(*) as 'Liczba pozycji zamówienia',
             SUM(LineTotal) as 'Suma pozycji zamówienia',
             AVG(LineTotal) as 'Średnia z pozycji zamówienia',
             MAX(LineTotal) as 'Najdroższa pozycja zamówienia',
             MIN(LineTotal) as 'Najtańsza pozycja zamówienia',
             MAX(LineTotal) - MIN(LineTotal) as 'Rozstęp pozycji zamówienia',
             STDEV(LineTotal) as 'Odchylenie od średniej z pozycji zamówienia'

  FROM 
             [AdventureWorks2012].[Sales].[SalesOrderDetail] s
GROUP BY 
             SalesOrderID
HAVING 
         COUNT(*) > 50
ORDER BY 
        'Liczba pozycji zamówienia' DESC

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ń:

SELECT
             c.CustomerID,
             soh.SalesOrderID as 'Numer zamówienia',
             COUNT(*) as 'Liczba pozycji zamówienia',
             SUM(LineTotal) as 'Suma pozycji zamówienia'          
FROM 
             [AdventureWorks2012].[Sales].[SalesOrderDetail] sod
JOIN 
             [AdventureWorks2012].[Sales].[SalesOrderHeader] soh
ON 
              sod.SalesOrderID = soh.SalesOrderID
JOIN 
              [AdventureWorks2012].[Sales].[Customer] c
ON 
               c.CustomerID = soh.CustomerID
GROUP BY 
         c.CustomerID, soh.SalesOrderID
ORDER BY 
         c.CustomerID, soh.SalesOrderID

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

SELECT 
       [SalesOrderID] AS Query1
FROM 
       [AdventureWorks2012].[Sales].[SalesOrderDetail]


SELECT 
        [SalesOrderID] AS Query2
FROM 
        [AdventureWorks2012].[Sales].[SalesOrderDetail]
GROUP BY 
        [SalesOrderID]


SELECT DISTINCT 
         [SalesOrderID] AS Query3
FROM 
         [AdventureWorks2012].[Sales].[SalesOrderDetail]

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):

SELECT SUM(SubTotal), YEAR(OrderDate)

  FROM [AdventureWorks2012].[Sales].[SalesOrderHeader]

  GROUP BY YEAR(OrderDate)

ORDER BY 2

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

• Author
• Recent Posts

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.

Latest posts by Piotr Ziuziański (see all)

• Funkcje agregujące i klauzule GROUP BY oraz HAVING - October 5, 2016