Brug aggregeringsfunktioner

  • 3 minutter

T-SQL indeholder aggregerede funktioner som SUM, MAX og AVG til at udføre beregninger, der tager flere værdier og returnerer et enkelt resultat.

Arbejde med aggregeringsfunktioner

De fleste af de forespørgsler, vi har kigget på, fungerer på en række ad gangen ved hjælp af en WHERE-delsætning til at filtrere rækker. Hver returnerede række svarer til én række i det oprindelige datasæt.

Der leveres mange aggregerede funktioner i SQL Server. I dette afsnit ser vi på de mest almindelige funktioner, f.eks. SUM, MIN, MAX, AVG og COUNT.

Når du arbejder med aggregeringsfunktioner, skal du overveje følgende punkter:

  • Aggregeringsfunktioner returnerer en enkelt (skalar)værdi og kan bruges i SELECT-sætninger næsten overalt, hvor en enkelt værdi kan bruges. Disse funktioner kan f.eks. bruges i delsætningerne SELECT, HAVING og ORDER BY. De kan dog ikke bruges i WHERE-delsætningen.
  • Aggregeringsfunktioner ignorerer NULLs, undtagen når count(*) bruges.
  • Aggregeringsfunktioner på en SELECT-liste har ikke en kolonneoverskrift, medmindre du angiver et alias ved hjælp af AS.
  • Aggregeringsfunktioner på en SELECT-liste fungerer på alle rækker, der overføres til SELECT-handlingen. Hvis der ikke er nogen GROUP BY-delsætning, opsummeres alle rækker, der opfylder et filter i WHERE-delsætningen. Du får mere at vide om GROUP BY i næste emne.
  • Medmindre du bruger GROUP BY, bør du ikke kombinere aggregeringsfunktioner med kolonner, der ikke er inkluderet i funktioner på den samme SELECT-liste.

Sql Server indeholder en mekanisme til brugerdefinerede aggregeringsfunktioner via CLR (.NET Common Language Runtime) for at udvide ud over de indbyggede funktioner. Dette emne ligger uden for dette moduls område.

Indbyggede aggregeringsfunktioner

Som nævnt indeholder Transact-SQL mange indbyggede aggregeringsfunktioner. Ofte anvendte funktioner omfatter:

Funktionsnavn

syntaks

beskrivelse

SUM

SUM(udtryk)

Totaler alle numeriske værdier, der ikke er NULL, i en kolonne.

AVG

AVG(udtryk)

Beregner gennemsnittet af alle numeriske værdier, der ikke er NULL, i en kolonne (sum/antal).

MIN.

MIN(udtryk)

Returnerer det mindste tal, det tidligste dato/klokkeslæt eller den første forekommende streng (i henhold til sorteringsregler for sortering efter sortering).

MAKS

MAX(udtryk)

Returnerer det største tal, den seneste dato/det seneste klokkeslæt eller den sidste forekommende streng (i henhold til sorteringsregler for sortering efter sortering).

COUNT eller COUNT_BIG

COUNT(*) eller COUNT(udtryk)

Med (*) tæller alle rækker, herunder rækker med NULL-værdier. Når en kolonne er angivet som udtryk, returneres antallet af rækker, der ikke er NULL, for den pågældende kolonne. COUNT returnerer en int. COUNT_BIG returnerer en big_int.

Hvis du vil bruge en indbygget samling i en SELECT-delsætning, skal du overveje følgende eksempel i MyStore-eksempeldatabasen:

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

Resultaterne af denne forespørgsel ser nogenlunde sådan ud:

Gennemsnitlig pris

Minimumpris

Maksimumpris

744.5952

2.2900

3578.2700

Bemærk, at ovenstående eksempel opsummerer alle rækker fra tabellen Production.Product . Vi kan nemt ændre forespørgslen for at returnere gennemsnits-, minimum- og maksimumpriserne for produkter i en bestemt kategori ved at tilføje en WHERE-delsætning som denne:

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

Når du bruger aggregeringer i en SELECT-delsætning, skal alle kolonner, der refereres til på SELECT-listen, bruges som input for en aggregatfunktion eller refereres til i en GROUP BY-delsætning.

Overvej følgende forespørgsel, som forsøger at inkludere feltet ProductCategoryID i de aggregerede resultater:

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

Kørsel af denne forespørgsel resulterer i følgende fejl

Msg 8120, Niveau 16, Stat 1, Linje 1

Kolonnen 'Production.ProductCategoryID' er ugyldig på valglisten, fordi den ikke findes i en aggregeringsfunktion eller GROUP BY-delsætningen.

Forespørgslen behandler alle rækker som en enkelt samlet gruppe. Derfor skal alle kolonner bruges som input til aggregeringsfunktioner.

I de forrige eksempler aggregerede vi numeriske data, f.eks. prisen og mængderne i det forrige eksempel. Nogle af aggregeringsfunktionerne kan også bruges til at opsummere data for dato, klokkeslæt og tegn. Følgende eksempler viser brugen af aggregeringer med datoer og tegn:

Denne forespørgsel returnerer det første og sidste firma efter navn ved hjælp af MIN og MAX:

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

Denne forespørgsel returnerer de første og sidste værdier for CompanyName i databasens sorteringssekvens, som i dette tilfælde er alfabetisk rækkefølge:

MinCustomer

MaxCustomer

En cykelbutik

Gult cykelfirma

Andre funktioner kan indlejres med aggregeringsfunktioner.

Funktionen YEAR-skalar bruges f.eks. i følgende eksempel til kun at returnere årsdelen af ordredatoen, før MIN og MAX evalueres:

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

Tidligste

Senest

2008

2021

Funktionerne MIN og MAX kan også bruges sammen med datodata til at returnere de tidligste og seneste kronologiske værdier. AVG og SUM kan dog kun bruges til numeriske data, som omfatter heltal, penge, flydende datatyper og decimaldatatyper.

Brug af DISTINCT med aggregeringsfunktioner

Du skal være opmærksom på brugen af DISTINCT i en SELECT-delsætning til at fjerne dublerede rækker. Når DISTINCT bruges sammen med en aggregeringsfunktion, fjernes dublerede værdier fra inputkolonnen, før oversigtsværdien udlægges. DISTINCT er nyttigt, når du opsummerer entydige forekomster af værdier, f.eks. kunder i ordretabellen.

I følgende eksempel returneres antallet af kunder, der har afgivet ordrer, uanset hvor mange ordrer de har afgivet:

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

COUNT(<some_column>) tæller blot, hvor mange rækker der har en værdi i kolonnen. Hvis der ikke er nogen NULL-værdier, vil COUNT(<some_column>) være det samme som COUNT(*). COUNT (DISTINCT <some_column>) tæller, hvor mange forskellige værdier der er i kolonnen.

Brug af aggregeringsfunktioner med NULL

Det er vigtigt at være opmærksom på den mulige tilstedeværelse af NULLs i dine data, og hvordan NULL interagerer med T-SQL-forespørgselskomponenter, herunder aggregeringsfunktion. Der er et par overvejelser, du skal være opmærksom på:

  • Med undtagelse af COUNT, der bruges sammen med indstillingen (*), ignorerer T-SQL-aggregeringsfunktioner NULLs. En SUM-funktion tilføjer f.eks. kun værdier, der ikke er NULL. NUL-værdier evalueres ikke til nul. COUNT(*) tæller alle rækker, uanset værdi eller ikke-værdi i en hvilken som helst kolonne.
  • Tilstedeværelsen af NULLs i en kolonne kan føre til unøjagtige beregninger for AVG, som kun vil lægge udfyldte rækker sammen og dividere summen med antallet af rækker, der ikke er NULL. Der kan være en forskel i resultaterne mellem AVG(<column>) og (SUM(<column>)/COUNT(*)).

Overvej f.eks. følgende tabel med navnet t1:

C1

C2

1

NUL

2

10

3

20

4

30

5

40

6

50

Denne forespørgsel illustrerer forskellen mellem, hvordan AVG håndterer NULL, og hvordan du kan beregne et gennemsnit med en beregnet KOLONNE 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;

Resultatet ville være:

sum_nonnulls

count_all_rows

count_nonnulls

gennemsnitlig

arith_average

150

6

5

30

25

I dette resultatsæt er kolonnen med navnet average den aggregering, der internt henter summen af 150 og dividerer med antallet af værdier, der ikke er null, i kolonne c2. Beregningen er 150/5 eller 30. Den kolonne , der kaldes arith_average dividerer eksplicit summen med antallet af alle rækker, så beregningen er 150/6 eller 25.

Hvis du har brug for at opsummere alle rækker, uanset om de er NULL eller ej, kan du overveje at erstatte NULLs med en anden værdi, der ikke ignoreres af din aggregeringsfunktion. Du kan bruge funktionen COALESCE til dette formål.