Grupowanie i funkcje agregujące w języku MS SQL.

Grupowanie danych polega na łączeniu wielu wierszy w jeden.

Funkcje grupujące (agregujące) operują na zbiorach, a nie pojedynczych wartościach. Wywołuje się je w klauzuli SELECT, tak samo jak funkcje systemowe, gdyż zwracają one pojedyncze (skalarne) wartości. Mogą pojawić się one także w klauzuli HAVING, ORDER BY.

Funkcji grupujących można używać m.in. do:

  • podsumowywania wyników działań innych poleceń SQL
  • zliczania rekordów w grupie
  • uzyskiwania ogólnych wyników np. średniej ceny produktu
  • podsumowania np. sprzedaży dla różnych kategorii i regionów
  • analizy danych

Najczęściej używane funkcje grupujące:

  • COUNT()
  • SUM()
  • AVG()
  • MIN()
  • MAX()

Funkcja COUNT()

Służy do zliczania wierszy lub wartości w kolumnach. Wywołana jako COUNT(*), zlicza wszystkie rekordy w grupie przechowywane w tabeli włącznie z duplikatami i rekordami zawierającymi wartość specjalną NULL.

Za pomocą poniższego zapytania odczytasz wszystkie kolumny i rekordy z tabeli Person.Person. Tabela zawiera 19 972 rekordów.

SELECT *
FROM Person.Person;

Wynik zapytania

dane z tabeli Person.Person

Za pomocą poniższego zapytania utworzysz jedną grupę z całej tabeli Person.Person. Funkcją COUNT() zliczysz ile jest rekordów w grupie, która została utworzona. Jeżeli wywołasz funkcję COUNT() z gwiazdką, zliczone zostaną wszystkie rekordy, nie wyłączając tych, które zawierają wartość specjalną NULL.

SELECT COUNT(*)
FROM Person.Person;

Powyższe zapytanie grupuje dane, ale nie zawiera klauzuli GROUP BY. Tego typu zapytania nazywane są zapytaniami niejawnie grupującymi dane. SQL Server pozwala na umieszczenie w nich pustej klauzuli GROUP BY:

SELECT COUNT(SalesOrderID)
FROM Sales.SalesOrderHeader
GROUP BY ();

Wynik zapytania

Tabela Person.Person grupowanie

Jest to najprostszy przypadek grupowania – wszystkie wiersze tabeli zostały połączone w jedną grupę. W tym przypadku funkcja grupująca została wywołana tylko jeden raz dla całej tabeli.

Aby zliczyć wszystkie rekordy z wyjątkiem tych, które zawierają NULL, należy wywołać funkcję COUNT() z nazwą kolumny jako jej argumentem. W tym przypadku wyeliminowane zostaną rekordy zawierające NULL. Nie zostaną natomiast wyeliminowane powtarzające się dane tzn. jeśli jest dwóch klientów o nazwisku Kowalski, obydwaj zostaną uwzględnieni w wyniku zapytania.

SELECT COUNT (LastName)
FROM Person.Person;

Aby wywołać funkcję COUNT() tylko dla danych niepowtarzających się, nazwę kolumny stanowiącej jej argument, należy poprzedzić słowem kluczowym DISTINCT. Słowo kluczowe DISTINCT należy umieścić w nawiasie. Stanowi ono część argumentu funkcji grupującej.

SELECT COUNT (DISTINCT LastName)
FROM Person.Person;

Klauzula GROUP BY

Funkcje grupujące mogą być wywoływane dla określonych grup, a nie tylko dla całych tabel. Podział wierszy na logiczne grupy umożliwia klauzula GROUP BY. Podstawą zaklasyfikowania wiersza do danej grupy jest wartość jednej z kolumn lub wynik wyrażenia.

Za pomocą poniższego zapytania utworzysz kilka grup z danych w tabeli Person.Person. Podstawą zgrupowania danych są informacje zawarte w kolumnie PersonType. Wszystkie rekordy zawierające tę samą wartość w kolumnie PersonType zostaną zakwalifikowane do jednej grupy. Funkcją COUNT() zliczysz ilość rekordów w poszczególnych grupach.

SELECT PersonType, COUNT(*)
FROM Person.Person
GROUP BY PersonType;

Wynik zapytania

MS SQL kilka grup

Uwaga! Jeżeli w klauzuli SELECT użyjesz funkcji agregującej np. COUNT(*) i oprócz niej wymienisz jakiekolwiek kolumny lub wyrażenia, które nie są częścią funkcji agregującej – kolumny te i wyrażenia musisz dodać do klauzuli GROUP BY lub zamknąć w funkcji agregującej.

W poniższym przykładzie, w klauzuli SELECT, występuje funkcja agregująca SUM() i kolumna ShipCountry. Kolumna ShipCountry nie jest częścią funkcji agregującej, musi więc być użyta również w klauzuli GROUP BY.

SELECT   o.ShipCountry,    SUM(od.UnitPrice * od.Quantity) as suma 
FROM dbo.Orders o
JOIN dbo.[Order Details] od
ON o.OrderID = od.OrderID
WHERE o.OrderDate BETWEEN '19970825' and '19971016'
GROUP BY o.ShipCountry;

Funkcja SUM()

Zwraca sumę wartości w grupie. Jej argumentami mogą być tylko wartości numeryczne. Funkcja ignoruje wartość specjalną NULL.

Za pomocą poniższego zapytania zsumujesz ilość sprzedanych produktów (kolumna Quantity) dla każdego ID produktu (ProductID).

SELECT ProductID, SUM(Quantity) 
FROM [Order Details]
GROUP BY ProductID;

Wynik zapytania

MS SQL funkcja suma

Funkcja AVG()

Zwraca średnią wartości w grupie. Jej argumentami mogą być tylko wartości numeryczne. Funkcja ignoruje wartość specjalną NULL.

Za pomocą poniższego zapytania wyliczysz średnią ilość sprzedanych produktów dla każdego ID produktu.

SELECT ProductID, AVG(Quantity) 
FROM [Order Details]
GROUP BY ProductID;

Funkcja MIN()

Argumentami funkcji MIN() mogą być dane liczbowe, dane typu daty i czasu oraz dane tekstowe. Funkcja zwraca najmniejszą wartość w grupie. W przypadku dat za najmniejszą uznawana jest najwcześniejsza data.

Za pomocą poniższego zapytania odczytasz minimalną cenę jednostkową (kolumna UnitPrice) dla każdego ID produktu.

SELECT ProductID, MIN(UnitPrice) AS Cena_minimalna
FROM [Order Details]
GROUP BY ProductID;

Funkcja MAX()

Argumentami funkcji MAX() mogą być dane liczbowe, dane typu daty i czasu oraz dane tekstowe. Funkcja zwraca największą wartość w grupie. W przypadku dat za największą uznawana jest najpóźniejsza data.

Za pomocą poniższego zapytania odczytasz maksymalną cenę jednostkową dla każdego ID produktu.

SELECT ProductID, MAX(UnitPrice) AS Cena_maksymalna
FROM [Order Details]
GROUP BY ProductID;

Pozostałe funkcje grupujące:

CHECKSUM_AGG()

Funkcja CHECKSUM_AGG() zwraca sumę kontrolną wartości w grupie. Jej argumentem muszą być liczby całkowite.

Za pomocą poniższego zapytania:

  • funkcją CAST() konwertujesz typ z money na int w kolumnie ListPrice
  • funkcją CHECKSUM_AGG() zwracasz sumę kontrolną dla wartości z kolumny ListPrice
SELECT CHECKSUM_AGG(CAST(ListPrice AS INT))
FROM Production.Product;

Funkcja COUNT_BIG()

Funkcja COUNT_BIG() działa w taki sam sposób jak funkcja COUNT(). Od funkcji COUNT() różni się typem zwracanych wartości. W jej przypadku liczba wierszy w grupie zwracana jest jako dane typu BIGINT.

SELECT COUNT_BIG (*)
FROM Person.Person;

Funkcja STDEV()

Funkcja STDEV() zwraca odchylenie standardowe wartości w grupie.

SELECT STDEV(StandardCost)
FROM Production.Product;

Funkcja STDEVP()

Funkcja STDEVP() zwraca odchylenie standardowe próbki wartości w grupie.

SELECT STDEVP(StandardCost)
FROM Production.Product;

Funkcja VAR()

Funkcja VAR() zwraca wariancję wartości w grupie.

SELECT VAR(StandardCost)
FROM Production.Product;

Funkcja VARP()

Funkcja VARP() zwraca wariancję próbki wartości w grupie.

SELECT VARP(StandardCost)
FROM Production.Product;

Wyrażenia

Funkcje grupujące mogą być częścią wyrażeń. W poniższym przykładzie wyliczamy średni koszt produktu a następnie podwyższamy ten koszt o 23% podatku.

SELECT AVG(StandardCost) as SrKoszt, AVG(StandardCost)*1.23 as SrKosztPodatek
FROM Production.Product;

Zagnieżdżanie funkcji grupujących

Funkcji grupujących nie można zagnieżdżać. Argumentem funkcji grupującej nie może być wynik innej funkcji grupującej.

Klauzula WHERE a funkcje grupujące

W klauzuli WHERE nie można umieścić funkcji grupujących. Podczas fizycznego wykonywania zapytania, najpierw wykonywana jest klauzula WHERE a dopiero później dane są grupowane. Wiersze wyeliminowane w klauzuli WHERE nie zostaną pogrupowane.

Klauzula ORDER BY a funkcje grupujące

Dane są najpierw grupowane, a potem sortowane. Dlatego w klauzuli ORDER BY można umieścić tylko kolumny lub wyrażenia użyte do grupowania lub funkcje grupujące.

Poniższe zapytanie zwróci błąd, gdyż kolumna OrderDate występuje w klauzuli ORDER BY a nie została użyta do grupowania (nie występuje w klauzuli GROUP BY).

SELECT YEAR(a.OrderDate), a.CustomerID, SUM(b.UnitPrice * b.Quantity) 
FROM dbo.Orders a
JOIN dbo.[Order Details] b
ON a.OrderID = b.OrderID
WHERE a.OrderDate BETWEEN '19970825' and '19971016'
GROUP BY YEAR(a.OrderDate), a.CustomerID
ORDER BY a.OrderDate;

Operatory CUBE i ROLLUP

Jeżeli dane są grupowane według wartości kilku kolumn, kolejność występowania kolumn w klauzuli GROUP BY wyznacza podział na grupy i podgrupy.

W poniższym zapytaniu w klauzuli GROUP BY wymienione są kolumny OrderDate i CustomerID. Według tych kolumn utworzone zostały grupy.

SELECT a.OrderDate, a.CustomerID, SUM(b.UnitPrice * b.Quantity) 
FROM dbo.Orders a
JOIN dbo.[Order Details] b
ON a.OrderID = b.OrderID
WHERE a.OrderDate BETWEEN '19970825' and '19971016'
GROUP BY a.OrderDate, a.CustomerID;

Wynik zapytania

MS SQL grupowanie - grupy bez podsumowania

Do wyniku zapytania możemy dodać sumy pośrednie za pomocą operatora ROLLUP

SELECT o.OrderDate, o.CustomerID, SUM(od.UnitPrice * od.Quantity) 
FROM dbo.Orders o
JOIN dbo.[Order Details] od
ON o.OrderID = od.OrderID
WHERE o.OrderDate BETWEEN '19970825' and '19971016'
GROUP BY ROLLUP (o.OrderDate, o.CustomerID);

Wynik zapytania

SQL operator ROLLUP

Operator ROLLUP dodał podsumowania dla grup utworzonych na podstawie wartości pierwszej z kolumn (OrderDate) wymienionych w klauzuli ORDER BY. Dodane przez niego wiersze zawierają wartości NULL w drugiej kolumnie (CustomerID) użytej w grupowaniu. W ostatniej kolumnie, zawierającej wynik działania funkcji grupującej SUM(od.UnitPrice * od.Quantity), znajduje się podsumowanie.

Za pomocą operatora CUBE można dodać do wyniku zapytania podsumowania dla wszystkich możliwych kombinacji grup i podgrup.

SELECT o.OrderDate, o.CustomerID, SUM(od.UnitPrice * od.Quantity) 
FROM dbo.Orders o
JOIN dbo.[Order Details] od
ON o.OrderID = od.OrderID
WHERE o.OrderDate BETWEEN '19970825' and '19971016'
GROUP BY CUBE (o.OrderDate, o.CustomerID);

Wynik zapytania

SQL grupowanie operator CUBE

Funkcje GROUPING i GROUPING_ID

Po wstawieniu podsumowań za pomocą operatorów ROLLUP i CUBE powstają dodatkowe grupy, które są oznaczone za pomocą wartości NULL.

Wartość NULL wstawiona dla oznaczenia grupy jest nieodróżnialna od wartości specjalnej NULL znajdującej się w tabeli. Aby dokonać rozróżnienia, która wartość NULL pochodzi z podsumowań, a która znajdowała się w tabeli można użyć funkcji GROUPING() lub GROUPING_ID().

Funkcja GROUPING() zwraca wartość 1, jeżeli dany wiersz zawiera podsumowania dodane do wyniku przez operatory CUBE lub ROLLUP oraz wartość 0, jeżeli dany wiersz zawiera wartości wyliczone dla danej grupy.

SELECT o.OrderDate, GROUPING(o.OrderDate), o.CustomerID, GROUPING(o.CustomerID),
       SUM(od.UnitPrice * od.Quantity) 
FROM dbo.Orders o
JOIN dbo.[Order Details] od
ON o.OrderID = od.OrderID
WHERE o.OrderDate BETWEEN '19970825' and '19971016'
GROUP BY CUBE (o.OrderDate, o.CustomerID);

Wynik zapytania

SQL funkcja GROUPING

Funkcja GROUPING_ID() zwraca liczbę porządkową podsumowania.

SELECT GROUPING_ID( o.OrderDate, o.CustomerID) , o.OrderDate, o.CustomerID,
       SUM(od.UnitPrice * od.Quantity) 
FROM dbo.Orders o
JOIN dbo.[Order Details] od
ON o.OrderID = od.OrderID
WHERE o.OrderDate BETWEEN '19970825' and '19971016'
GROUP BY CUBE (o.OrderDate, o.CustomerID);

Wynik zapytania

SQL funkcja GROUPING_ID

Operator GROUPING SETS

Klauzula GROUP BY umożliwia wskazanie jednego porządku grupowania. Operator GROUPING SETS rozszerza funkcjonalność klauzuli GROUP BY – umożliwia on zdefiniowanie w jednym zapytaniu wielu różnych porządków grupowania.

SELECT 
  o.OrderDate, 
  o.ShipCity, 
  o.CustomerID, 
  GROUPING_ID(o.OrderDate,  o.ShipCity, o.CustomerID) as gid, 
  --             4               2             1 
  SUM(od.UnitPrice * od.Quantity) as suma , count(*) as liczba_rekordow
FROM dbo.Orders o
JOIN dbo.[Order Details] od
ON o.OrderID = od.OrderID
WHERE o.OrderDate BETWEEN '19970825' and '19971016'
GROUP BY GROUPING SETS
 (
   (o.OrderDate, o.ShipCity, o.CustomerID),
   (o.OrderDate, o.ShipCity),
   (o.OrderDate, o.CustomerID)
 )
 ORDER BY gid;

Wynik zapytania

SQL operator GROUPING SETS

W kolumnie o nazwie „gid” znajduje się wynik działania operatora GROUPING_ID.

  • cyfra 0 oznacza, że żadna wybrana kolumna nie została pominięta w grupowaniu
  • cyfra 1 oznacza, że pominięta została kolumna pierwsza
  • cyfra 2 oznacza, że pominięta została kolumna druga

Operator GROUPING_ID numeruje, od prawej do lewej, kolumny i grupy kolumn użyte do grupowania. W powyższym zapytaniu:

  • cyfra 1 to numer kolumny CustomerID
  • cyfra 2 to numer kolumny ShipCity
  • cyfra 3 reprezentuje zbiór kolumn ShipCity i CustomerID
  • cyfra 4 to numer kolumny OrderDate
  • cyfra 7 reprezentowałaby zbiór kolumn o.OrderDate, o.ShipCity, o.CustomerID

Celem wpisu jest kompletna prezentacja tematu grupowania w MS SQL Server. Jeśli jakiegoś zagadnienia brakuje, napisz do nas maila lub komentarz pod wpisem. Uzupełnimy wpis lub prześlemy do Ciebie informacje mailem.

Każde z zapytań możesz uruchomić w bazie AdventureWorks2012 lub Northwind – w zależności od tego, z której bazy dana tabla pochodzi.

Dodaj komentarz

Twój adres email nie zostanie opublikowany.