Оконні функції: прихована суперсила SQL

На перший погляд може здатися, що в SQL вже є все для аналізу даних: GROUP BY, агрегати, підзапити... Але це тільки початок. Ласкаво просимо у світ оконних функцій — потужного інструменту, який дозволяє працювати з даними по рядках, зберігаючи весь контекст.

Оконні функції дозволяють робити обчислення — суму, середнє, ранги та інше — по «вікну» рядків, не згортуючи дані. Це означає, що на відміну від звичайних агрегатних функцій (SUM(), AVG(), COUNT()), ти отримуєш і результат, і деталі у кожному рядку.

Уяви, що ти хочеш порахувати накопичений дохід по замовленнях. З GROUP BY ти б втратив конкретні замовлення — залишилася б тільки сума. А з оконною функцією ти додаси результат прямо до кожного рядка, нічого не втрачаючи.

Оконні функції особливо зручні, бо вони не знищують дані: кожен рядок залишається на місці, а результати обчислень просто додаються у нові стовпці. Це дозволяє виконувати складну аналітику без підзапитів і громіздких конструкцій — все робиться прямо всередині одного запиту. Такі функції ідеально підходять для задач типу ранжування, обчислення ковзаючих середніх або порівняння значень між рядками. Код залишається читабельним, а результат — точним.

Коли це особливо корисно:

• Рейтинг співробітників, продавців, продуктів — хто на якому місці.
• Часові ряди — як щось змінювалось по днях або тижнях.
• Продажі та фінанси — скільки накопичилось на кожному кроці, які замовлення вище середнього, хто входить у топ-25%.

Де застосовуються оконні функції

У будь-якій сфері, де важливий контекст, а не тільки підсумок:

• звіти по продажах;
• аналіз поведінки клієнтів;
• побудова графіків з накопичувальними метриками;
• сегментація даних (наприклад, по квартилях);
• розрахунок відхилень і трендів.

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

Приклад 1: Ранжування даних

Уяви, у тебе є список студентів з їх оцінками за екзамен. Ти хочеш присвоїти кожному студенту його позицію в класі. За допомогою оконних функцій це елементарно. Наприклад, функції RANK() або ROW_NUMBER() допоможуть впоратись із цим завданням.

Приклад 2: Аналіз часових даних

А що, якщо треба дослідити, як доходи компанії змінювались по місяцях? Тобі потрібна накопичувальна сума доходів. Використовуючи оконну функцію SUM() з певним вікном, ти легко можеш отримати цей результат.

Приклад 3: Квантілі та розбиття на групи

Розбити дані на рівні групи (наприклад, по доходах) для сегментації клієнтів? Тут на допомогу прийде функція NTILE(). Давай дізнаємось, хто з клієнтів у топ-25%, а хто внизу рейтингу.

Як це виглядає?

Оконна функція просто додає результат у підсумковий набір даних:

SELECT
    student_id,
    grade,
    RANK() OVER (ORDER BY grade DESC) AS rank
FROM 
    students;

Тут ми отримуємо таблицю, де у кожного студента є його унікальний ранг по оцінці.

Проста аналогія

Уяви, що ти на пробіжці з групою друзів. Кожен біжить зі своєю швидкістю, але ти хочеш знати, яке місце займаєш у забігу прямо зараз. Замість того щоб зупинити всіх і скласти таблицю лідерів (як це робить GROUP BY), ти просто дивишся на тих, хто біжить поруч, і визначаєш свій поточний рейтинг.

Оце і є оконна функція: вона не зупиняє забіг, не розбиває всіх по групах — вона просто додає інформацію, зберігаючи рух і деталі. Кожен продовжує бігти, але тепер у тебе є додаткова аналітика — наприклад, скільки людей попереду, який у тебе темп у порівнянні із середнім і т.д.

Переваги перед традиційними підходами

Розглянемо класичну задачу: підрахунок рангу продавців по доходу. Є два підходи:

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

2. З оконними функціями. Всього один запит з красивим і зрозумілим синтаксисом, і результат вже у тебе. Наприклад:

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

Цей запит одразу розділяє продавців по регіонах і нумерує їх у порядку спадання доходу.

Реальний приклад

Тепер уяви, що ти аналітик і аналізуєш дані по продажах. Тобі треба дізнатись:

• сумарні доходи за кожен місяць,
• як змінився дохід у порівнянні з попереднім місяцем,
• ранжувати регіони по загальному доходу.

Все це можна зробити з використанням оконних функцій і навіть в одному запиті. Але це вже тема для наступних лекцій.

Тепер, озброївшись базовими знаннями про оконні функції, ти готовий перейти до їх синтаксису і побачити силу функцій ROW_NUMBER(), RANK(), DENSE_RANK() і NTILE(). Переходимо до наступної лекції!