Що таке віконні функції і коли вони замінюють підзапити?

Пояснення

Віконна функція обчислює значення по набору рядків, повʼязаних із поточним, і при цьому не згортає їх: на виході стільки ж рядків, скільки на вході, просто зʼявляється додаткова колонка. Набір задає речення OVER: PARTITION BY ділить результат на незалежні розділи (нумерація починається заново для кожного user_id), ORDER BY задає порядок усередині розділу, а рамка (ROWS/RANGE) визначає, які саме сусідні рядки потрапляють у розрахунок. Це і є принципова відмінність від GROUP BY, який із десяти замовлень користувача робить один рядок: вікно лишає всі десять і кожному дописує ранг, суму або значення сусіда.

Три функції покривають більшість практичних задач. ROW_NUMBER() дає суцільні унікальні номери 1, 2, 3 — навіть якщо значення однакові, порядок між ними база обере довільно. RANK() при ничиїй ставить однаковий ранг і пропускає наступні (1, 1, 3), DENSE_RANK() не пропускає (1, 1, 2); різниця важлива, коли треба «топ-3 з урахуванням рівних результатів». LAG(col, 1, default) бере значення з попереднього рядка розділу, LEAD — з наступного, і третій аргумент рятує від NULL на межі розділу. Раніше те саме писали корельованим підзапитом (SELECT count(*) FROM orders o2 WHERE o2.user_id = o.user_id AND o2.total > o.total) або самоджойном по n - 1: обидва читають таблицю багато разів, вікно — один раз із сортуванням.

Ключовий момент, на якому валяться на співбесіді, — момент обчислення. Віконні функції рахуються після FROM, WHERE, GROUP BY і HAVING, але до ORDER BY і LIMIT. Тому у WHERE їх писати не можна: WHERE row_number() OVER (...) <= 3 дає помилку і в MySQL, і в PostgreSQL. Канонічний шаблон top-N у групі — CTE або похідна таблиця, де вікно живе в SELECT, і зовнішній запит із WHERE rn <= 3. Наслідок із того самого правила корисний і в інший бік: усе, що відсіює WHERE, до вікна взагалі не доходить, тому WHERE status = 'paid' всередині CTE звужує розділ, а не фільтрує вже пронумеровані рядки.

Running total — це агрегат із рамкою: sum(total) OVER (PARTITION BY user_id ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Явний ROWS тут не прикраса. Якщо рамку не вказати, стандарт наказує RANGE UNBOUNDED PRECEDING AND CURRENT ROW, а RANGE працює зі значеннями ORDER BY: усі рядки з однаковим created_at вважаються однією позицією й отримують однакову суму, що включає їх усіх. На секундній точності часу це майже непомітно, на даті — ламає звіт. Без ORDER BY рамка охоплює весь розділ, і sum(total) OVER (PARTITION BY user_id) дає загальну суму користувача в кожному його рядку — зручно, щоб порахувати частку замовлення від обороту без другого запиту.

Межі теж варто назвати. Віконні функції є в PostgreSQL із 8.4, у MySQL з 8.0, MariaDB з 10.2, SQLite з 3.25 — на легасі MySQL 5.7 доведеться повертатись до підзапитів. Продуктивність не безкоштовна: PARTITION BY user_id ORDER BY total DESC без індексу (user_id, total) дає в плані Sort над повним скануванням, і на великій таблиці це дорожче за альтернативу. Для «одного найкращого рядка на групу» майже завжди виграє DISTINCT ON у PostgreSQL або LATERAL із LIMIT 1 (MySQL 8.0.14+), бо вони читають по кілька рядків з індексу замість сортування всього розділу. Тож правило просте: вікно — коли потрібні всі рядки з додатковим контекстом, GROUP BY — коли потрібен один рядок на групу, а вибір між вікном і LATERAL вирішує EXPLAIN.

КодSQL

-- Топ-3 замовлення кожного користувача + наростаючий підсумок + дельта до попереднього
WITH ranked AS (
    SELECT
        o.id,
        o.user_id,
        o.total,
        o.created_at,
        -- унікальні номери 1,2,3… всередині кожного user_id
        row_number() OVER (PARTITION BY o.user_id ORDER BY o.total DESC) AS rn,
        -- при однакових сумах дає однаковий ранг і розрив: 1,1,3
        rank()       OVER (PARTITION BY o.user_id ORDER BY o.total DESC) AS rnk,
        -- сума всіх попередніх замовлень користувача; ROWS, а не дефолтний RANGE,
        -- інакше рядки з однаковим created_at отримають однакову суму
        sum(o.total) OVER (
            PARTITION BY o.user_id ORDER BY o.created_at
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS running_total,
        -- сума попереднього замовлення; 0 для першого рядка користувача
        lag(o.total, 1, 0) OVER (PARTITION BY o.user_id ORDER BY o.created_at) AS prev_total
    FROM orders o
    WHERE o.status = 'paid'          -- WHERE відпрацьовує ДО обчислення вікна
)
SELECT id, user_id, total, running_total, total - prev_total AS diff
FROM ranked
WHERE rn <= 3                        -- фільтр по вікну можливий лише в зовнішньому запиті
ORDER BY user_id, rn;

-- Індекс, який прибирає Sort із плану WindowAgg
CREATE INDEX orders_user_total ON orders (user_id, total DESC);

-- Для топ-1 у групі дешевше: PostgreSQL читає по одному рядку на користувача
SELECT DISTINCT ON (user_id) user_id, id, total
FROM orders
WHERE status = 'paid'
ORDER BY user_id, total DESC;

Що хоче почути інтервʼюер

Що віконна функція не зменшує кількість рядків, на відміну від GROUP BY: кожен вхідний рядок лишається і отримує додаткову колонку.

Різницю ROW_NUMBER (завжди унікальні 1, 2, 3), RANK (при ничиїй однакове число і розрив: 1, 1, 3) і DENSE_RANK (1, 1, 2).

Що вікно рахується після WHERE, GROUP BY і HAVING, тому фільтрувати по ROW_NUMBER можна лише в зовнішньому запиті чи CTE — це і є типовий шаблон top-N у групі.

Що SUM() OVER (ORDER BY …) дає running total, і що за замовчуванням рамка RANGE UNBOUNDED PRECEDING включає всі рядки-однолітки з тим самим значенням ORDER BY, тому при дублікатах треба явно писати ROWS.

Що LAG/LEAD зі значеннями попереднього й наступного рядка замінюють самоджойн по `n - 1` і мають третій аргумент — значення за замовчуванням.

Версії: MySQL 8.0, MariaDB 10.2, PostgreSQL 8.4+, SQLite 3.25 — у MySQL 5.7 віконних функцій немає, і це впливає на вибір рішення для легасі-проєкту.

Типові помилки

Писати `WHERE row_number() OVER (...) <= 3` — віконні функції у WHERE і HAVING заборонені, потрібен підзапит або CTE.

Плутати ROW_NUMBER і RANK: брати RANK для пагінації й отримувати дірки в нумерації, або ROW_NUMBER для «топ-3 з урахуванням ничиїх» і випадково відрізати один із рівних результатів.

Забувати PARTITION BY і отримувати наскрізну нумерацію по всій таблиці замість нумерації всередині користувача.

Писати `SUM(amount) OVER (ORDER BY created_at)` для running total на даних із однаковими датами й дивуватися, що кілька рядків мають однакову суму: дефолтна рамка RANGE, а не ROWS.

Вважати, що віконна функція завжди швидша: PARTITION BY і ORDER BY без відповідного індексу дають Sort у плані, і для top-1 у групі LATERAL або DISTINCT ON часто дешевші.

Замінювати GROUP BY віконним агрегатом і дивуватися дублікатам: вікно рядки не згортає, потрібен ще DISTINCT або зовнішній фільтр.

ПОРАДА

Скажіть одним реченням: «GROUP BY згортає рядки, вікно лишає їх на місці й додає колонку». Далі назвіть три робочі шаблони — ROW_NUMBER + PARTITION BY для top-N, SUM() OVER для наростаючого підсумку, LAG для дельти між сусідніми рядками — і одразу згадайте, що фільтрувати по вікну можна тільки зовні.

Додаткові питанняЗ ВІДПОВІДЯМИ

Логічний порядок обробки: FROM і JOIN, потім WHERE, GROUP BY, HAVING, і лише після цього обчислюються віконні функції, а вже за ними SELECT DISTINCT, ORDER BY, LIMIT. На момент WHERE номера ще не існує, тому і MySQL, і PostgreSQL повертають помилку («Window function is allowed only in SELECT list and ORDER BY» / «window functions are not allowed in WHERE»). Робочий шаблон: CTE або похідна таблиця з ROW_NUMBER() у SELECT, а фільтр `WHERE rn <= 3` у зовнішньому запиті. Якщо потрібні всі рядки при ничиїй — беріть RANK() замість ROW_NUMBER.

ROWS рахує фізичні рядки: `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` — це всі рядки до поточного включно, і два рядки з однаковою датою отримають різні суми. RANGE рахує за значенням ORDER BY: усі рядки з тим самим значенням (peers) вважаються однією позицією, тому вони отримають однакову суму, що включає їх усіх. Коли є ORDER BY і немає явної рамки, за замовчуванням береться саме `RANGE UNBOUNDED PRECEDING AND CURRENT ROW` — звідси більшість сюрпризів із running total. Без ORDER BY рамка — весь розділ, тому `SUM(x) OVER (PARTITION BY user_id)` дає загальну суму по користувачу в кожному рядку. PostgreSQL 11+ додав ще GROUPS і RANGE зі зміщенням (`RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW`), MySQL 8 підтримує RANGE зі зміщенням, але не GROUPS.

Найчастіше на top-1 у групі по великій таблиці: ROW_NUMBER змушує прочитати й відсортувати всі рядки розділу, навіть якщо потрібен один. У PostgreSQL для цього є `DISTINCT ON (user_id) … ORDER BY user_id, total DESC`, а універсально — `LATERAL` (у MySQL 8.0.14+ теж є LATERAL) з підзапитом `ORDER BY total DESC LIMIT 1` по індексу `(user_id, total DESC)`: база робить index seek на кожного користувача замість повного сортування. Також вікно нічого не дає, якщо результат усе одно згортається в один рядок на групу — там дешевший звичайний GROUP BY. Перевіряти треба EXPLAIN: ознака проблеми — великий Sort або WindowAgg над Seq Scan.

Через іменоване вікно: `SELECT row_number() OVER w, sum(total) OVER w FROM orders WINDOW w AS (PARTITION BY user_id ORDER BY created_at)`. Речення WINDOW стоїть після HAVING і перед ORDER BY, підтримується в PostgreSQL і в MySQL 8.0. Можна й успадковувати: `WINDOW w2 AS (w ROWS UNBOUNDED PRECEDING)` — та сама розбивка й сортування, інша рамка. Це не лише коротше: план стає читабельнішим, бо база бачить одне вікно й робить одне сортування замість кількох WindowAgg.

Три варіанти. Перший — корельований підзапит `WHERE (SELECT count(*) FROM orders o2 WHERE o2.user_id = o.user_id AND o2.total > o.total) < 3`, простий, але квадратичний по розміру групи. Другий — самоджойн із агрегатом або трюк зі змінними користувача (`@rn := IF(@u = user_id, @rn + 1, 1)`), який працює, але офіційно не має гарантованого порядку обчислення і в 8.0 оголошений застарілим. Третій і найчастіше правильний — оновити MySQL: 5.7 без розширеної підтримки з жовтня 2023, а 8.0 дає і віконні функції, і CTE. Для звітів альтернатива — рахувати наростаючі підсумки в матеріалізованій таблиці, а не на льоту.

оновлено 4 вересня 2026 · ліцензія CC-BY-SA-4.0 Знайшли неточність? Напишіть →