PySpark Window Functions

Merhabalar bugünkü yazımda size PySpark Window Functions’ın  işlevinden ve PySpark Sql ve PySpark DataFrame API ile kullanımından bahsedeceğim.

Pyspark: Apache Spark, Scala programlama dilinde yazılmıştır. PySpark, Apache Spark ve Python işbirliğini desteklemek yani Spark için yazılmış bir Python API’dır. PySpark sayesinde Python ve Spark’ın bazı özellikleri birlikte kullanılabilir hale gelir. Ayrıca PySpark, Apache Spark ve Python programlama dilinde Esnek Dağıtılmış Veri Kümeleri (Resilient Distributed Datasets) ile arayüz oluşturmanıza yardımcı olur. Spark, Python ve Java gibi bir programlama dili olmadığı için PySpark’a ihtiyaç duyulmuştur. Bu sayede geliştiriciler ve veri bilimciler, verileri hızlı bir şekilde sorgulamak, analiz etmek ve dönüştürmek için PySpark’ı uygulamalarına dahil ederler.

Window Functions: PySpark Window Functions işlevleri bir grup  girdi satırı üzerinde çeşitli analitik işlemler gerçekleştirir ve her girdi satırı için ayrı ayrı sonuç döndürür. Bu işlemin yapılabilmesi için kullanılan veriyi partitionlamamız gerekir. PySpark Sql üç tür işlevi destekler. Bunlar;

• Sıralama Fonksiyonları
• Analitik Fonksiyonlar
• Toplama (aggregate) Fonksiyonları

Kullandığımız Versiyonlar ;

• Pyspark 3
• Python 3.6

İlk olarak gerekli kütüphaneleri import ediyoruz;

[fusion_syntax_highlighter theme=”” language=”x-sh” line_numbers=”” line_wrapping=”” copy_to_clipboard=”” copy_to_clipboard_text=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” font_size=”” border_size=”” border_color=”” border_style=”” background_color=”” line_number_background_color=”” line_number_text_color=”” margin_top=”” margin_right=”” margin_bottom=”” margin_left=””]ZnJvbSBweXNwYXJrLnNxbCBpbXBvcnQgZnVuY3Rpb25zIGFzIEYKZnJvbSBweXNwYXJrLnNxbCBpbXBvcnQgU3BhcmtTZXNzaW9uCmZyb20gcHlzcGFyay5zcWwuZnVuY3Rpb25zIGltcG9ydCAqCmZyb20gcHlzcGFyay5zcWwud2luZG93IGltcG9ydCBXaW5kb3cKaW1wb3J0IHB5c3Bhcmsuc3FsLmZ1bmN0aW9ucyBhcyBmdW5j[/fusion_syntax_highlighter]

Spark session oluşturuyoruz.

[fusion_syntax_highlighter theme=”” language=”x-sh” line_numbers=”” line_wrapping=”” copy_to_clipboard=”” copy_to_clipboard_text=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” font_size=”” border_size=”” border_color=”” border_style=”” background_color=”” line_number_background_color=”” line_number_text_color=”” margin_top=”” margin_right=”” margin_bottom=”” margin_left=””]c3BhcmsgPSBTcGFya1Nlc3Npb24uYnVpbGRlci5hcHBOYW1lKCJ3aW5kb3dfZnVuY19leGFtcGxlIikgXAogICAgICAgICAgICAgICAgICAgICAgICAgICAgLmNvbmZpZygic3BhcmsuZXhlY3V0b3IubWVtb3J5IiwgIjEwZyIpIFwKICAgICAgICAgICAgICAgICAgICAgICAgICAgIC5jb25maWcoInNwYXJrLnlhcm4uZXhlY3V0b3IubWVtb3J5T3ZlcmhlYWQiLCAiMTBnIikgXAogICAgICAgICAgICAgICAgICAgICAgICAgICAgLmNvbmZpZygic3BhcmsuZHluYW1pY0FsbG9jYXRpb24ubWF4RXhlY3V0b3JzIiwgIjUwIikgXAogICAgICAgICAgICAgICAgICAgICAgICAgICAgLmNvbmZpZygic3Bhcmsuc3FsLmF1dG9Ccm9hZGNhc3RKb2luVGhyZXNob2xkIiwgIi0xIikgXAogICAgICAgICAgICAgICAgICAgICAgICAgICAgLmNvbmZpZygic3Bhcmsuc3FsLmJyb2FkY2FzdFRpbWVvdXQiLCAiMzYwMCIpLmdldE9yQ3JlYXRlKCk=[/fusion_syntax_highlighter]

Csv formatındaki datayı okuyoruz.

[fusion_syntax_highlighter theme=”” language=”x-sh” line_numbers=”” line_wrapping=”” copy_to_clipboard=”” copy_to_clipboard_text=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” font_size=”” border_size=”” border_color=”” border_style=”” background_color=”” line_number_background_color=”” line_number_text_color=”” margin_top=”” margin_right=”” margin_bottom=”” margin_left=””]ZGY9c3BhcmsucmVhZC5jc3YoIi90bXAvZGF0YS50eHQiKQ==[/fusion_syntax_highlighter]

Datayı tanıyalım ve inceleyelim;

printSchema() fonksiyonu ile şemayı inceleyebiliriz.

[fusion_syntax_highlighter theme=”” language=”x-sh” line_numbers=”” line_wrapping=”” copy_to_clipboard=”” copy_to_clipboard_text=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” font_size=”” border_size=”” border_color=”” border_style=”” background_color=”” line_number_background_color=”” line_number_text_color=”” margin_top=”” margin_right=”” margin_bottom=”” margin_left=””]ZGYucHJpbnRTY2hlbWEoKQoKcm9vdAogfC0tIGlkOiBzdHJpbmcgKG51bGxhYmxlID0gdHJ1ZSkKIHwtLSBuYW1lOiBzdHJpbmcgKG51bGxhYmxlID0gdHJ1ZSkKIHwtLSBjaXR5OiBzdHJpbmcgKG51bGxhYmxlID0gdHJ1ZSkKIHwtLSBhZ2U6IHN0cmluZyAobnVsbGFibGUgPSB0cnVlKQogfC0tIGdlbmRlcjogc3RyaW5nIChudWxsYWJsZSA9IHRydWUpCiB8LS0gY2FsbGRhdGU6IHN0cmluZyAobnVsbGFibGUgPSB0cnVlKQogfC0tIGNhbGx0aW1lOiBzdHJpbmcgKG51bGxhYmxlID0gdHJ1ZSk=[/fusion_syntax_highlighter]

Genel olarak datayı incelemek için ise show() fonksiyonunu kullanacağız.

[fusion_syntax_highlighter theme=”” language=”x-sh” line_numbers=”” line_wrapping=”” copy_to_clipboard=”” copy_to_clipboard_text=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” font_size=”” border_size=”” border_color=”” border_style=”” background_color=”” line_number_background_color=”” line_number_text_color=”” margin_top=”” margin_right=”” margin_bottom=”” margin_left=””]ZGYuc2hvdygxMCxGYWxzZSkKCistLS0rLS0tLS0rLS0tLS0tLS0rLS0tKy0tLS0tLSstLS0tLS0tLSstLS0tLS0tLSsKfGlkIHxuYW1lIHxjaXR5ICAgIHxhZ2V8Z2VuZGVyfGNhbGxkYXRlfGNhbGx0aW1lfAorLS0tKy0tLS0tKy0tLS0tLS0tKy0tLSstLS0tLS0rLS0tLS0tLS0rLS0tLS0tLS0rCnwxICB8VG9sZ2F8aXN0YW5idWx8MjQgfEUgICAgIHwyMDIxMDExNnwxNSAgICAgIHwKfDIgIHxFY2UgIHxNYW5pc2EgIHwyMSB8SyAgICAgfDIwMjAxMDE0fDIwICAgICAgfAp8NCAgfFRvbGdhfGFua2FyYSAgfDExIHxFICAgICB8MjAyMTA1MDZ8NTAgICAgICB8CnwxICB8VG9sZ2F8aXN0YW5idWx8MjQgfEUgICAgIHwyMDIxMTExMXwzMCAgICAgIHwKfDMgIHxCdXJha3xIYXRheSAgIHw0MSB8RSAgICAgfDIwMjAwOTA4fDE4ICAgICAgfAp8NSAgfE1lcnZlfGl6bWlyICAgfDI3IHxLICAgICB8MjAxOTA1MDZ8MjIgICAgICB8Cnw3ICB8U2FtZXR8b3JkdSAgICB8NjQgfEUgICAgIHwyMDE5MDEwMXw4OSAgICAgIHwKfDkgIHxlbGlmIHxtYXJkaW4gIHw1NCB8SyAgICAgfDIwMjAxMDEwfDM2ICAgICAgfAp8NCAgfFRvbGdhfGFua2FyYSAgfDExIHxFICAgICB8MjAyMTA2MDd8MjIgICAgICB8Cnw2ICB8QmlsYWx8QnVyc2EgICB8MjEgfEUgICAgIHwyMDIxMDkwOXwxMiAgICAgIHwKKy0tLSstLS0tLSstLS0tLS0tLSstLS0rLS0tLS0tKy0tLS0tLS0tKy0tLS0tLS0tKw==[/fusion_syntax_highlighter]

Burada parantez içindeki ilk değer görmek istediğim satır sayısını, ikinci değer ise tüm sütunları görmek istediğimi belirtiyor.

Datamız üzerinde örnekler yapmaya başlayalım;

1. Sıralama Fonksiyonları

1.1. row_number(): 1’den başlayarak her window partitionın sonuna kadar sıralı satır numarası verir.

Bu ve diğer örnekler için oluşturulan  window’da “id” kolonu partition kolonu olarak belirlenmiş ve sıralama “calldate” kolonuna göre yapılmıştır.

[fusion_syntax_highlighter theme=”” language=”x-sh” line_numbers=”” line_wrapping=”” copy_to_clipboard=”” copy_to_clipboard_text=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” font_size=”” border_size=”” border_color=”” border_style=”” background_color=”” line_number_background_color=”” line_number_text_color=”” margin_top=”” margin_right=”” margin_bottom=”” margin_left=””]bXlfdz1XaW5kb3cucGFydGl0aW9uQnkoImlkIikub3JkZXJCeSgiY2FsbGRhdGUiKQpkZjI9ZGYud2l0aENvbHVtbigicm93X251bSIscm93X251bWJlcigpLm92ZXIobXlfdykpLnNob3coMTUsRmFsc2UpCgorLS0tKy0tLS0tKy0tLS0tLS0rLS0tKy0tLS0tLSstLS0tLS0tLSstLS0tLS0tLSstLS0tLS0tKwp8aWQgfG5hbWUgfGNpdHkgICB8YWdlfGdlbmRlcnxjYWxsZGF0ZXxjYWxsdGltZXxyb3dfbnVtfAorLS0tKy0tLS0tKy0tLS0tLS0rLS0tKy0tLS0tLSstLS0tLS0tLSstLS0tLS0tLSstLS0tLS0tKwp8NyAgfFNhbWV0fG9yZHUgICB8NjQgfEUgICAgIHwyMDE4MDIyMHwxOCAgICAgIHwxICAgICAgfAp8NyAgfFNhbWV0fG9yZHUgICB8NjQgfEUgICAgIHwyMDE5MDEwMXw4OSAgICAgIHwyICAgICAgfAp8NyAgfFNhbWV0fG9yZHUgICB8NjQgfEUgICAgIHwyMDE5MDYwNHw4OSAgICAgIHwzICAgICAgfAp8NyAgfFNhbWV0fG9yZHUgICB8NjQgfEUgICAgIHwyMDIwMDcxNnwyNiAgICAgIHw0ICAgICAgfAp8NyAgfFNhbWV0fG9yZHUgICB8NjQgfEUgICAgIHwyMDIwMTAxMXwzNiAgICAgIHw1ICAgICAgfAp8NyAgfFNhbWV0fG9yZHUgICB8NjQgfEUgICAgIHwyMDIwMTAxOHw0MCAgICAgIHw2ICAgICAgfAp8NyAgfFNhbWV0fG9yZHUgICB8NjQgfEUgICAgIHwyMDIwMTAxOHw2NSAgICAgIHw3ICAgICAgfAp8NyAgfFNhbWV0fG9yZHUgICB8NjQgfEUgICAgIHwyMDIxMDYwOHwxMSAgICAgIHw4ICAgICAgfAp8MTEgfGFsaSAgfGFudGFseWF8NTMgfEUgICAgIHwyMDE3MDYwNHwzNSAgICAgIHwxICAgICAgfAp8MTEgfGFsaSAgfGFudGFseWF8NTMgfEUgICAgIHwyMDIwMDUxNnw4NSAgICAgIHwyICAgICAgfAp8MTEgfGFsaSAgfGFudGFseWF8NTMgfEUgICAgIHwyMDIwMDgxNHwxNyAgICAgIHwzICAgICAgfAp8MTEgfGFsaSAgfGFudGFseWF8NTMgfEUgICAgIHwyMDIwMDkxNHw0MSAgICAgIHw0ICAgICAgfAp8MTEgfGFsaSAgfGFudGFseWF8NTMgfEUgICAgIHwyMDIwMTExOHwzNSAgICAgIHw1ICAgICAgfAp8MTEgfGFsaSAgfGFudGFseWF8NTMgfEUgICAgIHwyMDIwMTExOHwyMiAgICAgIHw2ICAgICAgfAp8MTEgfGFsaSAgfGFudGFseWF8NTMgfEUgICAgIHwyMDIxMTExOHwzMyAgICAgIHw3ICAgICAgfAorLS0tKy0tLS0tKy0tLS0tLS0rLS0tKy0tLS0tLSstLS0tLS0tLSstLS0tLS0tLSstLS0tLS0tKw==[/fusion_syntax_highlighter]

Çıktının idlere göre gruplandığını ve her grubun da kendi içinde calldate’e göre sıralandığını görüyoruz. Her grubun row_number’ı da kendi içinde artan şekilde verilmiştir.

1.2.rank(): Bir window partition’ları için elde edilen sonucuçları kendi içlerinde sıralar. Tekrar eden değerler varsa sonrakine geçişte değeri tekrar ettiği kadar arttırır. Daha iyi anlamak için id değeri 7 olan kişiye ait son 3 satırı inceleyebiliriz.

[fusion_syntax_highlighter theme=”” language=”x-sh” line_numbers=”” line_wrapping=”” copy_to_clipboard=”” copy_to_clipboard_text=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” font_size=”” border_size=”” border_color=”” border_style=”” background_color=”” line_number_background_color=”” line_number_text_color=”” margin_top=”” margin_right=”” margin_bottom=”” margin_left=””]ZGYzPWRmLndpdGhDb2x1bW4oInJhbmsiLHJhbmsoKS5vdmVyKG15X3cpKS5zaG93KDE1LEZhbHNlKQoKKy0tLSstLS0tLSstLS0tLS0tKy0tLSstLS0tLS0rLS0tLS0tLS0rLS0tLS0tLS0rLS0tLSsKfGlkIHxuYW1lIHxjaXR5ICAgfGFnZXxnZW5kZXJ8Y2FsbGRhdGV8Y2FsbHRpbWV8cmFua3wKKy0tLSstLS0tLSstLS0tLS0tKy0tLSstLS0tLS0rLS0tLS0tLS0rLS0tLS0tLS0rLS0tLSsKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAxODAyMjB8MTggICAgICB8MSAgIHwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAxOTAxMDF8ODkgICAgICB8MiAgIHwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAxOTA2MDR8ODkgICAgICB8MyAgIHwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAyMDA3MTZ8MjYgICAgICB8NCAgIHwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAyMDEwMTF8MzYgICAgICB8NSAgIHwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAyMDEwMTh8NDAgICAgICB8NiAgIHwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAyMDEwMTh8NjUgICAgICB8NiAgIHwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAyMTA2MDh8MTEgICAgICB8OCAgIHwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAxNzA2MDR8MzUgICAgICB8MSAgIHwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAyMDA1MTZ8ODUgICAgICB8MiAgIHwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAyMDA4MTR8MTcgICAgICB8MyAgIHwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAyMDA5MTR8NDEgICAgICB8NCAgIHwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAyMDExMTh8MzUgICAgICB8NSAgIHwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAyMDExMTh8MjIgICAgICB8NSAgIHwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAyMTExMTh8MzMgICAgICB8NyAgIHwKKy0tLSstLS0tLSstLS0tLS0tKy0tLSstLS0tLS0rLS0tLS0tLS0rLS0tLS0tLS0rLS0tLSs=[/fusion_syntax_highlighter]

1.3.dense_rank(): rank() fonksiyonu ile benzer işleve sahiptir ancak sıralamada tekrarlayan değerler olsa bile boşluk bırakmaz (değer atlamaz).

[fusion_syntax_highlighter theme=”” language=”x-sh” line_numbers=”” line_wrapping=”” copy_to_clipboard=”” copy_to_clipboard_text=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” font_size=”” border_size=”” border_color=”” border_style=”” background_color=”” line_number_background_color=”” line_number_text_color=”” margin_top=”” margin_right=”” margin_bottom=”” margin_left=””]ZGYzPWRmLndpdGhDb2x1bW4oImRlbnNlX3JhbmsiLGRlbnNlX3JhbmsoKS5vdmVyKG15X3cpKS5zaG93KDE1LEZhbHNlKQoKKy0tLSstLS0tLSstLS0tLS0tKy0tLSstLS0tLS0rLS0tLS0tLS0rLS0tLS0tLS0rLS0tLS0tLS0tLSsKfGlkIHxuYW1lIHxjaXR5ICAgfGFnZXxnZW5kZXJ8Y2FsbGRhdGV8Y2FsbHRpbWV8ZGVuc2VfcmFua3wKKy0tLSstLS0tLSstLS0tLS0tKy0tLSstLS0tLS0rLS0tLS0tLS0rLS0tLS0tLS0rLS0tLS0tLS0tLSsKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAxODAyMjB8MTggICAgICB8MSAgICAgICAgIHwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAxOTAxMDF8ODkgICAgICB8MiAgICAgICAgIHwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAxOTA2MDR8ODkgICAgICB8MyAgICAgICAgIHwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAyMDA3MTZ8MjYgICAgICB8NCAgICAgICAgIHwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAyMDEwMTF8MzYgICAgICB8NSAgICAgICAgIHwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAyMDEwMTh8NDAgICAgICB8NiAgICAgICAgIHwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAyMDEwMTh8NjUgICAgICB8NiAgICAgICAgIHwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAyMTA2MDh8MTEgICAgICB8NyAgICAgICAgIHwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAxNzA2MDR8MzUgICAgICB8MSAgICAgICAgIHwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAyMDA1MTZ8ODUgICAgICB8MiAgICAgICAgIHwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAyMDA4MTR8MTcgICAgICB8MyAgICAgICAgIHwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAyMDA5MTR8NDEgICAgICB8NCAgICAgICAgIHwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAyMDExMTh8MzUgICAgICB8NSAgICAgICAgIHwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAyMDExMTh8MjIgICAgICB8NSAgICAgICAgIHwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAyMTExMTh8MzMgICAgICB8NiAgICAgICAgIHwgICAgICAgIHwKKy0tLSstLS0tLSstLS0tLS0tKy0tLSstLS0tLS0rLS0tLS0tLS0rLS0tLS0tLS0rLS0tLS0tLS0tLSs=[/fusion_syntax_highlighter]

1.4.percent_rank(): Temelde rank fonksiyonu ile benzer çalışır.Ancak belli farklılıklar vardır. Her window partition içindeki ilk değer 0 son değer ise 1 değerini alır. Aradakiler içinse 0-1 aralığı eleman sayısına bölünür ve sonraki satıra geçtikçe 0’dan başyarak çıkan değer kadar arttırılır.

[fusion_syntax_highlighter theme=”” language=”x-sh” line_numbers=”” line_wrapping=”” copy_to_clipboard=”” copy_to_clipboard_text=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” font_size=”” border_size=”” border_color=”” border_style=”” background_color=”” line_number_background_color=”” line_number_text_color=”” margin_top=”” margin_right=”” margin_bottom=”” margin_left=””]ZGY0PWRmLndpdGhDb2x1bW4oInBlcmNlbnRfcmFuayIscGVyY2VudF9yYW5rKCkub3ZlcihteV93KSkuc2hvdygxNSxGYWxzZSkKCistLS0rLS0tLS0rLS0tLS0tLSstLS0rLS0tLS0tKy0tLS0tLS0tKy0tLS0tLS0tKy0tLS0tLS0tLS0tLS0tLS0tLS0rCnxpZCB8bmFtZSB8Y2l0eSAgIHxhZ2V8Z2VuZGVyfGNhbGxkYXRlfGNhbGx0aW1lfHBlcmNlbnRfcmFuayAgICAgICB8CistLS0rLS0tLS0rLS0tLS0tLSstLS0rLS0tLS0tKy0tLS0tLS0tKy0tLS0tLS0tKy0tLS0tLS0tLS0tLS0tLS0tLS0rCnw3ICB8U2FtZXR8b3JkdSAgIHw2NCB8RSAgICAgfDIwMTgwMjIwfDE4ICAgICAgfDAuMCAgICAgICAgICAgICAgICB8Cnw3ICB8U2FtZXR8b3JkdSAgIHw2NCB8RSAgICAgfDIwMTkwMTAxfDg5ICAgICAgfDAuMTQyODU3MTQyODU3MTQyODV8Cnw3ICB8U2FtZXR8b3JkdSAgIHw2NCB8RSAgICAgfDIwMTkwNjA0fDg5ICAgICAgfDAuMjg1NzE0Mjg1NzE0Mjg1NyB8Cnw3ICB8U2FtZXR8b3JkdSAgIHw2NCB8RSAgICAgfDIwMjAwNzE2fDI2ICAgICAgfDAuNDI4NTcxNDI4NTcxNDI4NTV8Cnw3ICB8U2FtZXR8b3JkdSAgIHw2NCB8RSAgICAgfDIwMjAxMDExfDM2ICAgICAgfDAuNTcxNDI4NTcxNDI4NTcxNCB8Cnw3ICB8U2FtZXR8b3JkdSAgIHw2NCB8RSAgICAgfDIwMjAxMDE4fDQwICAgICAgfDAuNzE0Mjg1NzE0Mjg1NzE0MyB8Cnw3ICB8U2FtZXR8b3JkdSAgIHw2NCB8RSAgICAgfDIwMjAxMDE4fDY1ICAgICAgfDAuNzE0Mjg1NzE0Mjg1NzE0MyB8Cnw3ICB8U2FtZXR8b3JkdSAgIHw2NCB8RSAgICAgfDIwMjEwNjA4fDExICAgICAgfDEuMCAgICAgICAgICAgICAgICB8CnwxMSB8YWxpICB8YW50YWx5YXw1MyB8RSAgICAgfDIwMTcwNjA0fDM1ICAgICAgfDAuMCAgICAgICAgICAgICAgICB8CnwxMSB8YWxpICB8YW50YWx5YXw1MyB8RSAgICAgfDIwMjAwNTE2fDg1ICAgICAgfDAuMTY2NjY2NjY2NjY2NjY2NjZ8CnwxMSB8YWxpICB8YW50YWx5YXw1MyB8RSAgICAgfDIwMjAwODE0fDE3ICAgICAgfDAuMzMzMzMzMzMzMzMzMzMzMyB8CnwxMSB8YWxpICB8YW50YWx5YXw1MyB8RSAgICAgfDIwMjAwOTE0fDQxICAgICAgfDAuNSAgICAgICAgICAgICAgICB8CnwxMSB8YWxpICB8YW50YWx5YXw1MyB8RSAgICAgfDIwMjAxMTE4fDM1ICAgICAgfDAuNjY2NjY2NjY2NjY2NjY2NiB8CnwxMSB8YWxpICB8YW50YWx5YXw1MyB8RSAgICAgfDIwMjAxMTE4fDIyICAgICAgfDAuNjY2NjY2NjY2NjY2NjY2NiB8CnwxMSB8YWxpICB8YW50YWx5YXw1MyB8RSAgICAgfDIwMjExMTE4fDMzICAgICAgfDEuMCAgICAgICAgICAgICAgICB8CistLS0rLS0tLS0rLS0tLS0tLSstLS0rLS0tLS0tKy0tLS0tLS0tKy0tLS0tLS0tKy0tLS0tLS0tLS0tLS0tLS0tLS0r[/fusion_syntax_highlighter]

Şu ana kadar incelediğimiz fonksiyonların işlevleri daha kolay ayırt edebilmek için aşağıdaki tabloya bakabiliriz;

[fusion_syntax_highlighter theme=”” language=”x-sh” line_numbers=”” line_wrapping=”” copy_to_clipboard=”” copy_to_clipboard_text=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” font_size=”” border_size=”” border_color=”” border_style=”” background_color=”” line_number_background_color=”” line_number_text_color=”” margin_top=”” margin_right=”” margin_bottom=”” margin_left=””]ZGYud2l0aENvbHVtbigicm93X251bSIscm93X251bWJlcigpLm92ZXIobXlfdykpXAogIC53aXRoQ29sdW1uKCJyYW5rIixyYW5rKCkub3ZlcihteV93KSlcCiAgLndpdGhDb2x1bW4oImRlbnNlX3JhbmsiLGRlbnNlX3JhbmsoKS5vdmVyKG15X3cpKVwKICAud2l0aENvbHVtbigicGVyY2VudF9yYW5rIixwZXJjZW50X3JhbmsoKS5vdmVyKG15X3cpKS5zaG93KDIwLEZhbHNlKQoKKy0tLSstLS0tLSstLS0tLS0tKy0tLSstLS0tLS0rLS0tLS0tLS0rLS0tLS0tLS0rLS0tLS0tLSstLS0tKy0tLS0tLS0tLS0rLS0tLS0tLS0tLS0tLS0tLS0tLSsKfGlkIHxuYW1lIHxjaXR5ICAgfGFnZXxnZW5kZXJ8Y2FsbGRhdGV8Y2FsbHRpbWV8cm93X251bXxyYW5rfGRlbnNlX3Jhbmt8cGVyY2VudF9yYW5rICAgICAgIHwKKy0tLSstLS0tLSstLS0tLS0tKy0tLSstLS0tLS0rLS0tLS0tLS0rLS0tLS0tLS0rLS0tLS0tLSstLS0tKy0tLS0tLS0tLS0rLS0tLS0tLS0tLS0tLS0tLS0tLSsKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAxODAyMjB8MTggICAgICB8MSAgICAgIHwxICAgfDEgICAgICAgICB8MC4wICAgICAgICAgICAgICAgIHwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAxOTAxMDF8ODkgICAgICB8MiAgICAgIHwyICAgfDIgICAgICAgICB8MC4xNDI4NTcxNDI4NTcxNDI4NXwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAxOTA2MDR8ODkgICAgICB8MyAgICAgIHwzICAgfDMgICAgICAgICB8MC4yODU3MTQyODU3MTQyODU3IHwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAyMDA3MTZ8MjYgICAgICB8NCAgICAgIHw0ICAgfDQgICAgICAgICB8MC40Mjg1NzE0Mjg1NzE0Mjg1NXwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAyMDEwMTF8MzYgICAgICB8NSAgICAgIHw1ICAgfDUgICAgICAgICB8MC41NzE0Mjg1NzE0Mjg1NzE0IHwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAyMDEwMTh8NDAgICAgICB8NiAgICAgIHw2ICAgfDYgICAgICAgICB8MC43MTQyODU3MTQyODU3MTQzIHwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAyMDEwMTh8NjUgICAgICB8NyAgICAgIHw2ICAgfDYgICAgICAgICB8MC43MTQyODU3MTQyODU3MTQzIHwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAyMTA2MDh8MTEgICAgICB8OCAgICAgIHw4ICAgfDcgICAgICAgICB8MS4wICAgICAgICAgICAgICAgIHwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAxNzA2MDR8MzUgICAgICB8MSAgICAgIHwxICAgfDEgICAgICAgICB8MC4wICAgICAgICAgICAgICAgIHwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAyMDA1MTZ8ODUgICAgICB8MiAgICAgIHwyICAgfDIgICAgICAgICB8MC4xNjY2NjY2NjY2NjY2NjY2NnwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAyMDA4MTR8MTcgICAgICB8MyAgICAgIHwzICAgfDMgICAgICAgICB8MC4zMzMzMzMzMzMzMzMzMzMzIHwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAyMDA5MTR8NDEgICAgICB8NCAgICAgIHw0ICAgfDQgICAgICAgICB8MC41ICAgICAgICAgICAgICAgIHwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAyMDExMTh8MzUgICAgICB8NSAgICAgIHw1ICAgfDUgICAgICAgICB8MC42NjY2NjY2NjY2NjY2NjY2IHwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAyMDExMTh8MjIgICAgICB8NiAgICAgIHw1ICAgfDUgICAgICAgICB8MC42NjY2NjY2NjY2NjY2NjY2IHwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAyMTExMTh8MzMgICAgICB8NyAgICAgIHw3ICAgfDYgICAgICAgICB8MS4wICAgICAgICAgICAgICAgIHwKfDMgIHxCdXJha3xIYXRheSAgfDQxIHxFICAgICB8MjAxNTA2MTd8MzYgICAgICB8MSAgICAgIHwxICAgfDEgICAgICAgICB8MC4wICAgICAgICAgICAgICAgIHwKfDMgIHxCdXJha3xIYXRheSAgfDQxIHxFICAgICB8MjAxODAxMzB8MTIgICAgICB8MiAgICAgIHwyICAgfDIgICAgICAgICB8MC4xNjY2NjY2NjY2NjY2NjY2NnwKfDMgIHxCdXJha3xIYXRheSAgfDQxIHxFICAgICB8MjAyMDAzMzB8MTMgICAgICB8MyAgICAgIHwzICAgfDMgICAgICAgICB8MC4zMzMzMzMzMzMzMzMzMzMzIHwKfDMgIHxCdXJha3xIYXRheSAgfDQxIHxFICAgICB8MjAyMDA2MDh8NTYgICAgICB8NCAgICAgIHw0ICAgfDQgICAgICAgICB8MC41ICAgICAgICAgICAgICAgIHwKfDMgIHxCdXJha3xIYXRheSAgfDQxIHxFICAgICB8MjAyMDA5MDh8MTggICAgICB8NSAgICAgIHw1ICAgfDUgICAgICAgICB8MC42NjY2NjY2NjY2NjY2NjY2IHwKKy0tLSstLS0tLSstLS0tLS0tKy0tLSstLS0tLS0rLS0tLS0tLS0rLS0tLS0tLS0rLS0tLS0tLSstLS0tKy0tLS0tLS0tLS0rLS0tLS0tLS0tLS0tLS0tLS0tLSs=[/fusion_syntax_highlighter]

1.5.ntile(): Bir window partition içindeki sonuç satırlarının göreli sırasını döndürür. İlk örnekte ntile fonksiyonuna parametre olarak 2’yi verdik yani sonuçlar 1 ya da 2 değerini alacak şekilde gelecek. İkinci örnekte ise prametre olarak 3’ü verdik bu durumda da 1-2-3 değerleri alınabilir.

[fusion_syntax_highlighter theme=”” language=”x-sh” line_numbers=”” line_wrapping=”” copy_to_clipboard=”” copy_to_clipboard_text=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” font_size=”” border_size=”” border_color=”” border_style=”” background_color=”” line_number_background_color=”” line_number_text_color=”” margin_top=”” margin_right=”” margin_bottom=”” margin_left=””]ZGYud2l0aENvbHVtbigibnRpbGUiLG50aWxlKDIpLm92ZXIobXlfdykpLnNob3coMTUsRmFsc2UpCgorLS0tKy0tLS0tKy0tLS0tLS0rLS0tKy0tLS0tLSstLS0tLS0tLSstLS0tLS0tLSstLS0tLSsKfGlkIHxuYW1lIHxjaXR5ICAgfGFnZXxnZW5kZXJ8Y2FsbGRhdGV8Y2FsbHRpbWV8bnRpbGV8CistLS0rLS0tLS0rLS0tLS0tLSstLS0rLS0tLS0tKy0tLS0tLS0tKy0tLS0tLS0tKy0tLS0tKwp8NyAgfFNhbWV0fG9yZHUgICB8NjQgfEUgICAgIHwyMDE4MDIyMHwxOCAgICAgIHwxICAgIHwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAxOTAxMDF8ODkgICAgICB8MSAgICB8Cnw3ICB8U2FtZXR8b3JkdSAgIHw2NCB8RSAgICAgfDIwMTkwNjA0fDg5ICAgICAgfDEgICAgfAp8NyAgfFNhbWV0fG9yZHUgICB8NjQgfEUgICAgIHwyMDIwMDcxNnwyNiAgICAgIHwxICAgIHwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAyMDEwMTF8MzYgICAgICB8MiAgICB8Cnw3ICB8U2FtZXR8b3JkdSAgIHw2NCB8RSAgICAgfDIwMjAxMDE4fDQwICAgICAgfDIgICAgfAp8NyAgfFNhbWV0fG9yZHUgICB8NjQgfEUgICAgIHwyMDIwMTAxOHw2NSAgICAgIHwyICAgIHwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAyMTA2MDh8MTEgICAgICB8MiAgICB8CnwxMSB8YWxpICB8YW50YWx5YXw1MyB8RSAgICAgfDIwMTcwNjA0fDM1ICAgICAgfDEgICAgfAp8MTEgfGFsaSAgfGFudGFseWF8NTMgfEUgICAgIHwyMDIwMDUxNnw4NSAgICAgIHwxICAgIHwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAyMDA4MTR8MTcgICAgICB8MSAgICB8CnwxMSB8YWxpICB8YW50YWx5YXw1MyB8RSAgICAgfDIwMjAwOTE0fDQxICAgICAgfDEgICAgfAp8MTEgfGFsaSAgfGFudGFseWF8NTMgfEUgICAgIHwyMDIwMTExOHwzNSAgICAgIHwyICAgIHwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAyMDExMTh8MjIgICAgICB8MiAgICB8CnwxMSB8YWxpICB8YW50YWx5YXw1MyB8RSAgICAgfDIwMjExMTE4fDMzICAgICAgfDIgICAgfAorLS0tKy0tLS0tKy0tLS0tLS0rLS0tKy0tLS0tLSstLS0tLS0tLSstLS0tLS0tLSstLS0tLSs=[/fusion_syntax_highlighter]

[fusion_syntax_highlighter theme=”” language=”x-sh” line_numbers=”” line_wrapping=”” copy_to_clipboard=”” copy_to_clipboard_text=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” font_size=”” border_size=”” border_color=”” border_style=”” background_color=”” line_number_background_color=”” line_number_text_color=”” margin_top=”” margin_right=”” margin_bottom=”” margin_left=””]ZGYud2l0aENvbHVtbigibnRpbGUiLG50aWxlKDMpLm92ZXIobXlfdykpLnNob3coMTUsRmFsc2UpCgorLS0tKy0tLS0tKy0tLS0tLS0rLS0tKy0tLS0tLSstLS0tLS0tLSstLS0tLS0tLSstLS0tLSsKfGlkIHxuYW1lIHxjaXR5ICAgfGFnZXxnZW5kZXJ8Y2FsbGRhdGV8Y2FsbHRpbWV8bnRpbGV8CistLS0rLS0tLS0rLS0tLS0tLSstLS0rLS0tLS0tKy0tLS0tLS0tKy0tLS0tLS0tKy0tLS0tKwp8NyAgfFNhbWV0fG9yZHUgICB8NjQgfEUgICAgIHwyMDE4MDIyMHwxOCAgICAgIHwxICAgIHwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAxOTAxMDF8ODkgICAgICB8MSAgICB8Cnw3ICB8U2FtZXR8b3JkdSAgIHw2NCB8RSAgICAgfDIwMTkwNjA0fDg5ICAgICAgfDEgICAgfAp8NyAgfFNhbWV0fG9yZHUgICB8NjQgfEUgICAgIHwyMDIwMDcxNnwyNiAgICAgIHwyICAgIHwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAyMDEwMTF8MzYgICAgICB8MiAgICB8Cnw3ICB8U2FtZXR8b3JkdSAgIHw2NCB8RSAgICAgfDIwMjAxMDE4fDQwICAgICAgfDIgICAgfAp8NyAgfFNhbWV0fG9yZHUgICB8NjQgfEUgICAgIHwyMDIwMTAxOHw2NSAgICAgIHwzICAgIHwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAyMTA2MDh8MTEgICAgICB8MyAgICB8CnwxMSB8YWxpICB8YW50YWx5YXw1MyB8RSAgICAgfDIwMTcwNjA0fDM1ICAgICAgfDEgICAgfAp8MTEgfGFsaSAgfGFudGFseWF8NTMgfEUgICAgIHwyMDIwMDUxNnw4NSAgICAgIHwxICAgIHwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAyMDA4MTR8MTcgICAgICB8MSAgICB8CnwxMSB8YWxpICB8YW50YWx5YXw1MyB8RSAgICAgfDIwMjAwOTE0fDQxICAgICAgfDIgICAgfAp8MTEgfGFsaSAgfGFudGFseWF8NTMgfEUgICAgIHwyMDIwMTExOHwzNSAgICAgIHwyICAgIHwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAyMDExMTh8MjIgICAgICB8MyAgICB8CnwxMSB8YWxpICB8YW50YWx5YXw1MyB8RSAgICAgfDIwMjExMTE4fDMzICAgICAgfDMgICAgfAorLS0tKy0tLS0tKy0tLS0tLS0rLS0tKy0tLS0tLSstLS0tLS0tLSstLS0tLS0tLSstLS0tLSs=[/fusion_syntax_highlighter]

2. Analitik Fonksiyonlar

2.1.cume_dist(): Bir window partition içindeki değerlerin kümülatif dağılımını eldek etmek için kullanılır.

[fusion_syntax_highlighter theme=”” language=”x-sh” line_numbers=”” line_wrapping=”” copy_to_clipboard=”” copy_to_clipboard_text=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” font_size=”” border_size=”” border_color=”” border_style=”” background_color=”” line_number_background_color=”” line_number_text_color=”” margin_top=”” margin_right=”” margin_bottom=”” margin_left=””]ZGYud2l0aENvbHVtbigiY3VtZV9kaXN0IixjdW1lX2Rpc3QoKS5vdmVyKG15X3cpKS5zaG93KDE1LEZhbHNlKQoKKy0tLSstLS0tLSstLS0tLS0tKy0tLSstLS0tLS0rLS0tLS0tLS0rLS0tLS0tLS0rLS0tLS0tLS0tLS0tLS0tLS0tLSsKfGlkIHxuYW1lIHxjaXR5ICAgfGFnZXxnZW5kZXJ8Y2FsbGRhdGV8Y2FsbHRpbWV8Y3VtZV9kaXN0ICAgICAgICAgIHwKKy0tLSstLS0tLSstLS0tLS0tKy0tLSstLS0tLS0rLS0tLS0tLS0rLS0tLS0tLS0rLS0tLS0tLS0tLS0tLS0tLS0tLSsKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAxODAyMjB8MTggICAgICB8MC4xMjUgICAgICAgICAgICAgIHwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAxOTAxMDF8ODkgICAgICB8MC4yNSAgICAgICAgICAgICAgIHwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAxOTA2MDR8ODkgICAgICB8MC4zNzUgICAgICAgICAgICAgIHwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAyMDA3MTZ8MjYgICAgICB8MC41ICAgICAgICAgICAgICAgIHwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAyMDEwMTF8MzYgICAgICB8MC42MjUgICAgICAgICAgICAgIHwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAyMDEwMTh8NDAgICAgICB8MC44NzUgICAgICAgICAgICAgIHwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAyMDEwMTh8NjUgICAgICB8MC44NzUgICAgICAgICAgICAgIHwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAyMTA2MDh8MTEgICAgICB8MS4wICAgICAgICAgICAgICAgIHwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAxNzA2MDR8MzUgICAgICB8MC4xNDI4NTcxNDI4NTcxNDI4NXwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAyMDA1MTZ8ODUgICAgICB8MC4yODU3MTQyODU3MTQyODU3IHwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAyMDA4MTR8MTcgICAgICB8MC40Mjg1NzE0Mjg1NzE0Mjg1NXwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAyMDA5MTR8NDEgICAgICB8MC41NzE0Mjg1NzE0Mjg1NzE0IHwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAyMDExMTh8MzUgICAgICB8MC44NTcxNDI4NTcxNDI4NTcxIHwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAyMDExMTh8MjIgICAgICB8MC44NTcxNDI4NTcxNDI4NTcxIHwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAyMTExMTh8MzMgICAgICB8MS4wICAgICAgICAgICAgICAgIHwKKy0tLSstLS0tLSstLS0tLS0tKy0tLSstLS0tLS0rLS0tLS0tLS0rLS0tLS0tLS0rLS0tLS0tLS0tLS0tLS0tLS0tLSs=[/fusion_syntax_highlighter]

2.2.lead(): Fonksiyonun aldığı parametredeki offset değerine göre sonraki satır verisini getiren fonksiyondur. Sql’deki lead fonksiyonu ile aynı işlevdedir. Aşağıdaki örnekte her kişinin sonraki arama tarihinin yeni kolona yazdırıldığını görebiliriz.

[fusion_syntax_highlighter theme=”” language=”x-sh” line_numbers=”” line_wrapping=”” copy_to_clipboard=”” copy_to_clipboard_text=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” font_size=”” border_size=”” border_color=”” border_style=”” background_color=”” line_number_background_color=”” line_number_text_color=”” margin_top=”” margin_right=”” margin_bottom=”” margin_left=””]ZGYud2l0aENvbHVtbigic29ucmFraSIsIGZ1bmMubGVhZChjb2woImNhbGxkYXRlIikpLm92ZXIobXlfdykpLnNob3coMTUsRmFsc2UpCistLS0rLS0tLS0rLS0tLS0tLSstLS0rLS0tLS0tKy0tLS0tLS0tKy0tLS0tLS0tKy0tLS0tLS0tKwp8aWQgfG5hbWUgfGNpdHkgICB8YWdlfGdlbmRlcnxjYWxsZGF0ZXxjYWxsdGltZXxzb25yYWtpIHwKKy0tLSstLS0tLSstLS0tLS0tKy0tLSstLS0tLS0rLS0tLS0tLS0rLS0tLS0tLS0rLS0tLS0tLS0rCnw3ICB8U2FtZXR8b3JkdSAgIHw2NCB8RSAgICAgfDIwMTgwMjIwfDE4ICAgICAgfDIwMTkwMTAxfAp8NyAgfFNhbWV0fG9yZHUgICB8NjQgfEUgICAgIHwyMDE5MDEwMXw4OSAgICAgIHwyMDE5MDYwNHwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAxOTA2MDR8ODkgICAgICB8MjAyMDA3MTZ8Cnw3ICB8U2FtZXR8b3JkdSAgIHw2NCB8RSAgICAgfDIwMjAwNzE2fDI2ICAgICAgfDIwMjAxMDExfAp8NyAgfFNhbWV0fG9yZHUgICB8NjQgfEUgICAgIHwyMDIwMTAxMXwzNiAgICAgIHwyMDIwMTAxOHwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAyMDEwMTh8NDAgICAgICB8MjAyMDEwMTh8Cnw3ICB8U2FtZXR8b3JkdSAgIHw2NCB8RSAgICAgfDIwMjAxMDE4fDY1ICAgICAgfDIwMjEwNjA4fAp8NyAgfFNhbWV0fG9yZHUgICB8NjQgfEUgICAgIHwyMDIxMDYwOHwxMSAgICAgIHxudWxsICAgIHwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAxNzA2MDR8MzUgICAgICB8MjAyMDA1MTZ8CnwxMSB8YWxpICB8YW50YWx5YXw1MyB8RSAgICAgfDIwMjAwNTE2fDg1ICAgICAgfDIwMjAwODE0fAp8MTEgfGFsaSAgfGFudGFseWF8NTMgfEUgICAgIHwyMDIwMDgxNHwxNyAgICAgIHwyMDIwMDkxNHwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAyMDA5MTR8NDEgICAgICB8MjAyMDExMTh8CnwxMSB8YWxpICB8YW50YWx5YXw1MyB8RSAgICAgfDIwMjAxMTE4fDM1ICAgICAgfDIwMjAxMTE4fAp8MTEgfGFsaSAgfGFudGFseWF8NTMgfEUgICAgIHwyMDIwMTExOHwyMiAgICAgIHwyMDIxMTExOHwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAyMTExMTh8MzMgICAgICB8bnVsbCAgICB8CistLS0rLS0tLS0rLS0tLS0tLSstLS0rLS0tLS0tKy0tLS0tLS0tKy0tLS0tLS0tKy0tLS0tLS0tKw==[/fusion_syntax_highlighter]

2.3.lag(): Fonksiyonun aldığı parametredeki offset değerine göre önceki satır verisini getiren fonksiyondur. Sql’deki lag fonksiyonu ile aynı işlevdedir. Aşağıdaki örnekte her kişinin önceki arama tarihinin yeni kolona yazdırıldığını görebiliriz.

[fusion_syntax_highlighter theme=”” language=”x-sh” line_numbers=”” line_wrapping=”” copy_to_clipboard=”” copy_to_clipboard_text=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” font_size=”” border_size=”” border_color=”” border_style=”” background_color=”” line_number_background_color=”” line_number_text_color=”” margin_top=”” margin_right=”” margin_bottom=”” margin_left=””]ZGYud2l0aENvbHVtbigiw7ZuY2VraSIsIGZ1bmMubGFnKGNvbCgiY2FsbGRhdGUiKSkub3ZlcihteV93KSkuc2hvdygxNSxGYWxzZSkKKy0tLSstLS0tLSstLS0tLS0tKy0tLSstLS0tLS0rLS0tLS0tLS0rLS0tLS0tLS0rLS0tLS0tLS0rCnxpZCB8bmFtZSB8Y2l0eSAgIHxhZ2V8Z2VuZGVyfGNhbGxkYXRlfGNhbGx0aW1lfMO2bmNla2kgIHwKKy0tLSstLS0tLSstLS0tLS0tKy0tLSstLS0tLS0rLS0tLS0tLS0rLS0tLS0tLS0rLS0tLS0tLS0rCnw3ICB8U2FtZXR8b3JkdSAgIHw2NCB8RSAgICAgfDIwMTgwMjIwfDE4ICAgICAgfG51bGwgICAgfAp8NyAgfFNhbWV0fG9yZHUgICB8NjQgfEUgICAgIHwyMDE5MDEwMXw4OSAgICAgIHwyMDE4MDIyMHwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAxOTA2MDR8ODkgICAgICB8MjAxOTAxMDF8Cnw3ICB8U2FtZXR8b3JkdSAgIHw2NCB8RSAgICAgfDIwMjAwNzE2fDI2ICAgICAgfDIwMTkwNjA0fAp8NyAgfFNhbWV0fG9yZHUgICB8NjQgfEUgICAgIHwyMDIwMTAxMXwzNiAgICAgIHwyMDIwMDcxNnwKfDcgIHxTYW1ldHxvcmR1ICAgfDY0IHxFICAgICB8MjAyMDEwMTh8NDAgICAgICB8MjAyMDEwMTF8Cnw3ICB8U2FtZXR8b3JkdSAgIHw2NCB8RSAgICAgfDIwMjAxMDE4fDY1ICAgICAgfDIwMjAxMDE4fAp8NyAgfFNhbWV0fG9yZHUgICB8NjQgfEUgICAgIHwyMDIxMDYwOHwxMSAgICAgIHwyMDIwMTAxOHwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAxNzA2MDR8MzUgICAgICB8bnVsbCAgICB8CnwxMSB8YWxpICB8YW50YWx5YXw1MyB8RSAgICAgfDIwMjAwNTE2fDg1ICAgICAgfDIwMTcwNjA0fAp8MTEgfGFsaSAgfGFudGFseWF8NTMgfEUgICAgIHwyMDIwMDgxNHwxNyAgICAgIHwyMDIwMDUxNnwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAyMDA5MTR8NDEgICAgICB8MjAyMDA4MTR8CnwxMSB8YWxpICB8YW50YWx5YXw1MyB8RSAgICAgfDIwMjAxMTE4fDM1ICAgICAgfDIwMjAwOTE0fAp8MTEgfGFsaSAgfGFudGFseWF8NTMgfEUgICAgIHwyMDIwMTExOHwyMiAgICAgIHwyMDIwMTExOHwKfDExIHxhbGkgIHxhbnRhbHlhfDUzIHxFICAgICB8MjAyMTExMTh8MzMgICAgICB8MjAyMDExMTh8CistLS0rLS0tLS0rLS0tLS0tLSstLS0rLS0tLS0tKy0tLS0tLS0tKy0tLS0tLS0tKy0tLS0tLS0tKw==[/fusion_syntax_highlighter]

3. Toplama (Aggregate) Fonksiyonları

Her window partition için sum, avg, max, min değerlerini hesaplayabiliriz. Bunun için aşağıdaki örneği inceleyelim.

[fusion_syntax_highlighter theme=”” language=”x-sh” line_numbers=”” line_wrapping=”” copy_to_clipboard=”” copy_to_clipboard_text=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” font_size=”” border_size=”” border_color=”” border_style=”” background_color=”” line_number_background_color=”” line_number_text_color=”” margin_top=”” margin_right=”” margin_bottom=”” margin_left=””]ZnJvbSBweXNwYXJrLnNxbC5mdW5jdGlvbnMgaW1wb3J0IGNvbCxhdmcsc3VtLG1pbixtYXgscm93X251bWJlciAKZGYud2l0aENvbHVtbigicm93Iixyb3dfbnVtYmVyKCkub3ZlcihteV93KSkgXAogIC53aXRoQ29sdW1uKCJhdmciLCBhdmcoY29sKCJjYWxsdGltZSIpKS5vdmVyKG15X3cyKSkgXAogIC53aXRoQ29sdW1uKCJzdW0iLCBzdW0oY29sKCJjYWxsdGltZSIpKS5vdmVyKG15X3cyKSkgXAogIC53aXRoQ29sdW1uKCJtaW4iLCBtaW4oY29sKCJjYWxsdGltZSIpKS5vdmVyKG15X3cyKSkgXAogIC53aXRoQ29sdW1uKCJtYXgiLCBtYXgoY29sKCJjYWxsdGltZSIpKS5vdmVyKG15X3cyKSkgXAogIC53aGVyZShjb2woInJvdyIpPT0xKS5zZWxlY3QoImlkIiwibmFtZSIsImF2ZyIsInN1bSIsIm1pbiIsIm1heCIpLnNob3coMTUsRmFsc2UpCistLS0rLS0tLS0tKy0tLS0tLS0tLS0tLS0tLS0tLSstLS0tLSstLS0rLS0tKwp8aWQgfG5hbWUgIHxhdmcgICAgICAgICAgICAgICB8c3VtICB8bWlufG1heHwKKy0tLSstLS0tLS0rLS0tLS0tLS0tLS0tLS0tLS0tKy0tLS0tKy0tLSstLS0rCnw3ICB8U2FtZXQgfDQ2Ljc1ICAgICAgICAgICAgIHwzNzQuMHwxMSB8ODkgfAp8MTEgfGFsaSAgIHwzOC4yODU3MTQyODU3MTQyODV8MjY4LjB8MTcgfDg1IHwKfDMgIHxCdXJhayB8MzEuMTQyODU3MTQyODU3MTQyfDIxOC4wfDEyIHw2NiB8Cnw4ICB8QnVya2F5fDQ3Ljg3NSAgICAgICAgICAgIHwzODMuMHwxNCB8NzUgfAp8NSAgfE1lcnZlIHwzMy42MzYzNjM2MzYzNjM2MyB8MzcwLjB8MTMgfDc3IHwKfDYgIHxCaWxhbCB8NDAuMDkwOTA5MDkwOTA5MDkgfDQ0MS4wfDEyIHw3MCB8Cnw5ICB8ZWxpZiAgfDI2LjUgICAgICAgICAgICAgIHwxMDYuMHwxMiB8NDAgfAp8MSAgfFRvbGdhIHwzNi43Nzc3Nzc3Nzc3Nzc3OCB8MzMxLjB8MTEgfDk4IHwKfDQgIHxUb2xnYSB8MjkuNSAgICAgICAgICAgICAgfDM1NC4wfDEwIHw1NSB8CnwxNCB8YnVzZSAgfDM4Ljc3Nzc3Nzc3Nzc3Nzc4IHwzNDkuMHwxMCB8OTkgfAp8MiAgfEVjZSAgIHw0MC44ICAgICAgICAgICAgICB8NjEyLjB8MTUgfDc3IHwKKy0tLSstLS0tLS0rLS0tLS0tLS0tLS0tLS0tLS0tKy0tLS0tKy0tLSstLS0r[/fusion_syntax_highlighter]

id değeri 7 olan kaydı ele alırsak ortalama konuşma süresinin 46.75 s, toplam konuşma süresinin 374 s, min konuşma süresinin 11 s ve max konuşma süresinin 89 s olduğunu gözlemleyebiliriz.