Normal aggregate fonksiyonlarından en büyük farkı her grup için
bir çok satır dönmesidir ki bu satırlara window olarak tanımlıyoruz ve bu
analytic_clause içinde tanımlanmaktır.
Analitik fonksiyonlar ORDER BY hariç en son gerçekleşen
işlemdir.Tüm JOIN,WHERE,GROUP BY,HAVING analitik fonksiyonlar önce hesaplanmış
olur.Bu yüzdende aslında analitik fonksyionları sadece SELECT ve ORDER BY içerisinde görebiliriz.
Aşağıdaki şekillerde göreceğiniz syntax yapıları sizi korkutmasın
hepsini tek tek açıklayacağım😊
Ufak bir not tüm Syntax resimlerini Oracle’ın kendi sayfasından
aldım bu arada,böyle daha kolay oldu yapmışlar bizim için zaten 😊
En baştan şunu söylemekte ciddi fayda var OVER ifadesi işin içine
giriyorsa ben herşeyi bitmiş, hazır data üzerinde işlem yapacağım o yüzden bir
daha GROUP BY yapmana gerek yok diyor gibi düşünebiliriz.Çünkü bazı functionlar
hem aggregate function olarak hem analytic function olarak kullanılabiliyor.Farkı
bize OVER() verecek.OVER() görürsek GROUP BY yok, OVER yoksa GROUP BY yazılabilir
Bir analitik fonksiyonun yapısı aşağıdaki gibidir.
Buradaki analytic_functiondan kasıt direk fonsiyonun
ismidir:
AVG,
COUNT,
SUM,
LEAD,
LAG,
MEDIAN,
ROW_NUMBER...
Arguments kısmı ismi ise
Hangi sütunda işlemin gerçekleşeceği veya ne kadarlık bir offset olacağını
vb kuralları belirlediğimiz kısımdır.Bu
her fonksiyon için ayrı ayrı argumentler olabilir biri için kullandığınız
argümanı diğer bir fonksyion için kullanamayabilirsiniz.Bu yüzden de aslında
her fonksyionun işlevini iyi bilmemiz gerekiyor.
Analytic_clause ise
Verinin nasıl gruplanacağını,ilgili satırların(pencerenin) nasıl
sıralanacağını veya bu pencerenin büyüklüğünü belirlediğimiz kısımdır.
Analytic_clause’ın yapısı ise aşağıdaki gibidir.
QUERY_PARTITION_CLAUSE
Analytic clause’daki query_partition_caluse’ın yapısı aşağıdaki
gibidir.
Buradaki Partition BY ifadesi aslında Group By ile
aynı işlevi görür demek yanlış olmaz.Yalnız Group by tüm datayı
gruplarken Parititon By ilgili satır
için verileri mantıksal gruplar!!!!
Eğer partition belirtmezseniz tüm datayı tek grup gibi
değerlendirecektir.Bu kısım örneklerle daha açıklayıcı hale gelecektir.
ORDER_BY_CLAUSE
Analytic clause’daki order_by_caluse’ın yapısı aşağıdaki gibidir.
Buradaki order by ifadesi normal order by
ifadesinden hiç farklı değildir.Yalnız buradaki fark yine biri tüm datayı
sıralarken analitik fonksiyon içindeki order by ilgili satıra gelecek olan
pencerenin hangi sırayla geleceğini belirler.!!!!
Buradaki SIBLINGS ifadesi ORACLE’da olan bir özellik başka
DB’lerde olmayabilir çok önemli bir konu değil ama akıllarda soru kalmasın
CONNECT BY PRIOR ile hiyerarşik sorguların sıralanmasında kullanıyor.Ben bugüne
kadar kullanan değil bahseden görmedim dikkate almayın 😊
Expr,position,c_alias ifadesi ise sütun veya sütunun select
içindeki sırası veya sütuna verdiğiniz alias olabilir buraya istediğinizi
yazabilirsiniz.Her zaman sütunun ismini vermekte fayda var araya bir sütun
eklersiniz position kayar veya alias’ı değiştirir unutursunuz vb.
ASC,DESC ifadesi hangi sütunun nasıl sıralama
yapmasını istediğinizi gösterir ASC A->Z iken DESC Z->A şeklindedir aynı
mantık tarih ve sayısal alanlar içinde geçerlidir.
NULLS FIRST,NULLS LAST ifadesi ise null’ların sıralamada
hangi sırada gözükmesini istediğinizi belirtir.
Bu Oracle ve Postgresql’de olan bir özellik bildiğim kadarıyla ama
diğer DB’lerde de bir kaç ufak hile ile çözebiliriz.COALESCE atıp sütuna bu
sorunu çözebiliriz.
En üstte bir virgül var dikkat ederseniz bu da birden çok sütun
için bu işlemleri tekrarlayabilirsiniz demektir.
WINDOWING_CLAUSE
Analytic clause’daki windowing_caluse’ın yapısı aşağıdaki
gibidir.
Bu kısım görece analitik fonksyionların en az bilinen ve uygulanan
alanıdır diyebiliriz.
ROWS ilgili gruplandırmaya geçerli olan fiziksel
satırları ifade etmek için vardır
RANGE ise ilgili gruplandırmadaki her satır için belirli
bir pencere tanımlar.Bu mantıksal bir penceredir.Bunu çok kullanmamanızı
tavsiye ederim bazen sizin belirlediğiniz pencere dışına taşabilir ROWS iyidir😊
BETWEEN|AND ifadesi başlangıç ve bitiş satırlarını ifade
etmektedir ilgili pencere için.Where’de yazdığımızdan farklı bir kural değil.
UNBOUNDED PRECEDING
Bu ifade ilgili pencerenin ilk
satırdan başladığını ifade etmek için kullanılır ve sadece başlangıç için
kullanılabilir bitiş için kullanılamaz.
UNBOUNDED FOLLOWING
Bu ifade ilgili pencerenin son
satırda bittiğini ifade eder ve sadece bitiş için kullanılabilir başlangıç
için kullanılamaz.
CURRENT ROW
Bu ifade pencerenin ilgili kayıttan
başladığını veya bitttiğini ifade eder.Eğer başlangıç olarak (AND’den önce)
yazıldıysa PRECEDING’lı bitiş ifadesi olamaz.Eğer bitiş olarak kullanıldıysa bu
durumda da FOLLOWING’li bir başlangıç ifadesi olamaz.
value_expr PRECEDING ilgili satırdan(current row) ne kadar önce başladığını value_expr
FOLLOWING ilgili satırdan ne kadar sonra pencerenin bittiğini belirtmek
için kullanılır.Buradaki value_expr aslında sayısal bir değer 1,2,10
gibi.
Hiç kullanmaz isek ne olur derseniz
aslında windowing clause’ımız aşağıdaki gibi default değerini alarak tüm
pencereyi tarar.RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
Bazı analitik fonksiyonlarda tutarlılık elde edemeyebilirsiniz
eğer unique bir sıra değeri yakalayamıyorsanız buda aslında sorgunuzu tutarsız
bir hale getirebilir.
Çoğu örneği aynı data set üzerinden yapmaya çalışacağım ki
anlaşılırlığı artsın eğer data set değiştireceksem onun da ekran görüntüsünü
ekleyeceğim.
Şimdi ORDER_ITEMS isimli bir tabloda çalıştığımızı hayal edelim
görüntüsü aşağıdaki gibi.
Bu tabloyu sepet gibi düşünelim orderlar sepetiniz product’lar eklediğiniz ürünler ve
quantity adediniz unit_price’nız 1 tanesinin fiyatı.Ve orderlar böyle devam
edip gidiyor.
Şimdi bunu kullanarak bazı hesaplamalar yapalım.Buraya kadar ki
teorik kısım karmaşık gelmiş olabilir örneklerle iyice pekişecektir.
AVG
AVG bir aggreagte fonksyion olmasına rağment OVER ifadesiyle
kullanıldığında bir analitik fonksiyona dönüşür.AVG() OVER() ifadesinde
artık GROUP BY yazmamalısınız sorgunuza dikkat edin.
Sytanx’i yukardaki gibidir bize ortalama değeri döner belirtilen
pencere için burada şuna dikkat etmek lazım eğer DISTINCT ifadesini
kullanırsanız eğer sadece query_partition_clause
kullanabilirsiniz.order_by_clause veya windowing_clause kullanamazsınız.
Klasik bir aggregate AVG fonksiyonu yazsakdık şöyle bir sytax’imiz
olacaktı.
select
ORDER_ID,
PRODUCT_ID,
AVG(UNIT_PRICE) as ORTALAMA
from YT.ORDER_ITEMS
GROUP BY
ORDER_ID,PRODUCT_ID
ORDER BY
ORDER_ID,PRODUCT_ID
Bu bize order_id
ve product_id bazında ortalama değeri getirecekti ama şuna dikkat aynı
order_id’e ait iki tane aynı product_id eklendiyse bu tek satıra düşecekti.
Şimdi aynı
mantığı burası için çalıştıralım.
select
ORDER_ID,PRODUCT_ID,AVG(UNIT_PRICE) OVER(PARTITION BY
ORDER_ID,PRODUCT_ID) AS ORTALAMA from YT.ORDER_ITEMS
Yukarda
görüldüğü gibi satırlar teke düşmedi ve her satırın yanına order_id ve
product_id bazında ortalama geldi.
Şimdi
farkı daha iyi anlamak için bir tanede windowing clause ekleyelim.
SELECT
ORDER_ID,PRODUCT_ID,UNIT_PRICE,AVG(UNIT_PRICE) OVER(PARTITION BY
ORDER_ID ORDER BY
ORDER_ID ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) ORTALAMA
FROM YT.ORDER_ITEMS
ORDER BY
ORDER_ID,PRODUCT_ID
Şimdi bu
kodu açıklayalım burada git unit_price’ın bana ortalamasını getir ama getirken o
order_id’e ait tüm satırlardan ortalama alma!!! Sadece bir önceki satırın
unit_price’ını,kendisinkini(current row)
Ve bir
sonrakinin unit_price’I üzerinden bir ortalama al diyoruz aslında.
Örneğin
ben buradaki 1 PRECEDING’I 2 PRECEDING yapsaydım 2 satır önceden başla ve bir
sonraki satıra kadar kiler üzerinden ortalama al demiş olacaktım.
Yine
pekiştirmek için tekrarlayalım örneğin 1 nolu order_id’nin 78 nolu product_id’e
bakarsanız 617.99 rakamının aslında bir önceki satır olan 64 nolu product,78
nolu product ve 126 nolu product_id’lerin unit_pricenin toplamı olduğunu
görebilirsiniz.
COUNT
COUNT bir aggreagte fonksyion olmasına rağment OVER ifadesiyle
kullanıldığında bir analitik fonksiyona dönüşür.COUNT() OVER() ifadesinde
artık GROUP BY yazmamalısınız sorgunuza dikkat edin.
Sytanx’i yukardaki gibidir bize satır sayısı değeri döner
belirtilen pencere için burada şuna dikkat etmek lazım eğer DISTINCT
ifadesini kullanırsanız eğer sadece query_partition_clause
kullanabilirsiniz.order_by_clause veya windowing_clause kullanamazsınız.
İsterseniz * ifadesi ile tüm satırların sayısınıda getirebiliriz.AVG’da
anlattığım aggeragte kuralı burada da daha doğrusu hem aggregate hem analytic
olarak kullanılan tüm fonskiyonlarda geçerlidir.Aggeragate ederseniz group by’a
koyduğunuz sütunlar bazında tabloya distinct atmış olursunuz aslında.
SELECT
ORDER_ID,
PRODUCT_ID,
COUNT (ORDER_ID) OVER (PARTITION BY
ORDER_ID) ADET,
COUNT (DISTINCT
ORDER_ID) OVER (PARTITION BY
ORDER_ID) ADET2,
COUNT (*) OVER (PARTITION BY
ORDER_ID, PRODUCT_ID) ADET3,
COUNT (*) OVER (PARTITION BY
ORDER_ID ORDER BY
ORDER_ID,PRODUCT_ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
ADET4,
COUNT (*) OVER (PARTITION BY
ORDER_ID,PRODUCT_ID ORDER BY
ORDER_ID,PRODUCT_ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
ADET5
FROM YT.ORDER_ITEMS
ORDER BY
ORDER_ID, PRODUCT_ID
Şimdi üstteki sorgunun çıktısına bakalım.ADET sütununa aslında
ORDER_ID kaç kere varsa gelsin dedik.
ADET2’de ORDER_ID’yi DISTINCT’ledik ve bunu yine ORDER_ID bazında
grupladık haliyle her satıra 1 geldiğini gördük.
ADET3’de ise ORDER_ID yanında birde PRODUCT_ID bazında grupladık
ve gördüğünüz gibi 1 Nolu ORDER_ID’de 64 Nolu PRODUCT_ID 2 kere bulunduğu için
karşına 2 diğerleri 1 kere bulunduğu için 1 getirdi.
ADET4’e bakarsanız ORDER_ID bazında grupladım ve
ORDER_ID,PRODUCT_ID bazında sıraladım sonrasında ise COUNT’a konu satırlar
kendisi ve önceki satırlardır dedim ve her satırın ilgili ORDER_ID için 1 er 1
er arttığını gördük.
ADET5’te aslında ADET4’ün aynısını yaptım tek farkla artık
gruplandırmaya PRODUCT_ID’de eklendi ve gördüğünüz gibi aslında 64 nolu
PRODUCT_ID için sırasıyla 1 ve 2 alırken diğerleri tek satır olduğundan 1
değerini aldı.
DENSE_RANK
Bu fonksyionun amacı satırın grup içindeki sırasını
belirtmektir.ROW_NUMBER’dan farkı aynı değere sahip iki satır varsa ikisine de
aynı değeri atamasıdır.DENSE_RANK’da windowin_clause çağıramazsınız.
SELECT
ORDER_ID,
PRODUCT_ID,
DENSE_RANK() OVER(ORDER BY
ORDER_ID,PRODUCT_ID)
SIRA1,
DENSE_RANK() OVER(PARTITION BY
ORDER_ID ORDER BY
ORDER_ID,PRODUCT_ID)
SIRA2,
DENSE_RANK() OVER(PARTITION BY
ORDER_ID,PRODUCT_ID ORDER BY
ORDER_ID,PRODUCT_ID)
SIRA3
FROM YT.ORDER_ITEMS
ORDER BY
ORDER_ID, PRODUCT_ID
Şimdi bu sorgunun alttaki çıktısını inceleyelim.SIRA1’de dikkat
ederseniz hiç bir şeyin bazında gruplamadım.O yüzden de DENSE-RANK
içindeki ORDER BY’ da belirttiğim sıraya göre değerleri atadı.Burada ORDER_ID
ve PRODUCT_ID’nın aynı olduğu satırlara aynı değeri verdiğine dikkat edin ve
ORDER_ID değişse bile 1’den 2’ye sıranın başa dönmediğine dikkat edin!!!.
SIRA2’de ise bu sefer ORDER_ID bazında gruplandırma ekliyorum ve
sıranın her yeni ORDER_ID’de tekrardan 1’den başladığını görüyoruz.
SIRA3’de ise ORDER_ID’nın yanına PRODUCT_ID’de ekliyorum ve hepsi
1 dönüyor.Aslında 64 nolu PRODUCT_ID için 1 ve 2 gibi değerler görmeyi
bekleyebilirdik ama aslında onları kendi içinde değerlendirdi ve aynı
olduklarını gördüğü için her ikisinede 1 verdi.Diğerleri zaten 1 olması normal.
FIRST_VALUE& LAST_VALUE
İlgili pencerenin ilk değerini döndürür.Burada NULL’ları
engellemek için IGNORE NULLS diyebiliriz.Default değeri RESPECT NULLS’dır ve
NULL değerleri döndürür.Buradaki ilk değer sizin sorgunuzun döndüğü ilk değer değildir!!.Sorgunuzun
sıralaması bambaşka olabilir analtitik fonksiyonun kendi analytic_clause’da
aldığı sıralama önemlidir.!!!
İlgili pencerenin son değerini döndürür.Burada NULL’ları
engellemek için IGNORE NULLS diyebiliriz.Default değeri RESPECT NULLS’dır ve
NULL değerleri döndürür.Buradaki son değer sizin sorgunuzun döndüğü son değer değildir!!.Sorgunuzun
sıralaması bambaşka olabilir analtitik fonksiyonun kendi analytic_clause’da
aldığı sıralama önemlidir.!!!
Burada LAST_VALUE ve FIRST_VALUE beraber değerlendireceğim ikiside
1-1 aynı birinde ilk iken diğerinde son kayıt aslında
SELECT
ORDER_ID,
PRODUCT_ID,
QUANTITY,
FIRST_VALUE(QUANTITY) OVER()
ILK_DEGER1,
FIRST_VALUE(QUANTITY) IGNORE NULLS OVER()
ILK_DEGER2,
FIRST_VALUE(QUANTITY) OVER(PARTITION BY
ORDER_ID) ILK_DEGER3,
FIRST_VALUE(QUANTITY) IGNORE NULLS OVER(PARTITION BY
ORDER_ID) ILK_DEGER4,
FIRST_VALUE(QUANTITY) OVER(PARTITION BY
ORDER_ID,PRODUCT_ID) ILK_DEGER5,
FIRST_VALUE(QUANTITY) IGNORE NULLS OVER(PARTITION BY
ORDER_ID ORDER BY
ORDER_ID,PRODUCT_ID ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING)
ILK_DEGER6
FROM YT.ORDER_ITEMS
ORDER BY
ORDER_ID, PRODUCT_ID
Şimdi alttaki çıktıyı inceleyelim.
ILK_DEGER1 NULL geliyor çünkü dikkat ederseniz OVER içinde hiç bir
kriter yok ve haliyle tüm data içindeki ilk değere bakıyor ki o da ORDER_ID 1
ve PRODUCT_ID 30 olan kaydın QUANTITY’si.
ILK_DEGER2’ye bakarsanız 52 görüyoruz tüm satırlarda çünkü
NULL’dan sonraki ilk dolu değer 52 bunu da IGNORE NULLS ile sağladık.
ILK_DEGER3’de ise ORDER_ID 1 için NULL 2 için 49 değerini
görüyoruz.Çünkü ORDER_ID 1 in ilk değeri NULL.
ILK_DEGER4’de ise ORDER_ID 1 için 52 (NULL’dan sonraki ilk dolu
değer) ve 2 için 49’u görüyoruz.
ILK_DEGER5’de ise artık ORDER_ID’nin yanında PRODUCT_ID içinde kırmak
istiyoruz.Ve aynı PRODUCT_ID’ye sahip 64’nolu product için 52 görürken
diğerlerinde kendi quantitylerini görüyoruz çünkü tek satırlar.
ILK_DEGER6’da ise ORDER_ID bazında kır ama bunu yaparken iligli
satırın 1 öncesi ve 2 sonrası arasındaki ilk değeri getir diyorum ve bunu
yaparken de NULL’ları umursama diyorum ve hepsinde 1 önceki satırın değerini
getiriyor.
Tam tersi mantık LAST_VALUE ile kurulabilir.
LAG& LEAD
Verdiğiniz offset değerine göre size önceki satırın değerini getiren
bir analitik fonksiyondur.offset’in default değeri 1’dir.RESPECT yada IGNORE
NULLS ifadeleri null değerleri dikkate alınıp alınmayacağı ile ilgilidir
default’u RESPECT NULLS’dur. 3 parametre
almaktadır.LAG(sütun_ismi,offset,default) şeklinde max tanımlama
yapılabilir.Buradaki default ise offset window’u geçerse ne dönsün
demektir.Default değeri null’dır.Daha açık olmak gerekirse örneğin ilk kayıttan
önce başka bir kayıt olmayacağı için getirebileceği değer yoktur bu durumda
null döndüğünü görürsünüz eğer bu defaultu tanımlarsanız tanımladığınız değer
döner.
LEAD LAG’ın tersi olarak sonraki kaydı getirir geri kalan tüm
scriptler ve mantık aynı olduğu için burada bir daha yazmıyorum.
SELECT
ORDER_ID,
PRODUCT_ID,
QUANTITY,
LAG(QUANTITY) OVER(ORDER BY
ORDER_ID) AS ONCEKI_DEGER1,
LAG(QUANTITY) IGNORE NULLS OVER(ORDER BY
ORDER_ID) AS ONCEKI_DEGER2,
LAG(QUANTITY,1,111111) OVER(ORDER BY
ORDER_ID) AS ONCEKI_DEGER3,
LAG(QUANTITY,1,111111) IGNORE NULLS OVER(ORDER BY
ORDER_ID) AS ONCEKI_DEGER4,
LAG(QUANTITY,2,22222) IGNORE NULLS OVER(ORDER BY
ORDER_ID) AS ONCEKI_DEGER5,
LAG(QUANTITY) OVER(PARTITION BY
ORDER_ID ORDER BY
ORDER_ID) AS ONCEKI_DEGER6,
LAG(QUANTITY,1,9999) OVER(PARTITION BY
ORDER_ID,PRODUCT_ID ORDER BY
ORDER_ID) AS ONCEKI_DEGER7
FROM YT.ORDER_ITEMS
ORDER BY
ORDER_ID, PRODUCT_ID
ONCEKI_DEGER1 sütununu incelersek ilk 2 satırın boş diğer
satırların bir önceki satırın quantity’si olduğunu görebiliriz.ilk 2 satırın
boş olmasının sebebi ilk satır kendinden önce değer olmadığından boş 2.satır
ise 1.satırın quantity’si boş olduğu boş.
ONCEKI_DEGER2’de IGNORE NULLS dememize ragmen ilk iki satır boş
geldi.İlk satırın boş olması normaldi ama 2. Dolu olmalıydı fakat kendinden
önce hiç dolu değer olmadığı için o da boş geldi.
ONCEKI_DEGER3’de 1 ve 111111 değerleri geldi buradaki 1’den kasıt
1 satır önceki değer demektir.111111 ise range dışına çıkarsa yani 1.satırın
öncesinin olmadığı gibi bu durumda ne yazayım demektir.
ONCEKI_DEGER4’de IGNORE NULLS dediğimiz için default değerimiz
111111’I getirdi.
ONCEKI_DEGER5’de ise 2 satır önceye gitmesini ve NULL’ları
önemsememsini istedik.
Buraya
kadar hep tüm data set üzerinden ilerledik.
ONCEKI_DEGER6’da ise artık hesaplamanın ORDER_ID bazlı olacağını
ilettik ve ORDER_ID 2 olanın ilk satırının boş geldiğini gördük çünkü hesaplama
artık her ORDER_ID için tekrar tekrar yapılacak.
ONCEKI_DEGER7’de ise ORDER_ID yanında bir de PRODUCT_ID ekledik bu
durumda sadece aynı satırlar için ORDER_ID 1 PRODUCT_ID 64 için çalıştı geri
kalan satırlara default değerimiz 9999’u bastı.
Aynı mantık LEAD içinde kurulabilir
LISTAGG
İlgili gruptaki tüm değerleri verdiğiniz
delimiter(virgül,pipe,nokta vb.) birleştirir ve size bir text döner.
SELECT
ORDER_ID,
LISTAGG(ITEM_ID,',') OVER()
BIRLESTIRME1,
LISTAGG(ITEM_ID,',') OVER(PARTITION BY
ORDER_ID) BIRLESTIRME2,
LISTAGG(ITEM_ID,',') WITHIN GROUP ORDER BY
ITEM_ID) OVER(PARTITION BY ORDER_ID)
BIRLESTIRME3
FROM YT.ORDER_ITEMS
ORDER BY
ORDER_ID, PRODUCT_ID
BIRLESTIRME1’de herhangi bir kural işletmediğimiz için tablodaki
tüm ITEM_ID’leri tüm satırlara yazdı.
BIRLESTIRME2’de bu ITEM_ID’leri ORDER_ID bazında grupla dedik ve
sadece o ORDER_ID’e ait ITEM_ID’ler birleştirildi.
BIRLESTIRME3’de bu ITEM_ID’leri ORDER_ID bazında grupla dedik ve
sadece o ORDER_ID’e ait ITEM_ID’ler birleştirildi.Fakat bu sefer benim için
birleştirme sırası önemli dedik ve sırasıyla A->Z şeklinde birleştirdi.
MAX&MIN
İlgili penceredeki maksimum değeri döndürür.
İlgili penceredeki minimum değeri döndürür.
SELECT
ORDER_ID,
PRODUCT_ID,
QUANTITY,
UNIT_PRICE,
QUANTITY*UNIT_PRICE PRODUCT_AMOUNT,
MAX(QUANTITY*UNIT_PRICE) OVER() MAX1,
MAX(QUANTITY*UNIT_PRICE) OVER(PARTITION BY
ORDER_ID) MAX2,
MAX(QUANTITY*UNIT_PRICE) OVER(PARTITION BY
ORDER_ID ORDER BY
ORDER_ID,PRODUCT_ID ROWS UNBOUNDED PRECEDING ) MAX3
FROM YT.ORDER_ITEMS
ORDER BY
ORDER_ID, PRODUCT_ID
Burada en
yüksek getiri sağlayan ürünün getirisini bulmaya çalışalım.
QUANTITY*UNIT_PRICE bize istediğimiz değeri getirecek.
Şimdi bu
değer için bir MAX bulmaya çalışalım.
MAX1’de
hiç bir Kural olmadığı için gitti tüm satırlardaki QUANTITY ile UNIT_PRICE’ı
çarptı ve en büyük değeri tüm satırlara yazdı.
MAX2’de
işin içine ORDER_ID bazında gruplandırma girdi ve her ORDER_ID için ilgili
order’ın en yüksek product getirisini yazdı.
MAX3’de
ise her satıra ilgili satır veya öncesindeki satırlar arasından max’I bul
dedik.
Aynı mantık MIN içinde
kurulabilir
MEDIAN
Median bize ilgili gruptaki satırların ortanca değerini verir.NULL’ları
dikkate almaz!!!
Burada expr olarak koyduğunuz sütun sayısal veya tarihsel bir alan
olmalıdır.
Ve order_by_clause veya windowing_clause kullanamadığınıza dikkat
edin.
SELECT
ORDER_ID,
PRODUCT_ID,
QUANTITY,
UNIT_PRICE,
QUANTITY*UNIT_PRICE PRODUCT_AMOUNT,
MEDIAN(QUANTITY*UNIT_PRICE) OVER()
MEDIAN1,
MEDIAN(QUANTITY*UNIT_PRICE) OVER(PARTITION BY
ORDER_ID) MEDIAN2
FROM YT.ORDER_ITEMS
ORDER BY
ORDER_ID, PRODUCT_ID
Median fonksyionu
bir order by veya windowing değeleri alamaz sadece partition
kullanabilirsiniz.O yüzden çok farklı versiyonu olmayan basit bir fonksiyondur.
MEDIAN1
bize tüm datasetinin medianını getirdi her satıra.
MEDIAN2
ise bize ORDER_ID bazında medianı getirdi her farklı ORDER_ID için
NTH_VALUE
Bize iligli penceredeki n.inci değeri döndürür.FROM FIRST default
değeridir en üstten sayıp n.inci değeri getirir FROM LAST ile sondan da
başlatabiliriz istersek.
Genellikle az kullanılan ama kullanışlı bir fonksiyondur.
SELECT
ORDER_ID,
PRODUCT_ID,
ITEM_ID,
QUANTITY,
NTH_VALUE(QUANTITY,3) OVER ()
SIRA1,
NTH_VALUE(QUANTITY,3) OVER (PARTITION BY
ORDER_ID) SIRA2,
NTH_VALUE(QUANTITY,3) FROM LAST OVER (PARTITION BY
ORDER_ID) SIRA3,
NTH_VALUE(QUANTITY,3) FROM FIRST OVER (PARTITION BY
ORDER_ID ORDER BY
ORDER_ID,ITEM_ID) SIRA4,
NTH_VALUE(QUANTITY,1) FROM FIRST OVER (PARTITION BY
ORDER_ID ORDER BY
ORDER_ID,ITEM_ID ROWS 2 PRECEDING)
SIRA5
FROM YT.ORDER_ITEMS
ORDER BY
ORDER_ID, ITEM_ID
SIRA1 basitçe gitti tüm tabloyu sıraladı ve baştan 3.değeri
getirdi ki o da 3.satırın QUANTITY değeri olan 52.
SIRA2 de işin içine ORDER_ID grubu girdi ve artık ORDER_ID bazında
3.değerleri ilgili ORDER_ID’lere yazdı.ORDER_ID 1 için 52,2 için 39 gibi.
SIRA3 de bu sefer ORDER_ID bazında sondan sayarak 3. Değeri getir
dedim ve ORDER_ID 1 için 105,2 için 100 değerini döndürdü.
SIRA4 de bu sefer ORDER BY girdi işin içine ve 3.satırı 3.satırdan
başlatarak getirmeye başladı.
SIRA5 de bir de windowing girdi işin içine ve 2 satır öncesi ile
kendisinin olduğu 3 satırlık bir penecere yarattım bunun ilk satırını getir
dedim aslında bir nevi her hücreye 2 önceki satırın değerini getir dmeiş
oldum.Ki bu da aslında LAG fonksiyonudur 😊
PERCENTILE_CONT&PERCENTILE_DISC
Mediandaki mantığın benzeri şekilde çalışır.Hatta Median ile
Percentile_cont(0.5) aynı değeri döndürür.expr kısmına 0 ile 1 arası bir değer
verebilirsiniz o da size o verdiğiniz değerdeki sütunun değerini döner.Eğer satır
sayınız çift bir rakam ise percentile_cont ortanca iki değerin ortalamasını
getirirken percentile_disc ise ilk değeri getirir.O yüzden iki fonksiyonda aynı
işlevi yapmasına rağmen farklı sonuçlar görebiliriz.Ama satır sayısı tek olan
data setlerinde ikiside aynı değeri döndürür.
Bu fonskyion içindeki ORDER BY’a yazdığın sütun çok önemlidir
çünkü o sütunun değerini döndürecektir.O yüzden de sadece bir sütun
yazabilirsiniz!!!
SELECT
ORDER_ID,
PRODUCT_ID,
QUANTITY,
UNIT_PRICE,
QUANTITY*UNIT_PRICE PRODUCT_AMOUNT,
PERCENTILE_CONT(0.3) WITHIN GROUP (ORDER BY
QUANTITY*UNIT_PRICE ASC) OVER()
PERCENTILE1,
PERCENTILE_CONT(0.3) WITHIN GROUP (ORDER BY
QUANTITY*UNIT_PRICE DESC) OVER()
PERCENTILE2,
PERCENTILE_CONT(0.3) WITHIN GROUP (ORDER BY
QUANTITY*UNIT_PRICE ASC) OVER(PARTITION BY
ORDER_ID) PERCENTILE3,
PERCENTILE_CONT(0.3) WITHIN GROUP (ORDER BY
QUANTITY*UNIT_PRICE DESC) OVER(PARTITION BY
ORDER_ID) PERCENTILE4
FROM YT.ORDER_ITEMS
ORDER BY
ORDER_ID, PRODUCT_ID
Bu örnek üzerinde
daha net anlaşılabilir o yüzden bu fonksiyona verdiğiniz değeri şöyle düşünün
100 satır varsa bana 30. satırı getir demek istiyoruz aslında.Ya da satır
sayısı çarpı*0.3’üncü satırı getir.
PERCENTILE1’de
QUANTITY*UNIT_PRICE
çarpımını sırala ve bana satır sayısı*0.3’deki değeri getir diyoruz.
PERCENTILE2’de
veriyi büyükten küçüğe sıralayıp ve bana satır sayısı*0.3’deki değeri getir
diyoruz.
PERCENTILE3’de
işine içine ORDER_ID giriyor bu sefer ORDER_ID bazında satırları grupla ve o ORDER_ID’ye
ait satır sayısı*0.3’üncü satırı getir diyoruz.
PERCENTILE4’de
ise PERCENTILE3’ün aynısı fakat sıralamayı büyükten küçüğe yap diyoruz.
Bu
fonksyion özellikle data setinizde çok ciddi aykırı değerler varsa genelde
yığıldıkları aralıkları bulmak için mantıklı oluyor.Ben genelde 0.25,0.5 ve
0.75’i değerlerinin yanına birde ortalamalarını getiriyorum ve data ne kadar
güvenilir anlamaya çalışıyorum.
PERCENTILE_DISC aynı şekilde yazılabilir.
RANK
Bize ilgili satırın sıra numarasını döndürür.DENSE_RANK veya
ROW_NUMBER’dan farkı ise DENSE_RANK aynı değere sahip satırlara aynı numarayı
verdikten sonra bir sonraki değere geçerken RANK aynı değere sahip satırlara
aynı numarayı verdikten sonra bir sonraki değeri ne kadar satır ataması yaptıysa
ondan sonra devam eder.
SELECT
ORDER_ID,
PRODUCT_ID,
RANK() OVER(ORDER BY
ORDER_ID) RANK1,
RANK() OVER(ORDER BY
ORDER_ID,PRODUCT_ID) RANK2,
RANK() OVER(PARTITION BY
ORDER_ID ORDER BY
ORDER_ID,PRODUCT_ID) RANK3
FROM YT.ORDER_ITEMS
ORDER BY
ORDER_ID, PRODUCT_ID
RANK1’de hiç bir kural işletmiyorum(ORDER BY zorunlu!) ve gidiyor ORDER_ID
bazında tabloya bakıyor ilk 13 satırın hepsi 1 nolu ORDER_ID’e eşit o yüzden
hepsine 1 diyor.2 nolu ORDER_ID için 14’ten başlıyor çünkü ilk 13 satıra 1 atamıştı
ve bu sefer tüm 2 lere 14 değerini atıyor.
RANK2’de ORDER BY’a PRODUCT_ID de ekliyorum ve bu sefer sıralamaya
dikkat edin 2. Ve 3. Satırlar aynı olduğu için 2 değerini aldı fakat 4.satıra
başlarken 4 diyerek devam etti.
RANK3’de ise sıralamayı her yeni ORDER_ID’de tekrar 1 den başlat
diyebilmek için PARTITION BY ORDER_ID ekledim.
ROW_NUMBER
Herhalde en çok kullanılan analitik fonksiyon olabilir.İlgili veri
setinin sırasını getirir ve benzersiz bir sırayla getirir yani aynı değere
sahip sıra numarası göremezsiniz DENSE_RANK ve RANK’daki gibi.
Burada çok önemli bir konu var tabi aynı değere sahip satırlara
sıra numarası atadığında bir dahaki çalıştırdığınızda aynı satırlar aynı sıra
numarası alacağı anlamına gelmez!!! Bu yüzden mümkünse unique bir sıra
yakalamaya çalışın ya da bunun farkında olarak işlem yapın.
SELECT
ORDER_ID,
PRODUCT_ID,
ROW_NUMBER() OVER(ORDER BY
ORDER_ID) SIRA1,
ROW_NUMBER() OVER(ORDER BY
ORDER_ID,PRODUCT_ID) SIRA2,
ROW_NUMBER() OVER(PARTITION BY
ORDER_ID ORDER BY
ORDER_ID) SIRA3,
ROW_NUMBER() OVER(PARTITION BY
ORDER_ID ORDER BY
ORDER_ID,ITEM _ID) SIRA4
FROM YT.ORDER_ITEMS
ORDER BY
ORDER_ID, PRODUCT_ID
SIRA1’de
hiç bir kural çalışmıyor (ORDER BY zorunlu!) ve tablodaki tüm satırları
sıralıyor getiriyor.Burada unique bir değer olmadığı için bu sıra başka bir
çalıştırmada değişebilir!!!
SIRA2’de sıralamaya
PRODUCT_ID dahil olmasına ragmen SIRA1’den farklı bir sonuç elde etmedik çünkü
zaten data set’de PRODUCT_ID bazında sıralıydı fakat sıralamada ITEM_ID
deseydik bambaşka bir sonuç elde edecektik.
SIRA3’de artık
her order’da tekrar 1 den başla saymaya demek için PARTITION BY ORDER_ID
ekledik.
SIRA4’de
SIRA2’deki gibi anlattığımı gösterebilmek için ORDER_ID VE ITEM_ID bazında
sıraladım ve sıralamanın nasıl değiştiğine dikkat edin.Ve her yeni order’da
tekrardan 1 den başlayacaktır PARTITION BY’dan dolayı.
SUM
İlgili pencerenin toplamını getirir.Aggerate fonskiyondan farkı
aggerate halinde OVER() bulunmaz
SELECT
ORDER_ID,
PRODUCT_ID,
QUANTITY*UNIT_PRICE AMOUNT,
SUM(QUANTITY*UNIT_PRICE) OVER() SUM1,
SUM(QUANTITY*UNIT_PRICE) OVER(ORDER BY
ORDER_ID,ITEM_ID) SUM2,
SUM(QUANTITY*UNIT_PRICE) OVER(PARTITION BY ORDER_ID) SUM3,
SUM(QUANTITY*UNIT_PRICE) OVER(PARTITION BY ORDER_ID ORDER BY
ORDER_ID,ITEM_ID) SUM4,
SUM(QUANTITY*UNIT_PRICE) OVER(PARTITION BY ORDER_ID ORDER BY
ORDER_ID,ITEM_ID ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) SUM5
FROM YT.ORDER_ITEMS
ORDER BY
ORDER_ID,ITEM_ID
SUM1’de
hiç bir kural vermediğimiz için tüm satırların QUANTITY*UNIT_PRICE’nı topladı ve getirdi.
SUM2’de ORDER_ID,ITEM_ID bazında sıraladık ve Running sum denen
artarak toplama işlemi yaptı.
SUM3’de
ise toplamları order bazında kır dedim ve her satıra o order’ın toplamını
getirdi.
SUM4’de
ise yine artarak topladı ama her order’da sıfırlandı toplamlar ve o order için
bir daha arta arta gitti.
SUM5’de
ise order bazında gruplandırdım ve sıraladım fakat bu sefer her satıra toplamı
getirecekken 1 öncesi+kendisi+2 sonrasının değerlerini al bu satıra yaz dedim.
Hiç yorum yok:
Yorum Gönder