Пояснення пагінації в PostgreSQL з ROW_NUMBER

Home » Python » Пояснення пагінації в PostgreSQL з ROW_NUMBER

Розбиття великих наборів результатів у PostgreSQL на сторінки на перший погляд може здаватися простим, але для ефективного та правильного виконання цього завдання, особливо коли у вашому стовпці впорядкування є зв'язки, потрібно трохи більше, ніж просто встановлення LIMIT/OFFSET на запит. Функція вікна ROW_NUMBER() є одним із найуніверсальніших інструментів для вирішення цієї проблеми, а також відкриває доступ до низки додаткових аналітичних варіантів використання, таких як ранжування, пошук перших N запитів або виявлення дублікатів.

У цьому посібнику детально описано, як використовувати ROW_NUMBER() для пагінації в PostgreSQL, як вона насправді працює «під капотом», чим вона відрізняється від інших функцій ранжування, яких наслідків для продуктивності очікувати та як поводяться інші основні механізми баз даних з подібними шаблонами. Ми також розглянемо складні реальні сценарії, такі як пагінація на основі курсора, коли стовпець сортування містить дублікати, та як їх об'єднати. ROW_NUMBER() з CTE, об'єднаннями та підзапитами для чистого, готового до використання SQL.

Що насправді робить віконна функція PostgreSQL ROW_NUMBER()

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

Загальний синтаксис у PostgreSQL виглядає так:

ROW_NUMBER() OVER ( [PARTITION BY partition_expression] ORDER BY order_expression )

Дві частини всередині OVER речення контролює, як призначаються номери рядків: PARTITION BY (необов'язково) розділяє набір результатів на незалежні групи та ORDER BY (обов'язковий) визначає порядок у кожному розділі, який визначає, який рядок отримує який номер.

Якщо ви пропустите PARTITION BY, функція обробляє весь результуючий набір як один розділ і просто нумерує всі рядки відповідно до заданого порядку. Як тільки ви додасте PARTITION BY, нумерація рядків починається з 1 у кожному розділі, що надзвичайно корисно для ранжування за категоріями, запитів щодо перших N елементів у групі та групової дедуплікації.

На відміну від функцій ранжування, таких як RANK() or DENSE_RANK(), ROW_NUMBER() повністю ігнорує зв'язки та ніколи не повторює число, навіть коли рядки мають однакові значення у стовпцях сортування. Це робить його ідеальним для детермінованого нумерування сторінок та точного розбиття рядків.

Базові приклади ROW_NUMBER() для розвитку інтуїції

Перед використанням ROW_NUMBER() Для пагінації корисно побачити її в дії на простих прикладах, де метою є лише контрольована нумерація рядків. Уявіть собі стіл employees з колонами id, name, department та salary.

Щоб призначити глобальний номер рядка, відсортований за спаданням зарплати, можна написати:

SELECT
id,
name,
department,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

Цей запит повертає всіх співробітників, відсортованих за зарплатою від найвищої до найнижчої, з row_num = 1 для найвисокооплачуванішого працівника, 2 для другого, і так далі, без пропусків та повторюваних значень. Зв'язки в зарплаті розриваються довільно, якщо ви не продовжите ORDER BY з додатковими колонами.

Якщо ж вам потрібно, щоб нумерація рядків починалася заново всередині кожного відділу, ви об'єднуєте PARTITION BY з ORDER BY:

SELECT
id,
name,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;

Тут кожен відділ отримує свою власну послідовність 1, 2, 3 тощо, тому ви можете легко знайти «найбільш заробітну плату в кожному відділі», згодом відфільтрувавши row_num = 1 у зовнішньому запиті або CTE. Цей шаблон є основою для багатьох запитів типу "перший N на групу".

Щоб чітко відокремити логіку нумерації від логіки фільтрації, зазвичай віконну функцію обгортають у CTE або підзапит, а потім фільтрують згенеровані номери рядків у зовнішньому SELECT. Це особливо важливо, оскільки віконні функції не можна використовувати безпосередньо в WHERE речення того ж SELECT, яке їх визначає.

Використання ROW_NUMBER() для класичної пагінації на основі сторінок

Найпростіший спосіб зробити пагінацію за допомогою ROW_NUMBER() У PostgreSQL потрібно обчислити індекс рядка для кожного рядка, а потім запитати числовий діапазон, який відповідає потрібній сторінці. Це іноді використовується як альтернатива OFFSET/LIMIT, а також добре працює під час перенесення коду пагінації з SQL Server або Oracle.

Припустимо, вам потрібна сторінка результатів розміром @PageSize і номер сторінки @PageNumber (індекс на основі 0). Типовий T-SQL запит виглядає так:

SELECT PK_ID, Truco, Descripcion
FROM (
SELECT
PK_ID,
Truco,
Descripcion,
ROW_NUMBER() OVER (ORDER BY Truco, PK_ID) AS RowNumber
FROM TrucosInformaticos
) AS Trucos
WHERE RowNumber BETWEEN (@PageSize * @PageNumber) + 1
AND @PageSize * (@PageNumber + 1);

Та сама логіка безпосередньо перекладається в PostgreSQL – ви лише адаптуєте синтаксис параметрів і, за бажанням, обгортаєте їх у функцію, а не у збережену процедуру. Суть полягає в тому, щоб обчислити ROW_NUMBER() один раз, а потім розріжте рядки на числовий інтервал, який відповідає межам сторінки.

Наприклад, у PostgreSQL для фіксованої сторінки можна написати:

WITH ranked_posts AS (
SELECT
id,
title,
ROW_NUMBER() OVER (ORDER BY title, id) AS row_num
FROM posts
)
SELECT id, title
FROM ranked_posts
WHERE row_num BETWEEN 11 AND 20;

Це повертає рядки 11-20 у порядку, визначеному ORDER BY title, id, що фактично дає вам другу сторінку розміром 10. Великою перевагою над простим OFFSET є те, що номери рядків є явними та можуть бути поєднані з додатковою логікою, наприклад, для зворотного об'єднання, фільтрації або подальшої аналітики.

Розбиття на сторінки у стилі курсора, коли стовпець сортування містить дублікати

Розбиття на сторінки на основі зсуву легко обміркувати, але воно може спричинити проблеми з продуктивністю великих таблиць, а також стає нестабільним, коли базові дані змінюються між запитами. Курсорна пагінація (також відома як пагінація набору ключів) має на меті вирішити цю проблему, використовуючи останній побачений елемент як прив'язку замість абсолютного зміщення.

Ситуація ускладнюється, коли стовпець, за яким ви сортуєте, містить дублікати значень. Розглянемо схему з posts та comments:

CREATE TABLE posts(
id uuid PRIMARY KEY,
title varchar(255) NOT NULL
);

CREATE TABLE comments(
id uuid PRIMARY KEY,
post_id uuid NOT NULL REFERENCES posts
);

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

SELECT p.*, COUNT(c.id) AS comments_count
FROM posts AS p
LEFT JOIN comments AS c ON p.id = c.post_id
GROUP BY p.id
ORDER BY comments_count DESC;

Для пагінації на основі курсора може виникнути спокуса вибрати до певного comments_count поріг, а потім застосувати LIMIT:

WITH cte AS (
SELECT p.*, COUNT(c.id) AS comments_count
FROM posts AS p
LEFT JOIN comments AS c ON p.id = c.post_id
GROUP BY p.id
ORDER BY comments_count DESC
)
SELECT *
FROM cte
WHERE comments_count <= (
SELECT comments_count FROM cte WHERE id = '00000000-0000-0000-0000-000000000003'
)
LIMIT 3;

Проблема виникає, коли кілька постів мають однаковий comments_count. Якщо два дописи мають кількість 2, і ваш курсор вказує на один з них, використовуючи <= включає обидва ці зв'язані рядки на другій сторінці, використовуючи < пропускає всі рядки з однаковою кількістю та перестрибує занадто далеко, пропускаючи деякі публікації, які ви очікували побачити.

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

Одним із способів вирішення проблеми є створення синтетичного, унікального ключа сортування шляхом об'єднання кількості коментарів з первинним ключем, наприклад, за допомогою конкатенації:

WITH cte AS (
SELECT
p.,
COUNT(c.id) AS comments_count,
CONCAT(COUNT(c.id), ':', p.id) AS comments_count_id
FROM posts AS p
LEFT JOIN comments AS c ON p.id = c.post_id
GROUP BY p.id
ORDER BY comments_count_id DESC
)
SELECT *
FROM cte
WHERE comments_count_id < (
SELECT comments_count_id
FROM cte
WHERE id = '00000000-0000-0000-0000-000000000003'
)
LIMIT 3;

Шляхом створення складеного ключа, такого як '2:00000000-...-0003', ви робите замовлення суворо унікальним і можете сміливо сказати «дайте мені рядки з comments_count_id менше, ніж якір» без двозначності. Це та ж ідея, що й завжди, включаючи id у вашому ORDER BY як тай-брейк.

На практиці вам не потрібно об'єднувати елементи в рядок; ви можете просто використовувати кілька стовпців у ORDER BY та закодуйте їх у вашому об'єкті курсора на рівні програми. Важливою частиною з боку бази даних є те, що загальне впорядкування є унікальним та відтворюваним між викликами.

Пагінація за допомогою ROW_NUMBER() проти LIMIT та OFFSET

PostgreSQL підтримує класичний LIMIT та OFFSET синтаксис "з коробки", і для багатьох малих та середніх наборів результатів його цілком можна використовувати. Ви просто вказуєте, скільки рядків пропустити та скільки повернути.

Однак, пагінація на основі OFFSET має два великі недоліки: продуктивність та стабільність. As OFFSET Навіть якщо дані зростають, PostgreSQL все одно має сканувати та відкидати всі попередні рядки, перш ніж почати повертати результати, що стає дороговартісним для великих наборів даних. А якщо дані змінюються між запитами, сторінки можуть «зміщуватися» та відображати дублікати або пропускати рядки.

використання ROW_NUMBER() для пагінації є спосіб матеріалізувати індекс рядка один раз, а потім акуратно його розбити:

WITH numbered_products AS (
SELECT
product_id,
product_name,
price,
ROW_NUMBER() OVER (ORDER BY product_name) AS row_number
FROM products
)
SELECT product_id, product_name, price
FROM numbered_products
WHERE row_number BETWEEN 11 AND 20
ORDER BY product_name;

Цей шаблон читається інтуїтивно: спочатку кожному продукту присвоюється його позиція у відсортованому списку, потім зовнішній запит вибирає рядки з 11 по 20. Доки базові дані не змінюються між виконанням та використанням сторінки, ви отримуєте стабільний фрагмент логічної послідовності.

Тим не менш, ROW_NUMBER()Пагінація на основі . також не є панацеєю для продуктивності. База даних все ще повинна оцінювати віконну функцію для всіх відповідних рядків, щоб призначити номери, тому для надзвичайно великих таблиць це може бути так само дорого, як і великий OFFSET. Це проявляється в поєднанні з додатковою фільтрацією або коли вам потрібна детермінована логіка на основі номерів рядків, що виходить за рамки простої пагінації.

Як пагінація віконних функцій працює в різних механізмах баз даних

Функції вікна, такі як ROW_NUMBER() є стандартизованими функціями SQL, але кожен механізм баз даних оптимізує їх по-різному для шаблонів, подібних до пагінації. Деякі продукти можуть розпізнавати запити типу «top-N з віконною функцією» та завчасно зупиняти сканування за допомогою індексного доступу; інші ж непомітно оброблятимуть повний набір щоразу.

Розглянемо цей типовий запит у стилі top-N / пагінації, використовуючи ROW_NUMBER над упорядкованим індексом у sales стіл:

SELECT *
FROM (
SELECT
sales.*,
ROW_NUMBER() OVER (ORDER BY sale_date DESC, sale_id DESC) AS rn
FROM sales
) AS tmp
WHERE rn BETWEEN 11 AND 20
ORDER BY sale_date DESC, sale_id DESC;

Тут, ROW_NUMBER підраховує рядки відповідно до порядку, визначеного в OVER пункт, а зовнішній WHERE обмежує результат певною сторінкою (рядки 11-20). Це логічно еквівалентно запиту top-N, поєднаному зі зміщенням.

Oracle, наприклад, здатний розпізнати умову зупинки та використовувати індекс на sale_date та sale_id реалізувати поведінку «top-N у конвеєрі». Його план виконання може показувати WINDOW NOSORT STOPKEY, що вказує на те, що механізм не потребує додаткового сортування та зупиниться, щойно досягне верхньої межі запитуваного вікна.

Підтримка такого виду оптимізації не є універсальною. Деякі версії PostgreSQL та інших рушіїв, таких як MySQL, MariaDB та Db2, не зупиняють сканування індексів на ранній стадії в цих шаблонах на основі віконних функцій, тобто вони все одно обробляють більше рядків, ніж необхідно для відображення запитуваної сторінки.

В останніх версіях PostgreSQL (15+ і новіших) покращилася продуктивність та оптимізація віконних функцій, але їхня поведінка все ще може відрізнятися між основними версіями. Завжди перевіряйте плани виконання разом з EXPLAIN (ANALYZE) щоб перевірити, чи ваша база даних може використовувати індекси та зупинятися раніше, чи вона сканує та сортує весь набір результатів.

Поєднання ROW_NUMBER() з DISTINCT, CTE та підзапитами

Поширена проблема під час використання ROW_NUMBER() пліч-о-пліч DISTINCT полягає в тому, що віконна функція обчислюється перед кроком видалення дублікатів. Це може призвести до незрозумілих результатів, коли значення, що здавалося б, повторювані, все одно отримують різні номери рядків.

Наприклад, якщо ви спробуєте перерахувати різні ціни з products таблицю з одним запитом, наприклад:

SELECT DISTINCT price,
ROW_NUMBER() OVER (ORDER BY price) AS rn
FROM products;

Ви можете бути здивовані, побачивши кілька рядків з однаковим price але різні rn значення, оскільки функція вікна виконувалася на всіх рядках до цього DISTINCT видалено дублікати з остаточної проекції.

Надійний спосіб вирішення цієї проблеми — спочатку матеріалізувати окремі значення (через CTE або підзапит), а потім застосувати ROW_NUMBER() на додаток до цього:

WITH prices AS (
SELECT DISTINCT price
FROM products
)
SELECT price,
ROW_NUMBER() OVER (ORDER BY price) AS rn
FROM prices;

Або ж ви можете використовувати підзапит безпосередньо в FROM пункт, що досягає того ж ефекту. Найважливіша ідея полягає в тому, щоб чітко визначити, який набір результатів є «вікном», де ROW_NUMBER() має працювати, і якщо вам потрібна унікальність, спочатку побудуйте цей набір.

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

ROW_NUMBER() для ранжування, перших N елементів та видалення дублікатів

Хоча наша увага приділяється пагінації, було б марною думкою не згадати про це ROW_NUMBER() – це фантастичний інструмент для ранжування та дедуплікації даних. Багато з тих самих шаблонів, які ви використовуєте для пагінації, також використовуються як логіка ранжування.

Щоб отримати перші N записів у кожній категорії, можна розділити їх за категоріями та ранжувати рядки за метрикою, такою як ціна за спаданням:

WITH ranked_products AS (
SELECT
product_id,
product_name,
category_id,
price,
ROW_NUMBER() OVER (
PARTITION BY category_id
ORDER BY price DESC
) AS rank
FROM products
)
SELECT product_id, product_name, category_id, price
FROM ranked_products
WHERE rank <= 2;

Це повертає два найдорожчі товари в кожній категорії. Потім ви можете приєднатися до categories таблиці за допомогою USING (category_id) або явне об'єднання для відображення імен, зрозумілих для людини.

Для видалення дублікатів, ROW_NUMBER() часто використовується в поєднанні з PARTITION BY щоб позначити всі екземпляри в кожній групі, крім першого, як дублікати. Розглянемо просту таблицю:

CREATE TABLE items (
id INT,
name VARCHAR
);

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

SELECT
id,
name,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS row_number
FROM items
ORDER BY id;

Будь-який рядок з row_number > 1 є дублікатом. Тоді ви можете використовувати CTE та DELETE заява про їх видалення:

DELETE FROM items
WHERE id IN (
WITH ranked_items AS (
SELECT
id,
name,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS row_number
FROM items
)
SELECT id
FROM ranked_items
WHERE row_number > 1
);

Після виконання цього, вибравши з items відображатимуться лише різні назви, з одним репрезентативним рядком для кожного значення. Це чистий, декларативний спосіб видалення дублікатів, одночасно контролюючи, який саме рядок зберігати.

ROW_NUMBER() проти RANK() проти DENSE_RANK() у сценаріях пагінації

PostgreSQL пропонує кілька функцій вікна ранжування: ROW_NUMBER(), RANK() та DENSE_RANK(). Хоча всі вони призначають упорядковані числа, вони поводяться по-різному, коли в відсортованих стовпцях є зв'язки.

Важливі відмінності полягають у наступному:

• ROW_NUMBER() завжди призначає унікальне ціле число кожному рядку, навіть коли виникають равенства; числа суворо послідовні (1, 2, 3, 4, …).
• RANK() надає однаковий ранг ідентичним значенням, але пропускає числа після збігів (наприклад, 1, 2, 2, 4 – ранг 3 відсутній).
• DENSE_RANK() також дає той самий ранг для нічиїх, але не пропускає числа (1, 2, 2, 3).

Для пагінації, ROW_NUMBER() зазвичай є найбезпечнішим вибором, оскільки він гарантує рівно один рядок на число, що природно відповідає діапазонам сторінок, таким як 1-10, 11-20 тощо. Якщо ви використовували RANK() or DENSE_RANK(), через зв’язки ви можете отримати сторінки з меншою або більшою кількістю рядків, ніж очікувалося.

З іншого боку, для випадків використання звітності, таких як результати змагань, де пов'язані значення повинні займати однакову позицію, RANK() or DENSE_RANK() відображають намір краще, ніж ROW_NUMBER(). Ви все ще можете розбивати ці результати на сторінки, але вам слід пам’ятати, що «позиція» більше не відповідає безпосередньо фізичному номеру рядка.

Практичні поради, підводні камені та міркування щодо продуктивності

При використанні ROW_NUMBER() Щодо пагінації та аналітики, кілька корисних практик позбавлять вас від ледь помітних помилок та непотрібних проблем з продуктивністю. Більшість із них зводяться до явного та детерміністичного характеру.

Завжди визначайте чітке ORDER BY всередині OVER() пункт. Без нього PostgreSQL може повертати рядки в будь-якому порядку для цілей віконної функції, а номери рядків можуть змінюватися між виконаннями, навіть якщо базові дані ідентичні.

Завжди, коли це можливо, додавайте унікальний стовпець (часто первинний ключ) в кінці ORDER BY список Це перетворює порядок на повний та уникає неоднозначності зі зв'язками, що є критично важливим для пагінації на основі курсора та передбачуваних результатів top-N.

Не очікуйте використання віконних функцій безпосередньо в WHERE речення того ж самого SELECT. Натомість обгорніть їх у CTE або підзапит і відфільтруйте за похідним стовпцем у зовнішньому запиті. Цей шаблон простий, його можна використовувати повторно та він забезпечує читабельність SQL.

Під час пагінації, коли це практично можливо, надавайте перевагу впорядкуванню за індексованими стовпцями. обидві ORDER BY та ROW_NUMBER() покладатися на сортування або сканування індексів; правильне індексування може мати вирішальне значення між мілісекундами та секундами у великих таблицях.

Будьте обережні при поєднанні PARTITION BY з пагінацією в деяких движках. У деяких продуктах та версіях баз даних використання функцій розділеного вікна всередині представлень або підзапитів може вимкнути оптимізацію stopkey, що призведе до обробки більшої кількості рядків, ніж необхідно. Тестування з реалістичними даними та читання планів запитів є важливим.

Для дуже великих наборів даних та високодинамічних даних розгляньте можливість їх поєднання ROW_NUMBER() пагінація для подань у стилі адміністратора з пагінацією наборів ключів на основі курсора для кінцевих точок, орієнтованих на користувача. Таким чином, ви отримуєте як детерміновані запити до інструментів, так і ефективну, стабільну навігацію у вашому API або інтерфейсі користувача.

Розглянуто в цілому, ROW_NUMBER() це не просто трюк з пагінацією: це потужний аналітичний будівельний блок, який дозволяє нумерувати, ранжувати, зрізати та очищувати набори результатів у PostgreSQL та інших основних SQL-рушіях з тією ж базовою логікою. Опанування цього – разом із ґрунтовним розумінням OVER(), PARTITION BY, та відмінності від RANK() та DENSE_RANK() – надає вам дуже гнучкий інструментарій для ефективної пагінації, запитів top-N та надійної дедуплікації в реальних застосунках.