Yığılan cəmlərin hesablanması üçün pəncərə funksiyalarından istifadə: SUM(), AVG()

Təsəvvür elə: şirkətinin gəlirlərini izləyirsən, onlayn mağazada satışlara baxırsan və ya sadəcə öz illik xərclərini analiz edirsən. Sənə təkcə hər ay üçün gəlir və ya xərcləri görmək yox, həm də onların ay-ay necə toplandığını başa düşmək lazımdır.

Adi aqreqat funksiyalar (GROUP BY) burada kömək eləmir — onlar datanı qruplaşdırıb hər qrupa bir sətir qaytarır. Bəs əgər biz hər ayı və eyni zamanda yığılan cəmi görmək istəyiriksə? Bax, burada SUM() və pəncərə funksiyaları köməyə gəlir.

Yığılan cəmlər üçün pəncərə funksiyalarının əsasları

Pəncərə funksiyaları pəncərə çərçivələri üzrə aqreqat əməliyyatlar etməyə imkan verir. Bu sayədə, məsələn, hər sətirdə dəyərləri toplamaq olur, amma digər sətirləri silmədən. Artıq GROUP BY üçün qurban verməyə ehtiyac yoxdur!

SUM() pəncərə funksiyası ilə sintaksisi

Yığılan cəmi hesablamaq üçün əsas şablon belədir:

SELECT
    column_name,
    SUM(column_name) OVER (PARTITION BY partition_column ORDER BY order_column) AS cumulative_sum
FROM 
    table_name;

Burada:

• SUM(column_name) — dəyərləri toplayır.
• OVER() — hesab üçün pəncərəni təyin edir.
• PARTITION BY — datanı qruplara bölür (istəyə bağlıdır).
• ORDER BY — pəncərə daxilində sətirlərin sırasını təyin edir.

Nümunə: aylara görə yığılan gəlir

Gəlirlərinin olduğu bir cədvəl təsəvvür elə:

Hər ay üçün gəliri və yığılan nəticəni görmək istəyirik. Gəlin SQL-sorğu yazaq:

SELECT
    ay,
    gəlir,
    SUM(gəlir) OVER (ORDER BY ay) AS yığılan_gəlir
FROM 
    gəlirlər;

Nəticə:

Burada nə baş verir:

1. ORDER BY ay OVER() içində PostgreSQL-ə deyir ki, sətirləri xronoloji ardıcıllıqla nəzərə alsın.
2. Hər sətir üçün cəm əvvəlki bütün sətirlər (və cari sətir) nəzərə alınaraq hesablanır.

Burada baş verənlərə diqqətlə bax. Birinci sətir üçün SUM() yalnız 1-ci sətirin cəmini verir, ikinci üçün iki sətirin cəmini, üçüncü üçün üç sətirin cəmini. Ona görə ayların sırası çox vacibdir!

Nümunə: regionlara görə yığılan gəlir

Əgər regionlara görə satış cədvəlin olsaydı, bir hissəsi belə görünərdi:

İndi hər region üçün yığılan gəliri ayrıca hesablamaq istəyirik:

SELECT
    region,
    ay,
    gəlir,
    SUM(gəlir) OVER (PARTITION BY region ORDER BY ay) AS yığılan_gəlir
FROM 
    satışlar;

Nəticə belə olacaq:

İndi hər region ayrıca analiz olunur (PARTITION BY region), amma region daxilində sətirlər zamana görə sıralanır (ORDER BY ay).

Sürüşən orta (AVG())

Okey, yığılan cəmlər əladır, bəs son 3 ay üçün trendə baxmaq istəsən? Bunun üçün sürüşən orta idealdır.

Nümunə: gəlirlərin sürüşən ortası

Yenə gəlirlər cədvəli ilə işləyirik, və budur datalar:

3 aylıq sürüşən orta üçün sorğu:

SELECT
    ay,
    gəlir,
    AVG(gəlir) OVER (
        ORDER BY ay 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS sürüşən_orta
FROM 
    gəlirlər;

Nəticə:

İzah:

1. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW PostgreSQL-ə deyir ki, cari sətir və iki əvvəlki sətirə baxıb orta hesablansın.
2. Nəticədə hər ay üçün son 3 ayın orta gəlirini görürük.

Yəni hər sətir üçün 3 sətirlik pəncərə təyin edirik: cari və iki əvvəlki. Sonra onların ortasını tapırıq. Çox rahatdır.

ORDER BY necə işləyir və təsiri

Pəncərə funksiyaları sətirlərin düzgün sırasından asılıdır. Əgər sıra səhvdirsə (və ya ümumiyyətlə yoxdursa), nəticələr gözlənilməz ola bilər.

Nümunə: ORDER BY olmadan səhvlər

Əgər ORDER BY OVER()-dən çıxarsaq, yığılan cəm əvəzinə hər sətir üçün bütün gəlirlərin cəmini alacağıq:

SELECT
    ay,
    gəlir,
    SUM(gəlir) OVER () AS səhv_yığılan_cəm
FROM 
    gəlirlər;

Nəticə:

Sətirlər sıralanmır və yığılan cəm əvəzinə funksiya sadəcə bütün sətirləri toplayır.

Real istifadə halları

Gəlir analizi:

• Yığılan cəmlər satışların və ya şirkət gəlirlərinin necə artdığını izləməyə kömək edir.
• Sürüşən orta "təmiz" trendi səs-küysüz göstərir.

Maliyyə modelləşdirmə:

Banklar və maliyyə şirkətləri pəncərə funksiyalarından ödənişlər, borc artımı və digər metrikləri analiz etmək üçün istifadə edir.

Zaman sıralarının qurulması:

Onlayn istifadəçi sayı, səhifə baxışları, gəlir və s. kimi zaman dataları SUM() və AVG() ilə analiz üçün idealdır.