Віконна функція рахує агрегат або позицію по групі рядків (вікні), не згортаючи їх у один рядок: ROW_NUMBER і RANK дають нумерацію всередині PARTITION BY, LAG бере значення попереднього рядка, SUM() OVER (ORDER BY …) дає running total — усе це замінює корельовані підзапити й самоджойни одним проходом.
Віконна функція обчислює значення по набору рядків, повʼязаних із поточним, і при цьому не згортає їх: на виході стільки ж рядків, скільки на вході, просто зʼявляється додаткова колонка. Набір задає речення 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.
-- Топ-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 + PARTITION BY для top-N, SUM() OVER для наростаючого підсумку, LAG для дельти між сусідніми рядками — і одразу згадайте, що фільтрувати по вікну можна тільки зовні.