Analitika üçün əsas window funksiyaları

Başlamazdan əvvəl təsəvvür elə ki, minlərlə satış sətrindən ibarət bir cədvəllə işləyirsən. Sənin tapşırığın: hər kateqoriyada birinci olan satıcını, ikinci olanı və s. tapmaqdır. Və ya, məsələn, seçimdə bütün sətirləri nömrələmək lazımdır ki, ardıcıllığı izləyə biləsən. Bütün bunları window funksiyaları ilə rahatlıqla eləmək olur.

Window funksiyaları — SQL-də olan və dataset-in bir hissəsi (ona "pəncərə" deyək) üzərində işləyən funksiyalardır. Aggregat funksiyalardan fərqli olaraq (məsələn, SUM() və ya AVG()), window funksiyaları sətirləri olduğu kimi saxlayır, sadəcə onlara hesablanmış dəyərlər əlavə edir.

Aggregat funksiyalardan fərqi

Aggregat funksiyalar datanı "yığır", sətirləri qruplaşdırır:

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

Nəticə: departamentlərin sayına görə cəmi bir neçə sətir.

Window funksiyası ilə müqayisə edək — burada sətirlər yerində qalır, amma əlavə bir sütun yaranır, məsələn, ROW_NUMBER():

SELECT employee_name, department,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_within_department
FROM employees;

Burada sən bütün sətirləri görəcəksən, amma əlavə rank_within_department sütunu olacaq, hər işçiyə öz şöbəsində nömrə veriləcək.

Əsas window funksiyaları

OVER() sintaksisi

Hər bir window funksiyasının ən vacib hissəsi — bu sehrli OVER() sözüdür. O, funksiyanın hansı "pəncərə" ilə işləyəcəyini göstərir. OVER() içində qrup üzrə bölmə (PARTITION BY) və/və ya sıralama qaydası (ORDER BY) təyin edə bilərsən.

Ümumi sintaksis:

<window_funksiyası>() OVER (
   [PARTITION BY <qruplaşdırma>] 
   [ORDER BY <sıralama>]
)

Komponentlər:

• PARTITION BY: Sətirləri qruplara bölürük. Məsələn, "datani şöbələrə görə böl".
• ORDER BY: Sətirlərin işlənmə ardıcıllığını göstəririk. Məsələn, "işçiləri maaşa görə azalan sıralama ilə düz".

ROW_NUMBER() funksiyası

ROW_NUMBER() funksiyası sətirləri 1-dən başlayaraq nömrələyir, göstərilən "pəncərə" daxilində. Bəzən bu, müvəqqəti cədvəldə sətir nömrəsi yaratmaq və ya yazının ardıcıllığını tapmaq üçün faydalıdır.

Nümunə. sales (satışlar) cədvəli:

Sorgu:

SELECT seller_name, product_category, revenue,
       ROW_NUMBER() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS row_number
FROM sales;

Nəticə:

Necə işləyir:

1. Data product_category üzrə qruplara bölünür.
2. Hər qrup revenue-ya görə (azalan sıralama ilə) sıralanır.
3. Hər qrupun içindəki sətirlərə ardıcıl nömrə verilir.

RANK() funksiyası

RANK() funksiyası sətirləri sıralamaq üçün istifadə olunur. ROW_NUMBER()-dan fərqli olaraq, eyni dəyərləri nəzərə alır və əgər dəyərlər üst-üstə düşürsə, nömrələri (rank) atlayır.

Nümunə:

SELECT seller_name, product_category, revenue,
       RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS rank
FROM sales;

Nəticə:

DENSE_RANK() funksiyası

DENSE_RANK() RANK()-a bənzəyir, bir fərqlə: rank nömrələrini atlamır, əgər eyni dəyərlər varsa.

Nümunə. Eyni gəlirli satış əlavə edək:

Sorgu:

SELECT seller_name, product_category, revenue,
       DENSE_RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS dense_rank
FROM sales;

Nəticə:

İstifadə nümunələri: sətirlərin nömrələnməsi

Tapşırıq: orders cədvəlində bütün sifarişləri tarixə görə nömrələmək.

SELECT order_id, customer_name, order_date,
       ROW_NUMBER() OVER (ORDER BY order_date) AS order_number
FROM orders;

Nəticə: sifarişlərin yerinə yetirilmə ardıcıllığı ilə nömrələnmiş siyahısı alınır.

İstifadə nümunələri: hər kateqoriyada top-3 satıcı

Tapşırıq: hər məhsul kateqoriyasında ən yaxşı üç satıcını tapmaq.

WITH ranked_sales AS (
    SELECT seller_name, product_category, revenue,
           RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS rank
    FROM sales
)
SELECT seller_name, product_category, revenue
FROM ranked_sales
WHERE rank <= 3;

İstifadə nümunələri: eyni göstəriciləri tapmaq

Tapşırıq: hər kateqoriyada eyni gəlirli satıcılar olub-olmadığını tapmaq.

SELECT seller_name, product_category, revenue,
       DENSE_RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS dense_rank
FROM sales;

İndi görmək olur ki, rank-lar harada "ilişib" qalıb, yəni eyni dəyərlər var.