- tweet
-
OLAP sistemlerinde Datawarehouse ve ETL de sıklıkla kullanılan analitik fonksiyonların örnekleri
Analitik fonksiyonlar, genel olarak verileri, istediğimiz parçalarda ve istediğimiz sırada ele almamızı sağlar. Bunun için OVER ((PARTITION BY … ORDER BY …)) ifadelerini kullanıyoruz. Analitik fonksiyonların yapılarını ve kullanımlarını, karşılaştığımız problemler üzerinden anlatmanın daha anlaşılır olduğunu düşündüğüm için örnekler üzerinden devam edeceğim.
İlk örnekte tüm çalışanlara göre ve departmanlara göre sıra ve toplamları elde ediyoruz. ROW_NUMBER() ifadesiyle sırano ve COUNT() ifadesiyle toplamları, departmanlar bazında sırano ve toplam sayılari elde etmek için PARTITION BY ve empno bazında sıralama için ORDER BY kullanıyoruz.
select A.DEPTNO
, row_number () over (partition by A.DEPTNO order by A.EMPNO)DEPT_EMP_SIRANO
, A.EMPNO
, row_number () over (order by A.DEPTNO, A.EMPNO) EMP_SIRANO
, count ( * ) over (partition by A.DEPTNO) DEPT_TOP_EMP_SAYISI
, count ( * ) over () TOP_EMP_SAYISI
, count (distinct A.DEPTNO) over () TOP_DEPT_SAYISI
from SCOTT.EMP A
order by A.DEPTNO, A.EMPNO;
Tüm maaş ve departmanlar ayırımında toplamlarını ve yüzdelerini veren bir sorguda siralamalar için RANK(), ayrintili siralama için DENSE_RANK(), yüzdeleri hesaplamak için RATIO_TO_REPORT() ve toplamlar için SUM() yazdık. RANK ve DENSE_RANK ifadeleri arasınaki fark, rank, aynı değerlerden sonraki kayıt sıranosuna kayıt sayısına ekleyerek devam eder, dense_rank ise aynı olan kayıt sayısını dikkate almaz. Kayıt sayısı önemli olduğunda rank, sıralama önemli olduğunda dense_rank kullanılabilir. Örnekte empno=7902 ve empno=7788 çalışanları için top_sal_sırano ve detay_top_sal_sırano aynıdır ve sonraki kayıt (empno=7566) için RANK() ifadesi bize top_sal_sırano=4 verirken DENSE_RANK(), detay_top_sal_sırano=3 verir.
select EMPNO
, SAL
, round (RATIO_TO_REPORT (SAL) over () * 100, 2) RAPOR_SAL_YUZDE
, rank () over (order by SAL desc) TOP_SAL_SIRANO
, dense_rank () over (order by SAL desc) DETAY_TOP_SAL_SIRANO
, DEPTNO
, rank () over (partition by DEPTNO order by SAL desc) DEPT_SAL_SIRANO
, dense_rank () over (partition by DEPTNO order by SAL desc)DETAY_DEPT_SAL_SIRANO
, round (RATIO_TO_REPORT (SAL) over (partition by DEPTNO) * 100, 2)DEPT_SAL_YUZDE
, sum (SAL) over (partition by DEPTNO) TOP_DEPT_SAL
, round ( ( (sum (SAL) over (partition by DEPTNO)) / (sum (SAL) over ()))* 100, 2) TOP_DEPT_YUZDE
, sum (SAL) over () TOPLAM_SAL
from SCOTT.EMP
order by SAL desc;
LEAD(<değer>, <öteleme_sayısı>, <varsayılan>) ve LAG(<değer>, <öteleme_sayısı>, <varsayılan>) ifadeleri ile seçilen bir kaydın istenilen değer kadar öncesi ve sonrasına erişilebilir. Örnekte <değer>, hiredate, <öteleme sayisi> 1 ve <varsayılan> TO_DATE (’10000101′, ‘YYYYMMDD’) olmaktadır. Öteleme sayisini 2 verdiğimizde aldığımız değerden 2 kayıt öncesine ulaşabiliriz.
LEAD (hiredate, 3, TO_DATE (’29990101′, ‘YYYYMMDD’))
OVER (PARTITION BY deptno ORDER BY hiredate ASC)
Çalışan işe alım tarihi girilmediğinde NULLS LAST ve NULLS FIRST ifadeleri ile boş kayıtları sıralamada ilk sıraya veya sonlara alabiliriz.
LEAD (hiredate, 1, TO_DATE (’29990101′, ‘YYYYMMDD’))
OVER (PARTITION BY deptno ORDER BY hiredate ASC NULLS LAST)
Bu örnekte, bir çalışanın işe alımından önceki ve sonraki işe alım tarihlerini ve önceki tarih ile arasında geçen gün sayısına bakabiliyoruz. Deptno=10 için ilk işe alınan empno=7782 için önceki kayıt ulaşılamaz olduğundan previous_hiredate varsayılan olarak TO_DATE (’10000101′, ‘YYYYMMDD’) olarak aldık, fakat gacen_gun_sayisi hesaplanırken varsayilan değeri kullanmadık.
SELECT deptno, empno, hiredate
, LAG (hiredate, 1
, TO_DATE (‘10000101’, ‘YYYYMMDD’))
OVER (PARTITION BY deptno ORDER BY hiredate ASC)
previous_hiredate
, LEAD (hiredate, 1, TO_DATE (‘29990101’, ‘YYYYMMDD’))
OVER (PARTITION BY deptno ORDER BY hiredate ASC)
next_hiredate
, hiredate- LAG (hiredate) OVER (PARTITION BY deptno ORDER BY hiredateASC NULLSLAST) gecen_gun_sayisi
FROM scott.emp ORDER BY deptno, hiredate ASC;
Analitik fonksiyonlarla ele aldığımız kayıttan öncesini bir alan olarak tanımlayarak birikimli toplam hesaplayabiliriz.
with temp_running_total as (
select 1 col,100 val from dual union all
select 2 col,200 val from dual union all
select 3 col,250 val from dual union all
select 4 col,300 val from dual
)
select col, val, sum(val) over (order by col rows between unboundedpreceding andcurrent row) brk_toplam
from temp_running_total
Bu örnekte FIRST_VALUE() ve LAST_VALUE() kullanarak departman bazında ilk ve son işe alım tarihlerini ve departman yöneticisinin işe alım tarihlerini getirdik. IGNORE NULLS ifadesi, değer belirlenirken boş değerleri kullanmamak için kullanılır, boş değerler olmadığından emin olduğumuzda IGNORE NULLS ifadesini kaldırarak aynı değerleri MAX() ve MIN() ifadeleriyle ile hesaplayabiliriz. Departman bazında ilk, son ve yönetici değerlerini hesaplamak için bütün değerler bazında bakılmalıydı, ele alınan kaydın öncesindeki ve sonrasındaki kayıtlara ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ifadesi ile ulaştık.
SELECT deptno, empno, job, hiredate , FIRST_VALUE (hiredate IGNORE NULLS)
OVER (partition by deptno ORDER BY hiredate ROWS BETWEEN UNBOUNDEDPRECEDING AND UNBOUNDED FOLLOWING)
dept_first_hiredate
, LAST_VALUE (hiredate IGNORE NULLS)
OVER (partition by deptno ORDER BY hiredate ROWS BETWEEN UNBOUNDED
Jale Ozgur
PL/SQL , ETL, BI Developer
Latest posts by Jale Ozgur (see all)
- Analitik Fonksiyonlar - Ara 5, 2013
- ODI’da Seri ve Paralel Paket Çalıştırma - Ara 5, 2013
- Partitioning in Data Warehouse - Ara 5, 2013
