Összesítő függvények használata

  • 3 perc

A T-SQL olyan összesítő függvényeket biztosít, mint a SZUM, a MAX és az AVG, amelyek több értéket tartalmazó számításokat hajtanak végre, és egyetlen eredményt ad vissza.

Összesítő függvények használata

Az általunk megvizsgált lekérdezések többsége egyszerre egy sorban működik, egy WHERE záradék használatával a sorok szűréséhez. Minden visszaadott sor az eredeti adatkészlet egy sorának felel meg.

Az SQL Server számos összesítő függvényt biztosít. Ebben a szakaszban az olyan leggyakoribb függvényeket tekintjük át, mint a SUM, a MIN, a MAX, az AVG és a COUNT.

Az összesítő függvények használatakor a következő szempontokat kell figyelembe vennie:

  • Az összesítő függvények egyetlen (skaláris) értéket adnak vissza, és a SELECT utasításokban szinte bárhol használhatók, ahol egyetlen érték használható. Ezek a függvények például használhatók a SELECT, HAVING és ORDER BY záradékokban. A WHERE záradékban azonban nem használhatók.
  • Az összesítő függvények figyelmen kívül hagyják a NULL-eket, kivéve a DARAB(*) függvényt.
  • A SELECT listában szereplő összesítő függvények csak akkor rendelkeznek oszlopfejléccel, ha az AS használatával ad meg aliast.
  • A SELECT listában lévő összesítő függvények a SELECT műveletnek átadott összes sorban működnek. Ha nincs GROUP BY záradék, a WHERE záradékban lévő összes szűrőnek megfelelő sor összegzése történik. A GROUP BY-ról a következő témakörben olvashat bővebben.
  • Ha a GROUP BY függvényt nem használja, az összesítő függvényeket nem érdemes kombinálni azokkal az oszlopokkal, amelyek nem szerepelnek ugyanabban a SELECT listában szereplő függvényekben.

A beépített függvényeken túlnyúlva az SQL Server a .NET Common Language Runtime (CLR) használatával biztosít mechanizmust a felhasználó által definiált összesítő függvényekhez. Ez a témakör túlmutat a modul hatókörén.

Beépített összesítő függvények

Mint említettük, a Transact-SQL számos beépített összesítő függvényt biztosít. A gyakran használt függvények a következők:

Függvény neve

Szintaxis

Leírás

ÖSSZEG

SZUM(kifejezés)

Egy oszlop összes nem NULL értékű numerikus értékét összegozza.

AVG

AVG(kifejezés)

Egy oszlop összes nem NULL értékű numerikus értékének átlaga (összeg/darabszám).

MIN

MIN(kifejezés)

A legkisebb számot, a legkorábbi dátumot/időt vagy az első alkalommal előforduló sztringet adja vissza (a rendezési szabályok szerint).

MAX

MAX(kifejezés)

A legnagyobb számot, a legutóbbi dátumot/időt vagy a legutóbb előforduló sztringet adja vissza (a rendezési szabályok szerint).

DARAB vagy COUNT_BIG

DARAB(*) vagy DARAB(kifejezés)

A (*) függvény az összes sort megszámolja, beleértve a NULL értékű sorokat is. Ha egy oszlop kifejezésként van megadva, az adott oszlop nem NULL értékű sorainak számát adja vissza. A COUNT függvény egy intet ad vissza; COUNT_BIG egy big_int ad vissza.

Ha egy SELECT záradékban szeretne beépített aggregátumot használni, tekintse meg a következő példát a MyStore mintaadatbázisban :

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

A lekérdezés eredményei a következőképpen néznek ki:

Átlagár

Minimális ár

Maximális ár

744.5952

2.2900

3578.2700

Vegye figyelembe, hogy a fenti példa a Production.Product tábla összes sorát összegzi. A lekérdezést egyszerűen módosíthatjuk úgy, hogy egy adott kategóriában lévő termékek átlagát, minimumát és maximális árát adja vissza egy WHERE záradék hozzáadásával, például a következőhöz:

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

Ha a SELECT záradékban aggregátumokat használ, a SELECT listában hivatkozott összes oszlopot egy összesítő függvény bemeneteként kell használni, vagy egy GROUP BY záradékban kell hivatkozni rá.

Fontolja meg a következő lekérdezést, amely megpróbálja belefoglalni a ProductCategoryID mezőt az összesített eredményekbe:

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

A lekérdezés futtatása a következő hibát eredményezi

Msg 8120, Level 16, State 1, Line 1

A "Production.ProductCategoryID" oszlop érvénytelen a kiválasztási listában, mert nem szerepel sem aggregátumfüggvényben, sem a GROUP BY záradékban.

A lekérdezés az összes sort egyetlen összesített csoportként kezeli. Ezért minden oszlopot bemenetként kell használni a függvények összesítéséhez.

Az előző példákban olyan numerikus adatokat aggregáltunk, mint az előző példában szereplő ár és mennyiségek. Az összesítő függvények némelyike a dátum-, idő- és karakteradatok összegzésére is használható. Az alábbi példák az aggregátumok dátumokkal és karakterekkel való használatát mutatják be:

Ez a lekérdezés név szerint adja vissza az első és a vezetéknevet a MIN és a MAX használatával:

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

Ez a lekérdezés a CompanyName első és utolsó értékeit adja vissza az adatbázis rendezési sorrendjében, amely ebben az esetben betűrendbe van rendezve:

MinCustomer

MaxCustomer

Kerékpáráruház

Yellow Bike Company

Más függvények beágyazottak lehetnek összesítő függvényekkel.

A következő példában például a YEAR skaláris függvénnyel csak a rendelés dátumának évrészét adja vissza, a MIN és a MAX kiértékelése előtt:

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

Legkorábbi

Legutóbbi

2008

2021

A MIN és MAX függvények dátumadatokkal is használhatók a legkorábbi és legújabb időrendi értékek visszaadásához. Az AVG és a SZUM azonban csak numerikus adatokhoz használható, amelyek egész számokat, pénzt, lebegőpontos és decimális adattípusokat tartalmaznak.

A DISTINCT használata összesítő függvényekkel

Tisztában kell lennie azzal, hogy a DISTINCT egy SELECT záradékban való használatával eltávolítja az ismétlődő sorokat. Összesítő függvény használatakor a DISTINCT eltávolítja az ismétlődő értékeket a bemeneti oszlopból az összegző érték kiszámítása előtt. A DISTINCT akkor hasznos, ha az értékek egyedi előfordulását összegzi, például az ügyfeleket a rendelések táblában.

Az alábbi példa a megrendeléseket leadó ügyfelek számát adja vissza, függetlenül attól, hogy hány megrendelést adott le:

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

A DARAB(<some_column>) csak azt számolja meg, hogy hány sornak van valamilyen értéke az oszlopban. Ha nincsenek NULL értékek, a DARAB(<some_column>) ugyanaz lesz, mint a DARAB(*). A COUNT (DISTINCT <some_column>) függvény megszámolja, hogy hány különböző érték található az oszlopban.

Összesítő függvények használata NULL értékkel

Fontos tisztában lenni a NULL-ek lehetséges jelenlétével az adatokban, és hogy a NULL hogyan kommunikál a T-SQL-lekérdezés összetevőivel, beleértve az összesítő függvényt is. Néhány szempontot figyelembe kell venni:

  • A (*) beállítással használt COUNT kivételével a T-SQL összesítő függvények figyelmen kívül hagyják az NULL-eket. A SZUM függvény például csak nem NULL értékeket ad hozzá. A NULL-ek kiértékelése nem nullára történik. A DARAB(*) az összes sort megszámolja, függetlenül attól, hogy egy oszlopban van-e érték vagy nem érték.
  • A NULL-ek jelenléte egy oszlopban pontatlan számításokhoz vezethet az AVG-hez, amely csak a kitöltött sorokat összegzi, és elosztja ezt az összeget a nem NULL értékű sorok számával. Az AVG(<oszlop) és a (SZUM(>oszlop<>)/DARAB(*)) eredmények között eltérés lehet.

Vegyük például a következő t1 nevű táblát:

C1

C2

0

NULLA

2

10

3

20

4

30

5

40

6

50

Ez a lekérdezés azt mutatja be, hogy az AVG hogyan kezeli a NULL értéket, és hogyan számíthat ki átlagot egy SZUM/DARAB(*) számított oszlopmal:

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;

Az eredmény a következő:

nem null értékek összege

összes_sor_számlálása

count_nonnulls

átlagos

számtani átlag

150

6

5

30

25

Ebben az eredményhalmazban az átlag nevű oszlop az aggregátum, amely belsőleg lekéri a 150-et, és elosztja a c2 oszlopban lévő nem null értékek számával. A számítás 150/5 vagy 30. A arith_average nevű oszlop explicit módon osztja el az összeget az összes sor számával, így a számítás 150/6 vagy 25.

Ha összesítenie kell az összes sort, akár NULL, akár nem, fontolja meg a NULL-ek lecserélését egy másik értékre, amelyet az összesítő függvény nem hagy figyelmen kívül. Ehhez használhatja a COALESCE függvényt.