SQL ile ilgili konuları tamamladık. Şimdi bazı temel PL/SQL kavramlarını öğrenmeye çalışacağız. Elbette PL/SQL uzmanı olmak için uğraşmıyoruz :) Ancak bizim için çok yararlı olabilecek komutları öğrenmek durumundayız. Ki Oracle sertifikasyon sınavlarını başarmak isteyen biriyseniz; bu kavramların da sınav konularına dahil olduğunu bilmenizi isterim.
Bu kez, daha önceden öğrendiğimiz GROUP BY ifadesini yeni kavramlarla zenginleştireceğiz. Bunlar ROLLUP ve CUBE kavramları.
Biliyoruz ki Oracle'ın, iş dünyasında tercih edilmesinin önemli nedenleri arasında; "çok özel istatistiksel bilgi sunma kabiliyeti" sayılır. Hatta öyle ki bu yetkinliklerden OLAP uygulamasını kullanmadan da faydalanabilirsiniz. Biliyorsunuz ki OLAP çok boyutlu bir raporlama sistemi ve veritabanızdaki verilerle oluşturacağınız raporlamalarda size inanılmaz kolaylık sağlayan bir sistem. Ancak OLAP kullanmak istemediğinizi yada sisteminizde OLAP sisteminin aktif olmadığını düşünelim. Verilerinizi nasıl istatistiksel olarak görüp değerlendireceğiz?
Konuya geçmeden önce hemen GROUP BY ifadeli olan SQL cümlemizin son halini kısaca hatırlayalım:
SELECT [sütunisimleri,] grupfonksiyonu(sütunismi)...
FROM tabloismi
[WHERE sartlari]
[GROUP BY gruplamasütunlari]
[HAVING gruplamasarti]
[ORDER BY sütunisimleri];
Grup fonksiyonları olarak belirttiğimiz ifade aslında; COUNT, AVG, SUM, MAX, MIN gibi grup başına 1 satır döndüren fonksiyonlardır. Bunu SQL konusunda ayrıntılı olarak incelemiştik. İlgili konuları zaten okuduysanız sorun yok demektir. Şimdi bu kısa hatırlatmadan sonra konumuza geçebiliriz.
ROLLUP ve CUBE Ne Yapar?
Belirttiğimiz gibi verileriniz ile ilgili istatistiksel sonuçlar görmenizi sağlarlar. Bunu da sizin belirlediğiniz noktalarda sayısal ara değer hesaplama ile sağlarlar. Bu iki ifade veriniz üzerinde ara toplamlar alırlar. Fakat bunu farklı iki yöntemle gerçekleştirirler. Bu iki yöntemi birlikte görerek farklarını anlayalım. O yüzden öncelikle aşağıdaki SQL cümlesini ve sonuçlarını inceleyelim. Daha sonra aynı sorguyu; bu SQL üzerinde oynayarak ROLLUP ve CUBE ile deneyeceğiz.
SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 50
GROUP BY (department_id, job_id)
ORDER BY department_ıd, job_ıd;
DEPARTMENT_ID JOB_ID SUM(SALARY)
---------------------- ---------- ----------------------
10 AD_ASST 4400
20 MK_MAN 13000
20 MK_REP 6000
30 PU_CLERK 13900
30 PU_MAN 11000
40 HR_REP 6500
6 rows selected
Konuyu rahat öğrenmek adına bölüm ID'lerinin 50'den küçük olma şartını getirdik ki daha az sonuçla daha anlaşılır olabilir. Burada 50'den küçük bölüm ID'si olan bölümler, department_id ve job_id sütunlarına göre gruplandırılarak sunuluyor. Şimdi maaşlar üzerinde istatistiksel bilgileri hesaplamak istiyoruz diyelim. Önce ROLLUP deyimini uyguluyoruz:
SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 50
GROUP BY ROLLUP(department_id, job_id)
ORDER BY department_ıd, job_ıd;
DEPARTMENT_ID JOB_ID SUM(SALARY)
---------------------- ---------- ----------------------
10 AD_ASST 4400
10 4400
20 MK_MAN 13000
20 MK_REP 6000
20 19000
30 PU_CLERK 13900
30 PU_MAN 11000
30 24900
40 HR_REP 6500
40 6500
54800
11 rows selected
Görüldüğü üzere ROLLUP sayesinde bir takım ara toplam değerleri oluştu. Peki bu ara toplamlar neye göre hesaplandı? Burada ROLLUP'ın temel aldığı sütun department_id sütunudur. job_id sütunu değerlerini dikkate almaz ki sonuçlarda ara toplam değerlerinin yansıtıldığı sütunlarda NULL değerler görmeniz bu yüzdendir. Oysa department_id sütununda NULL değer göremezsiniz. ROLLUP şunu yaptı: Departman numarasına göre o departmandaki tüm maaşların toplamını hesapladı ve bunu ilgili bölümün sonrasında sonuç olarak çıktıya gönderdi. Buradaki ORDER BY ifadesini kaldırırsanız da sonuç okunabilirliğini koruyacaktır. Çünkü yine aynı departman numarasına sahip olan işleri alt alta sıralar. Yukarıdaki sonuçta en son satırı da gözden kaçırmayın. Zira her iki sütun da NULL değerlerle sonuç gösteriyor. Yani aslında ikisini de dikkate almadan sonuçtaki toplam maaşı bizlere hesaplamış oluyor.
Kısacası ROLLUP ile sonuçta birikimli/kümülatif bir toplam bulursunuz ve gelen sonuçta satırlar düzenli olarak gruplanmıştır.
Şimdi CUBE ile durumu inceleyelim:
SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 50
GROUP BY CUBE(department_id, job_id);
DEPARTMENT_ID JOB_ID SUM(SALARY)
---------------------- ---------- ----------------------
54800
HR_REP 6500
MK_MAN 13000
MK_REP 6000
PU_MAN 11000
AD_ASST 4400
PU_CLERK 13900
10 4400
10 AD_ASST 4400
20 19000
20 MK_MAN 13000
20 MK_REP 6000
30 24900
30 PU_MAN 11000
30 PU_CLERK 13900
40 6500
40 HR_REP 6500
17 rows selected
Gördüğünüz gibi alttaki satırlar tamamen ROLLUP deyiminin gösterdiği sonuçla aynı şeyleri yansıtıyor. Yani CUBE, aslında ROLLUP'ın sonuçlarını da içeriyor. Bu kısım departman ID'sine göre birikimli sonuçların yansıtıldığı kısım. Peki üst satırlar ne anlama geliyor? En üstteki satır yine tablodaki tüm maaş değerlerin toplamını yani 54.800'ü verirken bunun hemen altındaki departman ID'lerinin NULL olarak getirildiği satırlar ise maaş toplamlarının job_id'ye göre yapıldığı satırlardır. Yani burada department_id sütununun neden NULL olduğu çok açık. Çünkü artık değerler job_id'ye göre gruplandırılıyor ve toplamlar buna göre hesaplanıyor.
Çok da karmaşık değil gibi :) ROLLUP sadece ilk sütuna göre ara toplam değerlerini hesaplıyor. CUBE ise her iki sütunda göre de ara toplam değerlerini hesaplıyor. Yani CUBE, ROLLUP'ı da kapsıyor.
GROUPING ve GROUPING SETS
Şimdi konuyla ilgili diğer iki kavrama bakalım. PL/SQL'de istatistiksel işlemler için kullanılan fonksiyonumuz olan GROUPING ve GROUPING SETS ifadelerini tanıyalım.
GOUPING ile ROLLUP veya CUBE ile hesapladığınız satırlar üzerinde tanımlama işlemi yaparsınız. Örneğin ROLLUP veya CUBE fonksiyonları hesaplama satırlarında NULL değerler döndürüyordu. Peki, dönen sonuçlar içerisinde de NULL değerler mevcutsa, bu iki NULL değerleri nasıl ayıracağız? Bunu GROUPING fonksiyonu bizim için yapmaktadır. Eğer satır bir hesaplama satırıysa, yani ROLLUP veya CUBE kullanımı ile oluştuysa bu kısma "1" değerini verir. Diğer satırlara ise, yani SELECT ifadesinin döndürdüğü satırlara ise "0" değerini bırakır.
Kullanımı ise oldukça kolay. Nasıl ki MAX, MIN fonksiyonlarını SELECT listesi içinde kullanıyorsanız bu fonksiyonu da aynı şekilde kullanırsınız. Hemen kendi örneğimizde kullanalım:
SELECT department_id DEPTID, job_id JOB,
SUM(salary),
GROUPING(department_id) GRP_BOLUM,
GROUPING(job_id) GRP_IS
FROM employees
WHERE department_id < 50
GROUP BY ROLLUP(department_id, job_id);
DEPTID JOB SUM(SALARY) GRP_BOLUM GRP_IS
------------ ------- ----------- ---------- --------
10 AD_ASST 4400 0 0
10 4400 0 1
20 MK_MAN 13000 0 0
20 MK_REP 6000 0 0
20 19000 0 1
30 PU_MAN 11000 0 0
30 PU_CLERK 13900 0 0
30 24900 0 1
40 HR_REP 6500 0 0
40 6500 0 1
54800 1 1
11 rows selected
Yukarıdaki ilk ROLLUP ifadeli SQL cümlemizle aynı sonuçlar döndü. Yani 11 satır ve kümülatif toplamlar. Fonksiyonu kullandığımız SELECT listesindeki son iki sütun hariç... Bu iki sütunu GROUPING fonksiyonunu kullanarak ekledik. Yaptığı iş sonuçta görülüyor. Toplama işlemlerinin yapıldığı satırlar 1, diğerleri 0 olarak sonuç dönmüş durumda.
Gelelim GROUPING SETS kısmına. Bu ifade ile bir SQL cümlesinde birden fazla gruplandırma kümeleri oluşturabilirsiniz. Mesela elinizde A, B, C şeklinde üç adet sütununuz var. Öyle bir durumdasınız ki A ile B'yi, B ile de C'yi gruplandırmanız gerekiyor. Bu durumda;
SELECT A, B, C
FROM X
GROUP BY GROUPING SETS ((A, B), (B, C));
ifadesi işinizi görecektir. Sorgunun sonucunda sanki iki adet SELECT ifadesinin birleşimini görüyormuşsunuz gibi bir sonuç oluşur. A ve B'nin birlikte gruplandığı kısımda C sütununu NULL görürken, B, C grubunda ise A sütununu NULL görürsünüz. Sabah akşam kullanacağınız bir sorgu tipi değil ama karşınıza çıkabilir :) Aşağıdaki sorguyu ve sonuçlarını takip edelim:
SELECT department_id, job_id,
manager_id,MAX(salary)
FROM employees
GROUP BY GROUPING SETS ((department_id,job_id), (job_id,manager_id));
DEPARTMENT_ID JOB_ID MANAGER_ID MAX(SALARY)
---------------------- ---------- ------------------- -----------
AC_MGR 101 12008
SH_CLERK 122 3800
SH_CLERK 124 3100
...
110 AC_ACCOUNT 8300
90 AD_VP 17000
50 ST_CLERK 3600
80 SA_REP 11500
...
52 rows selected.
İlk grupta department_id sütununu NULL görüyoruz. Demek ki job_id ve manager_id sütunları ile bir gruplandırma söz konusu. Alttaki sonuçlarda da manager_id sütununu NULL görüyoruz. Yani department_id iel job_id sütunu bir küme oluşturuyor.
Diğer Kullanımlar
Bu kullanımların dışında PL/SQL'in izin verdiği bir kaç kullanım tipi daha mevcut. Örneğin bir sorguda hem CUBE hemde ROLLUP ifadesini aynı anda kullanabilirsiniz. Üstelik hangi sütunda hangi metodun kullanılacağını da belirlersiniz. Aşağıdaki sorguyu inceleyelim:
SELECT department_id, job_id, manager_id, SUM(salary), GROUPING(job_id) GRP_JOBID, GROUPING (manager_id) GRP_MANID
FROM employees GROUP BY department_id,
ROLLUP(job_id),
CUBE(manager_id);
Biraz açıklayalım: Bu ifadenin sonucunun biraz karmaşık olacağını siz de tahmin ediyorsunuzdur. Bu karmaşayı önlemek için GOUPING kullanarak bu fonksiyonların ne kadar işe yaradığını görmüş olursunuz. İlk satırlarda aşağıda görüldüğü gibi sorgunun normal sonucu izlenmekte. Yani sanki "GROUP BY department_id, job_id, manager_id" ifadesi ile bir gruplandırma yapılmış gibi -ki zaten öyle- sonuçları görüyoruz.
SA_REP 149 7000 0 0
10 AD_ASST 101 4400 0 0
20 MK_MAN 100 13000 0 0
20 MK_REP 201 6000 0 0
30 PU_MAN 100 11000 0 0
...
Bundan hemen sonra aşağıdaki sonuçlar görülüyor. Burada manager_id sütununa göre CUBE işleminin gerçekleştirildiğini görüyoruz:
...
SA_REP 7000 0 1
10 AD_ASST 4400 0 1
20 MK_MAN 13000 0 1
20 MK_REP 6000 0 1
30 PU_MAN 11000 0 1
...
Bundan sonra da ROLLUP ve CUBE işlemlerinin doğası gereği birlikte gelen aşağıdaki sonuçlar sıralanıyor:
...
149 7000 1 0
7000 1 1
10 101 4400 1 0
10 4400 1 1
20 100 13000 1 0
...
93 rows selected
Şimdi ise daha farklı bir kullanım örneği inceleyeceğiz. ROLLUP veya CUBE ile işlem önceliklerini parantez ile değiştirerek işlemler de yapabilirsiniz. Örneğin ROLLUP|CUBE(a, (b, c), d) gibi bir işlemde öncelikle b ve c sütununa ait ara toplamlar bulunur. Daha sonra buradan bulunan sonuçlara e dersek; a, e, c sütunlarına ait ara toplam değerleri hesaplanır. Aşağıdaki sorguyu inceleyelim:
SELECT department_id, job_id, manager_id, SUM(salary), GROUPING(department_ıd) GRP_DEPID, GROUPING(job_id) GRP_JOBID, GROUPING(manager_ıd) GRP_MANID
FROM employees GROUP BY ROLLUP( department_id,(job_id, manager_id));
SA_REP 149 7000 0 0 0
7000 0 1 1
10 AD_ASST 101 4400 0 0 0
10 4400 0 1 1
...
110 AC_ACCOUNT 205 8300 0 0 0
110 20308 0 1 1
691416 1 1 1
...
46 rows selected.
Görüldüğü gibi öncelikle job_id ve manager_id sütunlarına ait ara değerler hesaplanmış, bu değerler bulununca da en sonda tüm değerlerin ara toplamı bulunmuştur.
Konumuzu burada bitiriyoruz. İyi çalışmalar!
Hiç yorum yok:
Yorum Gönder