Məlumatların təhlili üçün window funksiyalarının istifadə nümunələri

İndi sən hazırsan ki, praktik nümunələrə baş vurasan və görəsən, bütün bu şeylər real tapşırıqlarda necə işləyir!

Nümunə: regionlar üzrə satışların rütbəsinin hesablanması

Təsəvvür elə ki, bizdə sales adlı bir cədvəl var və orda müxtəlif regionlarda satışlar barədə məlumatlar saxlanılır. Bizə hər region üçün satışların rütbəsini müəyyən etmək lazımdır.

Tapşırıq: hər region üzrə satışların rütbəsini (RANK) tapmaq

SELECT
    region,
    sales_amount,
    RANK() OVER (PARTITION BY region ORDER BY sales_amount DESC) AS sales_rank
FROM 
    sales;

Nəticə:

Diqqət elə, biz PARTITION BY region istifadə etdik ki, hər region üçün rütbəni ayrıca hesablayaq. Əgər PARTITION BY istifadə etməsəydik, rütbə bütün cədvəl üzrə qlobal şəkildə hesablanardı.

Nümunə: gəlirin kümülyativ cəminin hesablanması

İndi isə transactions cədvəlini işləyək və hər bir müştəri üçün gəlirin kümülyativ (yığılan) cəmini hesablayaq.

Tapşırıq: hər müştəri üçün kümülyativ cəmi hesablamaq

SELECT
    customer_id,
    purchase_date,
    amount,
    SUM(amount) OVER (PARTITION BY customer_id ORDER BY purchase_date) AS cumulative_sum
FROM 
    transactions;

Nəticə:

Burda əsas məqam — OVER() içində ORDER BY purchase_date istifadə etməkdir ki, kümülyativ cəm xronoloji ardıcıllıqla hesablansın.

Nümunə: məlumatların kvartillərə bölünməsi

Təsəvvür elə ki, bizdə students cədvəli var və orda adlar və test nəticələri göstərilib. Biz istəyirik ki, tələbələri nəticələrinə görə 4 qrupa bölək.

Tapşırıq: NTILE() istifadə edərək tələbələri 4 qrupa bölmək

SELECT
    name,
    test_score,
    NTILE(4) OVER (ORDER BY test_score DESC) AS quartile
FROM 
    students;

Sorğunun nəticəsi belə olacaq:

NTILE(4) məlumatları 4 qrupa bölür. Ən yüksək nəticə göstərən tələbələr birinci qrupa, ən aşağı nəticə göstərənlər isə sonuncu qrupa düşür.

Nümunə: zaman məlumatlarının analizi

site_visits cədvəlində saytın günlər üzrə ziyarət sayı saxlanılır. Bizə hər sayt üçün günlər arasında ziyarət fərqini hesablamaq lazımdır.

Bizim tapşırığımız — günlər arasında ziyarət fərqini hesablamaqdır

SELECT
    site_id,
    visit_date,
    visits,
    visits - LAG(visits) OVER (PARTITION BY site_id ORDER BY visit_date) AS visit_diff
FROM 
    site_visits;

Nəticə:

LAG() funksiyası əvvəlki sətrin dəyərini götürməyə imkan verir. Əgər əvvəlki sətr üçün məlumat yoxdursa, nəticə NULL olacaq. Bu barədə daha ətraflı növbəti dərslərdə öyrənəcəksən :P