Oracle analitik fonksiyonlarında temel prensip bu fonksiyonların sorgu sonuç bilgilerinin üzerinde çalıştırılmasıdır. Analitik fonksiyonlar içeren bir sorguda önce ” where , joinler , group by, having ” çalıştırılır, daha sonra analitik fonksiyonlar sonuçlar üzerinde çalıştırılır , en son olarakta “order by ” çalıştırılır. Analitik fonksiyonlar “View, alt sorgu” kullanımı gerektiren bir çok sorguda bu gereksinimi ortadan kaldırarak çok büyük performans artışı sağlamaktadır. Bu durumu bir örnekle gösterelim.
Kategorilerde bulunan ürünlerin toplamını ve bu toplamların bütün kategorilerdeki ürünlerin toplamına oranını gösteren bir sql :
1) Alt sorgu kullanılarak :
select kategori_id,count(urun_id) Toplam, ( count(urun_id)/(select count(urun_id) from urunler) )*100 Oran from urunler group by kategori_id
2) Analiktik Fonksiyon Kullanarak
select kategori_id,count(urun_id) Toplam, (COUNT(urun_id)/COUNT(COUNT(urun_id)) over())*100 Oran from urunler group by kategori_id
Alt sorgu veya view kullanımı özellikle kayıt sayısı yüksek olan tablolar için ciddi performans sorunlarına yol açacaktır.
Analitik fonksiyonlarının sağladığı bir diğer avantaj da gruplama yapılan sorgularda sonuç sahalarındaki kısıtlamayı ortadan kaldırmasıdır. Örneğin :
ürün id, kategori id ve bu kategoride bu ürünün bağlı olduğu kategorideki ürün sayısını veren sorgudaki urun id bilgisi gruplama yapmayı engellemektedir. ancak alt sorgu veya view kullanarak sonuca ulaşılabilir. Fakat analitik fonksiyonlar kullanılarak alt sorgulara gerek kalmadan sonuç alınabilir.
select urun_id,kategori_id, count(urun_id) over( partition by kategori_id) kategori_urun_sayisi from urunler
İkinci bir örnek olarak ORACLETURK@yahoogroups.com grubuna gönderilen bir soruyu verebiliriz.
Soru :
|
Merhabalar, ‘kullanıcı_bilgileri’ adlı tablonun içeriği aşağıdaki gibidir; kul_adi kul_termid kul_tarih ——- ———- ——— abulut abc8 03-04-2007 abulut abe5 08-07-2008 abulut abk2 06-05-2008 hsezen dhk1 12-02-2005 hsezen ddk6 10-08-2006
bu tablo kullanılarak yapılacak sorgu sonucu aşağıdaki gibi her kullanıcının en son hangi tarihte hangi terminal id’si ile giriş yaptığı satırın çıkması gerekiyor;
kul_adi kul_termid kul_tarih ——- ———- ——— abulut abe5 08-07-2008 hsezen ddk6 10-08-2006 … .. ..
şeklinde olması lazım.Nasıl bir sorgu yazmam gerekiyor?
SELECT KUL_ADI,KUL_TERMID,MAX(KUL_TARIH)AS SON_TARIH FROM KULLANICI_BILGILERI GROUP BY KUL_ADI,KUL_TERMID ORDER BY KUL_ADI
gibi bi sorgu yaptım ancak farklı terminal id’ler olduğundan onlara göre gruplama yapıyor ve biçok satır çıkıyor… |
Cevap :
| Bu gibi bir sorun karşısında analitik fonksiyonlardan dense_rank() fonksiyonu “PARTITION BY” ve “ORDER BY DESC” ile birlikte kullanılarak istenilen sonuca ulaşılabilir.SELECT * FROM (SELECT kul_adi, kul_termid, kul_tarih, DENSE_RANK () OVER (PARTITION BY kul_adi ORDER BY kul_tarih DESC) sira FROM ts.deneme) WHERE sira = 1; |
Bu sorguda “partition by kul_adi” ile dense_rank() fonksiyonunun herbir kul_adi değeri için ayrı hesaplanması, “Order by kul_tarih DESC” ile de dense_rank() fonksiyonunun sıralama yaparken “kul_tarih” sahasının tersten sıralarayak
Bir analitik fonksiyonu aşağıdaki gibi bir sözdizimine sahip olabilir.
<fonksiyon_ismi>() OVER
(
[PARTITION BY <exp1> [, …]]
ORDER BY <exp2> [ASC|DESC]
[NULLS FIRST|NULLS LAST]
)
PARTITION BY : Bu komut analitik fonksiyonun kullanacağı sorgu sonuçlarını bölümlere ayırır. Örneğin yukarıdaki örnekte Count() fonksiyonu “partition by kategori_id” komutuyla “kategori_id” lere göre sayım yapmaktadır. Yani her kategori_id değeri için ayrı sayım yapmaktadır. “partition by” komutundan sonra saha isimleri veya oracle deyimleri kullanılabilir. Birden fazla saha ismi veya deyim yazılabilir. Örneğin : kategori_id,grup_id,nvl(ust_kategori,0) gibi. Eğer asıl sorguda “group by” kullanılmış ise sadece “select” listesindeki sahalar kullanılabilir, kullanılmamış ise tablolardaki tüm sahalar kullanılabilir. Eğer “partition by” kullanılmazsa tüm sorgu sonucu kullanır.
| Plan | ||
|---|---|---|
| SELECT STATEMENT ALL_ROWS Cost: 135 Bytes: 192,004 Cardinality: 8,348 | ||
| 2 WINDOW SORT Cost: 135 Bytes: 192,004 Cardinality: 8,348 | ||
| 1 TABLE ACCESS FULL TABLE URUNLER Cost: 134 Bytes: 192,004 Cardinality: 8,348 | ||
Plandaki “Window Sort” bölümü analitik fonksiyonun kullanımını belirtmektedir.
ORDER BY :
ORDER BY Olması durumunda : Analitik fonksiyonun kullanacağı kayıt seti halihazırdaki kayıt ve sıralamaya göre bölümdeki sonraki kayıtlardır.
ORDER BY Olmaması durumunda : Analitik fonksiyonun kullanacağı kayıt seti bölümdeki tüm kayıtlardır.
SIRALAMA (RANKING) FONKSİYONLARI
Bu fonksiyonlar belirtilen saha değerlerine göre kayıtın diğer kayıtlara göre sırasını belirlemeye yararlar. Sıralama fonksiyonları şunlardır :
-
RANK() ve DENSE_RANK()
-
CUME_DIST() ve PERCENT_RANK()
-
NTILE()
-
ROW_NUMBER()
RANK() ve DENSE_RANK() : Bu fonksiyonlar kayıtın tüm kayıtlar veya bölümleler içindeki sırasının bulunması sağlar. İki fonksiyon arasındaki fark aynı sıra değerine sahip kayıtların olması durumunda bir sonraki sıra numarasının ne şekilde belirleneceğidir. RANK() fonksiyonunda aynı sıra değerine sahip kayıtlardan sonraki kayıtın sıra değeri için boşluk bırakılır. DENSE_RANK() de ise boşluk bırakılmaz ve sıra değerleri birbirini takip eder.
|
sql > select malkodu,satis_sayisi,rank() over(order by satis_sayisi) Rank,dense_rank() over(order by satis_sayisi) Dense_Rank from web.urunler
|
Bu fonksiyonlar parametre almazlar. “Order By” kullanımı zorunludur. “Partition By” kullanımı zorunlu değildir. Kullanılmadığı taktirde tüm sorgu sonucu kullanılacaktır.
ROW_NUMBER() : “ORDER BY” ile belirtilen sıraya göre ve eğer belirtilmişse “PARTITION BY” ile belirtilen gruplar için her bir satıra 1 den başlamak üzere bir sıra numarası verir. “İLK N KAYIT” veya “SON N KAYIT” sorgularında rank() ve dense_rank() fonksiyonlarına nazaran daha iyi sonuç verir.
|
En Çok Satılan ilk 10 ürün
sql > select * from (select malkodu,satis_sayisi,row_number() over(order by satis_sayisi desc) Sira from web.urunler) where sira=<10
|
WINDOWING FONKSİYOLARI
Bu fonksiyonlar sorgu sonuç satırları üzerinde hareket ederek birtakım işlemler yapmamıza olanak sağlarlar. Örneğin bir önceki veya bir sonraki satırdaki bir sahadaki değeri kullanarak mevcut satır için hesaplama yapılabilmesini sağlar. Bir sahanın değerlerini ilk satırdan itibaren toplanarak her satır için değerini bulunabilir, ortalama alınabilir, maksimum veya minimum değeler bulunabilir. Özetle bu özellik “PARTITION BY” ile belirlenen grup içinde istenilen ölçüde(satır sayısı, tarih aralığı gibi) önceki veya sonraki kayıtlara ulaşılarak bu kayıtlardan değerlerin alınmasını sağlar.
|
Ürünlerin satış miktarları ve bu satış miktarlarının yukarıdan aşağıya toplanmış değerlerinin listesi
sql > select malkodu,satis_sayisi,sum(satis_sayisi) over(order by satis_sayisi ROWS UNBOUNDED PRECEDING) toplam from web.urunler
|
Windowing fonksiyonları Fiziksel (ROWS) ve Mantıksal(RANGE) olmak üzere ikiye ayrılmaktadır. Aralık satır sayısı belirtilecekse fiziksel , satır sayısı yerine değerlere ait aralık belirtilecekse Mantıksal olmaktadır.
Sözdizimi :
ROWS | RANGE
{ UNBOUNDED PRECEDING | <exp> PRECEDING} |
BETWEEN
{UNBOUNDED PRECEDING | <exp> PRECEDING}
AND
{CURRENT ROW | <exp> FOLLOWING} } )
ROWS işlemin fiziksel yani belirli satır aralığıyla olacağını belirtir.
RANGE işlemin Mantıksal yani saha değerine bağlı olarak belirleneceği durumlarda kullanılır. Örneğin 2 gün öncesi, 1 Aylık aralık gibi…
BETWEEN … AND …. Başlangıç ve bitiş noktalarını belirler.
UNBOUNDED PRECEDING işlemin eğer “PARTITION BY” var ise belirlediği aralığın en başından, eğer “PARTITION BY” yok ise tüm sorgu sonucunun en başından başlayacağını belirtir
UNBOUNDED FOLLOWING işlemin eğer “PARTITION BY” var ise belirlediği aralığın sonuna kadar, eğer “PARTITION BY” yok ise tüm sorgu sonucunun sonuna kadar olacağını belirtir.
CURRENT_ROW Eğer ROWS kullanıyorsa o anki satırı pozisyonu, RANGE kullanıyorsa o anki satırın Değeri anlamına gelmektedir.
LEAD ve LAG FONKSİYONLARI
Bu fonksiyonlar sayesinde sorgu sonucunda bir önceki veya bir sonraki satırdaki değerleri kullanarak sonuç elde edilebilmektedir. LEAD ile birlikte sonraki LAG ile birlikte önceki satırlardan değer alınabilmektedir.
LEAD(kolon veya formul, sayısal değer,varsayılan değer) şeklinde kullanılır. sayısal değer LEAD için kaç satır sonraki değerin kullanılacağını belirtir. Kullanılmazsa 1 varsayılan değeridir.
LAG(kolon veya formul, sayısal değer,varsayılan değer) şeklinde kullanılır. sayısal değer LAG için kaç satır önceki değerin kullanılacağını belirtir. Kullanılmazsa 1 varsayılan değeridir.
Varsayılan değer bilgisi sayısal değerin dışına çıkıldığında geri dönen değer olarak verilebilir. belirtilmezse Null değeri döner.
|
SQL> select malkodu,satis_sayisi from urunler;
Bir sonraki sorguda ürürün satış sayısının satis sayisina göre sıralı olarak bir önceki ürünün satış sayısından farkı bulunmaktadır. SQL>select malkodu,satis_sayisi, satis_sayisi – LAG(satis_sayisi,1,0) over(order by satis_sayisi) as FARK from urunler;
|
Merhaba Sühan Bey,
Analitik Fonksiyonları bu denli güzel örneklerle detaylı biçimde makalenizde anlatmanız çok yararlı oldu,çalışmalarınızda başarılar diliyorum.
Orhan Eripek.
Yorum�Yorumlar yazan: Orhan Eripek — Ağustos 7, 2008 @ 6:07 am
Çok güzel bir anlatım olmuş.
Emeğinize sağlık.
Yorum�Yorumlar yazan: vahit ünal — Kasım 13, 2008 @ 10:23 pm
tebrikler, faydalı bir anlatım. örnekleri test şemalarından biri üzerinde vermiş olsaydınız daha iyi olurdu.
Yorum�Yorumlar yazan: yusuf — Mart 11, 2009 @ 8:10 am
Eline sağlık çok iyi bir kaynak olmuş.
Yorum�Yorumlar yazan: Ferhat Demirci — Nisan 3, 2009 @ 3:12 pm