Utilizarea funcțiilor agregate

  • 3 minute

T-SQL oferă funcții agregate, cum ar fi SUM, MAX și AVG, pentru a efectua calcule care iau mai multe valori și a returna un singur rezultat.

Lucrul cu funcții agregate

Majoritatea interogărilor pe care le-am analizat operează pe un rând o dată, utilizând o clauză WHERE pentru a filtra rândurile. Fiecare rând returnat corespunde unui rând din setul de date original.

Multe funcții agregate sunt furnizate în SQL Server. În această secțiune, vom analiza cele mai comune funcții, cum ar fi SUM, MIN, MAX, AVG și COUNT.

Atunci când lucrați cu funcții agregate, trebuie să luați în considerare următoarele puncte:

  • Funcțiile agregate returnează o valoare unică (scalară) și pot fi utilizate în instrucțiuni SELECT aproape oriunde se poate utiliza o singură valoare. De exemplu, aceste funcții pot fi utilizate în clauzele SELECT, HAVING și ORDER BY. Totuși, acestea nu pot fi utilizate în clauza WHERE.
  • Funcțiile agregate ignoră NUL-urile, cu excepția cazului în care se utilizează COUNT(*).
  • Funcțiile agregate dintr-o listă SELECT nu au un antet de coloană decât dacă furnizați un alias utilizând AS.
  • Funcțiile agregate dintr-o listă SELECT operează pe toate rândurile transmise operațiunii SELECT. Dacă nu există nicio clauză GROUP BY, toate rândurile care satisfac orice filtru din clauza WHERE vor fi rezumate. Veți afla mai multe despre GROUP BY în următorul subiect.
  • Dacă nu utilizați GROUP BY, nu ar trebui să combinați funcții agregate cu coloane care nu sunt incluse în funcții în aceeași listă SELECT.

Pentru a extinde dincolo de funcțiile predefinite, SQL Server oferă un mecanism pentru funcțiile agregate definite de utilizator prin intermediul .NET Common Language Runtime (CLR). Acest subiect este dincolo de domeniul acestui modul.

Funcții agregate predefinite

După cum am menționat, Transact-SQL oferă multe funcții agregate predefinite. Funcțiile utilizate frecvent includ:

Nume funcție

sintaxă

Descriere

SUMĂ

SUM(expresie)

Totalizează toate valorile numerice non-NULL dintr-o coloană.

AVG

AVG(expresie)

Face media tuturor valorilor numerice non-NULL dintr-o coloană (sumă/contor).

MIN

MIN(expresie)

Returnează cel mai mic număr, cea mai veche dată/oră sau șir care apare prima (conform regulilor de sortare a asamblării).

MAX

MAX(expresie)

Returnează cel mai mare număr, cea mai recentă dată/oră sau șirul care are loc ultima dată (conform regulilor de sortare a asamblării).

COUNT sau COUNT_BIG

COUNT(*) sau COUNT(expresie)

Cu (*), contorizează toate rândurile, inclusiv rândurile cu valori NULL. Atunci când o coloană este specificată ca expresie, returnează numărul de rânduri non-NULL pentru acea coloană. COUNT returnează un int; COUNT_BIG returnează o big_int.

Pentru a utiliza o agregare predefinită într-o clauză SELECT, luați în considerare următorul exemplu în baza de date eșantion MyStore :

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

Rezultatele acestei interogări arată astfel:

Preț Mediu

Preț Minim

Preț Maxim

744.5952

2.2900

3578.2700

Rețineți că exemplul de mai sus rezumă toate rândurile din tabelul Producție.Produs . Am putea modifica cu ușurință interogarea pentru a returna prețurile medii, minime și maxime pentru produsele dintr-o anumită categorie, adăugând o clauză WHERE, astfel:

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

Atunci când utilizați agregate într-o clauză SELECT, toate coloanele la care se face referire în lista SELECT trebuie utilizate ca intrări pentru o funcție agregată sau să fie menționate într-o clauză GROUP BY.

Luați în considerare următoarea interogare, care încearcă să includă câmpul ProductCategoryID în rezultatele agregate:

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

Rularea acestei interogări are ca rezultat următoarea eroare

Msg 8120, Nivel 16, Județ 1, Linia 1

Coloana "Production.ProductCategoryID" nu este validă în lista de selectare, deoarece nu este conținută într-o funcție agregată sau în clauza GROUP BY.

Interogarea tratează toate rândurile ca un singur grup agregat. Prin urmare, toate coloanele trebuie utilizate ca intrări pentru a agregarea funcțiilor.

În exemplele anterioare, am agregat date numerice, cum ar fi prețul și cantitățile din exemplul anterior. Unele dintre funcțiile agregate pot fi utilizate, de asemenea, pentru a rezuma datele de tip dată, oră și caractere. Următoarele exemple arată utilizarea agregatelor cu date și caractere:

Această interogare returnează prima și firma de familie după nume, utilizând MIN și MAX:

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

Această interogare va returna primele și ultimele valori pentru CompanyName în secvența de asamblare a bazei de date, care, în acest caz, este ordine alfabetică:

MinCustomer

MaxCustomer

Un Magazin de biciclete

Compania de biciclete galbenă

Alte funcții pot fi imbricate cu funcții agregate.

De exemplu, funcția scalară YEAR este utilizată în următorul exemplu pentru a returna numai porțiunea de an a datei comenzii, înainte de evaluarea MIN și MAX:

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

Primele

Ultima

2008

2021

Funcțiile MIN și MAX pot fi utilizate, de asemenea, cu date calendaristice, pentru a returna cele mai vechi și mai recente valori cronologice. Cu toate acestea, AVG și SUM pot fi utilizate numai pentru date numerice, care includ numere întregi, bani, tipuri de date flotaante și zecimale.

Utilizarea DISTINCT cu funcții agregate

Ar trebui să țineți cont de utilizarea DISTINCT într-o clauză SELECT pentru a elimina rândurile dublate. Atunci când este utilizată cu o funcție agregată, DISTINCT elimină valorile dublate din coloana de intrare înainte de a calcula valoarea rezumat. DISTINCT este utilă atunci când rezumați apariții unice ale valorilor, cum ar fi clienții din tabelul comenzi.

Următorul exemplu returnează numărul de clienți care au plasat comenzi, indiferent câte comenzi au plasat:

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

COUNT(<some_column>) contorizează doar câte rânduri au o valoare în coloană. Dacă nu există valori NULL, COUNT(<some_column>) va fi același cu COUNT(*). COUNT (DISTINCT <some_column>) contorizează câte valori diferite există în coloană.

Utilizarea funcțiilor agregate cu NULL

Este important să țineți cont de posibila prezență a NUL-urilor în datele dvs. și de modul în care NULL interacționează cu componentele de interogare T-SQL, inclusiv cu funcția agregată. Există câteva considerații de care să țineți cont:

  • Cu excepția funcției COUNT utilizate cu opțiunea (*), funcțiile agregate T-SQL ignoră NUL-urile. De exemplu, o funcție SUM va aduna numai valori non-NULL. NUL-urile nu se evaluează la zero. COUNT(*) contorizează toate rândurile, indiferent de valoare sau de non-valoare în orice coloană.
  • Prezența NUL-urilor dintr-o coloană poate duce la calcule incorecte pentru AVG, ceea ce va însuma doar rândurile populate și va împărți suma respectivă la numărul de rânduri non-NULL. Pot exista o diferență în rezultate între AVG(<coloană>) și (SUM(<coloană>)/COUNT(*)).

De exemplu, luați în considerare următorul tabel denumit t1:

C1

C2

1

ZERO

2

10

3

20

4

30

5

40

6

50

Această interogare ilustrează diferența dintre modul în care gestionează AVG NULL și modul în care puteți calcula o medie cu o coloană calculată 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;

Rezultatul ar fi:

sum_nonnulls

count_all_rows

count_nonnulls

medie

arith_average

150

6

5

30

25

În acest rezultat, coloana denumită medie este agregata care obține intern suma de 150 și se împarte la numărul de valori non-nule din coloana c2. Calculul ar fi 150/5 sau 30. Coloana denumită arith_average împarte în mod explicit suma la numărul tuturor rândurilor, deci calculul este 150/6 sau 25.

Dacă trebuie să rezumați toate rândurile, indiferent dacă null sau nu, luați în considerare înlocuirea NUL-urilor cu o altă valoare care nu va fi ignorată de funcția agregată. Puteți utiliza funcția COALESCE în acest scop.