Group By, Rollup, Cube, Grouping Sets, Grouping, Grouping_ID kavramlarına genel bakış

    Evvelâ elimizde örnek veri olması açısından aşağıdaki sorguyu veritabanınızda çalıştırmanızı istirham ediyorum.

 

Create Table Sales

(

Id int primary key,

 Continent varchar(50),

Country varchar(50),

City varchar(50),

SalesAmount int

)

 

GO

 

Insert Into Sales Values(1, 'Asia', 'India', 'Bangalore', 1000)

Insert Into Sales Values(2, 'Asia', 'India', 'Chennai', 2000)

Insert Into Sales Values(3, 'Asia', 'Japan', 'Tokyo', 4000)

Insert Into Sales Values(4, 'Asia', 'Japan', 'Hiroshima', 5000)

Insert Into Sales Values(5, 'Europe', 'United Kingdom', 'London', 1000)

Insert Into Sales Values(6, 'Europe', 'United Kingdom', 'Manchester', 2000)

Insert Into Sales Values(7, 'Europe', 'France', 'Paris', 4000)

Insert Into Sales Values(8, 'Europe', 'France', 'Cannes', 5000)

 

ROLLUP ve CUBE kullanımı

Bu iki deyim herhangi bir OLAP yatırımı olmaksızın, OLTP veritabanında, verilerinizi hızlı bir şekilde özetleyerek, çok çok etkili istatistiksel sonuçlar almanızı sağlarlar..Aggregate functions(SUM, AVG, MAX, MIN ve COUNT) dediğimiz sözcüklerle kullanılırlar ve aynı anda en fazla 10 kolon üzerinde özetleme yapabilirler. Cube ve Rollup birbirlerine çok benzerler ancak aralarında çok ince bir fark vardır. Cube deyimi, en yalın ifade ile gruplama işlemini bir küpe dönüştürür ve bu gruplandırılmış seviyedeki her birleşim için alt toplam oluşturur. Yazım şekline bakacak olursak;  

 

Bakınız Resim-1.

 

 

 Rollup deyiminde ise hiyerarşinin tamamı için alt toplam oluşturulabilir. Yani içten dışa doğru sütunlara ait toplamları bularak ilerler. Şimdi bu deyime ait söz dizimine bakalım;

 

 Bakınız Resim-2.

 

Görüldüğü gibi şayet bu iki deyim üzerinde tek bir gruplama sütunu kullanıldı ise sonuçlar aynı gelecektir. Şimdi GROUP BY elementi üzerinden sorgularımızı biraz daha genişletip aradaki farkı daha iyi analiz edelim;

 

Bakınız Resim-3.

Bakınız Resim-4.

 

Cube deyimi ile yapılan sorguda 48 satır etkilenirken, Rollup ile yapılan sorgu sonucu 15 satır kayıt dönmüştür. Bunun sebebi, cube deyiminin seçilen sütunlardaki değerlerin tüm kombinasyonları için bir sonuç kümesi oluşturması, rollup deyiminde ise seçilen sütunlardaki değerler için hiyerarşik olarak sonuç üretilmesi denilebilir. Cube ve rollup kavramlarını +1 birim daha yakın mercek altına alıp çalışma mantığından bahsetmek istiyorum. Aynı zamanda yukarıda bahsettiklerimde karşılanmış olacak bu sayede.

 

Bakınız Resim-5.

Bakınız Resim-6.

 

Şekildeki sorgularda da görüldüğü gibi cube olası tüm kombinasyonları içerir ancak rollup'da özetle hiyerarşik düzen söz konusudur. Peki rollup ve cube kullanımını bilmiyor olsaydık sütun bazlı özet kırılım sonuçlarını nasıl elde ederdik ? Bu sorunun cevabı aşağıdaki şekilde olduğu gibidir.

 

Bakınız Resim-7.

 

Gördüğünüz gibi ne kadar karmaşık ve sorgu tekrarı gerektiren bir sorgu cümlesi.  Lütfen bu örnekleri sizde deneyiniz..

ROLLUP ve CUBE kavramları ile ilgili bu kadar detaylı bilgiden sonra sıradaki başlık konularını incelemeye başlayabiliriz. Bundan sonraki başlık konularıda bütün olarak birbirleriyle ilintili olduğu için bu detayların işimize yarayacağını düşünüyorum. O halde makalemize GROUPING SETS kavramıyla devam edebiliriz..  

 

GROUPING SETS kullanımı

 

GROUPING SETS; GROUP BY ile getirilen farklı sonuç sorgularını farklı seviyelerde ele almak için kullanılır. ROLLUP VE CUBE'de olduğu gibi UNION ALL sorgularına alternatiftir. Bu deyim Sql 2008 sonrası, geliştiricilerin kullanımına açılmıştır.GROUP BY ın bir alt sorgusu gibi çalışır ve çoklu sayıda sorgu yazmamıza imkan verir. Burada amaç farklı kırılımlarla veriyi ele alıp buna göre sonuç döndürmektir. Dilerseniz bu ifadenin yazım şekline canlı örnek üzerinden göz atalım;

 

Bakınız Resim-8.

 

Yukarıdaki örneğe bakıldığı zaman, ROLLUP deyimi ile aynı sonucu getirdiğini görüyoruz. Peki kullanım açısından GROUPING SETS ve ROLLUP/CUBE arasında ne fark vardır? Bu sorunun cevabı yine yukarıdaki sorguda saklı diyebilirim zira GROUPING SETS ifadesinde isteğe bağlı olarak kolonlar arasında kırılım yapabiliriz. Ancak ROLLUP/CUBE deyiminde böyle bir şansımız bulunmamaktadır. Bize hiyerarşiyi, yani ara ve genel toplam değerlerini olduğu gibi getirir. Örneğin sadece sütunların ara toplamlarını görmek isteyip genel toplamı görmek istemeyebilirsiniz böyle bir durumda '()' ile belirttiğim satırı sorgudan kaldırıyor olmamız yeterlidir. (Lütfen sizde GROUPING SETS ile farklı kombinasyonları deneyip aradaki farkı görmeye çalışınız.)

 

Anahtar Not: GROUPING SETS içerisinde kullanılan boyutların kullanım sırası önemli değildir. Sürekli aynı sonucu döndürür. Ayrıca yukarıdaki resimde yazmış olduğum ORDER BY deyiminin kullanımına dikkat ediniz.  

 

Bakınız Resim-9.

 

Yukarıdaki resimde GROUPING SETS içersinde ROLLUP kullanarak sizlere bu ifadenin bu şekilde de yazılabileceğini göstermek istiyorum. Bu ifadenin anlamı; "Bana Continent alanını sabitle, Country ve City alanlarının ara toplamlarını getir" anlamı taşımaktadır. Daha açık ifade ile bu örnekte ara toplamları istediğimiz kolonlar için açıp kapatabiliriz. Sizde bunun tam tersini yapar mısınız ? Yani Country ve City alanını sabitleyip Continent için ara toplam almayı dener misiniz?

 

Buraya kadar herşey güzel..Ancak çoklu grouping sets sorgularının geliştirici tarafına getirdiği bir karmaşa vardır, bu da özetleme kırılımlarından dönen 'NULL' satırlardır. Bu karmaşayı aşmak için GROUPING_ID fonsiyonunu kullanırız. Şimdi dilerseniz bu fonksiyonu detaylıca ele alalım.  

 

 GROUPING_ID kullanımı

 

 Öncelikle grouping_id fonksiyonuna niçin ihtiyaç duyarız konuya bunu açıklamakla başlayalım. Rollup ve cube ile yapmış olduğumuz örnek sorgu kümelerinde gördüğünüz gibi NULL satırlara rastlıyoruz. Elbette biz biliyoruzki hesaplamalara uğrayan ara ve genel toplamların yansımaları bunlar. Ancak çok çok büyük veri kümeleri ile çalıştığınızı tahayyül edin ve bu işleme soktuğunuz verilerinizin gerçekten NULL satıra sahip olduğunu düşünün. İşte bu durumda dönen satırlar arasındaki ayrımı kolayca fark etmek için bu fonksiyonu kullanırız. Şayet grouping_id için atadığımız parametre değeri geriye 1 döndürüyorsa bu demek oluyorki bu ifade özetlemeden dolayı NULL değer döndürmüştür, ancak herhangi bir özetlemeye sahip satır yoksa 0 değerini döndürür. Bu işlemi özetlenmiş bir sorgu sonucundan sadece ara toplam yada genel toplam satırlarını çekmek için kullanabiliriz. Şimdi bu tanımımızı destekleyecek örnekler yapalım;  

 

Bakınız Resim-10.

 

Bu sorguyu inceleyecek olursak; Grouping_id() fonksiyonu ile türetmiş olduğumuz G_Country sütunu için istediğimiz sonucu elde ettik yani gördüğünüz gibi özetlenmiş kısımlarda Country sütununda NULL satırlar için 1 sonucunu geri döndürdük. Gelelim G_Hepsi sütununa. Bu alanda farklı sayısal değerler gözümüze çarpıyor..Bu işlem matematiksel üs alma işlemini yapmıştır arkadaşlar. Son satırdaki 7 değerini inceleyecek olursak; şekildeki sırasıyla Continent(2 üzeri 2 = 4), Country(2 üzeri 1 = 2), City(2 üzeri 0 = 1) Toplam=4+2+1=7 sonucunu elde ettik. Peki bu bizim ne işimize yarar derseniz onu da sorgu üzerinde görelim;  

 

Bakınız Resim-11.

 

Sorgu sonucundan gördüğümüz üzere süzme işlemi yaparak ara toplam değerlerini getirdik ya da having koşulunda 7 diyip sadece genel toplamı da getirebiliriz. Bu artık size kalmış..

 

Anahtar Not: Grouping_id çalışma mantığı kısaca şu şekildedir: GROUPING_ID(A, B, C)= GROUPING(A) + GROUPING(B) + GROUPING(C)

 

Yani içerisinde bulunan parametrelerin tümünü parçalar. Ayrıca binary'den decimal'e doğru bir dönüştürme performansı sergiler. Herşey iyi güzelde sizce hesaplanmış satırların NULL olarak gözükmesi şık mı? Gelin bu satırlara isim verelim.. O halde son başlığımız olan grouping konusunu ele alabiliriz artık.  

 

GROUPING kullanımı

 

 

Grouping, cube ve rollup deyimi ile birlikte kullanılan bir gruplamalı fonksiyondur. Bu deyimlerden dönen ara toplam ve genel toplamların, kullanıcıya daha anlamlı gözükmesi için isimlendirme verilmesi amacı ile kullanılır. Yani NULL satırların Case When sözcükleriyle TOPLAM gibi ifadelere çevrilmesi görevini görür. Dilerseniz konuyu örnekle pekiştirelim;

 

Bakınız Resim-12.

 

Gördüğünüz gibi en alt satıra TOPLAM yazdırdık, aynı şekilde bunu kaç tane kırılım var ise Case When sözcükleriyle çoğaltıp örnek sorgularınızda yazdırabilirsiniz. Bu arada dikkatinizi çekti mi ROLLUP sözcüğünü bu kez farklı bir yazımla kullandım ancak sonuç kümesinde bir fark görülmeyecektir, ROLLUP ya da WITH ROLLUP(WITH CUBE) size aynı sonucu getirecektir. Sadece ROLLUP'da kırılım sütunları birden fazla ise parantez içerisine alınır, WITH ROLLUP'da ise virgülle ayrılır.

Aynı işlemi birde isnull ile yapmayı deneyelim;

 

Bakınız Resim-13.

 

Gördüğünüz gibi bir fark yok, aynı sonuç kümesi geldi. Peki o halde GROUPING kullanmanın bize sağladığı avantaj nedir ? Arkadaşlar bir kolon, ya özet kırılımdan dolayı ya da tablodaki değeri NULL olduğu için NULL olarak görünebilir. Grouping() fonksiyonu bize bu iki NULL türünü birbirinden ayırmamızı sağlar. Şimdi bunu güzel bir örnekle pekiştirelim. Öncelikle Sales tablosuna bir manipülasyon yapmamız gerekiyor.

 

Bakınız Resim-14.

 

City satırını gördüğünüz gibi güncelledik. Şimdi bu 3 sütunu özet kırılıma sokup NULL değerlerin yansımasına bakalım;

 

Bakınız Resim-15.

 

Bu örneğimizden de anlaşıldığı gibi NULL değerlerin niteliğini görmek için grouping ile isnull fonksiyonunu iç içe kullanmak en sağlıklı yöntem. Grouping fonksiyonunu rollup, cube ve grouping sets ile kullanabilirsiniz.

Şimdi sizlerden ricam benim buraya kadar anlattıklarımı farklı veri kümeleriyle yapmanız, konu böylece daha iyi oturacaktır. Bu amaçla aşağıdaki kod satırını makinenizde çalıştırıp örnek alıştırmaları deneyebilirsiniz.

 

Create Table Employees

(

Id int primary key,

Name varchar(50),

Gender nvarchar(10),

Salary int,

Country nvarchar(10)

)

 

GO

 

Insert Into Employees Values(1, 'Mark', 'Male', 5000, 'USA')

Insert Into Employees Values(2, 'John', 'Male', 4500, 'India')

Insert Into Employees Values(3, 'Pam', 'Female', 5500, 'USA')

Insert Into Employees Values(4, 'Sara', 'Female', 4000, 'India')

Insert Into Employees Values(5, 'Todd', 'Male', 3500, 'India')

Insert Into Employees Values(6, 'Mary', 'Female', 5000, 'UK')

Insert Into Employees Values(7, 'Ben', 'Male', 6500, 'UK')

Insert Into Employees Values(8, 'Elizabeth', 'Female', 7000, 'USA')

Insert Into Employees Values(9, 'Tom', 'Male', 5500, 'UK')

Insert Into Employees Values(10, 'Ron', 'Male', 5000, 'USA')

 

 

 Konuya dair anlatacaklarım bu kadar umarım faydalı olmuştur. Bir sonraki makalemde görüşmek üzere diyorum.

Not: Sql 2008 den önce kullanılan COMPUTE ve COMPUTE BY deyimleri, önceleri CUBE ve ROLLUP deyimine alternatif olarak kullanılıyordu, ancak takip eden sürümlerde kaldırıldı dolayısı ile eski kodlarınızda bu deyimler var ise elle değiştiriyor olmanız gerekiyor…