Udostępnij za pomocą

AVG (Transact-SQL)

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Punkt końcowy analizy SQL w usłudze Microsoft FabricHurtownia danych w usłudze Microsoft FabricBaza danych SQL w usłudze Microsoft Fabric

Funkcja ta zwraca średnią wartości w grupie. Ignoruje wartości zerowe.

Transact-SQL konwencje składni

Składnia

AVG ( [ ALL | DISTINCT ] expression )
   [ OVER ( [ partition_by_clause ] order_by_clause ) ]

Arguments

ALL

Stosuje funkcję agregacji do wszystkich wartości. WSZYSTKO jest domyślne.

ODMIENNY

Określa, że AVG działa tylko na jednej unikalnej instancji każdej wartości, niezależnie od liczby wystąpień tej wartości.

expression

Wyrażenie dokładnej kategorii numerycznego lub przybliżonego numerycznego typu danych, z wyjątkiem bitowego typu danych. Funkcje agregujące i podzapytania nie są dozwolone.

KONIEC ( [ partition_by_clause ] order_by_clause)

partition_by_clause dzieli zbiór wyników wytworzony przez klauzulę FROM na partycje, do których funkcja jest stosowana. Jeśli nie zostanie określony, funkcja traktuje wszystkie wiersze zestawu wyników zapytania jako pojedynczą grupę. order_by_clause określa logiczną kolejność wykonywania operacji. Wymagane jest order_by_clause. Więcej informacji można znaleźć w sekcji OVER Clause (Transact-SQL).

Typy zwracane

Wynik wyrażenia wyrażony określa typ zwrotu.

Wynik wyrażenia Typ zwracany
tinyint int
smallint int
int int
bigint bigint
Kategoria dziesiętna (P, S) dziesiętny(38, maks(s,6))
Kategoria pieniędzy i drobnych pieniędzy pieniędzy
Kategoria float i rzeczywiste float

Uwagi

Jeśli typ danych wyrażenia jest typem danych alias, typ return należy również do typu danych alias. Jednak jeśli podstawowy typ danych typu alias jest promowany, na przykład z tinyint na int, wartość zwrotu przyjmuje promowany typ danych, a nie typ danych alias.

AVG () oblicza średnią zestawu wartości, dzieląc sumę tych wartości przez liczbę wartości niezerowych. Jeśli suma przekroczy maksymalną wartość dla typu danych wartości zwrotnej, AVG() zwraca błąd.

AVG jest funkcją deterministyczną, gdy używa się bez klauzul OVER i ORDER BY. Jest niedeterministyczna, gdy jest określana za pomocą klauzul OVER i ORDER BY. Aby uzyskać więcej informacji, zobacz Funkcje deterministyczne i niedeterministyczne. Ponadto AVG może zachowywać się jak funkcja niedeterministyczna, gdy używa się go z float i rzeczywistymi typami danych. Ale podstawowym powodem jest przybliżony charakter tych typów danych.

Przykłady

A. Do obliczeń używając funkcji SUM i AVG

Ten przykład oblicza średnie godziny urlopu oraz sumę godzin zwolnień lekarskich, które wykorzystali wiceprezesi Adventure Works Cycles. Każda z tych funkcji agregujących generuje jedną wartość sumacyjną dla wszystkich pobranych wierszy. Przykład wykorzystuje bazę danych AdventureWorks2025.

SELECT AVG(VacationHours)AS 'Average vacation hours',
    SUM(SickLeaveHours) AS 'Total sick leave hours'
FROM HumanResources.Employee
WHERE JobTitle LIKE 'Vice President%';

Oto zestaw wyników.

Average vacation hours       Total sick leave hours
 ----------------------       ----------------------
25                           97

(1 row(s) affected)

B. Używając funkcji SUM i AVG z klauzulą GROUP BY

Gdy jest używana z klauzulą GROUP BY , każda funkcja agregowana generuje jedną wartość obejmującą każdą grupę, zamiast jednej wartości obejmującej całą tabelę. Poniższy przykład generuje wartości podsumowujące dla każdego obszaru sprzedaży w bazie AdventureWorks2025. Podsumowanie przedstawia średnią premię otrzymaną przez sprzedawców w każdym terytorium oraz sumę sprzedaży od początku roku dla każdego terytorium.

SELECT TerritoryID, AVG(Bonus)as 'Average bonus', SUM(SalesYTD) as 'YTD sales'
FROM Sales.SalesPerson
GROUP BY TerritoryID;
GO

Oto zestaw wyników.

TerritoryID Average Bonus         YTD Sales
----------- --------------------- ---------------------
NULL        0.00                  1252127.9471
1           4133.3333             4502152.2674
2           4100.00               3763178.1787
3           2500.00               3189418.3662
4           2775.00               6709904.1666
5           6700.00               2315185.611
6           2750.00               4058260.1825
7           985.00                3121616.3202
8           75.00                 1827066.7118
9           5650.00               1421810.9242
10          5150.00               4116871.2277

(11 row(s) affected)

C. Używanie AVG z DISTINCT

To stwierdzenie zwraca średnią cenę katalogową produktów w bazie AdventureWorks2025. Używając DISTINCT, obliczenia uwzględniają jedynie unikalne wartości.

SELECT AVG(DISTINCT ListPrice)
FROM Production.Product;

Oto zestaw wyników.

------------------------------
437.4042

(1 row(s) affected)

D. Używanie AVG bez DISTINCT

Bez DISTINCT AVG funkcja znajduje średnią cenę katalogową wszystkich produktów w tabeli Product bazy AdventureWorks2025, w tym wszelkie zduplikowane wartości.

SELECT AVG(ListPrice)
FROM Production.Product;

Oto zestaw wyników.

------------------------------
438.6662

(1 row(s) affected)

E. Używając klauzuli OVER

Poniższy przykład wykorzystuje funkcję AVG z klauzulą OVER, aby uzyskać średnią ruchomą rocznej sprzedaży dla każdego terytorium w tabeli Sales.SalesPerson w bazie AdventureWorks2025. Dane są podzielone przez i TerritoryID logicznie uporządkowane przez .SalesYTD Oznacza to, że funkcja AVG jest obliczana dla każdego terytorium na podstawie roku sprzedaży. Po TerritoryID pierwsze, są dwa wiersze dla roku sprzedaży 2005, które reprezentują dwóch sprzedawców z sprzedażą w danym roku. Średnia sprzedaż dla tych dwóch wierszy jest obliczana, a następnie trzeci wiersz reprezentujący sprzedaż za rok 2006 jest uwzględniany w kalkulacji.

SELECT BusinessEntityID, TerritoryID
   ,DATEPART(yy,ModifiedDate) AS SalesYear
   ,CONVERT(VARCHAR(20),SalesYTD,1) AS  SalesYTD
   ,CONVERT(VARCHAR(20),AVG(SalesYTD) OVER (PARTITION BY TerritoryID
                                            ORDER BY DATEPART(yy,ModifiedDate)
                                           ),1) AS MovingAvg
   ,CONVERT(VARCHAR(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID
                                            ORDER BY DATEPART(yy,ModifiedDate)
                                            ),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5
ORDER BY TerritoryID,SalesYear;

Oto zestaw wyników.

BusinessEntityID TerritoryID SalesYear   SalesYTD             MovingAvg            CumulativeTotal
---------------- ----------- ----------- -------------------- -------------------- --------------------
274              NULL        2005        559,697.56           559,697.56           559,697.56
287              NULL        2006        519,905.93           539,801.75           1,079,603.50
285              NULL        2007        172,524.45           417,375.98           1,252,127.95
283              1           2005        1,573,012.94         1,462,795.04         2,925,590.07
280              1           2005        1,352,577.13         1,462,795.04         2,925,590.07
284              1           2006        1,576,562.20         1,500,717.42         4,502,152.27
275              2           2005        3,763,178.18         3,763,178.18         3,763,178.18
277              3           2005        3,189,418.37         3,189,418.37         3,189,418.37
276              4           2005        4,251,368.55         3,354,952.08         6,709,904.17
281              4           2005        2,458,535.62         3,354,952.08         6,709,904.17

(10 row(s) affected)

W tym przykładzie klauzula OVER nie zawiera PARTITION BY. Oznacza to, że funkcja dotyczy wszystkich wierszy zwracanych przez zapytanie. Klauzula ORDER BY określona w klauzuli OVER określa logiczną kolejność, do której funkcja AVG ma zastosowanie. Zapytanie zwraca średnią ruchomą sprzedaży według roku dla wszystkich obszarów sprzedaży określonych w klauzuli WHERE. Klauzula ORDER BY określona w instrukcji SELECT określa kolejność, w jakiej instrukcja SELECT wyświetla wiersze zapytania.

SELECT BusinessEntityID, TerritoryID
   ,DATEPART(yy,ModifiedDate) AS SalesYear
   ,CONVERT(VARCHAR(20),SalesYTD,1) AS  SalesYTD
   ,CONVERT(VARCHAR(20),AVG(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate)
                                            ),1) AS MovingAvg
   ,CONVERT(VARCHAR(20),SUM(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate)
                                            ),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5
ORDER BY SalesYear;

Oto zestaw wyników.

BusinessEntityID TerritoryID SalesYear   SalesYTD             MovingAvg            CumulativeTotal
---------------- ----------- ----------- -------------------- -------------------- --------------------
274              NULL        2005        559,697.56           2,449,684.05         17,147,788.35
275              2           2005        3,763,178.18         2,449,684.05         17,147,788.35
276              4           2005        4,251,368.55         2,449,684.05         17,147,788.35
277              3           2005        3,189,418.37         2,449,684.05         17,147,788.35
280              1           2005        1,352,577.13         2,449,684.05         17,147,788.35
281              4           2005        2,458,535.62         2,449,684.05         17,147,788.35
283              1           2005        1,573,012.94         2,449,684.05         17,147,788.35
284              1           2006        1,576,562.20         2,138,250.72         19,244,256.47
287              NULL        2006        519,905.93           2,138,250.72         19,244,256.47
285              NULL        2007        172,524.45           1,941,678.09         19,416,780.93
(10 row(s) affected)

Treści powiązane

  • Last updated on