Explicació de la paginació a PostgreSQL amb ROW_NUMBER

Inici » Pitó » Explicació de la paginació a PostgreSQL amb ROW_NUMBER

Paginar grans conjunts de resultats a PostgreSQL pot semblar senzill a primera vista, però fer-ho de manera eficient i correcta, sobretot quan hi ha vincles a la columna d'ordenació, requereix una mica més que simplement afegir un LIMIT/OFFSET a una consulta. La funció de finestra ROW_NUMBER() és una de les eines més versàtils que teniu per resoldre aquest problema, alhora que desbloqueja un munt de casos d'ús analítics addicionals com ara la classificació, les consultes N principals o la detecció de duplicats.

Aquesta guia aprofundeix en com utilitzar-lo ROW_NUMBER() per a la paginació a PostgreSQL, com funciona realment, en què es diferencia d'altres funcions de classificació, quines implicacions de rendiment es poden esperar i com es comporten altres motors de bases de dades importants amb patrons similars. També analitzarem escenaris complicats del món real, com la paginació basada en cursors quan la columna d'ordenació conté duplicats i com combinar-los. ROW_NUMBER() amb CTE, unions i subconsultes per a un SQL net i llest per a la producció.

Què fa realment la funció de finestra ROW_NUMBER() de PostgreSQL

En el seu nucli, ROW_NUMBER() és una funció de finestra que assigna un enter seqüencial únic a cada fila d'un conjunt de resultats, començant per 1 i incrementant-se per 1 sense espais en blanc. Aquesta numeració es pot aplicar a tot el resultat o reiniciar-la per a cada grup de files, depenent de com definiu la finestra.

La sintaxi genèrica de PostgreSQL té aquest aspecte:

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

Dues parts dins de OVER clàusules que controlen com s'assignen els números de fila: PARTITION BY (opcional) divideix el conjunt de resultats en grups independents i ORDER BY (obligatori) defineix l'ordre dins de cada partició que determina quina fila rep quin número.

Si ometeu PARTITION BY, la funció tracta el conjunt de resultats complet com una única partició i simplement numera totes les files segons l'ordre especificat. Un cop afegiu PARTITION BY, la numeració de files es reinicia des d'1 a cada partició, cosa que és extremadament útil per a classificacions per categoria, consultes dels N primers per grup i deduplicació agrupada.

A diferència de les funcions de classificació com ara RANK() or DENSE_RANK(), ROW_NUMBER() ignora completament els empats i mai repeteix un número, fins i tot quan les files tenen valors idèntics a les columnes d'ordenació. Això el fa ideal per a la paginació determinista i el tall precís de files.

Exemples bàsics de ROW_NUMBER() per construir la intuïció

Abans d'usar ROW_NUMBER() per a la paginació, ajuda veure-ho en acció en exemples senzills, on l'objectiu és simplement numerar les files de manera controlada. Imagineu-vos una taula employees amb columnes id, name, department i salary.

Per assignar un número de fila global ordenat per salari descendent, podeu escriure:

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

Aquesta consulta retorna tots els empleats, ordenats per salari de més alt a més baix, amb row_num = 1 per a l'empleat més ben pagat, 2 per al segon, i així successivament, sense buits ni valors repetits. Els empats salarials es trenquen arbitràriament tret que s'ampliï el ORDER BY amb columnes addicionals.

Si, en canvi, necessiteu que la numeració de files es reiniciï dins de cada departament, combineu PARTITION BY amb ORDER BY:

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

Aquí, cada departament té la seva pròpia seqüència 1, 2, 3, ..., de manera que podeu trobar fàcilment "el que més guanya de cada departament" filtrant posteriorment per row_num = 1 en una consulta externa o CTE. Aquest patró és l'eix vertebrador de moltes consultes top-N per grup.

Per separar clarament la lògica de numeració de la lògica de filtratge, és habitual embolicar la funció de finestra en un CTE o subconsulta i després filtrar els números de fila generats a la SELECT externa. Això és especialment important perquè les funcions de la finestra no es poden utilitzar directament a la WHERE clàusula del mateix SELECT que els defineix.

Ús de ROW_NUMBER() per a la paginació clàssica basada en pàgines

La manera més senzilla de fer paginació amb ROW_NUMBER() a PostgreSQL és calcular l'índex de fila per a cada fila i després demanar un interval numèric que correspongui a la pàgina que voleu. Això s'utilitza de vegades com a alternativa a OFFSET/LIMIT, i també funciona bé en portar codi de paginació des de SQL Server o Oracle.

Suposem que voleu una pàgina de resultats amb la mida @PageSize i número de pàgina @PageNumber (índex basat en 0). La consulta T-SQL típica té aquest aspecte:

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);

La mateixa lògica es tradueix directament a PostgreSQL: només s'adapta la sintaxi dels paràmetres i, si es vol, s'inclou en una funció en lloc d'un procediment emmagatzemat. L'essència és: calcular ROW_NUMBER() una vegada i, a continuació, divideix les files segons un interval numèric que correspongui als límits de la pàgina.

Per exemple, a PostgreSQL, per a una pàgina fixa podríeu escriure:

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;

Això retorna les files 11-20 en l'ordre definit per ORDER BY title, id, donant-vos efectivament la segona pàgina amb una mida de pàgina 10. El gran avantatge respecte a l'OFFSET simple és que els números de fila són explícits i es poden combinar amb lògica addicional, per exemple, unint-los de nou, filtrant o fent més anàlisis.

Paginació d'estil cursor quan la columna d'ordenació té duplicats

La paginació basada en offset és fàcil de raonar, però pot causar problemes de rendiment en taules grans i també esdevé fràgil quan les dades subjacents canvien entre sol·licituds. La paginació basada en cursors (també anomenada paginació de conjunts de claus) pretén resoldre això utilitzant l'últim element vist com a àncora en lloc d'un desplaçament absolut.

Les coses es compliquen quan la columna per la qual ordeneu conté valors duplicats. Considereu un esquema amb posts i 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
);

Imagineu que primer creeu una consulta que ordena les publicacions per nombre de comentaris en ordre descendent:

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;

Per a la paginació basada en el cursor, es podria tenir la temptació de seleccionar fins a un cert comments_count llindar i després aplicar un 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;

El problema apareix quan diverses publicacions comparteixen el mateix comments_count. Si dues publicacions tenen un recompte de 2 i el cursor apunta a una d'elles, feu servir <= inclou les dues files lligades a la segona pàgina, mentre utilitza < salta totes les files amb el mateix recompte i salta massa lluny, ometent algunes publicacions que esperaves veure.

Aquest és un símptoma clàssic de l'ordenació per una clau no única en la paginació del cursor: la base de dades no pot segmentar determinísticament el conjunt de dades "al mig" d'un grup de lligams si el cursor només codifica el valor no únic. Necessiteu un ordre únic i estable per definir un cursor de manera segura.

Una solució alternativa és crear una clau d'ordenació sintètica i única combinant el recompte de comentaris amb la clau primària, per exemple mitjançant la concatenació:

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;

Construint una clau composta com '2:00000000-...-0003', feu que l'ordre sigui estrictament únic i podeu dir amb seguretat "doneu-me files amb comments_count_id "menys que l'àncora" sense ambigüitat. És la mateixa idea de sempre incloent-hi id en el seu ORDER BY com a desempat.

A la pràctica, no cal concatenar-les a una cadena; només cal utilitzar diverses columnes. ORDER BY i codificar-los a l'objecte cursor de la capa d'aplicació. La part important des del costat de la base de dades és que l'ordenació total sigui única i reproduïble entre crides.

Paginació amb ROW_NUMBER() vs LIMIT i OFFSET

PostgreSQL admet el clàssic LIMIT i OFFSET sintaxi de fàbrica, i per a molts conjunts de resultats petits o mitjans, està perfectament disponible. Simplement especifiqueu quantes files voleu ometre i quantes voleu retornar.

Tanmateix, la paginació basada en OFFSET té dos grans inconvenients: el rendiment i l'estabilitat. As OFFSET creix, PostgreSQL encara ha d'escanejar i descartar totes les files anteriors abans de poder començar a retornar resultats, cosa que esdevé costosa en conjunts de dades grans. I si les dades canvien entre sol·licituds, les pàgines poden "desplaçar-se" i mostrar duplicats o ometre files.

Ús ROW_NUMBER() per a la paginació us dóna una manera de materialitzar l'índex de fila una vegada i després tallar-lo netament:

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;

Aquest patró es llegeix intuïtivament: primer assigna a cada producte la seva posició a la llista ordenada i, a continuació, la consulta externa obté les files de l'11 a la 20. Sempre que les dades subjacents no canviïn entre l'execució i el consum de la pàgina, s'obté una porció estable de la seqüència lògica.

Dit això, ROW_NUMBER()La paginació basada en tampoc és una solució milagrosa per al rendiment. La base de dades encara ha d'avaluar la funció de la finestra sobre totes les files elegibles per assignar números, de manera que per a taules extremadament grans, això pot ser tan costós com un OFFSET gran. On destaca és quan es combina amb un filtratge addicional o quan es vol una lògica determinista basada en el número de fila més enllà de la paginació pura.

Com es comporta la paginació de funcions de finestra entre motors de bases de dades

Funcions de la finestra com ara ROW_NUMBER() són característiques SQL estandarditzades, però cada motor de base de dades les optimitza de manera diferent per a patrons semblants a la paginació. Alguns productes poden reconèixer les consultes "top-N amb funció de finestra" i aturar l'escaneig abans d'hora mitjançant l'accés a l'índex; d'altres processaran silenciosament el conjunt complet cada vegada.

Considereu aquesta consulta típica de paginació / top-N utilitzant ROW_NUMBER sobre un índex ordenat en un sales taula:

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;

Aquí, ROW_NUMBER compta les files segons l'ordre definit a OVER clàusula i l'exterior WHERE restringeix el resultat a una pàgina específica (files 11-20). Això és lògicament equivalent a una consulta top-N combinada amb un offset.

Oracle, per exemple, és capaç de reconèixer la condició d'aturada i utilitzar un índex a sale_date i sale_id per implementar un comportament de "top-N in pipeline". El seu pla d'execució pot mostrar WINDOW NOSORT STOPKEY, cosa que indica que el motor no necessita una ordenació addicional i s'aturarà tan bon punt hagi produït el límit superior de la finestra sol·licitada.

El suport per a aquest tipus d'optimització no és universal. Algunes versions de PostgreSQL i altres motors com MySQL, MariaDB i Db2 no aturen les exploracions d'índex aviat en aquests patrons basats en funcions de finestra, cosa que significa que encara processen més files de les estrictament necessàries per lliurar la pàgina sol·licitada.

Les versions recents de PostgreSQL (15+ i posteriors) han millorat el rendiment i les optimitzacions de les funcions de finestra, però el comportament encara pot variar entre les versions principals. Inspeccioneu sempre els plans d'execució amb EXPLAIN (ANALYZE) per veure si la base de dades és capaç d'explotar índexs i aturar-se abans d'hora, o si està escanejant i ordenant tot el conjunt de resultats.

Combinant ROW_NUMBER() amb DISTINCT, CTE i subconsultes

Un problema comú quan s'utilitza ROW_NUMBER() al costat de DISTINCT és que la funció de finestra s'avalua abans del pas d'eliminació de duplicats. Això pot donar lloc a resultats confusos on valors aparentment duplicats encara reben números de fila diferents.

Per exemple, si intenteu enumerar preus diferents d'un products taula amb una sola consulta com ara:

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

Potser us sorprendrà veure diverses files amb el mateix price però diferent rn valors, perquè la funció de la finestra s'ha executat en totes les files anteriors DISTINCT s'han eliminat els duplicats de la projecció final.

La manera robusta de gestionar això és materialitzar primer els valors diferents (mitjançant un CTE o una subconsulta) i després aplicar ROW_NUMBER() a més d'això:

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

Alternativament, podeu utilitzar una subconsulta directament a FROM clàusula, aconseguint el mateix efecte. La idea crítica és decidir explícitament quin conjunt de resultats és la "finestra" on ROW_NUMBER() hauria de funcionar, i si necessiteu singularitat, creeu primer aquest conjunt.

Aquest patró és extremadament útil per a tasques de paginació com ara "obtenir el producte amb el tercer preu més alt" o "enumerar preus diferents amb números de fila i després triar-ne un d'específic". Primer podeu obtenir preus ordenats únics amb ROW_NUMBER() i després uneix-te o filtra pel rang concret que t'interessi.

ROW_NUMBER() per a la classificació, els N primers i l'eliminació de duplicats

Tot i que el nostre objectiu principal és la paginació, seria un malbaratament no esmentar que ROW_NUMBER() és una eina fantàstica per classificar i deduplicar dades. Molts dels mateixos patrons que feu servir per a la paginació també serveixen com a lògica de classificació.

Per obtenir els N registres principals per categoria, podeu dividir per categoria i classificar les files per una mètrica com ara el preu descendent:

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;

Això retorna els dos productes més cars de cada categoria. Aleshores, pots unir-te a un categories taula utilitzant USING (category_id) o una unió explícita per mostrar noms llegibles per humans.

Per a l'eliminació de duplicats, ROW_NUMBER() s'utilitza sovint en combinació amb PARTITION BY per marcar totes les ocurrències de cada grup com a duplicades excepte la primera. Considereu una taula senzilla:

CREATE TABLE items (
id INT,
name VARCHAR
);

Suposem que inseriu diversos noms duplicats i voleu eliminar les còpies addicionals mantenint l'identificador més baix per a cada nom. Primer podeu identificar els duplicats:

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

Qualsevol fila amb row_number > 1 és un duplicat. Aleshores podeu utilitzar un CTE i un DELETE declaració per eliminar-los:

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
);

Després d'executar això, seleccionant entre items només mostrarà noms diferents, amb una fila representativa per valor. Aquesta és una manera neta i declarativa de deduplicar i alhora controlar exactament quina fila es conserva.

ROW_NUMBER() vs RANK() vs DENSE_RANK() en escenaris de paginació

PostgreSQL ofereix diverses funcions de finestra de classificació: ROW_NUMBER(), RANK()i DENSE_RANK(). Tot i que totes assignen números ordenats, es comporten de manera diferent quan hi ha empats a les columnes ordenades.

Les diferències importants són:

• ROW_NUMBER() sempre assigna un nombre enter únic a cada fila, fins i tot quan hi ha empats; els números són estrictament seqüencials (1, 2, 3, 4,...).
• RANK() dóna el mateix rang a valors idèntics però omet els números després dels empats (per exemple, 1, 2, 2, 4: falta el rang 3).
• DENSE_RANK() també dóna el mateix rang per als empats però no omet els números (1, 2, 2, 3).

Per a la paginació, ROW_NUMBER() sol ser l'opció més segura perquè garanteix exactament una fila per número, cosa que s'assigna naturalment a intervals de pàgines com ara 1-10, 11-20, etc. Si ho feies servir RANK() or DENSE_RANK(), podríeu acabar amb pàgines que tinguin menys o més files de les esperades a causa d'enllaços.

D'altra banda, per a casos d'ús com ara resultats de competicions on els valors empatats han de compartir la mateixa posició, RANK() or DENSE_RANK() representar la intenció millor que ROW_NUMBER(). Encara podeu paginar aquests resultats, però heu de tenir en compte que la "posició" ja no correspon directament al número de fila física.

Consells pràctics, inconvenients i consideracions sobre el rendiment

Quan s'utilitza ROW_NUMBER() Per a la paginació i l'analítica, un grapat de bones pràctiques us estalviaran errors subtils i mals de cap de rendiment innecessaris. La majoria d'ells es redueixen a ser explícits i deterministes.

Defineix sempre clarament ORDER BY dins de la OVER() clàusula. Sense això, PostgreSQL pot retornar files en qualsevol ordre per als propòsits de la funció window, i els números de fila poden canviar entre execucions fins i tot si les dades subjacents són idèntiques.

Sempre que sigui possible, incloeu una columna única (sovint la clau primària) al final de la ORDER BY llista Això converteix l'ordenació en un ordre total i evita l'ambigüitat amb els lligams, cosa que és fonamental per a la paginació basada en el cursor i per obtenir resultats predictibles entre els N primers.

No esperis utilitzar les funcions de la finestra directament a WHERE clàusula del mateix SELECT. En comptes d'això, emboliqueu-los en un CTE o una subconsulta i filtreu per la columna derivada a la consulta externa. Aquest patró és senzill, reutilitzable i manté el vostre SQL llegible.

Quan es pagina, és millor ordenar per columnes indexades sempre que sigui pràctic. Tots dos ORDER BY i ROW_NUMBER() confien en l'ordenació o les exploracions d'índex; una indexació adequada pot marcar la diferència entre mil·lisegons i segons en taules grans.

Aneu amb compte a l'hora de combinar PARTITION BY amb paginació en alguns motors. En certs productes i versions de bases de dades, l'ús de funcions de finestra particionades dins de vistes o subconsultes pot desactivar les optimitzacions de clau d'aturada que d'altra manera estarien disponibles, fent que el motor processi més files de les necessàries. És essencial fer proves amb dades realistes i llegir plans de consulta.

Per a conjunts de dades molt grans i dades altament dinàmiques, considereu la combinació ROW_NUMBER() paginació per a vistes "d'estil administrador" amb paginació de conjunts de claus basada en cursors per a punts finals orientats a l'usuari. D'aquesta manera, obtindreu consultes d'eines deterministes i una navegació eficient i estable a la vostra API o interfície d'usuari.

Vist en conjunt, ROW_NUMBER() no és només un truc de paginació: és un potent bloc de construcció analític que permet numerar, classificar, segmentar i netejar conjunts de resultats a PostgreSQL i a altres motors SQL importants amb la mateixa lògica subjacent. Dominar-ho, juntament amb una sòlida comprensió de OVER(), PARTITION BY, i les diferències de RANK() i DENSE_RANK() – us ofereix un conjunt d'eines molt flexible per a una paginació eficient, consultes top-N i deduplicació robusta en aplicacions del món real.