Korzystanie z funkcji agregujących
Język T-SQL udostępnia funkcje agregujące, takie jak SUM, MAX i AVG, do wykonywania obliczeń, które przyjmują wiele wartości i zwracają jeden wynik.
Praca z funkcjami agregacji
Większość zapytań, które przeglądaliśmy, działa na wierszu w danym momencie, używając klauzuli WHERE do filtrowania wierszy. Każdy zwrócony wiersz odpowiada jednemu wierszowi w oryginalnym zestawie danych.
Wiele funkcji agregujących jest dostępnych w programie SQL Server. W tej sekcji przyjrzymy się najbardziej typowym funkcjom, takim jak SUM, MIN, MAX, AVG i COUNT.
Podczas pracy z funkcjami agregacji należy wziąć pod uwagę następujące kwestie:
- Funkcje agregujące zwracają pojedynczą wartość (skalarną) i mogą być używane w instrukcjach SELECT niemal wszędzie, gdzie można użyć pojedynczej wartości. Na przykład te funkcje mogą być używane w klauzulach SELECT, HAVING i ORDER BY. Nie można ich jednak używać w klauzuli WHERE.
- Funkcje agregujące ignorują listy NUL, z wyjątkiem przypadków używania funkcji COUNT(*).
- Funkcje agregujące na liście SELECT nie mają nagłówka kolumny, chyba że podasz alias przy użyciu usługi AS.
- Funkcje agregujące na liście SELECT działają na wszystkich wierszach przekazanych do operacji SELECT. Jeśli nie ma klauzuli GROUP BY, wszystkie wiersze spełniające filtr w klauzuli WHERE zostaną podsumowane. Więcej informacji na temat funkcji GROUP BY znajdziesz w następnym temacie.
- Jeśli nie używasz funkcji GROUP BY, nie należy łączyć funkcji agregujących z kolumnami, które nie są zawarte w funkcjach na tej samej liście SELECT.
Aby wykraczać poza wbudowane funkcje, program SQL Server udostępnia mechanizm funkcji agregujących zdefiniowanych przez użytkownika za pośrednictwem środowiska uruchomieniowego języka wspólnego platformy .NET (CLR). Ten temat wykracza poza zakres tego modułu.
Wbudowane funkcje agregujące
Jak wspomniano, język Transact-SQL udostępnia wiele wbudowanych funkcji agregujących. Często używane funkcje to:
Nazwa funkcji
Składnia
Opis
SUM
SUM(expression)
Sumuje wszystkie wartości liczbowe inne niż NULL w kolumnie.
AVG
AVG(wyrażenie)
Określa średnią wszystkich wartości liczbowych innych niż NULL w kolumnie (suma/liczba).
MIN
MIN(wyrażenie)
Zwraca najmniejszą liczbę, najwcześniejszą datę/godzinę lub pierwszy występujący ciąg (zgodnie z regułami sortowania).
MAX
MAX(expression)
Zwraca największą liczbę, najnowszą datę/godzinę lub ostatni występujący ciąg (zgodnie z regułami sortowania).
LICZBA lub COUNT_BIG
COUNT(*) lub COUNT(expression)
W przypadku elementu (*)zlicza wszystkie wiersze, w tym wiersze z wartościami NULL. Gdy kolumna jest określona jako wyrażenie, zwraca liczbę wierszy innych niż NULL dla tej kolumny. Funkcja COUNT zwraca wartość int; COUNT_BIG zwraca big_int.
Aby użyć wbudowanej agregacji w klauzuli SELECT, rozważmy następujący przykład w przykładowej bazie danych MyStore :
SELECT AVG(ListPrice) AS AveragePrice,
MIN(ListPrice) AS MinimumPrice,
MAX(ListPrice) AS MaximumPrice
FROM Production.Product;
Wyniki tego zapytania wyglądają mniej więcej tak:
Średnia cena
Minimalna wartośćprice
Maksymalna wartośćprice
744.5952
2.2900
3578.2700
Powyższy przykład zawiera podsumowanie wszystkich wierszy z tabeli Production.Product . Można łatwo zmodyfikować zapytanie, aby zwrócić średnią, minimalną i maksymalną cenę produktów w określonej kategorii, dodając klauzulę WHERE w następujący sposób:
SELECT AVG(ListPrice) AS AveragePrice,
MIN(ListPrice) AS MinimumPrice,
MAX(ListPrice) AS MaximumPrice
FROM Production.Product
WHERE ProductCategoryID = 15;
W przypadku używania agregacji w klauzuli SELECT wszystkie kolumny, do których odwołuje się lista SELECT, muszą być używane jako dane wejściowe dla funkcji agregującej lub przywoływana w klauzuli GROUP BY.
Rozważ następujące zapytanie, które próbuje uwzględnić pole ProductCategoryID w zagregowanych wynikach:
SELECT ProductCategoryID, AVG(ListPrice) AS AveragePrice,
MIN(ListPrice) AS MinimumPrice,
MAX(ListPrice) AS MaximumPrice
FROM Production.Product;
Uruchomienie tego zapytania powoduje następujący błąd
Msg 8120, poziom 16, stan 1, wiersz 1
Kolumna "Production.ProductCategoryID" jest nieprawidłowa na liście wyboru, ponieważ nie jest zawarta w funkcji agregującej ani klauzuli GROUP BY.
Zapytanie traktuje wszystkie wiersze jako pojedynczą grupę zagregowaną. W związku z tym wszystkie kolumny muszą być używane jako dane wejściowe do agregowania funkcji.
W poprzednich przykładach zagregowaliśmy dane liczbowe, takie jak cena i ilości w poprzednim przykładzie. Niektóre funkcje agregujące mogą być również używane do podsumowania danych daty, godziny i znaków. W poniższych przykładach pokazano użycie agregacji z datami i znakami:
To zapytanie zwraca pierwszą i ostatnią firmę według nazwy, używając wartości MIN i MAX:
SELECT MIN(CompanyName) AS MinCustomer,
MAX(CompanyName) AS MaxCustomer
FROM SalesLT.Customer;
To zapytanie zwróci pierwsze i ostatnie wartości nazwy firmy w sekwencji sortowania bazy danych, co w tym przypadku jest kolejnością alfabetyczną:
MinCustomer
MaxCustomer
Sklep rowerowy
Żółta firma rowerowa
Inne funkcje mogą być zagnieżdżone za pomocą funkcji agregujących.
Na przykład funkcja skalarna YEAR jest używana w poniższym przykładzie, aby zwrócić tylko część roku daty zamówienia, zanim zostaną ocenione wartości MIN i MAX:
SELECT MIN(YEAR(OrderDate)) AS Earliest,
MAX(YEAR(OrderDate)) AS Latest
FROM Sales.SalesOrderHeader;
Najwcześniejsze
Najnowsze
2008
2021
Funkcje MIN i MAX mogą być również używane z danymi daty, aby zwrócić najwcześniejsze i najnowsze wartości chronologiczne. Jednak avg i SUM mogą być używane tylko dla danych liczbowych, które obejmują liczby całkowite, pieniądze, zmiennoprzecinkowe i dziesiętne.
Używanie funkcji DISTINCT z funkcjami agregowanymi
Należy pamiętać o użyciu funkcji DISTINCT w klauzuli SELECT w celu usunięcia zduplikowanych wierszy. W przypadku użycia z funkcją agregacji funkcja DISTINCT usuwa zduplikowane wartości z kolumny wejściowej przed obliczenie wartości podsumowania. Funkcja DISTINCT jest przydatna podczas podsumowywania unikatowych wystąpień wartości, takich jak klienci w tabeli zamówień.
Poniższy przykład zwraca liczbę klientów, którzy składali zamówienia, niezależnie od liczby złożonych zamówień:
SELECT COUNT(DISTINCT CustomerID) AS UniqueCustomers
FROM Sales.SalesOrderHeader;
FUNKCJA COUNT(<some_column>) tylko zlicza liczbę wierszy z pewną wartością w kolumnie. Jeśli nie ma wartości NULL, funkcja COUNT(<some_column>) będzie taka sama jak COUNT(*). FUNKCJA COUNT (DISTINCT <some_column>) zlicza liczbę różnych wartości w kolumnie.
Używanie funkcji agregujących z wartością NULL
Ważne jest, aby pamiętać o możliwej obecności list NUL w danych oraz o tym, jak wartość NULL współdziała ze składnikami zapytań języka T-SQL, w tym funkcją agregowaną. Należy pamiętać o kilku zagadnieniach:
- Z wyjątkiem funkcji COUNT używanych z opcją (*) funkcje agregujące T-SQL ignorują NULLs. Na przykład funkcja SUM doda tylko wartości inne niż NULL. Listy NUL nie są obliczane na zero. COUNT(*) zlicza wszystkie wiersze, niezależnie od wartości lub wartości w dowolnej kolumnie.
- Obecność list NUL w kolumnie może prowadzić do niedokładnych obliczeń dla avg, co spowoduje sumę tylko wypełnionych wierszy i podzielenie tej sumy przez liczbę wierszy innych niż NULL. Może wystąpić różnica w wynikach między parametrami AVG(<column>) i (SUM(<column>)/COUNT(*)).
Rozważmy na przykład następującą tabelę o nazwie t1:
C1
C2
1
NULL
2
10
3
20
100
30
5
40
6
50
To zapytanie ilustruje różnicę między sposobem, w jaki avg obsługuje wartość NULL i jak można obliczyć średnią z obliczoną kolumną SUM/COUNT(*):
SELECT SUM(c2) AS sum_nonnulls,
COUNT(*) AS count_all_rows,
COUNT(c2) AS count_nonnulls,
AVG(c2) AS average,
(SUM(c2)/COUNT(*)) AS arith_average
FROM t1;
Wynik będzie:
sum_nonnulls
count_all_rows
count_nonnulls
Średnia
arith_average
150
6
5
30
25
W tym zestawie wyników kolumna o nazwie average jest agregacją, która wewnętrznie pobiera sumę 150 i dzieli przez liczbę wartości innych niż null w kolumnie c2. Obliczenie będzie miało wartość 150/5 lub 30. Kolumna o nazwie arith_average jawnie dzieli sumę przez liczbę wszystkich wierszy, więc obliczenie wynosi 150/6 lub 25.
Jeśli musisz podsumować wszystkie wiersze, bez względu na wartość NULL, rozważ zastąpienie list NUL inną wartością, która nie zostanie zignorowana przez funkcję agregacji. W tym celu można użyć funkcji COALESCE.