Обяснение на странирането в PostgreSQL с ROW_NUMBER

Начало » Питон » Обяснение на странирането в PostgreSQL с ROW_NUMBER

Страничното разделяне на големи набори от резултати в PostgreSQL може да изглежда просто на пръв поглед, но ефикасното и правилното му изпълнение – особено когато има връзки в колоната за подреждане – изисква малко повече от просто задаване на LIMIT/OFFSET на заявка. Функцията на прозореца ROW_NUMBER() е един от най-универсалните инструменти, с които разполагате, за да решите този проблем, като същевременно отключва редица допълнителни аналитични случаи на употреба, като класиране, заявки от топ N или откриване на дубликати.

Това ръководство разглежда подробно как да използвате ROW_NUMBER() за страниране в PostgreSQL, как всъщност работи „под капака“, как се различава от други функции за класиране, какви последици за производителността да очаквате и как се държат други големи двигатели на бази данни с подобни модели. Ще разгледаме и сложни сценарии от реалния свят, като например пагинация, базирана на курсор, когато колоната за сортиране съдържа дубликати и как да се комбинират ROW_NUMBER() с CTE, съединения и подзаявки за чист, готов за производство SQL.

Какво всъщност прави функцията за прозорец ROW_NUMBER() на PostgreSQL

В основата си, 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()Пагинацията, базирана на .-базирания код, също не е чаровно лекарство за производителност. Базата данни все още трябва да оцени функцията window върху всички допустими редове, за да присвои номера, така че за изключително големи таблици това може да бъде също толкова скъпо, колкото и голямото OFFSET. Най-доброто е, когато се комбинира с допълнително филтриране или когато искате детерминистична, базирана на номера на редове логика, отвъд чистото номериране.

Как се държи пагинацията на прозоречни функции в различните двигатели на бази данни

Функции на прозореца, като например ROW_NUMBER() са стандартизирани SQL функции, но всеки двигател на базата данни ги оптимизира по различен начин за модели, подобни на странициране. Някои продукти могат да разпознават заявки от типа „top-N с функция за прозорец“ и да спрат сканирането преждевременно, използвайки достъп до индекси; други ще обработват тихо целия набор всеки път.

Разгледайте тази типична заявка в стил top-N / pagination, използвайки 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 е свободен да връща редове в произволен ред за целите на функцията window и номерата на редовете могат да се променят между изпълненията, дори ако основните данни са идентични.

Винаги, когато е възможно, включете уникална колона (често първичния ключ) в края на ORDER BY списък. Това превръща подреждането в пълен ред и избягва неясноти с връзки, което е критично за пагинацията, базирана на курсора, и за предвидими резултати от първите 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“ и стабилна дедупликация в реални приложения.