SQL ANALİTİK FONKSİYONLAR

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.