Aggregation Fonksiyonları – Bölüm 29

Bu bölümde, kayıtları işlerken yerleşik aggregate fonksiyonlarının nasıl kullanılacağını öğreneceğiz. Daha önceden SUM, MAX ve AVG gibi aggregate fonksiyonlarının olduğunu görmüştük.

Aggregation Fonksiyonlarıyla Çalışmak

Bir SELECT ifadesinde WHERE ile filtrelemeyi hatırlıyorsunuzdur.

Ayrıca, amaçlarınızı karşılayan kayıt sayısı ya da sipariş özetleri gibi analiz yapmanız gerekebilir. Bunun için aynı anda birden çok satırda çalışabilen aggregate işlevlerini kullanabilirsiniz.

SQL Server’da birçok aggregate fonksiyonu bulunmaktadır. Bu bölümde SUM, MIN, MAX, AVG ve COUNT gibi yaygın fonksiyonlar hakkında bilgi edineceğiz.

Aggregation fonksiyonlarını kullanırken aşağıdakileri göz önünde bulundurmakta fayda vardır:

• Aggregate işlevleri tek bir skaler değer döndürür ve SELECT, HAVING ve ORDER BY deyimleri gibi tek bir ifadenin işlendiği SELECT ifadelerinde kullanılabilir.
• Aggregate işlevleri COUNT (*) kullanımı hariç NULL değerleri yok sayarlar.
• SELECT listesindeki aggregate fonksiyonları sütun takma adı oluşturmaz. AS ile takma adı kendiniz belirtebilirsiniz.
• Aggregate işlevleri, SELECT aşamasına iletilen tüm satırlarda çalışır. GROUP BY deyimi yoksa, tüm satırlar özetlenir. Bir sonraki bölümde GROUP BY hakkında daha fazla bilgi edineceğiz.

SQL Server, yerleşik işlevlerinden de ötede işlem gerçekleştirmek için .NET Common Language Runtime (CLR) aracılığıyla kullanıcı tanımlı aggregate fonksiyonları için mekanizma bulundurur.

Yerleşik Aggregate Fonksiyonları

SQL Server’da yaygın olarak kullanılan fonksiyonlar şunlardır:

Fonksiyon ismi	Syntax	Açıklama
SUM	SUM(<expression>)	Bir sütundaki NULL olmayan tüm sayısal değerleri toplar.
AVG	AVG (<expression>)	Bir sütundaki NULL olmayan tüm sayısal değerlerin ortalamasını alır (sum/count)
MIN	MIN (<expression>)	En küçük sayıyı, en erken tarih / saati veya ilk oluşan dizeyi döndürür (harmanlama sıralama kurallarına göre).
MAX	MAX (<expression>)	En büyük sayıyı, en son tarih / saati veya son oluşan dizeyi döndürür (harmanlama sıralama kurallarına göre).
COUNT veya COUNT_BIG	COUNT(*) veya COUNT(<expression>)	(*) ile, NULL değer dahil tüm satırlar sayılır. Bir sütun şeklinde belirtildiğinde ise bu sütun için NULL olmayan satırların sayısını döndürülür. COUNT bir int döndürür; COUNT_BIG ise big_int döndürür.

Aggregate fonksiyonunun SELECT ifadesinde kullanımı aşağıda TSQL örnek veri tabanını kullanarak örneklendirilmiştir:

SELECT AVG(unitprice) AS avg_price,
MIN(qty)AS min_qty,
MAX(discount) AS max_discount
FROM Sales.OrderDetails; 

Yukarıdaki örnekte GROUP BY ifadesini kullanılmamıştır. Bu nedenle Sales.OrderDetails tablosundaki tüm satırlar, SELECT ifadesindeki aggregation formülleri tarafından özetlenmiştir.

Sonuç:

avg_price min_qty max_discount
--------- ------- ------------
26.2185 1 0.250 

Bu fonksiyonları SELECT ifadesinde kullanılırken, SELECT listesindeki tüm sütunların aggregate fonksiyonuna input olarak verilmesi veya GROUP BY ifadesi kullanılmasıgerekir.

Aşağıdaki sorgu bir hata döndürecektir:

SELECT orderid, AVG(unitprice) AS avg_price, MIN(qty)AS min_qty, MAX(discount) AS
max_discount
FROM Sales.OrderDetails; 

Dönen sonuç:

Msg 8120, Level 16, State 1, Line 1 
Column 'Sales.OrderDetails.orderid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. 

Örnekte GROUP BY deyimi kullanılmadığından tüm satırlar tek bir grup şeklinde ele alınır. Bu nedenle tüm sütunlar aggregate fonksiyonlarına input olarak verilmelidir. Orderid sütun ismini örnekten silmek hatayı önleyecektir.

Aggregate işlevlerle fiyat ve miktar gibi sayısal verilere ek olarak tarih, saat ve karakter verilerini de özetleyebilir. Aşağıda tarih ve karakter değerleri içeren örnek gösterilmektedir:

Bu sorgu MIN ve MAX kullanarak ilk ve son şirketi ada göre döndürür:

SELECT MIN(companyname) AS first_customer, MAX(companyname) AS last_customer
FROM Sales.Customers; 

Dönen sonuç:

first_customer last_customer
-------------- --------------
Customer AHPOP Customer ZRNDE 

Diğer fonksiyon türleri de aggregate fonksiyonları ile birlikte bulunabilir. Örneğin aşağıdaki sorguda YEAR fonksiyonu, MIN ve MAX ifadeleri işlenmeden önce sipariş tarihinin yalnızca yıl kısmını döndürmek için kullanılımıştır:

SELECT MIN(YEAR(orderdate))AS earliest, MAX(YEAR(orderdate)) AS latest
FROM Sales.Orders; 

Dönen sonuç:

earliest latest
-------- -------
2006 2008 

Aggregate Fonksiyonlarında DISTINCT Kullanımı

Önceki bölümlerde yinelenen satırlardan kurtulmak için DISTINCT ifadesinin kullanıldığını görmüştük. Bir aggregation fonksiyonuyla birlikte DISTINCT kullanıldığında, ilk önce yinelenen değerler kaldırılır sonra aggregate fonksiyonu çalışır.

Aşağıdaki örnekte, çalışan kimliği ve yıla göre gruplandırılmış sipariş veren müşterilerin listesi döndürülmektedir:

SELECT empid, YEAR(orderdate) AS orderyear,
COUNT(custid) AS all_custs,
COUNT(DISTINCT custid) AS unique_custs
FROM Sales.Orders
GROUP BY empid, YEAR(orderdate); 

Yukarıdaki örnekte GROUP BY kullanılmıştır. Sonraki bölümde bu ifadeyi ele alacağız. Aşağıdaki örnekte DISTINCT ve DISTINCT olmayan kayıtlar için aggregation fonksiyonlarının kullanımı gösterilmiştir:

empid orderyear all_custs unique_custs
--------------- --------- ------------
1 2006 26 22
1 2007 55 40
1 2008 42 32
2 2006 16 15
2 2007 41 35
2 2008 39 34
3 2006 18 16 
3 2007 71 46
3 2008 38 30 

Sütun 3’te custid sayısının COUNT’una ve sütun 4’te de DISTINCT COUNT’u arasındaki her satırdaki farka dikkat edin. Sütun 3, NULL içerenler dışındaki tüm satırları döndürür. Sütun 4 aslında şu soruyu yanıtlar: “Çalışan başına kaç müşteri düşmektedir?”

Aggregate Fonksiyonlarında NULL Kullanımı

Verilerde NULL’ların olası varlığı T-SQL sorgularıyla nasıl etkileşime girdiğinin farkında olmak önemlidir. Bu olay aggregate ifadeleri için de geçerlidir. Dikkat edilmesi gereken birkaç nokta bulunmaktadır:

• T-SQL aggregate işlevleri NULL’ları yok saymaktadır (COUNT(*) hariç). Örneğin bir SUM işlevi, yalnızca NULL olmayan değerler birbirine ekler. NULL değerleri sıfır olarak değerlendirmeye alınmaz.
• Bir sütundaki NULL’ların varlığı, AVG işlemi kullanırken yanlış hesaplamaya yol açabilir. AVG (<column>) ve (SUM (<column>) / COUNT (*)) arasında sonuçlarda fark ortaya çıkabilir.

Örneğin, t1 adlı aşağıdaki tablo:

C1	C2
1	NULL
2	10
3	20
4	30
5	40
6	50

Aşağıdaki sorguda AVG ve SUM / COUNT (*) ifadesiyle gerçekleştirilen ortalama hesaplamaları arasındaki farkı gösterilmektedir:

SELECT SUM(c2) AS sum_nonnulls,
COUNT(*)AS count_all_rows,
COUNT(c2)AS count_nonnulls,
AVG(c2) AS [avg],
(SUM(c2)/COUNT(*))AS arith_avg
FROM t1; 

Dönen sonuç:

sum_nonnulls count_all_rows count_nonnulls avg arith_avg
------------ -------------- -------------- --- ---------
150 6 5 30 25 

NULL olsun ya da olmasın tüm satırları özetlemeniz gerekiyorsa, NULL’ları aggregate fonksiyonu tarafından kullanılabilecek başka bir değerle değiştirmeyi deneyin.

Aşağıdaki örnekte bir ortalama hesabı yapmadan önce NULL değerleri 0 ile değiştirilmiştir. T2 adlı tabloda aşağıdaki satırlar bulunmaktadır:

c1 c2
----------- -----------
1 1
2 10
3 1
4 NULL
5 1
6 10
7 1
8 NULL
9 1
10 10
11 1
12 10 

Aritmetik ortalama işlemi yaparken NULL yok sayılır ve bunun yerine 0 sayısı kullanılır.

SELECT AVG(c2) AS AvgWithNULLs, AVG(COALESCE(c2,0)) AS AvgWithNULLReplace
FROM dbo.t2; 

Aşağıdaki gibi sonuçlar ile bir uyarı mesajı döner:

AvgWithNULLs AvgWithNULLReplace
------------ ------------------
4 3
Warning: Null value is eliminated by an aggregate or other SET operation.

Kaynak:
Aggregate Functions (Transact-SQL)