SQL dilininin sunduğu gruplama özelliklerini kullanarak doğrudan veritabanından çektiğimiz bir raporu belli kriterlere göre kırabilir ve alt özet oluşturabiliriz. SQL’in ROLLUP ve CUBE komutları, verileri hızlı bir şekilde özetlememizi sağlar. SQL Server 6.5 ve Oracle 8i ile birlikte gelmiş bu deyimler, özellikle herhangi bir OLAP yatırımı, uygulaması kurmadan etkili istatiksel sonuçlar çıkarmamıza imkan verir. Bu yazıda SQL Server tarafında bu komutları örneklendirmeye çalışacağız.
Söyleyeceklerimize geçmeden önce örnek bir tablo oluşturalım.
CREATE TABLE [dbo].[Satis] ( [KayitId] [int] IDENTITY (1, 1) NOT NULL , [Sehir] [char] (10) NULL , [Magaza] [char] (10) NULL , [Kategori] [char] (10) NULL , [Adet] [int] NULL ) ON [PRIMARY]
Satis tablosuna örnek birkaç kayıt girelim.
KayitId | Sehir | Magaza | Kategori | Adet |
---|---|---|---|---|
1 | Ankara | M01 | Kitap | 4 |
2 | Bursa | M02 | DVD | 2 |
3 | Ankara | M01 | DVD | 3 |
4 | Ankara | M01 | Kitap | 6 |
5 | Bursa | M02 | Kitap | 8 |
6 | Ankara | M03 | Kamera | 6 |
7 | Bursa | M02 | Kamera | 7 |
Bu tablodaki verileri mağazalarda yapılmış satışların belgelerini temsil ediyor. Her kayıt bir belge olarak düşünülebilir.
SQL Server’da veri özetlemenin temelinde gruplama fonksiyonları yatar. Bilindiği gibi T-SQL’de GROUP BY ifadesi kullanılarak veriler, belli kolon veya durumlara göre gruplandırılabilir. GROUP BY için birkaç hatırlatmada bulunmamız faydalı olacaktır;
Grup Fonksiyonu | Açıklama |
---|---|
SUM([ALL | DISTINCT] kolon bilgisi) | Numeric kolonun değerlerini toplar. Sayısal kolonlar için kullanılır. |
AVG([ALL | DISTINCT] kolon bilgisi) | Numeric kolonun değerlerinin ortalamasını verir. Sayısal kolonlar için kullanılır. |
COUNT([ALL | DISTINCT] kolon bilgisi) | Koşula uyan kayıtlarını sayısını döndürür. |
COUNT(*) | Seçilmiş tüm kayıtlarını sayısını döndürür |
MAX(kolon bilgisi) | Uygulandığı kolonun en büyük değerini bulur |
MIN(kolon bilgisi) | Uygulandığı kolonun en küçük değerini bulur |
SUM, AVG, COUNT, MAX ve MIN, null kayıtları görmemezlikten gelir fakat COUNT(*), o kayıtları da dikkate alır. Default olarak ALL sözcüğünün kullanıldığı SUM, AVG ve COUNT fonksiyonlarında, mükerrer kayıtları elemek için isteğe bağlı olarak DISTINCT anahtar sözcüğü, kullanılır. SUM ve AVG fonksiyonları, int, smallint, tinyint, decimal, numeric, float, real, money ve smallmoney gibi sayısal türdeki kolonlar için kullanılır. MIN ve MAX bit türündeki kolonlar için kullanılamaz. COUNT(*) dışındaki fonksiyonları, text ve image data türlerini desteklemez. AVG ve SUM, integer türündeki kolonlar integer düğer döndürür.
Grupsal fonksiyonlar, ORDER BY içerisinde kullanılabilir ancak WHERE cümleciğinde kullanılamaz. Ayrıca bu fonksiyonlar, başka bir grupsal fonksiyona veya bir subquery’e uygulanamaz.
GROUP BY deyimi, her kayıt grubu için satır satır özet geçmek amacıyla kullanılır. Yani “Sistemdeki satışları mağazalara göre özetlemek”, “Katılımcıları eğitim durumlarına göre gruplamak”,”Hangi ürün grubundan kaçar tane satılmış”,”Hangi mağaza en çok hangi ürün grubunda yüksek ciro yapmış” gibi soruların yanıtları GROUP BY ifadeside saklıdır. Şimdi örnek tablomuz üzerinde örnekler yapalım.
--Toplam kaç adet satış yapılmış ve bu satışların ortalaması nedir ? SELECT SUM(Adet) [Satış Adeti], AVG(Adet) [Ortalama Adet] FROM Satis --Bunun sonucunda Satış Adeti : 36, Ortalama Adet : 5 gelecektir. --Görüldüğü gibi ortalama int türünde geldi. Oysa ortalama net olarak 5 değildir. --Bunun için bir casting işlemi yapabiliriz. SELECT SUM(Adet) [Satış Adeti], AVG(CAST(Adet as numeric(4,2))) [Ortalama Adet] FROM Satis --Bu durumda Ortalama Adet : 5.142 gelecektir
Her mağaza kaç satış hareketi yapılmış ve toplam adetleri nedir. Bunu da adetlerine göre büyükten küçüğe sıralasın. Burada özetleme mağaza bazında istendiği için GROUP BY kullanacağız.
SELECT Magaza,COUNT(*) [Satış Adeti],SUM(Adet) Toplam FROM Satis GROUP BY Magaza ORDER BY 3 DESC --ORDER BY Toplam DESC --veya --ORDER BY SUM(Adet) DESC
İçerisinde en az 3 adet ürünün olduğu belgelerin sayılarını mağaza bazında özetleyelim. Burada veri gruplanmadan önce kısıtlanacağı için WHERE kullanılır.
SELECT Magaza,COUNT(*) [Satış Adeti] FROM Satis WHERE Adet >=3 GROUP BY Magaza
Adet olarak 15’in üzerinde satış yapmış yapmış mağazaları listeyelim. Bu durumda öncelikle mağaza bazında toplam satış adetleri alıp o değerler üzerinden 15 kriterini girmemiz lazım. Bunun için yani gruplandırılmış veri üzerinde kısıtlama yapmak için HAVING sözcüğü kullanılır. HAVING, GROUP BY’dan sonra yazılım.
SELECT Magaza,SUM(Adet) [Toplam Adet] FROM Satis GROUP BY Magaza HAVING SUM(Adet) >15
Mağaza ve kategori bazında adet toplamlarını getirelim. Yani her mağazada hangi ürün grubu kaç tane satılmış. Bunun için yapacağımız tek şey, tabloyu hem Mağaza hem de Kategori bazında kırmaktır.
SELECT Magaza,Kategori,SUM(Adet) [Toplam Adet] FROM Satis GROUP BY Magaza,Kategori ORDER BY 1,2
Örneğimizi biraz daha geliştirelim; Mağazalarda toplamda en çok satılan ürün gruplarını mağaza bazında gösterelim. Bunun için öncelikle önceki query’i oluşturacağız. Böylece mağaza bazında kategorilerin toplamı gelmiş olur ardından bu sonuçlarda adetleri yüksek olarak kategorileri, mazağa seviyesinde listeleyeceğiz. Bunun için subquery oluşturacağız.
SELECT Magaza,MAX(Kategori),MAX(ToplamAdet) FROM( SELECT Magaza,Kategori,SUM(Adet) ToplamAdet FROM Satis GROUP BY Magaza,Kategori ) as T GROUP BY Magaza
Burada güzel bir özellik kullandık. İç tabloyu gruplarken, GROUP BY’a Kategori yazmadık. Ama onu SELECT etmek için yazmak zorundaydık. Fakat en büyük adetli kolonda hep aynı değer olacağı için bunlardan birini MAX veya MIN ile alabiliriz. Bir anlamda sadece, GROUP BY’a eklenmemiş kolonun grupsal fonksiyonda kullanılmaz zorunluluğunu aşmak için böyle yaptık. Bu yöntem, özellikle dinamik raporlama araçlarında kolaylıklar sağlamaktadır.
Bu örnekleri verdikten sonra asıl konumuza dönebiliriz;
Verileri özetlerken ara özet veya alt özet satırları oluşturmak için WITH ROLLUP ve WITH CUBE deyimleri kullanılır. ROLLUP ile CUBE operatörleri arasındaki fark şudur;
SELECT Magaza,SUM(Adet)Adet FROM Satis GROUP BY Magaza WITH ROLLUP
Magaza | Adet |
---|---|
M01 | 13 |
M02 | 17 |
M03 | 6 |
NULL | 36 |
Görüldüğü gibi hem mağaza bazında toplam satışı hem de tüm toplamları göstermiş oldu. Şimdi hem mağaza hem de kategori bazında ara ve alt özet satırlar oluşturalım.
SELECT Magaza,Kategori,SUM(Adet)Adet FROM Satis GROUP BY Magaza,Kategori WITH ROLLUP
Magaza | Kategori | Adet |
---|---|---|
M01 | DVD | 7 |
M01 | Kitap | 6 |
M01 | NULL | 13 |
M02 | DVD | 2 |
M02 | Kamera | 7 |
M02 | Kitap | 8 |
M02 | NULL | 17 |
M03 | Kamera | 6 |
M03 | NULL | 6 |
NULL | NULL | 36 |
Görüldüğü gibi ROLLUP, sağdan sola doğru gruplama yaparak her grup için özet satır oluşturur.
SELECT Sehir,Magaza,Kategori,SUM(Adet) FROM Satis GROUP BY Sehir,Magaza,Kategori WITH ROLLUP
kodunu eğer WITH ROLLUP olmaksızın çalıştırırsak SQL Sever, her unique konbinasyon için tek bir özet satır oluşturur. Sehir Magaza Kategori SUM(Adet)
çalışma şekli şu şekilde olacaktır. Fakat WITH ROLLUP deyimi kullanıldığı için GROUP BY’da 3 kolon olduğu için sağdan sola doğru ek olarak 3 türlü gruplama özet satırı oluşturur.
Sehir | Magaza | Kategori | SUM(Adet) |
Sehir | Magaza | (NULL) | SUM(Adet) |
Sehir | (NULL) | (NULL) | SUM(Adet) |
(NULL) | (NULL) | (NULL) | SUM(Adet) |
CUBE operatörü, ROLLUP gibi özet satırı oluşturur. Tek farkı, GROUP BY deyiminde kullanılmış kolonların değerlerinin bütün kombinasyonları kadar group özeti yapar.
SELECT Magaza,SUM(Adet)Adet FROM Satis GROUP BY Magaza WITH CUBE
Magaza | Adet |
---|---|
M01 | 13 |
M02 | 17 |
M03 | 6 |
NULL | 36 |
Bu queryde sadece bir kolona göre gruplama olduğu için ROLLUP’tan farklı bir sonuç getirmedi. İki kolona göre gruplama yapalım.
SELECT Magaza,Kategori,SUM(Adet)Adet FROM Satis GROUP BY Magaza,Kategori WITH CUBE
Magaza | Kategori | Adet |
---|---|---|
M01 | DVD | 7 |
M01 | Kitap | 6 |
M01 | NULL | 13 |
M02 | DVD | 2 |
M02 | Kamera | 7 |
M02 | Kitap | 8 |
M02 | NULL | 17 |
M03 | Kamera | 6 |
M03 | NULL | 6 |
NULL | NULL | 36 |
NULL | DVD | 9 |
NULL | Kamera | 13 |
NULL | Kitap | 14 |
CUBE operatörünün çalışmasını daha net görmek için 3’lü kırılım yapalım.
SELECT Sehir,Magaza,Kategori,SUM(Adet) FROM Satis GROUP BY Sehir,Magaza,Kategori WITH CUBE
Bu durumda aşağıdaki gibi bir gruplama özet çıkarır.
Sehir | Magaza | Kategori | SUM(Adet) |
Sehir | Magaza | (NULL) | SUM(Adet) |
Sehir | (NULL) | (NULL) | SUM(Adet) |
(NULL) | (NULL) | (NULL) | SUM(Adet) |
(NULL) | Magaza | Kategori | SUM(Adet) |
(NULL) | Magaza | (NULL) | SUM(Adet) |
Sehir | (NULL) | Kategori | SUM(Adet) |
(NULL) | (NULL) | Kategori | SUM(Adet) |
Örneklerimizde SUM fonksiyonunu kullanmamız, ROLLUP veya CUBE’in sadece SUM ile kullanıldığı anlamına gelmiyor. AVG, MAX, MIN veya COUNT fonksiyonlarını da kullanabiliriz.
WITH CUBE veya WITH ROLLUP operatörleri aynı anda en fazla 10 kolon(kırılım noktası) üzerinde özetleme yapabilir.
GROUPING() fonksiyonu
ROLLUP veya CUBE yapılmış özetlemelerde örneklerde gördüğünüz gibi bazı kolonlar NULL değeri içermektedir. Bir kolon, ya tablodaki değeri NULL olduğu için ya da özetlemeden dolayı NULL olarak görünür. Özetlemenin yapıldığı querylerde ihtiyaç duyduğumuz fonksiyonlardan biri olan ve sadece ROLLUP veya CUBE operatörleriyle birlikte kullanılan GROUPING() fonksiyonu bu iki NULL türünü birbirinden ayırmamıza yarar. Daha anlaşılır şekilde söyleyecek olursak; GROUPING() fonksiyonu, parametre olarak aldığı ifadenin sonucu özetlemeden dolayı NULL içeriyorsa yani o satır, parametredeki kolonun özetiyse geriye 1 değilse 0 döndürür. Bunu genellikle, özetlenmiş bir kayıt dizisindeki NULL değerleri yerine TOPLAM ifadesini yazdırmak için kullanılır.
SELECT Magaza,SUM(Adet)Adet FROM Satis GROUP BY Magaza WITH ROLLUP
ifadesi, kayıt dizisinin en altına alt toplamı yazdırır.
Magaza | Adet |
---|---|
M01 | 13 |
M02 | 17 |
M03 | 6 |
NULL | 36 |
Fakat burada görüldüğü gibi en alt toplamı bu şekilde vermek pek anlaşılır bir durum değil onun için query şu şekilde düzelteceğiz.
SELECT (CASE WHEN GROUPING(Magaza)=1 THEN 'TOPLAM' ELSE Magaza END) Magaza, SUM(Adet)Adet FROM Satis GROUP BY Magaza WITH ROLLUP
Magaza | Adet |
---|---|
M01 | 13 |
M02 | 17 |
M03 | 6 |
TOPLAM | 36 |
Aynı şekilde bunu kaç kırılım noktası varsa hepsine uygulayabiliriz.
SELECT (CASE WHEN GROUPING(Magaza)=1 THEN 'TOPLAM' ELSE Magaza END) Magaza, (CASE WHEN GROUPING(Kategori)=1 THEN 'TOPLAM' ELSE Kategori END) Kategori, SUM(Adet)Adet FROM Satis GROUP BY Magaza,Kategori WITH ROLLUP
Magaza | Kategori | Adet |
---|---|---|
M01 | DVD | 7 |
M01 | Kitap | 6 |
M01 | TOPLAM | 13 |
M02 | DVD | 2 |
M02 | Kamera | 7 |
M02 | Kitap | 8 |
M02 | TOPLAM | 17 |
M03 | Kamera | 6 |
M03 | TOPLAM | 6 |
TOPLAM | TOPLAM | 36 |
hocam selamlar
gorup by’daki ilk sorguya göre with rollup yapmak mumkunmu ?
Oktay merhaba,tam olarak ne demek istediğini anlayamadım. Bahsettiğin örneği yazarsan sevinirim.
çok ii sql bilgisi olmayan biri için arayüz kullanarak sorgu (rapor) hazırlayabilecegimiz. sql arayüzü aracı varmıdır.
Tabloların genel şemasını ve içeriğini biliyorsanız SQL Reporting Services aracını kullanabilir veya Excel’den OLE DB aracılığıyla SQL Server’e erişip PIVOT aracını kullanabilir veya piyasadaki Query toollarını kullanabilirsiniz. Tabi bunların hepsi yapacağınız raporlamanın çapına ve verilerin çokluğuna bağlı olarak tercih edilmelidir. Yoksa Management Studio içerisindeki View Designer bölümü de sürükle-bırak mantığıyla görsel olarak sorgu hazırlamanızı sağlar.
vermiş olduğunuz bilgilerden ötürü teşekkür etmek istedim. elinize yüreğinize gönlünüze sağlık