Inspiracja , wiedza , realizacja
Jsystems

W przebudowie

Login



Java

Oracle

Linux

Android

PostgreSQL

Microsoft SQL Server

Fukcje agregujące i grupowanie

Rafał Czarkowski
Data dodania: Jun 21, 2016
Data aktualizacji: Jun 21, 2016

Funkcje agregujące


Funkcje agregujące, to takie funkcje, które zwracają jedną wartość wyliczoną na podstawie wielu wierszy. Wszystkie funkcje grupowe ignorują wiersze zawierające wartość null w kolumnie, na której działają.

Opcjonalnie do każdej funkcji można dodać modyfikator distinct co będzie skutkowało przeliczeniem nie uwzględniającym wartości, które się powtórzą.


AVG

avg (wyrażenie) – funkcja oblicza wartość średnią wyrażenia dla wszystkich wierszy.


select avg(ListPrice) from SalesLT.Product;




Wyświetlona została średnia cena produktu.


select avg(distinct ListPrice) from SalesLT.Product;




Wyświetlona została średnia cena produktu, nie uwzględniając powtórzonych wartości.


SUM

sum (wyrażenie) – funkcja oblicza sumę wartości wyrażeń dla wszystkich wierszy.


select sum(distinct ListPrice) from SalesLT.Product;




Wyświetlona została suma cen wszystkich produktów.


select sum(distinct ListPrice) from SalesLT.Product;




Wyświetlona została suma cen wszystkich produktów, nie uwzględniając powtórzonych wartości.


MIN

min (wyrażenie) – funkcje oblicza minimalną wartość wyrażenia, wartość wyrażenia może być liczbą, ciągiem znaków lub datą. W przypadku daty zwraca najwcześniejszą, w przypadku tekstu, pierwszy ciąg uszeregowany wg alfabetu.


select min(ListPrice) from SalesLT.Product;




Wyświetlona najniższa cena produktu.


select min(Name) from SalesLT.Product;




Wyświetlona „najniższa nazwa” produktu (względem porządku alfabetycznego).


select min(SellStartDate) from SalesLT.Product;




Wyświetlona najwcześniejsza data sprzedaży produktu.


MAX

max (wyrażenie) – funkcje oblicza maksymalną wartość wyrażenia, wartość wyrażenia może być liczbą, ciągiem znaków lub datą. W przypadku daty zwraca najpóźniejszą, w przypadku tekstu, ostatni ciąg uszeregowany wg alfabetu.


select max(ListPrice) from SalesLT.Product;




Wyświetlona najwyższa cena produktu.


select max(Name) from SalesLT.Product;




Wyświetlona „najwyższa nazwa” produktu (względem porządku alfabetycznego).


select max(SellStartDate) from SalesLT.Product;




Wyświetlona najstarsza data sprzedaży produktu.


COUNT

count (wyrażenie) - funkcja zwraca ilość wierszy dla których wyrażenie jest różne od null. Zamiast wpisywać nazwę kolumny, można uzyć znaku "*" - wtedy zostanie zliczona liczba wszystkich wierszy w tabeli.


select count(Name) from SalesLT.Product;




Wyświetlona została ilość nazw produktów (podliczenie ilości wierszy dla kolumny Name).


select count(*) from SalesLT.Product;



Wyświetlona została ilość wierszy (podliczenie ilości wierszy dla całej tabeli SalesLT.Product).


W przypadku funkcji agregujących nazwy kolumn również tracą swoją nazwę, dlatego i tu najlepiej użyć aliasów, by później wiedzieć co dana kolumna przedstawia.


select count(*) IlośćWierszy from SalesLT.Product;




Wyświetlona została ilość wierszy (podliczenie ilości wierszy dla całej tabeli SalesLT.Product) z odpowiednim aliasem dla tej kolumny.


Funkcji agregujących możemy używać także kilku na raz w jednym zapytaniu.


select count(*) IlośćWierszy from SalesLT.Product;




W jednym zapytaniu wyświetlone zostały wartości: średnia cena produktu, suma cen produktów, minimalna cena produktu, maksymalna cena produktu oraz ilość wszystkich produktów. Kolumnom zostały nadane odpowiednie aliasy.


Grupowanie


Grupowanie polega na podzieleniu zbioru wierszy na grupy, które mają pewną wspólną cechę. Grupowania dokonuje się w celu zastosowania funkcji agregujących nie w stosunku do całego zbioru wierszy, ale do poszczególnych grup wierszy. W celu zgrupowania rekordów należy dodać nową klauzulę group by wraz z wyspecyfikowaniem kolumny lub wyrażenia, według którego mają być pogrupowane wiersze. Na liście klauzuli select mogą się znaleźć tylko kolumny i wyrażenia, według których zapytanie jest grupowane, oraz wywołania funkcji agregujących.


select count(*) IlośćWierszy, ProductCategoryID 
from SalesLT.Product group by ProductCategoryID;




Wyświetlona została ilość wierszy , która została pogrupowana z uwzględnieniem odpowiedniego identyfikatora kategorii produktu.


Przed grupowaniem można wcześniej wyfiltrować wiersze, które nas interesują, za pomocą klauzuli where,


select count(*) IlośćWierszy, ProductCategoryID from SalesLT.Product 
where ProductCategoryID group by ProductCategoryID;




Wyświetlona została ilość wierszy , z uwzględnieniem produktów, których identyfikator kategorii produktu jest większy rób równy 10, a następnie dane te zostały pogrupowane na odpowiedni identyfikator kategorii produktu.

Grupowania można używać na więcej niż jedną kolumnę lub wyrażenie. Aby tego dokonać po klauzuli group by i pierwszą nazwą kolumny lub wyrażenia, według którego mają być pogrupowane wiersze, dodajemy następną nazwę kolumny lub wyrażenia, po której w ramach grupy powstałej w wyniku pierwszego grupowania, ma nastąpić następne grupowanie. Kolejne kolumny lub wyrażenia oddzielamy przecinkiem.


select count(*) IlośćWierszy, ProductCategoryID, ProductmodelID 
from SalesLT.Product group by ProductCategoryID, ProductmodelID;




Wyświetlona została ilość wierszy , która została pogrupowana z uwzględnieniem odpowiedniego identyfikatora kategorii produktu, a następnie w ramach tych grup, nastąpiło kolejne grupowanie z rozróżnieniem na identyfikator modelu produktu. Dokładnie widać to na wierszach numer 3 i 4.


select count(*) IlośćWierszy, ProductCategoryID, ProductmodelID 
from SalesLT.Product group by ProductCategoryID, ProductmodelID;




Wyświetlona została ilość wierszy , która została pogrupowana z uwzględnieniem odpowiedniego identyfikatora kategorii produktu, a następnie w ramach tych grup, nastąpiło kolejne grupowanie z rozróżnieniem na identyfikator modelu produktu a dalej wiersze zostały pogrupowane z uwzględnieniem odpowiedniego koloru. Dokładnie widać to na wierszach numer 1 i 2.


Operator HAVING


Przed dokonaniem grupowania można zastosować klauzulę where, która wybierze tylko część wierszy z tabeli. Klauzula where wykonuje się przed grupowaniem, a zatem nie można w tej klauzuli sprecyzować warunku zawierającego funkcje grupowe. Aby taki warunek zawrzeć w zapytaniu należy zastosować dodatkową klauzulę having wraz z odpowiednim warunkiem. Jest ona odpowiednikiem klauzuli where, tylko, że wykonuje się ona po procesie grupowania. Stanowi ona wsparcie dla agregacji.


select count(*) IlośćWierszy, ProductCategoryID, from SalesLT.Product 
group by ProductCategoryID, having count(*)>10;




Wyświetlona została ilość wierszy , która została pogrupowana z uwzględnieniem odpowiedniego identyfikatora kategorii produktu, a następnie pozostawione wyniku wyświetlenia zostały jedynie rekordy, gdzie podliczona ilość wierszy wynosiła więcej niż 10.


Operatory ROLLUP I CUBE


Dodanie rollup do group by spowoduje wyświetlenie w ostatnim wierszu wyświetlenia podsumowanie wszystkich pogrupowanych elementów. Natomiast przy grupowaniu po więcej niż jednej kolumnie lub wyrażeniu otrzymamy nie tylko pogrupowane wyniki, ale także podsumowanie dla pierwszej kolumny lub wyrażenia, użytego do pogrupowania wyników.


select count(*) IlośćWierszy, ProductCategoryID, from SalesLT.Product 
where ProductCategoryID in (9,10,22,24) group by rollup (ProductCategoryID);




Wyświetlona została ilość wierszy z pogrupowaniem na odpowiedni identyfikator kategorii produktu. Do grupowania został dodany rollup dzięki czemu w ostatnim wierszu otrzymaliśmy podsumowanie wszystkich przeliczanych rekordów. Filtrowanie zostało użyte by pokazać działanie na mniejszej ilości danych.


select count(*) IlośćWierszy, ProductCategoryID, ProductModelID from SalesLT.Product 
where ProductCategoryID in (9,10,22,24) group by rollup (ProductCategoryID, ProductModelID);




Wyświetlona została ilość wierszy, która została pogrupowana z uwzględnieniem odpowiedniego identyfikatora kategorii produktu, a następnie w ramach tych grup, nastąpiło kolejne grupowanie z rozróżnieniem na identyfikator modelu produktu. Do grupowania został dodany rollup dzięki czemu otrzymaliśmy podsumowanie dla pierwszego grupowania - po identyfikatorze kategorii produktu w ramach tej grupy, przed przejściem do grupowania do grupy – widać to w wierszu 4, 7, 9 i 12. W wierszu 13 otrzymaliśmy podsumowanie wszystkich przeliczanych rekordów.

Natomiast dodanie do instrukcji grupowania, przy grupowaniu po więcej niż jednej kolumnie lub wyrażeniu słowa cube spowoduje wygenerowanie podsumowań dla wszystkich możliwych kombinacji kolumn lub wyrażeń wymienionych w zapytaniu oraz dołączenie podsumowania wszystkich przeliczanych rekordów, lecz tym razem nie nastąpi to już w ostatnim wierszu wyświetlenia.


select count(*) IlośćWierszy, ProductCategoryID, ProductModelID from SalesLT.Product 
where ProductCategoryID in (9,10,22,24) group by cube (ProductCategoryID, ProductModelID);




Wyświetlona została ilość wierszy, która została pogrupowana z uwzględnieniem odpowiedniego identyfikatora kategorii produktu, a następnie w ramach tych grup, nastąpiło kolejne grupowanie z rozróżnieniem na identyfikator modelu produktu. Do grupowania został dodany cube dzięki czemu otrzymaliśmy podsumowanie dla drugiego grupowania - po identyfikatorze modelu produktu w ramach tej grupy, przed przejściem do grupowania do grupy – widać to w wierszu 2, 4, 6, 8, 10, 12, 14, 16. W wierszu 17 otrzymaliśmy podsumowanie wszystkich przeliczanych rekordów. Natomiast dalej, w wierszu 18. 19. 20 i 21 otrzymaliśmy podsumowanie dla pierwszego grupowania - po identyfikatorze kategorii produktu.