Кожен рядок лишається на місці
GROUP BY company_id і COUNT(*) дають рядок на компанію. COUNT(*) OVER (PARTITION BY company_id) дає ту саму кількість, але поруч з кожною вакансією: Senior PHP, Laravel Dev і Team Lead отримують по 3, Middle PHP 1. Слово OVER і робить функцію віконною, а дужки після нього описують : які рядки вона бачить.
PARTITION BY ділить рядки на групи, і вікно ніколи не виходить за межі своєї групи. ORDER BY у вікні задає порядок усередині групи, і від нього залежать ROW_NUMBER, RANK, DENSE_RANK та функції зсуву LAG і LEAD, що беруть значення з попереднього чи наступного рядка. Без PARTITION BY вся вибірка одна група.
Коли в OVER є ORDER BY, агрегати SUM, COUNT і AVG рахують не всю групу, а : за замовчуванням від початку групи до поточного рядка. Так виходить наростаючий підсумок. Віконні функції виконуються після WHERE, GROUP BY і HAVING, тому в WHERE їх писати не можна: їхнього значення там ще не існує. Є в MySQL з 8.0, у PostgreSQL давно і в SQLite з 3.25.
Хто отримає друге місце
Вакансії із зарплатою, від більшої до меншої: Team Lead 6000, Senior PHP і Laravel Dev по 5000, Middle PHP 3000. Три функції ранжування по-різному обходяться з нічиєю, і від цього залежить, скільки рядків поверне WHERE n <= 2.
ROW_NUMBER()
1, 2, 3, 4
Кожен рядок свій номер, нічия розбивається випадково. Senior PHP і Laravel Dev можуть помінятися місцями між запусками, якщо не додати в ORDER BY id.
RANK()
1, 2, 2, 4
Рівні зарплати ділять місце, наступне пропускається. WHERE r <= 2 поверне три вакансії.
DENSE_RANK()
1, 2, 2, 3
Без пропусків. Зручно для «три найвищі зарплати», коли нічиїх може бути багато.
ROW_NUMBER() OVER (ORDER BY salary_max DESC, id)
1, 2, 3, 4
Унікальна колонка в кінці ORDER BY робить порядок однозначним. Для пагінації і top-N це обовʼязково.
ORDER BY salary_max DESC з NULL
залежить від бази
Вакансія без зарплати в PostgreSQL отримає перше місце, бо NULL там більший за все, а в MySQL і SQLite останнє. Тут NULL прибрано через WHERE salary_max IS NOT NULL.
Однаковий результат у MySQL 8.0, PostgreSQL 16 і SQLite 3.45: Team Lead 1 · 1 · 1, Senior PHP 2 · 2 · 2, Laravel Dev 3 · 2 · 2, Middle PHP 4 · 4 · 3.
Остання вакансія кожної компанії
Класична задача: по одному рядку на групу, найсвіжіший. Через GROUP BY її не розвʼязати, бо MAX(published_at) дасть дату, а не решту колонок того рядка. ROW_NUMBER() нумерує вакансії кожної компанії від найновішої, і лишається відібрати номер 1.
Але WHERE ROW_NUMBER() ... = 1 не пройде: WHERE виконується раніше за віконні функції. Тому запит загортають у підзапит чи CTE і фільтрують ззовні. PostgreSQL для «одного на групу» має ще DISTINCT ON, коротший і без підзапиту, але лише в ньому.
+------------+-------------+--------------+---+ | company_id | title | published_at | n | +------------+-------------+--------------+---+ | 1 | Laravel Dev | 2026-09-10 | 1 | | 2 | Middle PHP | 2026-09-15 | 1 | +------------+-------------+--------------+---+ -- WHERE ROW_NUMBER() OVER (...) = 1: ERROR 3593 (HY000): You cannot use the window function 'row_number' in this context.
Той самий запит працює в PostgreSQL і SQLite без змін. Для двох останніх вакансій кожної компанії досить n <= 2.
company_id | title | published_at
------------+-------------+--------------
1 | Laravel Dev | 2026-09-10
2 | Middle PHP | 2026-09-15
(2 rows)
-- WHERE ROW_NUMBER() OVER (...) = 1:
ERROR: window functions are not allowed in WHERE DISTINCT ON лишає перший рядок кожного company_id у порядку ORDER BY, тому ORDER BY має починатися з тих самих колонок. Лише один рядок на групу; для top-3 потрібен ROW_NUMBER.
company_id title published_at ---------- ----------- ------------ 1 Laravel Dev 2026-09-10 2 Middle PHP 2026-09-15 -- DISTINCT ON (company_id): Parse error: near "ON": syntax error
CTE замість підзапиту читається легше, а виконується так само. DISTINCT ON SQLite не знає, тому код, який ганяють у тестах на SQLite, його не використовує.
Наростаючий підсумок і рамка за замовчуванням
SUM(...) OVER (ORDER BY created_at) рахує суму від першого рядка до поточного. Але за замовчуванням рамка це RANGE, а не ROWS: рядки з однаковим created_at для неї рівноправні, і всі вони входять у рамку одразу. Два відгуки за 1 жовтня отримують однаковий підсумок 2, хоча «по рядках» це 1 і 2.
Ця ж рамка ламає LAST_VALUE: «до поточного рядка» означає, що останнім значенням буде сам поточний рядок або його рівні. Щоб побачити справжній кінець групи, рамку розширюють до UNBOUNDED FOLLOWING. Поведінка однакова в усіх трьох базах, тому вкладки нижче показують три різні приклади, і кожен дає той самий результат у будь-якій з них.
id | created_at | range_default | rows_frame -----+------------+---------------+------------ 100 | 2026-10-01 | 2 | 1 101 | 2026-10-01 | 2 | 2 102 | 2026-10-02 | 4 | 3 103 | 2026-10-02 | 4 | 4 104 | 2026-10-03 | 5 | 5 (5 rows)
Для підсумку по днях RANGE саме те, що треба: день рахується цілим. Для нумерації рядків потрібен ROWS або ROW_NUMBER().
+-------------+------------+--------------+-----------+ | title | salary_max | last_default | last_full | +-------------+------------+--------------+-----------+ | Middle PHP | 3000 | Middle PHP | Team Lead | | Senior PHP | 5000 | Laravel Dev | Team Lead | | Laravel Dev | 5000 | Laravel Dev | Team Lead | | Team Lead | 6000 | Team Lead | Team Lead | +-------------+------------+--------------+-----------+
last_default повертає поточний рядок або останній з рівних йому, а не найбільшу зарплату. Це не баг MySQL: PostgreSQL і SQLite дають ту саму таблицю.
created_at per_day running ---------- ------- ------- 2026-10-01 2 2 2026-10-02 2 4 2026-10-03 1 5
Віконна функція над результатом GROUP BY: спершу групи по днях, потім наростаючий підсумок по групах. SUM(COUNT(*)) OVER виглядає дивно, але це законний запис у всіх трьох базах.
Остання вакансія кожної компанії
Той самий ROW_NUMBER() ... WHERE n = 1 на 200 000 вакансій. Віконній функції потрібні рядки, впорядковані за PARTITION BY і ORDER BY вікна, тож план або сортує, або читає готовий порядок з індексу (company_id, published_at DESC, id DESC). PostgreSQL з версії 15 ще й бачить умову n = 1 зовні і перестає нумерувати групу, щойно номер перевищив 1: це рядок Run Condition. MySQL 8.0 нумерує все, складає 200 000 рядків у тимчасову таблицю і лише потім відбирає перші.
SELECT * FROM (SELECT id, company_id, published_at, ROW_NUMBER() OVER (PARTITION BY company_id ORDER BY published_at DESC, id DESC) AS n FROM vacancies) t WHERE n = 1;Subquery Scan on t (actual time=0.636..321.077 rows=8821 loops=1)
Filter: (t.n = 1)
-> WindowAgg (actual time=0.634..319.695 rows=8821 loops=1)
Run Condition: (row_number() OVER (?) <= 1)
-> Incremental Sort (actual time=0.592..293.919 rows=200000 loops=1)
Sort Key: vacancies.company_id, vacancies.published_at DESC, vacancies.id DESC
Presorted Key: vacancies.company_id
-> Index Scan using vacancies_company_id_index on vacancies (rows=200000 loops=1)
Execution Time: 322.202 ms SELECT * FROM (SELECT id, company_id, published_at, ROW_NUMBER() OVER (PARTITION BY company_id ORDER BY published_at DESC, id DESC) AS n FROM vacancies) t WHERE n = 1;Subquery Scan on t (actual time=0.048..53.978 rows=8821 loops=1)
Filter: (t.n = 1)
-> WindowAgg (actual time=0.046..53.117 rows=8821 loops=1)
Run Condition: (row_number() OVER (?) <= 1)
-> Index Only Scan using vacancies_company_published on vacancies (actual time=0.039..34.607 rows=200000 loops=1)
Heap Fetches: 0
Execution Time: 54.376 ms SELECT * FROM (SELECT id, published_at, ROW_NUMBER() OVER (ORDER BY published_at DESC, id DESC) AS n FROM vacancies WHERE company_id = 42) t WHERE n <= 3;WindowAgg (actual time=0.018..0.020 rows=3 loops=1)
Run Condition: (row_number() OVER (?) <= 3)
-> Index Only Scan using vacancies_company_published on vacancies (actual time=0.014..0.014 rows=4 loops=1)
Index Cond: (company_id = 42)
Execution Time: 0.042 ms PostgreSQL 16, 200 000 вакансій, індекс vacancies_company_published на (company_id, published_at DESC, id DESC). Вивід знято зі справжньої бази, оцінки cost і Planning Time прибрано.
SELECT * FROM (SELECT id, company_id, published_at, ROW_NUMBER() OVER (PARTITION BY company_id ORDER BY published_at DESC, id DESC) AS n FROM vacancies) t WHERE n = 1;-> Index lookup on t using <auto_key0> (n=1) (actual time=1241..1262 rows=8821 loops=1)
-> Materialize (actual time=1241..1241 rows=200000 loops=1)
-> Window aggregate: row_number() OVER (PARTITION BY vacancies.company_id
ORDER BY vacancies.published_at desc,vacancies.id desc ) (actual time=239..323 rows=200000 loops=1)
-> Sort: vacancies.company_id, vacancies.published_at DESC, vacancies.id DESC
(actual time=239..256 rows=200000 loops=1)
-> Index scan on vacancies using vacancies_company_published (rows=200000 loops=1) MySQL 8.0.46, InnoDB, ті самі 200 000 вакансій і той самий індекс. Оцінки cost прибрано.
- WindowAgg
- Вузол, що рахує віконні функції. Біля нього rows=8821: по рядку на кожне значення company_id, разом з групою NULL. Решту рядків Run Condition відсік ще всередині.
- Run Condition
- Умову з зовнішнього WHERE PostgreSQL переніс у вікно. ROW_NUMBER у групі лише зростає, тож після першого рядка решту групи можна не віддавати нагору.
- Incremental Sort
- Індекс на company_id дав рядки, впорядковані за першою колонкою, і досортувати лишилося всередині кожної компанії. Складений індекс під вікно прибирає сортування зовсім: 322 мс проти 54.
- rows=4
- Для однієї компанії з індексом база прочитала 4 записи індексу, щоб віддати 3 рядки: Run Condition зупинив читання.
- Window aggregate
- Нумерує всі 200 000 рядків: умову n = 1 MySQL 8.0 у вікно не переносить.
- Sort
- Навіть з індексом у потрібному порядку MySQL тут сортує сам. Без індексу план той самий, лише Table scan замість Index scan.
- Materialize
- Підзапит з віконною функцією складено в тимчасову таблицю на 200 000 рядків, і на ній збудовано ключ <auto_key0> за n. Більша частина з 1,2 секунди йде саме сюди.
Перший перегляд кожного відвідувача, у PHP
Звіт аналітики будує джерела трафіку, сторінки входу й глибину перегляду з першого перегляду кожного відвідувача-дня. У SQL це ROW_NUMBER() OVER (PARTITION BY visitor ORDER BY occurred_at, id) = 1, а кількість переглядів COUNT(*) OVER (PARTITION BY visitor). Код сайту робить те саме в PHP: бере перегляди періоду, впорядковані за occurred_at і id, і оператор ??= лишає для кожного visitor лише перший рядок, а views++ рахує решту. Правила проєкту пояснюють вибір тим, що тести йдуть на SQLite, де немає DISTINCT ON. ROW_NUMBER() на SQLite з 3.25 працює, тож ціна PHP-версії інша: усі перегляди періоду приходять у памʼять процесу, а SQL-версія віддала б по рядку на відвідувача.
.ai/rules/analytics.mdПравило проєкту: перший перегляд кожного відвідувача-дня рахується в PHP, щоб тести на SQLite працювали, без DISTINCT ON.content/learn/databases/hub.yamlБлок «Як у PHP Ukraine» на головній розділу показує SQL-версію цього ж запиту з ROW_NUMBER() і DISTINCT ON, який у тестах на SQLite впав би.Номери, суми й останні значення не ті
Віконна функція в WHERE
WHERE ROW_NUMBER() OVER (...) = 1 дає помилку в усіх трьох базах: WHERE виконується до віконних функцій. HAVING теж не допоможе.
Натомість: Загорніть запит у підзапит або CTE і фільтруйте ззовні. У PostgreSQL для одного рядка на групу є DISTINCT ON.
ORDER BY у вікні без унікальної колонки
ROW_NUMBER() OVER (ORDER BY published_at) при двох вакансіях з однаковою датою нумерує їх як вийде. Між запусками і між MySQL та PostgreSQL порядок може відрізнятися, і top-N повертає різні рядки.
Натомість: Додайте в кінець ORDER BY первинний ключ: ORDER BY published_at DESC, id DESC.
LAST_VALUE з рамкою за замовчуванням
LAST_VALUE(title) OVER (ORDER BY salary_max) повертає поточний рядок, а не останній у групі: рамка за замовчуванням закінчується на поточному рядку.
Натомість: Розширте рамку: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Або візьміть FIRST_VALUE з протилежним порядком.
Наростаючий підсумок, що стрибає
SUM(amount) OVER (ORDER BY day) для кількох рядків за день дає всім однаковий підсумок, бо рамка RANGE вважає рівні значення одним кроком.
Натомість: Для підсумку «по рядках» пишіть ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW і додайте id в ORDER BY. Для підсумку по днях спершу GROUP BY day.
Зайвий самозвʼязок замість LAG
Різницю з попереднім рядком рахують через JOIN таблиці з самою собою за умовою «найбільша дата, менша за поточну». Це корельований підзапит на кожен рядок.
Натомість: LAG(published_at) OVER (PARTITION BY company_id ORDER BY published_at) бере попередній рядок за один прохід.
- Вікно
- Набір рядків, який бачить віконна функція для поточного рядка. Задається в OVER (...) через PARTITION BY, ORDER BY і рамку. Однакове вікно для кількох функцій можна винести в WINDOW w AS (...).
- Функції ранжування
- ROW_NUMBER дає 1, 2, 3 без повторів. RANK дає однакове місце рівним значенням і пропускає наступні: 1, 2, 2, 4. DENSE_RANK не пропускає: 1, 2, 2, 3.
- Рамка
- Підмножина групи, яку бачать агрегати і FIRST_VALUE, LAST_VALUE. За замовчуванням з ORDER BY це RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, без ORDER BY уся група.
Питання зі співбесід
GROUP BY стискає кожну компанію в один рядок, і в SELECT лишаються лише колонки групування й агрегати. OVER лишає всі рядки вакансій і додає до кожного кількість вакансій її компанії, тож поруч можна вибрати title, дату й будь-яку іншу колонку.
На рівних значеннях: ROW_NUMBER дає різні номери, RANK однакові з пропуском (1, 2, 2, 4), DENSE_RANK однакові без пропуску (1, 2, 2, 3). Для рівно трьох вакансій на компанію ROW_NUMBER з унікальною колонкою в ORDER BY. Для трьох найвищих рівнів зарплати, скільки б вакансій на них не було, DENSE_RANK.
Коли в OVER є ORDER BY, рамка за замовчуванням RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE вважає всі рядки з однаковим day рівними поточному, тож вони одразу входять у суму. Для покрокового підсумку потрібен ROWS і унікальний порядок.
Подивитися план. Віконній функції потрібен порядок PARTITION BY і ORDER BY, і індекс на (company_id, published_at DESC, id DESC) прибирає сортування в PostgreSQL, а з 15-ї версії Run Condition ще й не віддає нагору зайві рядки. MySQL 8.0 все одно нумерує і матеріалізує всю таблицю, тому там допомагає інший запис: LATERAL-підзапит з ORDER BY ... LIMIT 1 на кожну компанію (MySQL 8.0.14+, PostgreSQL) або DISTINCT ON у PostgreSQL. Якщо запит дуже частий, останню вакансію зберігають у компанії окремою колонкою.