Синтаксис OVER() і його ключові фішки

OVER() — це інструкція, яка каже SQL, над яким набором рядків треба застосувати віконну функцію. Можна сказати, це спосіб, який визначає "вікно" даних для застосування віконної функції. Уяви, що в нас є кімната, повна людей, і ми хочемо порахувати, скільки людей стоїть на кожному квадратному метрі підлоги. OVER() покаже, в якій саме частині кімнати ми сфокусуємо свої зусилля. Іншими словами, він задає, над яким набором рядків буде працювати функція.

Оператор OVER() використовується виключно з віконними функціями для виконання операцій над рядками однієї або кількох таблиць, без групування даних.

Синтаксис:

віконна_функція() OVER (
    [PARTITION BY ...]
    [ORDER BY ...]
    [ROWS/RANGE ...]
)

Де:

• PARTITION BY — ділить набір даних на логічні групи
• ORDER BY — задає порядок рядків всередині кожної групи
• ROWS/RANGE — уточнює розмір "вікна" (наприклад, поточний рядок + 1 наступний)

Приклад: OVER() без параметрів

Коли OVER() використовується без додаткових параметрів, це означає, що функція, яка вказана перед ним, буде працювати по всьому набору даних.

SELECT
    employee_id,
    salary,
    ROW_NUMBER() OVER () AS row_num -- ROW_NUMBER() буде застосована до всіх рядків результату
FROM employees;

Що відбувається?

1. ROW_NUMBER() призначає унікальний номер кожному рядку.
2. Оскільки в OVER() не вказано жодних параметрів, всі рядки з таблиці employees обробляються як одне ціле.

Результат:

Використання PARTITION BY для групування

Круто, а тепер уяви, що нас цікавить нумерація співробітників не по всій компанії, а всередині кожного відділу. Тут на сцену виходить PARTITION BY.

PARTITION BY всередині OVER() ділить дані на групи (або "розділи"). Для кожної групи функція рахує значення окремо. Тобто, якщо б ROW_NUMBER() був офіціантом, він би починав рахувати заново для кожного "стола" (розділу).

Приклад: використовуємо PARTITION BY

SELECT
    department_id,
    employee_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id) AS row_num
FROM employees;

Що відбувається?

1. Дані з таблиці employees розбиті на групи за значенням department_id.
2. У кожній групі рядкам призначається порядковий номер через ROW_NUMBER().

Результат:

Використання ORDER BY для порядку

Тепер давай додамо трохи структури. Уяви, що нам треба не просто нумерувати рядки, а робити це в певному порядку, наприклад, починаючи з найбільшої зарплати. Це вирішується через ORDER BY.

ORDER BY визначає порядок, у якому рядки будуть оброблятися віконною функцією.

Приклад: використовуємо ORDER BY всередині OVER()

SELECT
    department_id,
    employee_id,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;

Що відбувається?

1. Дані діляться на групи (PARTITION BY department_id).
2. Всередині кожної групи рядки сортуються за спаданням зарплати (ORDER BY salary DESC).
3. Кожному рядку присвоюється ранг з урахуванням сортування.

Результат:

Комбінування віконних функцій

SQL дозволяє використовувати кілька віконних функцій в одному запиті, причому кожна з них може працювати за своїми унікальними правилами. Це як в одній кімнаті одночасно грає музика і ведеться підрахунок людей — кожен процес незалежний!

Приклад: кілька віконних функцій

SELECT
    department_id,
    employee_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num,
    AVG(salary) OVER (PARTITION BY department_id) AS avg_salary
FROM employees;

Що відбувається?

1. ROW_NUMBER() нумерує рядки в кожній групі за спаданням зарплати.
2. AVG() рахує середню зарплату в кожній групі.

Результат:

Приклади з реального життя

Віконні функції з OVER() використовуються у багатьох реальних сценаріях. Ось лише кілька прикладів:

• Аналітика продажів: ранжування товарів за кількістю продажів у кожній категорії.
• Рейтинги: визначення позицій студентів у кожній групі за середнім балом.
• Часові ряди: накопичувальна сума продажів у розрізі часу.

Приклад з аналітики продажів:

SELECT
    category_id,
    product_id,
    product_name,
    SUM(sales) OVER (PARTITION BY category_id ORDER BY sales DESC) AS cumulative_sales
FROM products;

Поширені граблі при роботі з віконними функціями

1. Відсутність PARTITION BY

Якщо ти не використовуєш PARTITION BY, віконна функція застосовується до всієї таблиці. Це може дати неочікуваний результат, особливо якщо ти чекав розбивки по групах.

---

2. Некоректні типи даних у ORDER BY

ORDER BY всередині віконної функції чутливий до типів даних. Якщо ти сортуєш по полю з датою, збереженою як текст (VARCHAR), порядок може бути алфавітним, а не хронологічним.

3. Неправильне використання ROWS BETWEEN

За замовчуванням віконні функції працюють з рамками, які визначає ROWS BETWEEN. Якщо ти явно не вкажеш рамку, може застосовуватись поведінка RANGE, яка поводиться інакше і може повернути більше рядків, ніж ти очікував.

4. Неправильна робота з NULL

Віконні функції можуть по-різному обробляти NULL. Наприклад, RANK() і DENSE_RANK() будуть рахувати NULL як значення і дадуть йому окремий ранг.

5. Застосування агрегатних віконних функцій замість звичайних

Іноді використовують віконні агрегатні функції (SUM() OVER(...)) там, де достатньо звичайних агрегатів з GROUP BY, що ускладнює запит і гальмує виконання.