AVG (Transact-SQL)

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics AnalyticsPlatform System (PDW)Titik akhir analitik SQL di Microsoft FabricWarehouse di Microsoft Fabric

Fungsi ini mengembalikan rata-rata nilai dalam grup. Ini mengabaikan nilai null.

Konvensi sintaks transact-SQL

Sintaksis

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

Catatan

Untuk melihat sintaks Transact-SQL untuk SQL Server 2014 (12.x) dan versi yang lebih lama, lihat Dokumentasi versi sebelumnya.

Argumen

SEMUA
Menerapkan fungsi agregat ke semua nilai. ALL adalah default.

NYATA
Menentukan bahwa AVG hanya beroperasi pada satu instans unik dari setiap nilai, terlepas dari berapa kali nilai tersebut terjadi.

expression
Ekspresi kategori jenis data numerik atau perkiraan numerik yang tepat, kecuali untuk jenis data bit. Fungsi dan subkueri agregat tidak diizinkan.

OVER ( [ partition_by_clause ] order_by_clause)
partition_by_clause membagi tataan hasil yang dihasilkan oleh klausul FROM menjadi partisi tempat fungsi diterapkan. Jika tidak ditentukan, fungsi memperlakukan semua baris hasil kueri yang ditetapkan sebagai grup tunggal. order_by_clause menentukan urutan logis tempat operasi dilakukan. Order_by_clause diperlukan. Untuk informasi selengkapnya, lihat Klausul OVER (Transact-SQL).

Jenis yang dikembalikan

Hasil ekspresi yang dievaluasi menentukan jenis pengembalian.

Hasil ekspresi	Jenis Pengembalian
kecil	int
smallint	int
int	int
bigint	bigint
kategori desimal (p, s)	desimal(38, maks,6))
kategori uang dan smallmoney	Uang
float dan kategori nyata	float

Keterangan

Jika jenis ekspresi data adalah jenis data alias, jenis pengembalian juga merupakan jenis data alias. Namun, jika jenis data dasar dari jenis data alias dipromosikan, misalnya dari tinyint ke int, nilai yang dikembalikan akan mengambil jenis data yang dipromosikan, dan bukan jenis data alias.

AVG () menghitung rata-rata sekumpulan nilai dengan membagi jumlah nilai tersebut dengan hitungan nilai nonnull. Jika jumlah melebihi nilai maksimum untuk jenis data dari nilai yang dikembalikan, AVG() akan mengembalikan kesalahan.

AVG adalah fungsi deterministik ketika digunakan tanpa klausa OVER dan ORDER BY. Ini tidak deterministik ketika ditentukan dengan klausul OVER dan ORDER BY. Untuk informasi selengkapnya, lihat Fungsi Deterministik dan Nondeterministik.

Contoh

J. Menggunakan fungsi SUM dan AVG untuk perhitungan

Contoh ini menghitung rata-rata jam liburan, dan jumlah jam cuti sakit, yang telah digunakan oleh wakil presiden Adventure Works Cycles. Masing-masing fungsi agregat ini menghasilkan satu nilai ringkasan untuk semua baris yang diambil. Contohnya menggunakan database AdventureWorks2022.

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

Berikut adalah hasil yang ditetapkan.

Average vacation hours       Total sick leave hours
 ----------------------       ----------------------
25                           97
  
(1 row(s) affected)

B. Menggunakan fungsi SUM dan AVG dengan klausa GROUP BY

Saat digunakan dengan GROUP BY klausa, setiap fungsi agregat menghasilkan satu nilai yang mencakup setiap grup, alih-alih satu nilai yang mencakup seluruh tabel. Contoh berikut menghasilkan nilai ringkasan untuk setiap wilayah penjualan dalam database AdventureWorks2022. Ringkasan ini mencantumkan bonus rata-rata yang diterima oleh orang penjualan di setiap wilayah, dan jumlah penjualan tahun ke tanggal untuk setiap wilayah.

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

Berikut adalah hasil yang ditetapkan.

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. Menggunakan AVG dengan DISTINCT

Pernyataan ini mengembalikan harga daftar rata-rata produk dalam database AdventureWorks2022. Melalui penggunaan DISTINCT, perhitungan hanya mempertimbangkan nilai unik.

SELECT AVG(DISTINCT ListPrice)  
FROM Production.Product;  

Berikut adalah hasil yang ditetapkan.

------------------------------
437.4042
  
(1 row(s) affected)

D. Menggunakan AVG tanpa DISTINCT

Tanpa DISTINCT, AVG fungsi menemukan harga daftar rata-rata semua produk dalam Product tabel dalam database AdventureWorks2022, termasuk nilai duplikat apa pun.

SELECT AVG(ListPrice)  
FROM Production.Product;  

Berikut adalah hasil yang ditetapkan.

------------------------------
438.6662
  
(1 row(s) affected)

E. Menggunakan klausa OVER

Contoh berikut menggunakan fungsi AVG dengan klausul OVER, untuk memberikan rata-rata penjualan tahunan yang bergerak untuk setiap wilayah dalam Sales.SalesPerson tabel dalam database AdventureWorks2022. Data dipartisi oleh TerritoryID dan secara logis diurutkan oleh SalesYTD. Ini berarti bahwa fungsi AVG dihitung untuk setiap wilayah berdasarkan tahun penjualan. Perhatikan bahwa untuk TerritoryID 1, ada dua baris untuk penjualan tahun 2005, yang mewakili dua sales-people dengan penjualan tahun itu. Penjualan rata-rata untuk kedua baris ini dihitung, dan kemudian baris ketiga yang mewakili penjualan untuk tahun 2006 disertakan dalam perhitungan.

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;  

Berikut adalah hasil yang ditetapkan.

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)  
  

Dalam contoh ini, klausa OVER tidak menyertakan PARTITION BY. Ini berarti bahwa fungsi akan berlaku untuk semua baris yang dikembalikan oleh kueri. Klausa ORDER BY yang ditentukan dalam klausul OVER menentukan urutan logis yang diterapkan fungsi AVG. Kueri mengembalikan rata-rata penjualan yang bergerak, menurut tahun, untuk semua wilayah penjualan yang ditentukan dalam klausul WHERE. Klausa ORDER BY yang ditentukan dalam pernyataan SELECT menentukan urutan di mana pernyataan SELECT menampilkan baris kueri.

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;  

Berikut adalah hasil yang ditetapkan.

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)  

Baca juga

Fungsi Agregat (T-SQL)
Klausul OVER (Transact-SQL)