SQL’de veri özetleme ve gizli “OLAP raporlama”

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;GROUP BY ifadesi, syntax olarak FROM, JOIN, WHERE gibi deyimlerden sonra ORDER BY’dan önce kullanılır.GROUP BY ifadesi, verileri parametre olarak aldığı kolonlara göre özetlediği için özeti verilecek kolonlar, grupsal fonksiyonlar(aggregate functions) içerisinde kullanılır. Grupsal fonksiyonlar, aşağıdaki tabloda gösterilmiştir. Eğer özetleyici kolon veya kolonlar bilgisi SELECT etmeyeceksek GROUP BY kullanmaksızın grupsal fonksiyonlarını kullanabiliriz.GROUP BY’daki query mantığı, bir kolonu SELECT içerisinde ya grupsal fonksiyonlarla sunmak ya da GROUP BY içerisinde kullanmak gerekir. Yani gruplanmamış kolon olduğu gibi SELECT edilmez.

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;ROLLUP, gruplanmış kolonların hiyerarşisine göre özet satır oluşturur. GROUP BY deyiminde kullanılmış kolon sayısı kadar gruplama tipi gerçekleştirir.CUBE operatörü ise değerlerin tüm kombinasyonları için ara özet satırı oluşturur.ROLLUP, GROUP BY deyiminde verilmiş kolonları sağdan sola doğru gruplar. Ve ona göre her grubun altında özet satır oluşturur. Önce basit bir alt özeti oluşturalım.Mağazaların toplam satışını ve buna ek olarak en altta tüm satışlarını toplamını gösterelim.

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

SQL’de veri özetleme ve gizli “OLAP raporlama”” hakkında 0 yorum

  1. Ahmet Kaymaz Yazar

    Oktay merhaba,tam olarak ne demek istediğini anlayamadım. Bahsettiğin örneği yazarsan sevinirim.

    Cevapla
  2. mahir talan

    çok ii sql bilgisi olmayan biri için arayüz kullanarak sorgu (rapor) hazırlayabilecegimiz. sql arayüzü aracı varmıdır.

    Cevapla
  3. Ahmet Kaymaz Yazar

    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.

    Cevapla
  4. UGUR YILDIRIM

    vermiş olduğunuz bilgilerden ötürü teşekkür etmek istedim. elinize yüreğinize gönlünüze sağlık

    Cevapla

Bir yanıt yazın

E-posta adresiniz yayınlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir