Oracle Analitik Fonksiyonlar

Herkese selam,

Bu yazıda sizlere Oracle’ın analitik fonksiyonları hakkında bilgi vereceğim. Bu konu üzerinde uzun zamandır bir şeyler yazmak istiyordum ama bir türlü derleme fırsatı bulamadım. Bu yazı ile Oracle’ın analitik fonksyon desteğini ve sık kullanılan komutları ve ne işe yaradıklarını aktarmaya çalışacağım.

Oracle’ın analitik fonksiyon desteği, günlük hayatta bir çok raporlama ihtiyacımza cevap vermekte. Oracle her sürümünde yeni analitik fonksiyonlar ekleyerek SQL seviyesinde karmaşık raporlar oluşturmamıza olanak sağlamaktadır. Analitik fonksiyonlar sayesinde hem daha az karmaşık sorgular oluşturarak istediğimiz raporları üretiyoruz hemde daha performanslı sorgular elde ediyoruz (Genellikle self join yükünden kurtulmuş oluyoruz.). Bu bağlamda analitik fonksiyon kullanımı oldukça kritik ve önemli.

Örnek komutlara giriş yapmadan önce bir konuya da açıklık getirmek istiyorum. Genellikle aggregate (geleneksel yöntem) fonksiyonlar ile analitik fonksiyonlar karıştırılıyor. Bu 2 tip fonksiyonun temel farkını anlayabilmek için aşağıdaki tablonun incelenmesini faydalı buluyorum.

 

Fonksiyon

Aggregate Fonksiyonlarda Kullanımı

Analitik Fonksiyonlarda Kullanımı

MIN

+

+

MAX

+ +
COUNT +

+

SUM +

+

 

 

Söz Dizimi

Çıktı

Aggreagate(geleneksel) Genelikle “GROUP BY” cümlesi kullanılıyor. Her bir grup için 1 satır sonuç üretiliyor.
Analitik Genellikle “OVER”cümlesi kullanılıyor. Toplam satır sayısı değişmiyor.

 

Aggregate ve analitik fonksiyonlar arasındaki temel farkları inceledikten sonra şimdi sık kullanılan analitik fonksiyonları örnekler ile inceleyelim.

İlk örneğim, aggregate fonksiyonların ne olduğunu hatırlama amaçlı;

 

Aggregate Fonksyion Örneği:

SELECT department_id, SUM (salary)
FROM employees
GROUP BY department_id;

aggreagte1

 

 

Analitik Aggregation Örneği 1:

SELECT first_name, salary, SUM (salary) OVER (ORDER BY first_name) AS empsal FROM employees;

anlitik1

Yukarıdaki örnekte yazılan sorgu analitik olduğu için satır sayısında bir azalma olmadı, toplamda kümülatif olarak her satırda eklenerek arttı. Teknik olarak data first_name e göre sıralandı ve bu sıraya göre çalışan maaşları alt alta toplanarak kümülatif olarak artış empsal kolonunda gösterildi.

 

Analitik Aggregation Örneği 2:

SELECT first_name,
department_id,
SUM (salary) OVER (PARTITION BY department_id ORDER BY salary)
AS deptsal
FROM employees
ORDER BY department_id;

anlitik2

 

 

 

 

 

 

 

Yukarıdaki sorguda ise data department_id ye göre gruplanıyor ve her grup salary kolonuna gre azalan bir şekilde sıralanıp alt alt a grup bazında kümülatif olarak toplanıyor ve sonuç deptsal kolonunda gösteriliyor.

 

Windowing 1:

SELECT employee_id,
first_name,
salary,
SUM (salary) OVER (partition by department_id ORDER BY employee_id
 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS cumul
FROM employees
ORDER BY employee_id;

anlitik3

 

Yukarıdaki sorguda bir windowing sorgusu yazılmıştır. “ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW” söz deyimi ile window’un boyutu belirlenmiştir. Bu boyut bulunan satırın öncesi için ilk kayıta kadar, bulunan satırın sonrası için son kayıta kadar ayarlanmıştır. Yani üst ve alt sınır en üst seviye olarak ayarlanmıştır. Bu konfügrasyondan dolayı çıktı sonucu (cumul) kümülatif aggreagtion gibi gözükmektedir. Windowing i daha iyi anlayabilme adına 2. örneğe bakmak daha aydınlatıcı olacaktır.

 

Windowing 2:

SELECT employee_id,
first_name,
salary,
SUM (salary)
 OVER (Partition by department_id ORDER BY employee_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
AS cumul
FROM employees
ORDER BY employee_id;

Yukarıdaki sorguda “ ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ” söz deyimi ile window un sınırları o an ki satırın 1 öncesi ve 1 sonrası olarak ayarlanmıştır. Bu window size a göre toplamlar hesaplanmıştır. Aşağıdaki ekran çıktıları incelenerek sorgunun nasıl çalıştığı anlaşılabilir. İlgili söz deiyimi ile window size istenildiği gibi ayarlanabilir.

win1win2win3win4

 

 

First_Value:

SELECT first_name,
department_id,
FIRST_VALUE (salary)
 OVER (PARTITION BY department_id ORDER BY employee_id)
AS deptsal
FROM employees
ORDER BY department_id;

fvl

 

 

Yukarıdaki sorguda data department_id ye göre gruplanıp employee_id ye göre artan bir şekilde sıralanıyor ve her grup bazındaki ilk salary bilgisi tüm gruptaki kayıtların karşısına yazılıyor(deptsal).

 

Last_Value: 

SELECT first_name,
department_id,
salary,
 OVER (PARTITION BY department_id ORDER BY department_id)  AS deptsal FROM employees ORDER BY department_id;

 

lvl

Yukarıdaki sorguda data department_id ye göre gruplanıp department_id ye göre artan bir şekilde sıralanıyor ve her grup bazındaki son salary bilgisi tüm gruptaki kayıtların karşısına yazılıyor(deptsal).

 

LAG: 

SELECT employee_id,
first_name,
salary,
LAG (salary, 1) OVER (ORDER BY salary) AS LAG1
FROM employees;

lag

 

Yukarıdaki sorguda “LAG (salary, 1) OVER (ORDER BY salary) ” cümlesi ile salary kolonuna göre artan sıralanmış data üzerinde her bir satıra ait salary bilgisinin yanına bir önceki satıra ait maas bilgisi eklenmiştir(LAG1 kolonu). LAG ile verdiğimiz parametre ile sadece 1 önceki kayıt değil istenildiği kadar önceki kayıda dönülüp o bilgi alınıp yazılabilir (Ör: LAG (salary, 2)). Her satırda 1 önceki satıra ait maas bilgisi bulunmakta.

 

LEAD: 

SELECT employee_id,
first_name,
salary,
LEAD (salary, 1) OVER (ORDER BY salary) AS LEAD1
FROM employees;

lead

Yukarıdaki sorguda “LEAD (salary, 1) OVER (ORDER BY salary) AS LEAD1” cümlesi ile salary kolonuna göre artan sıralanmış data üzerinde her bir satıra ait salary bilgisinin yanına bir sonrak, satıra ait maas bilgisi eklenmiştir(LEAD1 kolonu). LEAD ile verdiğimiz parametre ile sadece 1 önceki kayıt değil istenildiği kadar önceki kayıda dönülüp o bilgi alınıp yazılabilir(Ör: LEAD (salary, 2)). Her satırda 1 önceki satıra ait maas bilgisi bulunmakta.

 

LISTAGG: 

SELECT department_id,
LISTAGG (first_name, ’, ’) WITHIN GROUP (ORDER BY department_id)
 AS newList
FROM employees
GROUP BY department_id;

listagg

 

Elimizde sol üst de görüldüğü gibi departmant ve çalışan isminin olduğu bir veri kümesi olsun ve bizde her bir departmana ait çalışanları tek bir satırda görmek isteyelim(sağ üstde olduğu gibi). Bu durumda “listagg” komutunu kullanırsak istediğimiz sonucu elde edebiliyoruz. “LISTAGG (first_name, ’, ’) WITHIN GROUP (ORDER BY department_id)” söz deyimi ile veriyi hangi kolona göre gruplayacağımızı ve aynı satıra yazılacak bilgiyi hangi delimiter ile ayıracağımızı söyleyip sorgumuzu yazıyoruz. Sonuçta sağ üstdeki gibi bir çıktı elde ediyoruz.

 

RANK: 

SELECT department_id,
last_name,
salary,
RANK () OVER (PARTITION BY department_id ORDER BY salary DESC) "Rank"
FROM employees
WHERE department_id = 60
ORDER BY department_id, "Rank", salary;

drank

 

RANK fonksiyonu ile verimizin belirlediğimiz konfügrasyonda sıralamasını buluyoruz. Yukarıdaki örnekte data department_id ye göre gruplanmış ve her grupta salary kolonuna göre azanal bir şekilde sıralanmış. Daha sonra elde edilen sonuca göre baştan sona doğru sıralamalar yazılmış. Burada dikkat edilmesi gereken şu, aynı değere sahip olan satırlara aynı sıra numarası atılır ve 1 sonra gelen kayıda bu değerin 2 fazlası atanarak sıralama yapılmaya  devam edilir (salary 4800 olan kayıtlar incelenebilir).

 

DENSE_RANK: 

SELECT department_id, last_name, salary,
RANK () OVER (PARTITION BY department_id ORDER BY salary DESC) "Rank",
DENSE_RANK () OVER (PARTITION BY department_id ORDER BY salary DESC) "Drank"
FROM employees;
WHERE department_id = 60 ORDER BY "Rank";

rank

 

Bu örnekte RANK ve DENSE_RANK i aynı sorguda kullanarak 2 si arasındaki farkı daha net ortaya koymak istedim. DENSE_RANK te aynı değere sahip olan satırlara aynı sıra numarası atılır ve 1 sonra gelen kayıda bu değerin 1 fazlası atanarak sıralamaya devam edilir. RANK ve DRANK kolonlarına bakılarak fark anlaşılabilir. DENSE_RANK’in bildiğimiz ÖSS sıralaması olduğunu bilmek daha akılda kalıcı olabilir.

 

PERCENT_RANK:

SELECT department_id,
last_name,
salary,
PERCENT_RANK () OVER (PARTITION BY department_id ORDER BY salary)
 AS pr
FROM employees
WHERE department_id = 60
ORDER BY department_id, pr;

prank

 

PERCENT_RANK sıralamayı, sıralanacak veriyi 0-1 aralığına oturtarak değerler atar. Her bir satırın sıra değerini bulmak içinde aşağıdaki formülü kullanır.

prankor

 

Oracle’ın analitik fonksiyonları oldukça fazla docs.oracle.com üzerinden analitik fonksiyon kataloğu geniş bir şekilde incelenebilir. Bu yazıda Oracle’ın analitik fonksiyon desteğini örnekler ile aktarmaya çalıştım umarım farkındalık anlamında faydalı bir çalışma olmuştur.

 

KAYNAKLAR:

http://connormcdonald.wordpress.com/

http://allthingsoracle.com/experts/alex-nuijten/

Dan Stober [ORACLE ANALYTIC FUNCTIONSWINDOWING CLAUSE]

http://docs.oracle.com/cd/E16655_01/server.121/e17749/toc.htm

http://www.oracle.com/technetwork/database/bi-datawarehousing/sql-analytics-index-1984365.html

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions056.htm

http://docs.oracle.com/cd/E16655_01/server.121/e17749/analysis.htm#DWHSG0202

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions101.htm

http://www.oracle.com/technetwork/database/bi-datawarehousing/sql-analytics-index-1984365.html

Advertisements

About ... from Emrah METE

Bilgisayar Mühendisi
This entry was posted in Oracle, Uncategorized and tagged , , , , , , , , , , , , , , , , . Bookmark the permalink.

4 Responses to Oracle Analitik Fonksiyonlar

  1. mhakanak says:

    Güzel bir anlatım olmuş, windowing örneklerinde partition by ibaresi eksik kalmış, eline sağlık

  2. Enes says:

    Çok sagol paylaşım için

  3. Mehmet says:

    Çok teşekkürler.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s