PARTITION BY-dan istifadə edərək məlumatları qruplara bölmək

Təsəvvür elə, böyük bir restoranda ofisiant (ya da barista, əgər qəhvə sevirsənsə) işləyirsən. Hər gün qazandığın çaypulu hesablayırsan. Amma bir məsələ var: restoran zonalara bölünüb və səni maraqlandırır ki, hər zonada nə qədər çaypulu yığılıb. PARTITION BY — SQL-də "restoranı zonalara bölmək" üçün istifadə olunur.

Daha rəsmi desək, PARTITION BY pəncərə funksiyalarında bütün cədvəl sətirlərini ayrıca qruplara (yəni "partisiyalara") bölmək üçün istifadə olunur. Hər bir qrupun içində pəncərə funksiyası ayrıca işləyir. Yəni, sanki hər "partisiya" üçün funksiyanı ayrıca tətbiq edirsən.

Nümunə: necə işləyir

Tutaq ki, bizdə sales adlı satışlar cədvəli var:

Əgər istəyirik ki, hər satıcının qazandığı pulu hesablayaq, amma hər region üçün ayrıca, PARTITION BY — məhz lazım olan şeydir.

PARTITION BY sintaksisi

Sintaksis çox sadədir:

pəncərə_funksiyası() OVER (PARTITION BY sütun_ya_da_sütunlar)

• pəncərə_funksiyası() — məsələn, SUM(), AVG(), ROW_NUMBER() və s.
• PARTITION BY sütun — hansı sütuna görə sətirləri bölmək lazımdır, onu göstərir.
• OVER() — SQL-ə deyir: "Bunu göstərilən pəncərədə et".

Nümunə: qruplara görə cəmləmə

Gəlin, hər region üçün satışların cəmini hesablayaq:

SELECT
    region,
    salesperson,
    amount,
    SUM(amount) OVER (PARTITION BY region) AS total_sales_by_region
FROM sales;

Nəticə belə olacaq:

Burada nə baş verir? SQL sətirləri region sütununun dəyərinə görə (North və South) qruplara bölür, sonra SUM() funksiyasını hər qrup üçün ayrıca tətbiq edir. Nəticədə, "North" qrupundakı sətirlər eyni cəmi alır, "South" qrupundakılar isə başqa cəmi.

PARTITION BY-dan istifadə nümunələri

Gəlin baxaq, PARTITION BY real həyatda hansı işlərdə kömək edə bilər.

Nümunə 1: Qrup daxilində sıralama

Tutaq ki, hər regionda satıcıları satış miqdarına görə sıralamaq istəyirik. Bunun üçün PARTITION BY və RANK() funksiyasını birləşdiririk:

SELECT
    region,
    salesperson,
    amount,
    RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rank_in_region
FROM sales;

Nəticə:

RANK() funksiyası hər region qrupu daxilində sıralama verir, 1-dən başlayır. Fikir ver, hər qrup üçün sıralama bir-dən başlayır.

Nümunə 2: Hər dəyəri qrupdakı orta ilə müqayisə etmək

Tutaq ki, hər satıcının öz regionunda orta satışdan nə qədər fərqləndiyini görmək istəyirik. AVG()-dan istifadə edirik:

SELECT
    region,
    salesperson,
    amount,
    AVG(amount) OVER (PARTITION BY region) AS avg_sales_by_region,
    amount - AVG(amount) OVER (PARTITION BY region) AS diff_from_avg
FROM sales;

Nəticə:

Əvvəlcə SQL sətirləri region-a görə qruplaşdırır. Sonra hər qrup üçün AVG(amount) hesablayır. Sonda isə hər sətir üçün fərqi çıxır: öz dəyəri minus orta.

Nümunə 3: Qrup daxilində sətirləri nömrələmək

Deyək ki, hər region qrupunda bütün tranzaksiyaları nömrələmək istəyirsən. ROW_NUMBER()-dan istifadə edirik:

SELECT
    region,
    salesperson,
    amount,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS row_number
FROM sales;

Nəticə:

GROUP BY ilə müqayisə

Çox vaxt PARTITION BY və GROUP BY qarışdırılır. Gəlin onları müqayisə edək:

GROUP BY

GROUP BY nəticənin strukturunu dəyişir — cədvəldəki sətirləri aqreqatlara çevirir. Məsələn:

SELECT
    region,
    SUM(amount) AS total_sales
FROM sales
GROUP BY region;

Nəticə:

Burada satıcılar haqda məlumat itir, çünki məlumatlar aqreqatlaşdırılır.

PARTITION BY

PARTITION BY isə əksinə, strukturu dəyişmir. Hələ də hər sətiri görürük, amma əlavə olaraq qruplara görə hesablanmış dəyərlərimiz var. Yəni, PARTITION BY detalları itirmədən aqreqat hesablamağa imkan verir.

PARTITION BY istifadə edəndə tez-tez edilən səhvlər

Səhv 1: PARTITION BY yaddan çıxıb

Bəzən məlumatı qruplaşdırmaq istəyirsən, amma PARTITION BY-ı unudursan. Məsələn:

SELECT
    region,
    salesperson,
    amount,
    SUM(amount) OVER () AS total_sales
FROM sales;

Nəticə:

Burada SUM(amount) bütün cədvəl üçün hesablanıb, regionlara görə yox. Əgər regionları nəzərə almaq istəyirsənsə, PARTITION BY region yazmağı unutma.

Səhv 2: ORDER BY-da səhv ardıcıllıq

Pəncərə daxilində sətirlərin sırası vacibdir, xüsusən RANK() və ya ROW_NUMBER() kimi funksiyalar üçün. OVER() içində ORDER BY istifadə edəndə diqqətli ol.