Använda mängdfunktioner

  • 3 minuter

T-SQL tillhandahåller aggregerade funktioner som SUM, MAX och AVG för att utföra beräkningar som tar flera värden och returnerar ett enda resultat.

Arbeta med mängdfunktioner

De flesta frågor som vi har tittat på fungerar på en rad i taget med hjälp av en WHERE-sats för att filtrera rader. Varje rad som returneras motsvarar en rad i den ursprungliga datauppsättningen.

Många mängdfunktioner finns i SQL Server. I det här avsnittet tittar vi på de vanligaste funktionerna som SUM, MIN, MAX, AVG och COUNT.

När du arbetar med aggregerade funktioner måste du tänka på följande:

  • Aggregeringsfunktioner returnerar ett enda värde (skalärt) och kan användas i SELECT-instruktioner nästan var som helst där ett enda värde kan användas. Dessa funktioner kan till exempel användas i satserna SELECT, HAVING och ORDER BY. De kan dock inte användas i WHERE-satsen.
  • Aggregerade funktioner ignorerar NULL:er, förutom när du använder COUNT(*).
  • Aggregerade funktioner i en SELECT-lista har ingen kolumnrubrik om du inte anger ett alias med as.
  • Mängdfunktioner i en SELECT-lista fungerar på alla rader som skickas till SELECT-åtgärden. Om det inte finns någon GROUP BY-sats sammanfattas alla rader som uppfyller alla filter i WHERE-satsen. Du lär dig mer om GROUP BY i nästa avsnitt.
  • Om du inte använder GROUP BY bör du inte kombinera aggregerade funktioner med kolumner som inte ingår i funktioner i samma SELECT-lista.

För att utöka utöver de inbyggda funktionerna tillhandahåller SQL Server en mekanism för användardefinierade aggregeringsfunktioner via .NET Common Language Runtime (CLR). Det ämnet ligger utanför omfånget för den här modulen.

Inbyggda mängdfunktioner

Som nämnts tillhandahåller Transact-SQL många inbyggda aggregerade funktioner. Vanliga funktioner är:

Funktionsnamn

Syntax

Beskrivning

SUM

SUM(expression)

Summerar alla icke-NULL-numeriska värden i en kolumn.

AVG

AVG(expression)

Medelvärden för alla icke-NULL-numeriska värden i en kolumn (summa/antal).

MIN

MIN(uttryck)

Returnerar det minsta talet, tidigaste datum/tid eller den första strängen (enligt sorteringssorteringsregler).

MAX

MAX(uttryck)

Returnerar det största talet, senaste datum/tid eller den senaste strängen (enligt sorteringssorteringsregler).

COUNT eller COUNT_BIG

COUNT(*) eller COUNT(expression)

Med (*) räknas alla rader, inklusive rader med NULL-värden. När en kolumn anges som uttryck returnerar antalet icke-NULL-rader för den kolumnen. COUNT returnerar en int; COUNT_BIG returnerar en big_int.

Om du vill använda en inbyggd aggregering i en SELECT-sats bör du överväga följande exempel i MyStore-exempeldatabasen:

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

Resultatet av den här frågan ser ut ungefär så här:

AveragePrice

MinimumPrice

MaximumPrice

744.5952

2.2900

3578.2700

Observera att exemplet ovan sammanfattar alla rader från tabellen Production.Product . Vi kan enkelt ändra frågan för att returnera genomsnittliga, lägsta och högsta priser för produkter i en specifik kategori genom att lägga till en WHERE-sats, så här:

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

När du använder aggregeringar i en SELECT-sats måste alla kolumner som refereras i SELECT-listan användas som indata för en aggregeringsfunktion eller refereras till i en GROUP BY-sats.

Tänk på följande fråga, som försöker inkludera fältet ProductCategoryID i de aggregerade resultaten:

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

Om du kör den här frågan resulterar det i följande fel

Msg 8120, nivå 16, delstat 1, rad 1

Kolumnen "Production.ProductCategoryID" är ogiltig i urvalslistan eftersom den inte finns i någon mängdfunktion eller i GROUP BY-satsen.

Frågan behandlar alla rader som en enda aggregerad grupp. Därför måste alla kolumner användas som indata för att aggregera funktioner.

I föregående exempel aggregerade vi numeriska data, till exempel pris och kvantiteter i föregående exempel. Vissa av aggregeringsfunktionerna kan också användas för att sammanfatta datum-, tids- och teckendata. I följande exempel visas användningen av aggregeringar med datum och tecken:

Den här frågan returnerar för- och efternamn efter namn med min och MAX:

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

Den här frågan returnerar de första och sista värdena för CompanyName i databasens sorteringssekvens, vilket i det här fallet är alfabetisk ordning:

MinCustomer

MaxCustomer

En cykelbutik

Gult cykelföretag

Andra funktioner kan vara kapslade med aggregerade funktioner.

Till exempel används funktionen YEAR scalar i följande exempel för att endast returnera årsdelen av orderdatumet, innan MIN och MAX utvärderas:

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

Tidigast

Senast

2008

2021

Funktionerna MIN och MAX kan också användas med datumdata för att returnera de tidigaste och senaste kronologiska värdena. AVG och SUM kan dock endast användas för numeriska data, som innehåller heltal, pengar, flyttal och decimaldatatyper.

Använda DISTINCT med aggregeringsfunktioner

Du bör vara medveten om användningen av DISTINCT i en SELECT-sats för att ta bort dubblettrader. När det används med en aggregeringsfunktion tar DISTINCT bort dubblettvärden från indatakolumnen innan sammanfattningsvärdet beräknas. DISTINCT är användbart när du sammanfattar unika förekomster av värden, till exempel kunder i ordertabellen.

I följande exempel returneras antalet kunder som har gjort beställningar, oavsett hur många beställningar de har gjort:

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

COUNT(<some_column>) räknar bara hur många rader som har ett visst värde i kolumnen. Om det inte finns några NULL-värden är COUNT(<some_column>) samma som COUNT(*). COUNT (DISTINCT <some_column>) räknar hur många olika värden som finns i kolumnen.

Använda mängdfunktioner med NULL

Det är viktigt att vara medveten om den möjliga förekomsten av NULL:er i dina data och hur NULL interagerar med T-SQL-frågekomponenter, inklusive aggregeringsfunktion. Det finns några saker att tänka på:

  • Med undantag för COUNT som används med alternativet (*) ignorerar T-SQL-mängdfunktioner NULL:er. Till exempel lägger en SUM-funktion bara till icke-NULL-värden. NULL:er utvärderas inte till noll. COUNT(*) räknar alla rader, oavsett värde eller icke-värde i en kolumn.
  • Förekomsten av NULLs i en kolumn kan leda till felaktiga beräkningar för AVG, som endast summerar ifyllda rader och delar den summan med antalet icke-NULL-rader. Det kan finnas en skillnad i resultat mellan AVG(<column>) och (SUM(<column>)/COUNT(*)).

Tänk till exempel på följande tabell med namnet t1:

C1

C2

1

NULL

2

10

3

20

4

30

5

40

6

50

Den här frågan illustrerar skillnaden mellan hur AVG hanterar NULL och hur du kan beräkna ett genomsnitt med en beräknad kolumn för 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 skulle bli:

sum_nonnulls

count_all_rows

count_nonnulls

genomsnitt

arith_average

150

6

5

30

25

I den här resultatuppsättningen är kolumnen med namnet average den mängd som internt hämtar summan av 150 och dividerar med antalet värden som inte är null i kolumn c2. Beräkningen skulle vara 150/5 eller 30. Kolumnen med namnet arith_average delar uttryckligen summan med antalet rader, så beräkningen är 150/6 eller 25.

Om du behöver sammanfatta alla rader, oavsett om de är NULL eller inte, bör du överväga att ersätta NULL:er med ett annat värde som inte ignoreras av din mängdfunktion. Du kan använda funktionen COALESCE för detta ändamål.