Що таке віконні функції та чим вони відрізняються від GROUP BY

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

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

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

Важливий технічний момент: віконні функції з’явилися в MySQL лише у версії 8.0. Якщо у вас MySQL 5.7 або старіша, приклади з цього уроку не запрацюють — перевірте версію командою SELECT VERSION();.

Синтаксис OVER() і PARTITION BY

Загальний вигляд віконної функції такий:

функція(...) OVER (
  PARTITION BY колонки_розбиття
  ORDER BY колонки_сортування
  -- необов'язково: рамка вікна (ROWS BETWEEN ...)
)

OVER() без параметрів: агрегат по всій таблиці

Порожні дужки означають, що вікном є весь набір рядків. Класична задача: показати кожне замовлення разом із часткою від загальної виручки.

SELECT
  id,
  customer_id,
  amount,
  SUM(amount) OVER () AS total_revenue,
  ROUND(amount / SUM(amount) OVER () * 100, 2) AS pct_of_total
FROM orders
WHERE status = 'completed';

Кожен рядок залишився на місці, але поруч з’явилися дві нові колонки: загальна виручка та відсоток замовлення від неї. З GROUP BY довелося б писати підзапит.

PARTITION BY: окреме вікно для кожної групи

PARTITION BY розбиває рядки на секції — аналог груп у GROUP BY, але без схлопування. Порахуємо для кожного замовлення середній чек саме цього клієнта:

SELECT
  id,
  customer_id,
  amount,
  AVG(amount) OVER (PARTITION BY customer_id) AS avg_customer_amount,
  amount - AVG(amount) OVER (PARTITION BY customer_id) AS diff_from_avg
FROM orders
WHERE status = 'completed';

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

ORDER BY усередині OVER

ORDER BY у вікні задає порядок обробки рядків. Він критичний для функцій ранжування, LAG/LEAD і наростаючих підсумків. Зверніть увагу: це сортування всередині вікна, воно не впливає на порядок рядків у фінальному результаті — за нього відповідає звичайний ORDER BY наприкінці запиту.

Якщо в одному запиті кілька функцій використовують однакове вікно, його можна оголосити один раз через ключове слово WINDOW після FROM/WHERE і посилатися на нього за іменем: OVER w. Запит стає коротшим і його легше підтримувати.

Функції ранжування: ROW_NUMBER, RANK, DENSE_RANK

Трійка функцій ранжування нумерує рядки за заданим порядком. Різниця між ними проявляється, коли значення повторюються:

• ROW_NUMBER() — наскрізна нумерація без повторів: 1, 2, 3, 4. Однакові значення все одно отримають різні номери.
• RANK() — однакові значення отримують однаковий ранг, наступний ранг «перестрибує»: 1, 2, 2, 4.
• DENSE_RANK() — однакові значення отримують однаковий ранг, без пропусків: 1, 2, 2, 3.

Приклад: ранжуємо замовлення кожного клієнта за сумою.

SELECT
  customer_id,
  id AS order_id,
  amount,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn,
  RANK()       OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS drnk
FROM orders
WHERE status = 'completed';

Найпопулярніше практичне застосування — «топ-N у кожній групі». Наприклад, найбільше замовлення кожного клієнта:

SELECT c.name, t.order_id, t.amount
FROM (
  SELECT
    id AS order_id,
    customer_id,
    amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn
  FROM orders
  WHERE status = 'completed'
) AS t
JOIN customers c ON c.id = t.customer_id
WHERE t.rn = 1;

Зверніть увагу на підзапит: відфільтрувати за результатом віконної функції безпосередньо у WHERE не можна — чому саме, розберемо в блоці помилок.

LAG і LEAD: порівняння з попереднім і наступним рядком

LAG(колонка, зсув, значення_за_замовчуванням) повертає значення з попереднього рядка вікна, LEAD() — з наступного. Це стандартний інструмент для аналізу динаміки: «як змінилися продажі порівняно з минулим місяцем?»

SELECT
  DATE_FORMAT(order_date, '%Y-%m') AS month,
  SUM(amount) AS revenue,
  LAG(SUM(amount)) OVER (ORDER BY DATE_FORMAT(order_date, '%Y-%m')) AS prev_revenue,
  ROUND(
    (SUM(amount) - LAG(SUM(amount)) OVER (ORDER BY DATE_FORMAT(order_date, '%Y-%m')))
    / LAG(SUM(amount)) OVER (ORDER BY DATE_FORMAT(order_date, '%Y-%m')) * 100, 1
  ) AS growth_pct
FROM orders
WHERE status = 'completed'
GROUP BY DATE_FORMAT(order_date, '%Y-%m');

Тут віконна функція працює разом із GROUP BY: спочатку MySQL групує замовлення за місяцями, а вже потім LAG «дотягується» до підсумку попереднього місяця. Для першого місяця попереднього рядка немає, тому LAG поверне NULL — це нормально.

Ще один типовий кейс — час між замовленнями клієнта:

SELECT
  customer_id,
  order_date,
  LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order,
  DATEDIFF(
    order_date,
    LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)
  ) AS days_between
FROM orders
WHERE status = 'completed';

Так ви знайдете клієнтів, які купують регулярно, і тих, хто «заснув» — готовий сегмент для реактиваційної розсилки.

Третій аргумент LAG стане в пригоді, коли NULL у першому рядку заважає розрахункам: LAG(amount, 1, 0) поверне 0 замість NULL. А якщо потрібне не сусіднє, а, скажімо, значення трьома рядками раніше — передайте зсув другим аргументом: LAG(amount, 3). LEAD працює дзеркально й корисний, наприклад, щоб одразу бачити дату наступного замовлення клієнта.

Running total і ковзні агрегати

Наростаючий підсумок

Якщо додати ORDER BY до агрегатної функції у вікні, вона рахуватиме значення наростаючим підсумком — від початку вікна до поточного рядка:

SELECT
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date, id) AS running_total
FROM orders
WHERE status = 'completed';

Колонка running_total показує накопичену виручку на кожну дату — саме те, що потрібно для графіка «виручка з початку періоду». Додавання id у сортування робить порядок однозначним, коли кілька замовлень мають однакову дату.

Ковзне середнє

Рамка вікна ROWS BETWEEN ... AND ... обмежує, які саме рядки навколо поточного враховує функція. Порахуємо ковзне середнє денної виручки за 7 днів:

SELECT
  order_date,
  SUM(amount) AS day_revenue,
  ROUND(AVG(SUM(amount)) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ), 2) AS moving_avg_7d
FROM orders
WHERE status = 'completed'
GROUP BY order_date;

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW означає «поточний рядок плюс шість попередніх» — разом сім рядків (за щоденних даних без пропусків це і є сім днів). Ковзне середнє згладжує денні коливання й показує тренд, тому його часто виводять на дашборди. До речі, у Power BI подібні речі робляться мірами DAX — ми дійдемо до цього в уроці 8.

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

1. Віконна функція у WHERE або HAVING. Запит WHERE ROW_NUMBER() OVER (...) = 1 поверне помилку. Причина — порядок виконання: WHERE відпрацьовує до обчислення віконних функцій. Рішення: обгорніть запит у підзапит або CTE й фільтруйте вже за псевдонімом колонки.

2. Плутанина RANK і ROW_NUMBER на однакових значеннях. Якщо два замовлення мають однакову суму, ROW_NUMBER довільно призначить їм 1 і 2, а RANK — обом 1. Для задачі «рівно один рядок на групу» беріть ROW_NUMBER, для чесних рейтингів з урахуванням нічиїх — RANK або DENSE_RANK.

3. Забутий ORDER BY у вікні для running total. SUM(amount) OVER () без ORDER BY поверне однакову загальну суму в кожному рядку, а не наростаючий підсумок. Для накопичення сортування у вікні обов’язкове.

4. Неоднозначне сортування. Якщо в ORDER BY order_date кілька рядків мають однакову дату, результат ROW_NUMBER чи running total може відрізнятися між запусками. Додавайте унікальну колонку (наприклад, id) як другий ключ сортування.

5. Стара версія MySQL. На MySQL 5.7 ви отримаєте синтаксичну помилку біля OVER. Оновіться до 8.0+ або, якщо це неможливо, використовуйте обхідні шляхи з підзапитами — але для навчання простіше поставити актуальну версію.

FAQ: часті питання про віконні функції SQL

Чи можна використовувати віконні функції разом із GROUP BY?

Так. Спочатку виконується групування й агрегація, а потім віконні функції працюють уже з результатами груп. Приклад із LAG по місячній виручці вище побудований саме так: LAG застосовується до SUM(amount) після GROUP BY.

Чим RANK відрізняється від DENSE_RANK?

Обидві дають однаковий ранг рядкам з однаковими значеннями. Різниця в тому, що RANK пропускає наступні номери (1, 2, 2, 4), а DENSE_RANK іде без пропусків (1, 2, 2, 3). Якщо вам потрібен «топ-3 категорії» без дірок у нумерації — беріть DENSE_RANK.

Чому не можна написати WHERE ROW_NUMBER() OVER (…) = 1?

Через логічний порядок виконання запиту: WHERE фільтрує рядки до того, як обчислюються віконні функції, тож у момент фільтрації значення ще не існує. Обгорніть запит у підзапит або CTE (WITH ... AS) і фільтруйте зовні: WHERE rn = 1.

Віконні функції — це повільно?

Зазвичай вони швидші за еквівалентні корельовані підзапити чи self-join, бо СУБД проходить дані меншу кількість разів. Але сортування великих вікон коштує ресурсів: допомагають індекси на колонках із PARTITION BY та ORDER BY і фільтрація зайвих рядків у WHERE до застосування вікна.

Що далі

Ви освоїли найпотужніший інструмент аналітичного SQL: тепер ви вмієте ранжувати рядки, порівнювати періоди та будувати накопичені й ковзні показники без громіздких підзапитів. Саме такі запити найчастіше трапляються в тестових завданнях на позицію дата-аналітика.

Потренуйтеся на своїй демо-базі, перш ніж рухатися далі: знайдіть друге за сумою замовлення кожного клієнта (ROW_NUMBER + підзапит), порахуйте для кожного міста з таблиці customers частку його виручки в загальній (SUM OVER з PARTITION BY) та побудуйте помісячний running total виручки.

На цьому SQL-блок курсу завершено — далі переходимо до візуалізації даних:

• Наступний урок: Що таке Power BI: повний огляд для початківців — установка, інтерфейс, підключення даних і перший візуал.
• Попередній урок: GROUP BY, підзапити та CASE у SQL: практичний гайд — поверніться, якщо агрегатні функції ще викликають питання.
• Повна програма: курс аналітики даних з нуля.