Oken frame-i ROWS və RANGE ilə sazlamaq

Window funksiyalarından istifadə edəndə belə bir sual yaranır: "Cari sətrin nəticəsini hesablamaq üçün window daxilində neçə sətir iştirak edir?" Bu suala cavab window frame-dən asılıdır.

Window frame — bu, window funksiyasının nəticəsini hesablamaq üçün istifadə olunan sətir diapazonudur. Bu diapazon cari sətrə və əlavə olaraq ROWS və ya RANGE ilə verilən şərtlərə əsaslanır.

Sadə nümunə: yığılan cəmi hesablamaq üçün sən göstərə bilərsən:

• Yalnız cari sətri nəzərə al.
• Cari sətri və yuxarıdakı bütün sətirləri nəzərə al.
• Cari sətri və müəyyən sayda yuxarı/aşağı sətirləri nəzərə al.

Məhz ROWS və RANGE window frame-ə hansı sətirlərin düşəcəyini idarə edir.

ROWS istifadəsi

ROWS window frame-i sətirlərin fiziki yerləşməsi səviyyəsində müəyyən edir. Yəni, o sətirləri yuxarıdan aşağıya ardıcıllıqla sayır, həmin sətirlərdəki dəyərlərdən asılı olmayaraq.

Sintaksis

window_funksiyasi OVER (
    ORDER BY sutun
    ROWS BETWEEN baslangic AND son
)

Açar ifadələr:

• CURRENT ROW — cari sətir.
• say PRECEDING — cari sətrin yuxarısında olan müəyyən sayda sətir.
• say FOLLOWING — cari sətrin aşağısında olan müəyyən sayda sətir.
• UNBOUNDED PRECEDING — window-un əvvəlindən.
• UNBOUNDED FOLLOWING — window-un sonuna qədər.

Nümunə: cari sətir və 2 əvvəlki üçün yığılan cəm

SELECT
    employee_id,
    salary,
    SUM(salary) OVER (
        ORDER BY employee_id
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_sum
FROM employees;

İzah:

• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW deməkdir: cari sətri və onun yuxarısındakı iki sətri götür.
• Yığılan cəm yalnız bu üç sətir üçün hesablanacaq.

Nəticə:

Nümunə: sabit sayda sətir ilə "sliding window" analizi

Tapşırıq: cari sətr və iki sonrakı üçün orta maaşı hesablamaq.

SELECT 
    employee_id,
    salary,
    AVG(salary) OVER (
        ORDER BY employee_id
        ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
    ) AS rolling_avg
FROM employees;

Nəticə:

RANGE istifadəsi

RANGE window frame-i sətirlərin yerləşməsinə görə yox, dəyərlərə görə qurur. Yəni, sətir frame-ə düşür, əgər onun ORDER BY sütunundakı dəyəri göstərilən diapazona düşürsə.

Sintaksis

window_funksiyasi OVER (
    ORDER BY sutun
    RANGE BETWEEN baslangic AND son
)

Nümunə: dəyərlər diapazonuna görə yığılan cəm

Tapşırıq: cari sətrin maaşından maksimum 2000 fərqlənən sətirlər üçün yığılan cəmi hesablamaq.

SELECT 
    employee_id,
    salary,
    SUM(salary) OVER (
        ORDER BY salary
        RANGE BETWEEN 2000 PRECEDING AND 2000 FOLLOWING
    ) AS range_sum
FROM employees;

İzah:

• RANGE BETWEEN 2000 PRECEDING AND 2000 FOLLOWING deməkdir: salary dəyəri cari sətrin dəyərindən ±2000 aralığında olan sətirləri götür.

Nəticə:

ROWS və RANGE müqayisəsi

• ROWS real sətirlərlə və onların sayı ilə işləyir. O, dəyərlərdən asılı deyil.
• RANGE ORDER BY sütunu üçün verilmiş dəyərlərin məntiqi diapazonu ilə işləyir.

Müqayisə üçün nümunə verək. Tutaq ki, bizdə sales adlı cədvəl var və orada belə məlumatlar var:

Sorguları müqayisə edək:

ROWS:

SELECT
    id,
    SUM(amount) OVER (
        ORDER BY amount
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS sum_rows
FROM sales;

Nəticə:

Burada hər sətir real göründüyü kimi cəminə əlavə olunur.

RANGE:

SELECT 
    id,
    SUM(amount) OVER (
        ORDER BY amount
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS sum_range
FROM sales;

Nəticə:

Burada 1 və 2 sətirləri birləşdi, çünki onların amount = 100. RANGE təkrarlanan dəyərləri amount sütununda nəzərə alır.

Real tapşırıqlardan nümunələr

1. Gəlirin artımını hesablamaq

Tapşırıq: əvvəlki sətrə nisbətən gəlirin dəyişməsini hesablamaq.

SELECT 
    month,
    revenue,
    revenue - LAG(revenue) OVER (
        ORDER BY month
    ) AS revenue_change
FROM sales_data;

2. Cari sətri qrupdakı orta ilə müqayisə etmək

Tapşırıq: hər şöbə üçün işçinin maaşını şöbənin orta maaşı ilə fərqini hesablamaq.

SELECT 
    department_id,
    employee_id,
    salary,
    salary - AVG(salary) OVER (
        PARTITION BY department_id
    ) AS salary_diff
FROM employees;

ROWS və RANGE istifadə edərkən səhvlər

Yanlış sətir sırası (ORDER BY) göstərmək: Əgər sıralama göstərilməsə, PostgreSQL səhv verəcək, çünki cari sətri müəyyən edə bilməyəcək.

ROWS və RANGE yanaşmalarını bir tapşırıqda qarışdırmaq: Yanaşmanı datana görə seç. ROWS sabit sayda sətir üçün, RANGE isə dəyər diapazonları üçün uyğundur.

RANGE-də təkrarlanan dəyərləri nəzərdən qaçırmaq: Unutma ki, RANGE bütün təkrarlanan dəyərləri nəzərə alır və bu nəticəni ciddi dəyişə bilər.