Gruplama fonksiyonlarında en temelinde group by daha sonrasında ise partition by, cube ve rollup yer almaktadır.

Gruplama Fonksiyonlarının Amacı

Toplama fonksiyonunu kullanılan durumlarda alt toplamlar oluşturabilmek için Group by ile birlikte Cube ve Rollup da kullanabiliriz.

Partition By

Gruplama fonksiyonlarından ilki olan Partition by fonksiyonu genellikle Over alt fonksiyonu ile beraber kullanılır. Partition by fonksiyonunu, sonuç kümesini bölümlere ayırmak ve bölümlenmiş verilerin her bir alt kümesinde hesaplama yapmak için kullanırız.

Over deyimi, toplama fonksiyonu içermeyen bir sorguya toplama fonksiyonlarının eklenebilmesini sağlamaktadır.

Partition by kullanımı:

Select COUNT(kolon1) OVER ( PARTITION BY kolon2, kolon3 ) kolon4 from tablo1

gibidir.

Northwind database’i kullanılarak yapılan örnekte Products tablosundan ProductName’e ve Orders tablosundan ShipCity’e göre Unit Price toplamını getirmeye çalışalım.(Ship Region kolonu null olanların olmadığı ve ProducName’in Alice Mutton olduğu bir sorgu olması isteniyor.)

1. Öncelikle kullanacağınız tabloları belirleyin. (Products, Orders)
2. Join yapılacak tabloların belirlenmesi gerekmektedir. (ShipRegion’ı Orders tablosundan ProductID’yi Product tablosundan çekmek için iki tabloyu inner join yapılması gerekmekte fakat tablolar arasında direkt bir bağlantı bulunmamaktadır. Bundan dolayı Order Details tablosundan products tablosuna bağlantı sağlanmalıdır. 2 tane inner join yapılarak istenen tablolara erişim sağlanmaktadır.)
3. Where koşulunu sağlayacak durumların yazılması gerekmektedir. (Burada koşullar ProductName’in Alice Mutton olması ve ShipRegion’ın null olmamasıdır.)
4. Group by yapılacak kolonların belirlenmesi gerekmektedir. (Fakat bu sorguda group by yerine partition by ile gerçekleştirerek kümülatif bir toplam unitprice bulunması isteniyor.)
5. Son olarak ise order by kullanılabilir. (Sıralamaların düzenli olması için ShipRegion ve ProductName’e göre order by yapılmaktadır.) (Komutların çalışma sırasını daha detaylı incelemek için)

Select p.ProductName, o.ShipRegion,
Sum(p.UnitPrice) OVER(PARTITION BY o.ShipRegion,p.ProductName) SumUPrice
from Products p 
inner join [Order Details] od on  od.ProductID=p.ProductID 
inner join Orders o on o.OrderID=od.OrderID
where o.ShipRegion is not null and ProductName='Alice Mutton'
order by o.ShipRegion,p.ProductName

Kodun çıktısı:

Partition by ile 16 satır veri olması sağlandı.

Partition By ve Group By Farkı

Group by, sonuç kümesini bir veya birden çok sütuna göre gruplandırmak amacıyla toplama işlevleriyle birlikte kullanılır.

Partition By ise toplama işlevlerini kullanarak sonuç kümesini bölümlere ayırır. Her bölüme ayrı ayrı uygulanır ve her bölüm için hesaplama yeniden başlatarak çalışmaktadır.

Group by ile Partition by arasındaki farkı anlamak için yukarıda yazılmış olan örnek group by ile yazılacak olursa

Select p.ProductName, o.ShipRegion,Sum(p.UnitPrice) SumUPrice from Products p
inner join [Order Details] od on od.ProductID=p.ProductID
inner join Orders o on o.OrderID=od.OrderID
where o.ShipRegion is not null and ProductName='Alice Mutton'
group by o.ShipRegion,p.ProductName
order by o.ShipRegion,p.ProductName

Kodun çıktısı(Group By)

Group by yaparak 8 adet satır elde edildi, shipregion ve productname bazında gruplandırılmış oldu.

Cube ve Rollup

Cube ve roll up birbirine çok benzemektedir. Cube ile gruplandırılmış her seviyede bir alt toplam oluşturabiliriz. Rollup’da ise genel bir alt toplam oluşmaktadır.

Cube şu şekilde oluşturulur:

Select kolon1, kolon2, toplama fonksiyonu from tablo Group by Cube(kolon1, kolon2)

Rollup ise şu şekilde oluşturulur:

Select kolon1, kolon2, toplama fonksiyonu From tablo Group by Rollup(kolon1, kolon2)

Bir örnekle açıklayacak olursak

Örnek: Çalışanların ID’sine göre yıl yıl UnitPrice ve Quantity miktarını çarparak toplam kazandırdıkları parayı bulalım.

Cube ile yapıldığında

Select EmployeeID, YEAR(OrderDate) date, SUM(UnitPrice*Quantity) price from Orders o
inner join [Order Details] od on od.OrderID=o.OrderID 
Group By Cube(EmployeeID, YEAR(OrderDate))

Cube ile EmployeeID’si 1,2,3… gruplandırarak 1996 yılındaki tüm çalışanların toplam kazandırdığı para miktarı bulunmaktadır.(Bunu EmployeeID’ye null atayarak getirmektedir.)

Buna ek olarak da EmployeeID’si 1 olan çalışanın 1996,1997.1998 yılında toplamda kazandırdığı para miktarını getirmektedir.(Bunu tarihe null atayarak employeeID ve price bazında getirmektedir.) Ayrıca hem tarihe null atayarak hem de employeeID’ye null atayarak tüm çalışanların toplamda ne kadar kazandırdığı da bulunmaktadır.

Rollup ile yapıldığında

Select EmployeeID, YEAR(OrderDate) date, SUM(UnitPrice*Quantity) price from Orders o
inner join [Order Details] od on od.OrderID=o.OrderID 
Group By Rollup(EmployeeID, YEAR(OrderDate))

Rollup ile employeeID’yi baz alarak tüm yılların toplamında o çalışanın ne kadar kazandırdığını bulmaktadır. Ayrıca cube de olduğu gibi hem tarihe null atayarak hem de employeeID’ye null atayarak tüm çalışanların toplamda ne kadar kazandırdığı da bulunmaktadır.

Group by ile yapıldığında

Select EmployeeID, YEAR(OrderDate) date, SUM(UnitPrice*Quantity) price from Orders o
inner join [Order Details] od on od.OrderID=o.OrderID 
Group By EmployeeID, YEAR(OrderDate)
order by EmployeeID, date

Tarih ve EmployeeID bazında gruplama yaparak sonuç ekranını getirmektedir. Burada satır sayısının 27 olmasının sebebi cube ve rollup’da olan alt toplamlar getirilmediği içindir.

Rollup ve Cube’deki null verileri ayırmak için Grouping Sets fonksiyonu kullanılabilir.