Statistische functies gebruiken

  • 3 minuten

T-SQL biedt statistische functies zoals SOM, MAX en AVG om berekeningen uit te voeren die meerdere waarden aannemen en één resultaat retourneren.

Werken met statistische functies

De meeste query's die we hebben bekeken, werken op een rij tegelijk met behulp van een WHERE-component om rijen te filteren. Elke geretourneerde rij komt overeen met één rij in de oorspronkelijke gegevensset.

Veel statistische functies zijn beschikbaar in SQL Server. In deze sectie bekijken we de meest voorkomende functies, zoals SUM, MIN, MAX, AVG en COUNT.

Wanneer u met statistische functies werkt, moet u rekening houden met de volgende punten:

  • Statistische functies retourneren één (scalaire) waarde en kunnen bijna overal in SELECT-instructies worden gebruikt. Deze functies kunnen bijvoorbeeld worden gebruikt in de componenten SELECT, HAVING en ORDER BY. Ze kunnen echter niet worden gebruikt in de WHERE-component.
  • Statistische functies negeren NULL's, behalve wanneer u COUNT(*)gebruikt.
  • Statistische functies in een SELECT-lijst hebben geen kolomkop, tenzij u een alias opgeeft met AS.
  • Statistische functies in een SELECT-lijst worden uitgevoerd op alle rijen die worden doorgegeven aan de SELECT-bewerking. Als er geen GROUP BY-component is, worden alle rijen die voldoen aan een filter in de WHERE-component samengevat. In het volgende onderwerp vindt u meer informatie over GROUP BY.
  • Tenzij u GROUP BY gebruikt, moet u statistische functies niet combineren met kolommen die niet zijn opgenomen in functies in dezelfde SELECT-lijst.

Sql Server biedt een mechanisme voor door de gebruiker gedefinieerde statistische functies via de .NET Common Language Runtime (CLR) om verder te gaan dan de ingebouwde functies. Dit onderwerp valt buiten het bereik van deze module.

Ingebouwde statistische functies

Zoals vermeld, biedt Transact-SQL veel ingebouwde statistische functies. Veelgebruikte functies zijn:

Functienaam

Syntaxis

Beschrijving

SOM

SUM(expressie)

Hiermee worden alle niet-NULL-numerieke waarden in een kolom totalen.

AVG

AVG(expressie)

Hiermee worden alle niet-NULL-numerieke waarden in een kolom (som/aantal) gemiddelden.

Minuut

MIN(expressie)

Retourneert het kleinste getal, de vroegste datum/tijd of de eerste tekenreeks (volgens sorteringssorteringsregels).

MAX

MAX(expressie)

Retourneert het grootste getal, de laatste datum/tijd of de laatste tekenreeks (volgens sorteringssorteringsregels).

AANTAL of COUNT_BIG

COUNT(*) of COUNT(expressie)

Met (*)worden alle rijen geteld, inclusief rijen met NULL-waarden. Wanneer een kolom is opgegeven als expressie, wordt het aantal niet-NULL-rijen voor die kolom geretourneerd. COUNT retourneert een int; COUNT_BIG retourneert een big_int.

Als u een ingebouwde statistische functie in een SELECT-component wilt gebruiken, kunt u het volgende voorbeeld in de MyStore-voorbeelddatabase gebruiken:

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

De resultaten van deze query zien er ongeveer als volgt uit:

Gemiddelde prijs

Minimumprijs

Maximumprijs

744.5952

2.2900

3578.2700

In het bovenstaande voorbeeld worden alle rijen uit de tabel Production.Product samengevat. We kunnen de query eenvoudig wijzigen om de gemiddelde, minimum- en maximumprijzen voor producten in een specifieke categorie te retourneren door een WHERE-component toe te voegen, zoals hieronder:

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

Wanneer u statistische functies gebruikt in een SELECT-component, moeten alle kolommen waarnaar wordt verwezen in de SELECT-lijst worden gebruikt als invoer voor een statistische functie of worden verwezen in een GROUP BY-component.

Houd rekening met de volgende query, die probeert het veld ProductCategoryID op te nemen in de geaggregeerde resultaten:

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

Het uitvoeren van deze query resulteert in de volgende fout

Msg 8120, Niveau 16, Staat 1, Regel 1

Kolom Production.ProductCategoryID is ongeldig in de selectielijst omdat deze niet is opgenomen in een statistische functie of de GROUP BY-component.

De query behandelt alle rijen als één geaggregeerde groep. Daarom moeten alle kolommen worden gebruikt als invoer voor het aggregeren van functies.

In de vorige voorbeelden hebben we numerieke gegevens samengevoegd, zoals de prijs en hoeveelheden in het vorige voorbeeld. Sommige statistische functies kunnen ook worden gebruikt om datum-, tijd- en tekengegevens samen te vatten. In de volgende voorbeelden ziet u het gebruik van aggregaties met datums en tekens:

Deze query retourneert het eerste en laatste bedrijf op naam, met MIN en MAX:

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

Deze query retourneert de eerste en laatste waarden voor CompanyName in de sorteringsreeks van de database, die in dit geval alfabetische volgorde is:

MinCustomer

MaxCustomer

Een fietswinkel

Gele Fietsbedrijf

Andere functies kunnen worden genest met statistische functies.

De scalaire functie JAAR wordt bijvoorbeeld gebruikt in het volgende voorbeeld om alleen het jaargedeelte van de orderdatum te retourneren, voordat MIN en MAX worden geëvalueerd:

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

Vroegste

Laatste

2008

2021

De functies MIN en MAX kunnen ook worden gebruikt met datumgegevens om de vroegste en meest recente chronologische waarden te retourneren. AVG en SUM kunnen echter alleen worden gebruikt voor numerieke gegevens, waaronder gehele getallen, geld, float- en decimale gegevenstypen.

DISTINCT gebruiken met statistische functies

U moet rekening houden met het gebruik van DISTINCT in een SELECT-component om dubbele rijen te verwijderen. Bij gebruik met een statistische functie verwijdert DISTINCT dubbele waarden uit de invoerkolom voordat de samenvattingswaarde wordt berekend. DISTINCT is handig bij het samenvatten van unieke exemplaren van waarden, zoals klanten in de tabel orders.

In het volgende voorbeeld wordt het aantal klanten geretourneerd dat orders heeft geplaatst, ongeacht het aantal orders dat ze hebben geplaatst:

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

COUNT(<some_column>) telt slechts het aantal rijen met een bepaalde waarde in de kolom. Als er geen NULL-waarden zijn, is COUNT(<some_column>) hetzelfde als COUNT(*). COUNT (DISTINCT <some_column>) telt het aantal verschillende waarden in de kolom.

Statistische functies gebruiken met NULL

Het is belangrijk om rekening te houden met de mogelijke aanwezigheid van NULL's in uw gegevens en hoe NULL communiceert met T-SQL-queryonderdelen, inclusief statistische functie. Er zijn enkele overwegingen waarmee u rekening moet houden:

  • Met uitzondering van COUNT die met de optie (*) wordt gebruikt, negeren statistische T-SQL-functies NULL's. Met een functie SOM worden bijvoorbeeld alleen niet-NULL-waarden toegevoegd. NULL's evalueren niet tot nul. COUNT(*) telt alle rijen, ongeacht waarde of niet-waarde in een kolom.
  • De aanwezigheid van NULL's in een kolom kan leiden tot onnauwkeurige berekeningen voor AVG, waarmee alleen ingevulde rijen worden opgeteld en die som wordt gedeeld door het aantal niet-NULL-rijen. Er kan een verschil zijn in resultaten tussen AVG(<column>) en (SUM(<column>)/COUNT(*)).

Denk bijvoorbeeld aan de volgende tabel met de naam t1:

C1

C2

1

NUL

2

10

3

20

4

30

5

40

6

50

Deze query illustreert het verschil tussen de manier waarop AVG NULL verwerkt en hoe u een gemiddelde kunt berekenen met een berekende kolom SOM/AANTAL(*):

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;

Het resultaat is:

sum_nonnulls

tel_alle_rijen

count_nonnulls

gemiddeld

rekenkundig gemiddelde

150

6

5

30

vijfentwintig

In deze resultatenset is de kolom met de naam gemiddelde de statistische waarde die intern de som van 150 ophaalt en deelt door het aantal niet-null-waarden in kolom c2. De berekening is 150/5 of 30. De kolom met de naam arith_average deelt de som expliciet door het aantal rijen, dus de berekening is 150/6 of 25.

Als u alle rijen wilt samenvatten, ongeacht of NULL of niet, kunt u overwegen de NULL's te vervangen door een andere waarde die niet wordt genegeerd door de statistische functie. U kunt hiervoor de functie COALESCE gebruiken.