Použití agregačních funkcí

  • 3 min

T-SQL poskytuje agregační funkce, jako je SUMA, MAX a AVG, k provádění výpočtů, které přebírají více hodnot a vracejí jeden výsledek.

Práce s agregačními funkcemi

Většina dotazů, které jsme se podívali na operace na řádku najednou, pomocí klauzule WHERE k filtrování řádků. Každý vrácený řádek odpovídá jednomu řádku v původní sadě dat.

Sql Server poskytuje mnoho agregačních funkcí. V této části se podíváme na nejběžnější funkce, jako jsou SUMA, MIN, MAX, AVG a COUNT.

Při práci s agregačními funkcemi je potřeba vzít v úvahu následující body:

  • Agregační funkce vrací jednu (skalární) hodnotu a lze ji použít v příkazech SELECT téměř kdekoli, kde lze použít jednu hodnotu. Tyto funkce lze například použít v klauzulích SELECT, HAVING a ORDER BY. Nelze je však použít v klauzuli WHERE.
  • Agregační funkce ignorují hodnoty NUL, s výjimkou použití funkce COUNT(*).
  • Agregační funkce v seznamu SELECT nemají záhlaví sloupce, pokud nezadáte alias pomocí AS.
  • Agregační funkce v seznamu SELECT fungují na všech řádcích předaných operaci SELECT. Pokud neexistuje žádná klauzule GROUP BY, všechny řádky, které vyhovují jakémukoli filtru v klauzuli WHERE, se shrnou. Další informace o GROUP BY najdete v dalším tématu.
  • Pokud nepoužíváte GROUP BY, neměli byste kombinovat agregační funkce se sloupci, které nejsou součástí funkcí ve stejném seznamu SELECT.

Sql Server poskytuje mechanismus pro uživatelem definované agregační funkce prostřednictvím modulu CLR (Common Language Runtime). Toto téma je nad rámec tohoto modulu.

Předdefinované agregační funkce

Jak už bylo zmíněno, Jazyk Transact-SQL poskytuje mnoho předdefinovaných agregačních funkcí. Mezi běžně používané funkce patří:

Název funkce

Syntaxe

Popis

SUM

SUMA(výraz)

Součet všech číselných hodnot, které nejsou null ve sloupci.

PRŮMĚR

AVG(výraz)

Zprůměruje všechny číselné hodnoty, které nejsou null ve sloupci (součet/počet).

MIN

MIN(výraz)

Vrátí nejmenší číslo, nejstarší datum a čas nebo první řetězec (podle pravidel řazení kolace).

MAX

MAX(výraz)

Vrátí největší číslo, poslední datum a čas nebo řetězec posledního výskytu (podle pravidel řazení kolace).

POČET nebo COUNT_BIG

COUNT(*) nebo COUNT(výraz)

Při (*) se spočítá všechny řádky včetně řádků s hodnotami NULL. Pokud je sloupec zadán jako výraz, vrátí počet řádků, které nejsou null pro daný sloupec. Funkce COUNT vrátí hodnotu int; COUNT_BIG vrátí big_int.

Pokud chcete použít integrovanou agregaci v klauzuli SELECT, zvažte následující příklad ukázkové databáze MyStore :

SELECT AVG(ListPrice) AS AveragePrice,
       MIN(ListPrice) AS MinimumPrice,
       MAX(ListPrice) AS MaximumPrice
FROM Production.Product;

Výsledky tohoto dotazu vypadají přibližně takto:

AveragePrice

Minimálnícecena

MaximumPrice

744.5952

2.2900

3578.2700

Všimněte si, že výše uvedený příklad shrnuje všechny řádky z tabulky Production.Product . Dotaz můžeme snadno upravit tak, aby vracel průměrné, minimální a maximální ceny produktů v konkrétní kategorii přidáním klauzule WHERE, například takto:

SELECT AVG(ListPrice) AS AveragePrice,
       MIN(ListPrice) AS MinimumPrice,
       MAX(ListPrice) AS MaximumPrice
FROM Production.Product
WHERE ProductCategoryID = 15;

Při použití agregací v klauzuli SELECT je nutné použít všechny sloupce odkazované v seznamu SELECT jako vstupy pro agregační funkci nebo odkazovat v klauzuli GROUP BY.

Zvažte následující dotaz, který se pokusí do agregovaných výsledků zahrnout pole ProductCategoryID :

SELECT ProductCategoryID, AVG(ListPrice) AS AveragePrice,
MIN(ListPrice) AS MinimumPrice,
MAX(ListPrice) AS MaximumPrice
FROM Production.Product;

Spuštění tohoto dotazu způsobí následující chybu.

Msg 8120, úroveň 16, stav 1, řádek 1

Sloupec Production.ProductCategoryID je v seznamu select neplatný, protože není obsažen v agregační funkci nebo klauzuli GROUP BY.

Dotaz považuje všechny řádky za jednu agregovanou skupinu. Proto musí být všechny sloupce použity jako vstupy pro agregační funkce.

V předchozích příkladech jsme agregovali číselná data, jako je cena a množství v předchozím příkladu. Některé agregační funkce se dají použít také k sumarizaci dat data, času a znaků. Následující příklady ukazují použití agregací s kalendářními daty a znaky:

Tento dotaz vrátí první a poslední společnost podle názvu pomocí MIN a MAX:

SELECT MIN(CompanyName) AS MinCustomer, 
       MAX(CompanyName) AS MaxCustomer
FROM SalesLT.Customer;

Tento dotaz vrátí první a poslední hodnoty pro CompanyName v pořadí kolace databáze, což je v tomto případě abecední pořadí:

MinCustomer

MaxCustomer

Obchod s koly

Žlutá společnost pro jízdní kola

Ostatní funkce můžou být vnořené s agregačními funkcemi.

Skalární funkce YEAR se například používá v následujícím příkladu k vrácení pouze části roku data objednávky před vyhodnocením MIN a MAX:

SELECT MIN(YEAR(OrderDate)) AS Earliest,
       MAX(YEAR(OrderDate)) AS Latest
FROM Sales.SalesOrderHeader;

Nejdřívější

Nejpozdější

2008

2021

Funkce MIN a MAX lze také použít s daty kalendářních dat a vrátit nejstarší a nejnovější chronologické hodnoty. Avg a SUM se ale dají použít jenom pro číselná data, která zahrnují celá čísla, peníze, plovoucí desetinné číslo a desetinné číslo.

Použití funkce DISTINCT s agregačními funkcemi

Měli byste vědět o použití funkce DISTINCT v klauzuli SELECT k odebrání duplicitních řádků. Při použití s agregační funkcí funkce DISTINCT před výpočtem souhrnné hodnoty odebere ze vstupního sloupce duplicitní hodnoty. Funkce DISTINCT je užitečná při sumarizaci jedinečných výskytů hodnot, jako jsou zákazníci v tabulce objednávek.

Následující příklad vrátí počet zákazníků, kteří zadali objednávky bez ohledu na to, kolik objednávek zadali:

SELECT COUNT(DISTINCT CustomerID) AS UniqueCustomers
FROM Sales.SalesOrderHeader;

COUNT(<some_column>) pouze spočítá, kolik řádků má ve sloupci hodnotu. Pokud neexistují žádné hodnoty NULL, funkce COUNT(<some_column>) bude stejná jako FUNKCE COUNT(*). POČET (DISTINCT <some_column>) spočítá, kolik různých hodnot je ve sloupci.

Použití agregačních funkcí s hodnotou NULL

Je důležité vědět o možné přítomnosti NULL ve vašich datech a o tom, jak funkce NULL komunikuje s komponentami dotazů T-SQL, včetně agregační funkce. Je potřeba vzít v úvahu několik aspektů:

  • S výjimkou funkce COUNT použité s možností (*) agregační funkce T-SQL ignorují hodnoty NULL. Například funkce SUMA přidá pouze hodnoty, které nejsou null. NULL se nevyhodnocují na nulu. Funkce COUNT(*) spočítá všechny řádky bez ohledu na hodnotu nebo hodnotu v libovolném sloupci.
  • Přítomnost seznamů NUL ve sloupci může vést k nepřesným výpočtům pro AVG, které sečtou pouze vyplněné řádky a vydělí tento součet počtem řádků, které nejsou null. Ve výsledcích může být rozdíl mezi AVG(<column>) a (SUM(<column>)/COUNT(*)).

Představte si například následující tabulku s názvem t1:

C1

C2

0

NULL

2

10

3

20

4

30

5

40

6

50

Tento dotaz ukazuje rozdíl mezi tím, jak AVG zpracovává hodnotu NULL a jak můžete vypočítat průměr pomocí počítaného sloupce 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;

Výsledkem by bylo:

sum_nonnulls

count_all_rows

count_nonnulls

průměrný

arith_average

150

6

5

30

25

V této sadě výsledků je pojmenovaný průměr sloupce agregací, která interně získá součet 150 a vydělí se počtem nenulových hodnot ve sloupci c2. Výpočet by byl 150/5 nebo 30. Sloupec s názvem arith_average explicitně vydělí součet počtem všech řádků, takže výpočet je 150/6 nebo 25.

Pokud potřebujete shrnout všechny řádky bez ohledu na hodnotu NULL nebo ne, zvažte nahrazení hodnot NUL jinou hodnotou, kterou agregační funkce nebude ignorovat. Pro tento účel můžete použít funkci COALESCE.