Salam Dostlar.

Bugünkü məqalədə çox maraqlı bir mövzunu izah etməyə çalışacağam. Bu gün Pəncərə funksiyaları (Window functions) barədə danışacağıq. Pəncərə funksiyalarının mənimsənməsinə müəyyən vaxt tələb olunur, lakin bir çox hallarda pəncərə funksiyaları adi üsullardan daha aydın və sadədir. Buna görə də yazılı şəkildə bu mövzuya daha ətraflı izah verməyə qərarına gəldim.

Verilənlərin analizi funksiyası cədvəl sətirlərinin çoxluğu ilə işləyir və tək bir qiymət qaytarır. Biz  cədvəlin bir necə sətrini seçirik və onlar üzərində analitik və ya statistik əməliyyatlar aparırıq.

Analitik əməliyyatlar nəyə lazımdır? Analitik əməliyyatlar əsasən artan nəticələri və sürüşən ortaları hesablamağa, həmçinin çoxlu sayda başqa hesablamaları yerinə yetirməyə imkan verir. Məsələn, statistik funksiyalar vasitəsilə biz belə suallara cavab tapa bilərik:

Şirkətdə çalışan bütün işçilərin sayını tapın.

Elvin müəllim neçə dərs keçir?

Dərsin orta müddəti nə qədərdir?

İlk dərs nə vaxt başlayır?

14 saylı sifariş üzrə cəmi məbləğı tapın.

Ən bahalı və ən ucuz mallar hansılardır?

Belə suallara cavab tapmaq üçün cədvəlin bir neçə sətirləri üzərində hesablamalar aparmalıyıq. Belə hesablamalar aparmaq üçün SQL standartında statistik  (ing. “aggregate”), reytinq (ranking) və analitik funksiyalar mövcuddur.

Verilənlərin analizi funksiyalarının iki tipi var:

1. Qrup funksiyaları
2. Pəncərə (window) funksiyaları.

Bu iki tip funksiyalar üzərində funksiyanın tətbiq ediləcək sətirlərin çoxluğunun (qruplarının) necə təyin etməsiylə fərqlənir. Biz qruplaşdırma sorğusu vasitəsilə cədvəldəki qrupları təyin edə bilərik və sonra da qrup funksiyasını hər bir qrup üçün tətbiq edə bilərik. GROUP BY sorğunun sonunda tətbiq edildiyi üçün biz tam sorğu ilə işləməliyik. Və ya biz pəncərə sorğusundan istifadə edərək cədvəldə pəncərələr təyin edə bilərik, sonra da pəncərə funksiyasını hər bir pəncərə üçün tətbiq edə bilərik. Bu zaman tam sorğu ilə deyil müəyyən etdiyimiz pəncərələrlə işləyirik.

Pəncərə (Window) funksiyaları cədvəl sətirlərinin çoxluğuna (yığımına) tətbiq edilən və OVER ifadəsilə təyin edilən funksiyalardır. Bunlar əsasən:

• Verilənlərin dublikatlarının silinməsi
• Hər bir qrupda ilk n sətrin qaytarılması
• Artan nəticələrinin hesablanması
• Boşluqların və diapazonların tapılması
• Sıralamanın iyerarxiyasının təyin edilməsi
• həmçinin çoxlu sayda başqa hesablamalarda istifadə edilir.

Pəncərə (Window) funksiyaları ANSI/ISO SQL:1999 standartında təsvir edildi. Həmin standartda bu funksiyaların adı  «OLAP functions» idi.  ANSI/ISO SQL:2003 standartında əlavə edildi və  sonra ANSI/ISO SQL:2008 və SQL:2011 standartlarında daha da təkmilləşdirildi. DB2, Oracle, Sybase, PostgreSQL VBİS-lər bu funksiyaları çoxdan dəstəkləyirdi. 2005-ci ildə Microsoft bu funksiyaların bir neçəsini: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), həmçinin OVER() əlavə etdi və 2012-ci versiyadan başlayaraq standartı tam olaraq dəstəkləməyə başladı.

Pəncərə funksiyası sətirlərin çoxluğuna (yığımına) tətbiq edilir. Pəncərə – SQL-in standart terminidir və funksiyanın işlədiyi kontekstin təsviri üçün istifadə olunur. SQL-də pəncərənin təyini üçün OVER ifadəsi istifadə olunur.  

Pəncərə (Window) funksiyalarının ümumi sintaksisinə baxaq:

<window function> OVER
 ([PARTITION BY <expression list>]
 [ORDER BY <expression [ASC|DESC] list>]
 [ROWS|RANGE <window frame extent>])

OVER ifadəsi pəncərəni (window) və ya cari sətrin nisbətində sətirlərin dəqiq yığımını, əgər lazımdırsa sıralamanı və digər elementləri təyin edir.

PARTITION BY  sorğuda GROUP BY kimi bizə nəticəni bir neçə hissəyə bölmək imkanını verir. Fərq ondadır ki,  PARTITION BY  tam sorğu ilə deyil lokal olaraq tək funksiya ilə işləyir. PARTITION BY  göstərilməyibsə sorğunun nəticəsində alınan bütün sətirləri bir qrup kimi qəbul edilir və funksiya  bu qrupa tətbiq edilir.

ORDER BY operatoru ilə alınan bölmənin (partition) içindəki sətirlər sıralanır.

ROWS|RANGE operatorları cari sətirdən əvvəl və sonra gələn sıralanmış sətirlərin fraqmentini təyin edir.

ROWS|RANGE operatorları arasında incə fərq var. ROWS bölmənin (partition) içərisində olan sətirlərin sayını hesablayır. RANGE isə bölmənin (partition) içərisində olan təkrarlamayan (DISTINCT) verilənlərin sayını qaytarır.  Hər ikisinin ORDER BY operatoru olmadan istifadəsi mənasızdır.  

Sintaksisin növbəti hissəsi <window frame extent> hissəsidir. Cari sətirdən əvvəl və sonra gələn sətirləri təyin edir.  Cari sətirdən əvvəl gələn heç bir sətir göstərilməyibsə ilkin təyinetməyə görə (default) olaraq pəncərənin ilk sətrindən cari sətrədək fraqment götürülür, yəni BETWEEN UNBOUNDED PRECEDING AND CURRENT_ROW.

<window frame extent> açıqlaması belədir:

BETWEEN <window frame preceding> AND <window frame following>

<window frame preceding> açıqlaması:

{UNBOUNDED PRECEDING
 | <unsigned_value_specification> PRECEDING
 | current_row}

<window frame following> açıqlaması:

{UNBOUNDED FOLLOWING
 | <unsigned_integer> FOLLOWING
 | current_row}

UNBOUNDED PRECEDING operatoru pəncərənin başlanğıcını bildirir. Başqa sözlə pəncərənin ilk sətrini bildirir. current_row cari sətri bildirir.  PRECEDING operatoru dəyişməyən rəqəm təyin  etməklə cari sətirdən əvvəl gələn sətirlərin sayını bildirir. RANGE üçün PRECEDING operatoru işləmir.

UNBOUNDED FOLLOWING operatoru pəncərənin bitməsini bildirir. Başqa sözlə pəncərənin sonuncu sətrini bildirir. current_row cari sətri bildirir.  FOLLOWING operatoru dəyişməyən rəqəm təyin  etməklə sətirdən sonra gələn sətirlərin sayını bildirir.

Ümumi yazılış nümunələri:

<window function> OVER (PARTITION BY .. ORDER BY ..
 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT_ROW)

<window function> OVER (PARTITION BY .. ORDER BY ..
 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

<window function> OVER (PARTITION BY .. ORDER BY ..
 ROWS BETWEEN 2 PRECEDING AND CURRENT_ROW)

<window function> OVER (PARTITION BY .. ORDER BY ..
 ROWS BETWEEN 2 PRECEDING AND 10 FOLLOWING)

Pəncərə (Window) funksiyalarının aşağıdakı kateqoriyaları var:

• Statistik funksiyalar (Aggregate functions)

SUM, COUNT, MIN, MAX, AVG

• Reytinq funksiyaları (Ranking functions)

RANK, DENSE_RANK, ROW_NUMBER, NTILE

• Analitik funksiyalar (Analytic functions)

CUME_DIST,  FIRST_VALUE, LAG, LAST_VALUE, LEAD, PERCENTILE_CONT, PERCENTILE_DISC, PERCENT_RANK

Beləliklə, bugünkü məqalənin də sonuna gəlib çatdıq. Pəncərə funksiyaları (Window functions) ilə ümumi tanış olduq. Növbəti məqalələrdə Pəncərə (Window) funksiyalarının hər bir kateqoriyası barədə daha ətraflı danışacağıq.

Növbəti məqalələrdə görüşənədək.

Diqqətinizə görə təşəkkür edirəm.