Sql'de Rollup ve Cube Kavrami

Sql’de raporlama yaparken, en cok istenen isteklerden biridir, tablonun sonunda ara toplam veya genel toplamin getirilmesi. Ornegin, AdventureWorks uzerinde, urun renklerine gore group by yapip, count ( *) ile de satilan toplam urun miktarini buluruz. Ama ornegin renklere gore de bir toplam almak istersek, bunu sadece group by fonksiyonlariyla yapamayiz.

Yellow    Jerseys    4
Yellow    Road Bikes    9
Yellow    Road Frames    5
Yellow    Touring Bikes    9
Yellow    Touring Frames    9

seklinde bir cikti elde ederiz. Fakat bizim istedigimiz, yellow rengine sahip toplam kac urunun satildigi bilgisidir. Iste bunu yaparken yazdigimiz query’e with rollup komutunu eklersek, istedigimiz raporu eklemis oluruz.

select PP.Color,PPS.Name,COUNT(*) from Production.Product PP inner join Production.ProductSubcategory PPS on PP.ProductSubcategoryID=PPS.ProductSubcategoryID group by PP.Color,PPS.Name with rollup

Bu kodu calistirdigimiz zaman bu sefer su sonucu almis olacagiz;



Goruldugu gibi NULL yazan satir aslinda bizim rengi yellow olan urunlerimizin toplam sayisi. Yani Ara Toplam diyebiliriz. Peki cift tarafli toplam yapmak istersek..

Yapmamiz gereken tek sey with rollup yerine with cube yazmak olacaktir. Sonuc kumesinde hem renklere gore bir liste, hem de urunlere gore bir liste yapilip ara toplam belirlenecektir.

Ustteki iki ornekte soyle bir eksiklik gozumuze carpiyor. Ara toplamlarin bulundugu hucrelerde NULL yaziyor. Biz, sonuc kumesine bakarak buralarin ara toplam satiri oldugunu anliyoruz. Ama daha buyuk raporlamalarda, hem bu karisacaktir hem de kullaniciya boyle anlama zorlugu cekmek dogru bir durum degildir. Ayrica soyle bir karisiklikliga da sebep olabilir. Ornegin RoadBikes’larin renklerine gore ara toplam hazirlanirken,

RoadBikes NULL 44

oldugu zaman, ara toplam mi, renk mi veritabanindan NULL geliyor? Bunun anlasilamadigi durumlarda ciddi zararla meydana cikabilir.

Iste bu hatadan kurtulmak icin, ara toplam satirlarinda NULL yerine Ara Toplam yazmaliyiz. Grouping metotu sayesinde, eger bu satir ara toplam satiriysa, geriye 1 degerini dondurur, aksi taktirde burasi ara toplam satiri degildir. Basit bir case-when yapisiyla bunun da ustesinden gelebiliriz.

Simdi baska ornekten, hem Cube hem de grouping fonksiyonunu kullanarak daha iyi algilayalim yapmak istediklerimizi. Yine AdventureWorks veritabani uzerinden gidersek, yasadigi sehirlere gore bay ve bayan calisanlari listeleyen bir kod yazalim. Cift tarafli toplam olsun ama. Yani Seatle 30 erkek, 40 bayan vs. diye sehirlere gore erkek bayan sayilarini yazsin, en sona da toplam erkek, toplam bayan sayilari yazilsin.
Bu ornegimizde, rollup keywordunu kullanamayiz, cunku rollup ile sadece tek tarafli toplam elde ederiz. Burada kullanmamiz gereken komut, Cube’dur.

Yazacagimiz sorgu su sekilde olmalidir,

SELECT PA.City,case when GROUPING(PA.City)=1 and GROUPING(HRE.Gender)=1 then 'Genel Toplam' when GROUPING(HRE.Gender)=1 then 'Ara Toplam' else HRE.Gender end,COUNT(*) FROM Person.Address PA INNER JOIN HumanResources.EmployeeAddress HRA ON PA.AddressID = HRA.AddressID

INNER JOIN HumanResources.Employee HRE ON HRA.EmployeeID =HRE.EmployeeID group by HRE.Gender,PA.City with cube 

Ekran ciktisina bakarsak,

Ve en son satirda da;

NULL Genel Toplam 290 –Sehirlere gore Genel Toplam
NULL F 84
NULL M 206

Goruldugu gibi, hem sehirlere gore hem de cinsiyetlere gore toplam yapmis olduk. Grouping fonksiyonundan geriye 1 deger dondurenler icin Ara Toplam veGenel Toplam yazilarini yazdirmis olduk. Boylece ortaya cikabilecek olan yanlislarin onune gecmis olduk.