Віконні функції

Що таке віконна функція і чим вона відрізняється від звичайної агрегації?

Віконна функція (window function) рахує значення по набору рядків, але, на відміну від агрегації через GROUP BY, не згортає їх в один рядок. Звичайний SUM поверх GROUP BY бере групу й повертає одне число — деталі окремих рядків зникають. Той самий SUM із OVER (...) бачить той самий набір рядків (він і зветься «вікно»), але повертає результат для кожного рядка окремо, дописуючи його поруч із деталями. Тобто на виході стільки ж рядків, скільки на вході, плюс ще одна колонка — ранг, порядковий номер, сума по групі чи значення з сусіднього рядка. Практичний сенс для QA: останній статус замовлення, наростаючий баланс, пошук дублікатів — усюди групове значення потрібне поруч із кожним рядком, а не замість них, і це задача саме для вікна.

Що вмикає ключове слово OVER?

OVER перемикає функцію у віконний режим — саме воно каже СУБД «рахуй по набору рядків, але не згортай їх». Без OVER SUM(amount) — звичайний агрегат, якому потрібен GROUP BY і який з'їдає деталі групи; з OVER (...) кожен рядок лишається на місці, а результат дописується поруч. Усередині дужок описується саме вікно: PARTITION BY, ORDER BY і рамка. Одне ключове слово повністю змінює семантику функції, тому на співбесіді від нього і відштовхуються, коли просять пояснити «чим вікно відрізняється від агрегації».

З чого складається вікно всередині OVER?

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

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

Усі три нумерують рядки в партиції за ORDER BY, різниця лише в поведінці на «нічиях» (ties) — коли значення сортування однакові. На рівних значеннях ROW_NUMBER() все одно роздає різні номери (1, 2, 3, 4) — який з рівних отримає менший, без тайбрейкера непередбачувано. RANK() присвоює нічиїм спільний ранг і компенсує це пропуском наступного номера: 1, 2, 2, 4. DENSE_RANK() теж дає спільний ранг, але нічого не пропускає: 1, 2, 2, 3. Вибір не косметичний: для дедуплікації потрібен саме ROW_NUMBER (унікальність), а для «топ-N з усіма нічиями» — RANK/DENSE_RANK.

Що роблять LAG і LEAD і що повертає LAG для першого рядка партиції?

LAG(column, offset, default) повертає значення колонки з рядка на offset позицій раніше в межах партиції за порядком ORDER BY (дефолтний зсув — 1), а LEAD — симетрично з рядка попереду. По суті це доступ до сусіднього рядка без self-join: порахувати дельту між значеннями, інтервал між подіями чи знайти розрив у ланцюжку статусів. Для першого рядка партиції попереднього не існує, тому LAG повертає NULL — або те, що передали третім аргументом як default. Тут важлива пастка тризначної логіки: порівняння з NULL дає не false, а NULL, тож необережна умова довкола LAG/LEAD мовчки викине перший рядок групи з перевірки.

У чому різниця між віконною функцією і GROUP BY?

Уся різниця вміщується в пару «згортає / не згортає». GROUP BY повертає один рядок на групу і втрачає доступ до деталей окремих рядків; віконна функція лишає всі рядки на місці й дописує пораховане по групі значення поруч. Через це й фільтрувати результат вони дозволяють по-різному: агрегат по групі можна відсіяти в HAVING, а віконну колонку — лише через обгортання в підзапит або CTE. Вибирають за формою результату: коли достатньо одного підсумкового рядка на групу — агрегація; коли деталі мають лишитися, а групове значення треба дописати до кожного рядка — вікно. І це не конкуренти: цілком нормально спершу агрегувати, а вже агрегований результат ранжувати віконною функцією.

Чим відрізняється SUM OVER (PARTITION BY) від SUM OVER (PARTITION BY ... ORDER BY ...)?

Різницю дає лише ORDER BY всередині вікна. SUM(amount) OVER (PARTITION BY user_id) без ORDER BY рахує тотал по партиції — однакове число в усіх рядках користувача, зручно для частки рядка від суми групи. SUM(amount) OVER (PARTITION BY user_id ORDER BY paid_at) з ORDER BY вмикає наростаючий підсумок (running total) — суму від першого платежу до поточного, тобто накопичуваний баланс, як у банківській виписці. Звідси типовий подив: очікували однаковий тотал, а бачите числа, що ростуть від рядка до рядка — це ORDER BY непомітно ввімкнув рамку «від початку партиції до поточного рядка». Тобто одне й те саме SUM дає два зовсім різні результати залежно від наявності ORDER BY у вікні.

Чому не можна написати WHERE row_number = 1?

Бо на етапі WHERE віконної колонки ще немає — до її розрахунку СУБД просто не дійшла. Причина в логічному порядку виконання запиту: FROM → WHERE → GROUP BY → HAVING → віконні функції → SELECT/DISTINCT → ORDER BY → LIMIT. Оскільки вікно обчислюється після WHERE/GROUP BY/HAVING, єдині місця для віконних функцій — список SELECT та ORDER BY запиту. Тому СУБД відхилить такий запит ще на етапі розбору, а не поверне порожній результат. Рішення — винести розрахунок вікна в підзапит чи CTE (WITH): на наступному рівні запиту ця колонка вже звичайна, і зовнішній WHERE спокійно її фільтрує.

Як видалити дублікати, лишивши один рядок, і чому тут критичний детермінований ORDER BY?

Класичний шаблон: пронумерувати рядки всередині кожної групи «однакових» через ROW_NUMBER() OVER (PARTITION BY <ключ> ORDER BY ...) і лишити лише rn = 1, а решту (rn > 1) — під видалення, обгорнувши все в CTE. Партиція за email об'єднує всі копії одного запису, сортування created_at DESC виводить найсвіжішу на перший номер — саме вона й виживає. Критична деталь — детермінованість ORDER BY: коли сортування йде лише за неунікальним полем, а дві копії мають однаковий created_at, вибір «переможця» віддається на розсуд СУБД і між прогонами може змінюватися. Унікальний тайбрейкер (id) робить результат відтворюваним — без нього чистка поводиться як флакі-тест: сьогодні зникла одна копія, завтра інша. Дисциплінований підхід — спершу зробити SELECT ... WHERE rn > 1 (побачити, скільки й яких дублікатів), а вже потім DELETE.

Як зробити «топ-N на групу» і яка тут пастка з нічиями?

Нумеруємо рядки в кожній партиції через ROW_NUMBER()/RANK() з потрібним ORDER BY, обгортаємо в CTE чи підзапит і фільтруємо зовнішнім WHERE rn <= N. Пастка — у виборі функції на межі списку. Якщо два товари мають однакову ціну на N-му місці, ROW_NUMBER лишить лише один із них (номери N і N+1) і мовчки викине рядок з тим самим значенням; RANK/DENSE_RANK дадуть їм однаковий ранг і лишать обидва. Тобто «топ-3» через ROW_NUMBER і «топ-3» через RANK — це різні відповіді на однакових даних. Тож перш ніж писати запит, з'ясуйте вимогу: рівно N рядків чи всі нічиї на межі — від цього залежить вибір функції, а не від смаку.

Чи можуть віконні функції зменшити кількість рядків на виході?

Ні, ніколи. Віконна функція за визначенням лишає стільки ж рядків, скільки на вході — вона лише дописує колонку, а не фільтрує. SUM(...) OVER (...) не згортає, ROW_NUMBER() не викидає рядки сам по собі. Це джерело поширеної помилки «вікно відфільтрувало зайве, а насправді ні»: щоб реально відкинути рядки за результатом вікна, потрібен окремий WHERE у зовнішньому запиті (через CTE/підзапит). Тому дедуплікація виглядає у два кроки: спочатку ROW_NUMBER пронумерував (усі рядки на місці), потім зовнішній WHERE rn > 1 або DELETE реально прибрав зайве.

У чому різниця рамок RANGE і ROWS і чому це «баг у сумах, якого немає»?

Коли у вікні є ORDER BY, рамка за замовчуванням це RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, а не ROWS. Різниця проявляється на рядках з однаковим значенням сортування: для RANGE всі такі рядки-нічиї рівні між собою, тому накопичена сума в них збігається; ROWS відлічує фізичні рядки один за одним незалежно від збігів значень. Тобто наростаючий підсумок на дублях значень у RANGE і ROWS буде різним. Розбіжність тонка, і перша підозра зазвичай падає на застосунок — хоча помиляється якраз запит перевірки. Якщо потрібен строго порядковий підсумок, задавайте рамку явно: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

У яких СУБД працюють віконні функції?

Це частина стандарту SQL, а не «фіча Postgres»: у PostgreSQL вони живуть багато років, MySQL додав їх у версії 8.0, SQLite — у 3.25. Практичний наслідок для QA: вікна можна використовувати і для звірки на проді (Postgres/MySQL), і в локальній тестовій БД на SQLite, тому їх очікують знати як базовий інструмент, а не екзотику. Тем БД у силабусі ISTQB CTFL немає, тож спиратися варто на офіційну документацію конкретної СУБД. Єдине, про що варто пам'ятати між рушіями, — це нюанси рамок і поведінки на нічиях, але сам синтаксис OVER (PARTITION BY ... ORDER BY ...) однаковий скрізь.

Три кейси, де віконна функція перетворює «звірити руками в Excel» на один запит: перевірка, що UI показує саме останній статус замовлення, звірка наростаючого балансу з виписки та безпечна дедуплікація після кривої міграції. Скрізь — що дивитися і де пастка.

Кейс 1. UI показує останній статус — а який останній?

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

WITH ranked AS (
  SELECT
    order_id,
    status,
    event_at,
    ROW_NUMBER() OVER (
      PARTITION BY order_id
      ORDER BY event_at DESC, id DESC
    ) AS rn
  FROM order_events
)
SELECT order_id, status
FROM ranked
WHERE rn = 1;

Тепер результат запиту — це «джерело істини» для оракула: рівно один рядок на замовлення з тим статусом, який має показати UI. У Playwright-тесті цей запит стає перевіркою проти екрана:

// db.query повертає масив рядків із SQL вище
const expected = await db.query(latestStatusSql);
const byOrder = new Map(expected.map((r) => [r.order_id, r.status]));

await page.goto('/orders');
for (const [orderId, status] of byOrder) {
  const row = page.getByTestId(`order-row-${orderId}`);
  await expect(row.getByTestId('status')).toHaveText(status);
}

Що дивитися і чому:

• ORDER BY event_at DESC ставить найсвіжіший першим, rn = 1 його лишає. Це і є визначення «останнього» — його не можна віддати GROUP BY, бо той не вміє повертати рядок, який дав максимум.
• id DESC як тайбрейкер — не косметика. Якщо дві події замовлення мають однаковий event_at, без унікального другого ключа rn = 1 дістанеться недетермінованому рядку, і тест почне флакати «через раз».
• Запит — оракул, UI — об'єкт перевірки. Якщо вони розійшлися, спочатку переконайтеся, що баг у застосунку, а не в тому, що ваш ORDER BY не збігається з логікою сортування продукту.

Кейс 2. Наростаючий баланс з виписки — і пастка RANGE

Користувач бачить у виписці «баланс наростаючим підсумком», і його треба звірити рядок за рядком. Це прямий привід дістати SUM ... OVER (ORDER BY ...) без експорту в Excel.

SELECT
  paid_at,
  amount,
  SUM(amount) OVER (
    PARTITION BY user_id
    ORDER BY paid_at, id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_balance
FROM payments
WHERE user_id = 42
ORDER BY paid_at, id;

Що дивитися і чому:

• ORDER BY у вікні — це вмикач наростаючого підсумку. Приберіть його — і SUM дасть однаковий тотал у кожному рядку замість накопичуваного балансу. Саме тут найчастіше дивуються «чому замість суми щось інше на кожному рядку».
• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW заданий явно навмисно. За замовчуванням із ORDER BY працює RANGE: якби вікно сортувалось лише за paid_at, два платежі з однаковим часом стали б «нічиїми» й отримали однакову накопичену суму. Тут тайбрейкер id уже прибирає нічиї, але явний ROWS фіксує намір «строго рядок за рядком» — і врятує, якщо тайбрейкер із вікна колись приберуть.
• Тайбрейкер id у вікні й у зовнішньому ORDER BY мають збігатися. Інакше порядок накопичення в запиті й порядок рядків на екрані розійдуться на нічиях за часом.

Точний збіг балансу рядок у рядок означає, що і сума, і порядок у продукті збігаються з очікуванням; розбіжність рівно на рядках з однаковим часом — майже завжди сигнал про RANGE vs ROWS, а не про помилку в даних.

Кейс 3. Дублікати після міграції — спершу подивитися, потім видаляти

Після кривої міграції в таблицю заповзли дублікати за email, і їх треба почистити, лишивши по одному. Головна дисципліна: віконна функція сама нічого не видаляє (вона не зменшує кількість рядків), тому спершу — діагностичний SELECT, і лише переконавшись — DELETE.

WITH ranked AS (
  SELECT
    id,
    email,
    ROW_NUMBER() OVER (
      PARTITION BY email
      ORDER BY created_at DESC, id DESC
    ) AS rn
  FROM users
)
-- КРОК 1: діагностика — скільки й яких дублікатів
SELECT email, count(*) AS copies
FROM ranked
WHERE rn > 1
GROUP BY email
ORDER BY copies DESC;

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

WITH ranked AS (
  SELECT id,
    ROW_NUMBER() OVER (
      PARTITION BY email
      ORDER BY created_at DESC, id DESC
    ) AS rn
  FROM users
)
-- КРОК 2: видаляємо все, крім найсвіжішого
DELETE FROM users
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

Що дивитися і чому:

• WHERE rn > 1, а не rn = 1. Лишаємо rn = 1 (найсвіжіший), видаляємо решту. Переплутати умову — значить видалити саме те, що хотіли зберегти.
• Детермінований ORDER BY created_at DESC, id DESC — умова відтворюваності. Без унікального id дві копії, створені в ту саму мілісекунду, дадуть недетермінований «переможець», і повторний прогін чистки лишить інший рядок — та сама природа, що й флакі-тест.
• SELECT перед DELETE — це не педантизм. Віконна колонка не фільтрує рядки сама; поки не обгорнули в зовнішній WHERE/DELETE, жоден рядок не зникне — тож безпечно спочатку подивитися, а вже потім чіпати дані.

Механізм і OVER

• Можу пояснити головну відмінність від GROUP BY: віконна функція рахує по набору рядків, але не згортає їх — на виході стільки ж рядків, скільки на вході.
• Розумію, що саме OVER вмикає віконний режим: без нього той самий SUM вимагає GROUP BY, з ним — лишає всі рядки на місці.
• Знаю три необов'язкові частини вікна: PARTITION BY (групи; без нього вся вибірка це одна велика партиція, а функція «скидається» на межах), ORDER BY усередині OVER (порядок у групі), рамка (ROWS/RANGE).
• Пам'ятаю, що ORDER BY усередині OVER — це не ORDER BY усього запиту, і плутати їх не можна.

Нумерація: ROW_NUMBER / RANK / DENSE_RANK

• Знаю, що всі три різняться лише поведінкою на нічиях (ties), і відтворюю послідовності: ROW_NUMBER → 1, 2, 3, 4 (унікальні), RANK → 1, 2, 2, 4 (з пропуском), DENSE_RANK → 1, 2, 2, 3 (без пропуску).
• Знаю, коли який доречний: дедуплікація — ROW_NUMBER (потрібна унікальність), «топ-N з усіма нічиями» — RANK/DENSE_RANK.
• Розумію пастку топ-N: ROW_NUMBER з rn <= 3 мовчки викине рядок-нічию на межі, RANK лишить обидва.

LAG / LEAD і агрегати як віконні

• Можу пояснити LAG/LEAD як «погляд на сусідній рядок»: попереднє/наступне значення за ORDER BY для «порівняй із сусідом».
• Пам'ятаю, що для першого рядка партиції LAG повертає NULL (або default третім аргументом), і що порівняння з NULL дає NULL, а не false.
• Знаю, що будь-який агрегат (SUM, COUNT, AVG, MIN, MAX) стає віконним з OVER (...), і розрізняю два режими: тотал по партиції (без ORDER BY у вікні) проти наростаючого підсумку (з ORDER BY у вікні).
• Знаю нюанс рамки: за замовчуванням із ORDER BY працює RANGE, і рядки з однаковим значенням отримують однакову накопичену суму; строго порядковий підсумок дає явний ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Де рахується вікно і чому не можна WHERE

• Можу пояснити логічний порядок виконання: WHERE/GROUP BY/HAVING відпрацьовують до віконних функцій.
• Знаю, що віконні функції дозволені лише в SELECT і ORDER BY запиту, а WHERE row_number = 1 не просто не працює — не компілюється.
• Розумію обхід: обгорнути в підзапит або CTE (WITH), щоб віконна колонка стала «звичайною», і фільтрувати зовнішнім WHERE.

Дедуплікація

• Знаю шаблон дослівно: ROW_NUMBER() OVER (PARTITION BY <ключ> ORDER BY ...) у CTE, лишаємо rn = 1, видаляємо rn > 1.
• Розумію, чому ORDER BY має бути детермінованим: без унікального тайбрейкера (id) СУБД лишає недетермінований рядок, і на наступному прогоні зникне інша копія — тому спершу SELECT ... WHERE rn > 1 для діагностики, і лише потім DELETE.

Віконні vs GROUP BY і типові пастки

• Можу однією фразою відрізнити: GROUP BY — один рядок на групу (згортає), вікно — всі рядки на місці (не згортає); тому агрегат по групі фільтрують у HAVING, а віконну колонку — лише через підзапит/CTE.
• Пам'ятаю: віконна функція ніколи не зменшує кількість рядків — щоб відкинути рядки, потрібен окремий WHERE/CTE.
• Розпізнаю пастку «per-user тотал перетворився на глобальний» — забули PARTITION BY, і функція взяла всю таблицю за одну партицію.
• Знаю, що віконні функції — стандарт SQL: PostgreSQL (давно), MySQL (8.0), SQLite (3.25).