Pəncərə funksiyaları

Müəllim hər kursun siyahısını istəyir: hər şagirdin balı, yanında isə kursun orta balı və şagirdin kursdakı yeri. GROUP BY bunu edə bilmir — o, sətirləri qrupa yığıb bir sətrə çevirir və adlar itir. Pəncərə funksiyaları isə hər sətri yerində saxlayır və ona qonşu sətirlərdən hesablanmış qiymət əlavə edir. Reytinqlər, artan cəmlər, «keçən aya görə dəyişmə» — analitikanın böyük hissəsi məhz bu alətlə yazılır.

OVER: sətirlərin «pəncərəsi»

Adi aqreqatın arxasına OVER yazan kimi o, pəncərə funksiyasına çevrilir. Aşağıda hər yazılışın yanında həmin kursun orta balı görünür: sətirlər yığılmır, orta isə hər kurs üçün ayrıca hesablanır.

SELECT e.course_id, e.student_id, e.score,
       ROUND(AVG(e.score) OVER (PARTITION BY e.course_id), 1) AS course_avg
FROM enrollments AS e
WHERE e.course_id IN (1, 3)
ORDER BY e.course_id, e.score DESC;

course_id | student_id | score | course_avg
1 | 11 | 97 | 87.3
1 | 1 | 92 | 87.3
1 | 5 | 85 | 87.3
1 | 2 | 75 | 87.3
3 | 11 | 94 | 80
3 | 2 | 81 | 80
3 | 6 | 77 | 80
3 | 4 | 68 | 80

ROW_NUMBER, RANK və DENSE_RANK

Üç sıralama funksiyası yalnız bərabər qiymətlərdə fərqlənir. Şagirdləri yaşa görə sıralayaq: üç nəfərin 17 yaşı, üç nəfərin 16 yaşı var. ROW_NUMBER bərabərliyə baxmır və 1, 2, 3 verir, ona görə təkrarlanmayan ikinci sıralama sütunu (id) əlavə edirik ki, nəticə sabit olsun.

SELECT first_name, age,
       ROW_NUMBER() OVER (ORDER BY age DESC, id) AS row_num,
       RANK()       OVER (ORDER BY age DESC)     AS rnk,
       DENSE_RANK() OVER (ORDER BY age DESC)     AS dense
FROM students
ORDER BY age DESC, id
LIMIT 7;

first_name | age | row_num | rnk | dense
Elvin | 17 | 1 | 1 | 1
Tural | 17 | 2 | 1 | 1
Fidan | 17 | 3 | 1 | 1
Murad | 16 | 4 | 4 | 2
Rəşad | 16 | 5 | 4 | 2
Səbinə | 16 | 6 | 4 | 2
Aysel | 15 | 7 | 7 | 3

PARTITION BY: hər qrupun öz reytinqi

PARTITION BY course_id hər kursu ayrıca pəncərəyə çevirir və nömrələmə hər kursda 1-dən başlayır. Bununla «hər kursun ən yaxşı şagirdi» sualını həll edirik: əvvəlcə CTE-də yerləri hesablayırıq, sonra pos = 1 olan sətirləri seçirik.

WITH ranked AS (
  SELECT c.title, s.first_name, e.score,
         RANK() OVER (PARTITION BY e.course_id ORDER BY e.score DESC) AS pos
  FROM enrollments AS e
  JOIN students AS s ON s.id = e.student_id
  JOIN courses  AS c ON c.id = e.course_id
)
SELECT title, first_name, score
FROM ranked
WHERE pos = 1
ORDER BY title;

title | first_name | score
Algebra | Fidan | 97
English B1 | Leyla | 90
Geometry | Leyla | 95
Mechanics | Fidan | 94
Organic Chemistry | Elvin | 72
Python Basics | Rəşad | 99
World History | Nigar | 93

Artan cəm: SUM() OVER (ORDER BY ...)

OVER-in içinə ORDER BY yazanda pəncərə «əvvəldən bu sətrə qədər» olur. Ona görə SUM artan cəm verir: hər ayın gəliri əvvəlki ayların cəminə əlavə olunur. Aşağıda əvvəlcə CTE ilə aylıq gəliri hesablayırıq, sonra onun üzərində pəncərə funksiyası işlədirik.

WITH monthly AS (
  SELECT SUBSTR(o.order_date, 1, 7) AS month,
         ROUND(SUM(o.quantity * p.price), 2) AS revenue
  FROM orders AS o
  JOIN products AS p ON p.id = o.product_id
  GROUP BY month
)
SELECT month, revenue,
       ROUND(SUM(revenue) OVER (ORDER BY month), 2) AS running_total
FROM monthly
ORDER BY month;

month | revenue | running_total
2025-01 | 1691 | 1691
2025-02 | 963.99 | 2654.99
2025-03 | 124.98 | 2779.97
2025-04 | 935.99 | 3715.96
2025-05 | 42 | 3757.96
2025-06 | 152.1 | 3910.06
2025-07 | 32 | 3942.06

Bir incəlik var: ORDER BY olanda susmaya görə çərçivə RANGE rejimindədir və sıralama açarı eyni olan sətirləri (həmyaşıdları) birlikdə götürür. Eyni gündə iki sifariş varsa, hər ikisi günün tam cəmini göstərir. Sətir-sətir artan cəm lazımdırsa, təkrarlanmayan açar əlavə et və ya çərçivəni açıq yaz: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

SELECT id, order_date, quantity,
       SUM(quantity) OVER (ORDER BY order_date)     AS by_range,
       SUM(quantity) OVER (ORDER BY order_date, id) AS by_row
FROM orders
WHERE order_date < '2025-05-01'
ORDER BY order_date, id;

id | order_date | quantity | by_range | by_row
1 | 2025-01-15 | 1 | 3 | 1
2 | 2025-01-15 | 2 | 3 | 3
3 | 2025-02-02 | 20 | 23 | 23
4 | 2025-02-10 | 1 | 24 | 24
5 | 2025-03-05 | 1 | 25 | 25
6 | 2025-03-18 | 2 | 27 | 27
7 | 2025-04-09 | 1 | 31 | 28
8 | 2025-04-09 | 3 | 31 | 31

LAG və LEAD: qonşu sətirlə müqayisə

LAG(x) pəncərədə əvvəlki sətrin qiymətini, LEAD(x) isə növbəti sətrin qiymətini qaytarır. Bununla «keçən aya görə dəyişmə» bir sorğu ilə hesablanır. Birinci ayın əvvəlkisi olmadığı üçün LAG orada NULL verir. LAG(x, 2) iki sətir geriyə baxır, LAG(x, 1, 0) isə NULL əvəzinə 0 qaytarır.

WITH monthly AS (
  SELECT SUBSTR(o.order_date, 1, 7) AS month,
         ROUND(SUM(o.quantity * p.price), 2) AS revenue
  FROM orders AS o
  JOIN products AS p ON p.id = o.product_id
  GROUP BY month
)
SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
       ROUND(revenue - LAG(revenue) OVER (ORDER BY month), 2) AS change
FROM monthly
ORDER BY month;

month | revenue | prev_revenue | change
2025-01 | 1691 | NULL | NULL
2025-02 | 963.99 | 1691 | -727.01
2025-03 | 124.98 | 963.99 | -839.01
2025-04 | 935.99 | 124.98 | 811.01
2025-05 | 42 | 935.99 | -893.99
2025-06 | 152.1 | 42 | 110.1
2025-07 | 32 | 152.1 | -120.1

SELECT category, name, price
       -- add price_rank here
FROM products
ORDER BY category, price DESC;

category | name | price | price_rank
Accessories | Backpack | 55 | 1
Accessories | Water bottle | 12 | 2
Electronics | Laptop | 1450 | 1
Electronics | Smartphone | 899.99 | 2
Electronics | Monitor | 310 | 3
Electronics | Headphones | 120.5 | 4
Games | Chess set | 42 | 1
Home | Desk lamp | 34.99 | 1
Stationery | Pen set | 7.9 | 1
Stationery | Notebook | 3.2 | 2

SELECT customer_id, order_date, quantity
       -- order_no and running_qty
FROM orders
WHERE customer_id IN (1, 2, 3)
ORDER BY customer_id, order_date, id;

customer_id | order_date | quantity | order_no | running_qty
1 | 2025-01-15 | 1 | 1 | 1
1 | 2025-01-15 | 2 | 2 | 3
1 | 2025-07-07 | 10 | 3 | 13
2 | 2025-02-02 | 20 | 1 | 20
2 | 2025-06-30 | 1 | 2 | 21
3 | 2025-02-10 | 1 | 1 | 1
3 | 2025-05-21 | 1 | 2 | 2