Paginarea în PostgreSQL cu ROW_NUMBER explicată

Acasă » Piton » Paginarea în PostgreSQL cu ROW_NUMBER explicată

Paginarea seturilor mari de rezultate în PostgreSQL poate părea simplă la prima vedere, dar realizarea ei eficientă și corectă - mai ales când există legături în coloana de ordonare - necesită ceva mai mult decât simpla aplicare a unui LIMIT/OFFSET într-o interogare. Funcția ferestrei ROW_NUMBER() este unul dintre cele mai versatile instrumente pe care le aveți pentru a rezolva această problemă, deblocând în același timp o serie de cazuri de utilizare analitice suplimentare, cum ar fi clasamentul, interogările de top N sau detectarea duplicatelor.

Acest ghid analizează în detaliu modul de utilizare ROW_NUMBER() pentru paginarea în PostgreSQL, cum funcționează de fapt în detaliu, cum diferă de alte funcții de clasificare, ce implicații de performanță să se aștepte și cum se comportă alte motoare de baze de date importante cu modele similare. De asemenea, vom analiza scenarii dificile din lumea reală, cum ar fi paginarea bazată pe cursor atunci când coloana de sortare conține duplicate și cum să le combinăm. ROW_NUMBER() cu CTE-uri, join-uri și subinterogări pentru SQL curat, pregătit pentru producție.

Ce face de fapt funcția ferestrei PostgreSQL ROW_NUMBER()

În centrul său, ROW_NUMBER() este o funcție de fereastră care atribuie un număr întreg secvențial unic fiecărui rând dintr-un set de rezultate, începând de la 1 și incrementând cu 1 fără goluri. Această numerotare poate fi aplicată întregului rezultat sau reluată pentru fiecare grup de rânduri, în funcție de modul în care definiți fereastra.

Sintaxa generică în PostgreSQL arată astfel:

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

Două părți în interiorul OVER clauza controlează modul în care sunt atribuite numerele de rând: PARTITION BY (opțional) împarte setul de rezultate în grupuri independente și ORDER BY (obligatoriu) definește ordinea din cadrul fiecărei partiții care determină ce rând primește ce număr.

Dacă omiteți PARTITION BY, funcția tratează setul complet de rezultate ca o singură partiție și numerotează pur și simplu toate rândurile conform ordinii specificate. Odată ce adăugați PARTITION BY, numerotarea rândurilor repornește de la 1 în fiecare partiție, ceea ce este extrem de util pentru clasamentele per categorie, interogările top-N-per-grup și deduplicarea grupată.

Spre deosebire de funcțiile de clasificare, cum ar fi RANK() or DENSE_RANK(), ROW_NUMBER() ignoră complet legăturile și nu repetă niciodată un număr, chiar și atunci când rândurile au valori identice în coloanele de sortare. Asta îl face ideal pentru paginarea deterministă și felierea precisă a rândurilor.

Exemple de bază cu ROW_NUMBER() pentru a construi intuiția

Înainte de a utiliza ROW_NUMBER() Pentru paginare, este util să o vedem în acțiune pe exemple simple, unde scopul este doar de a numerota rândurile într-un mod controlat. Imaginează-ți o masă employees cu coloane id, name, department și salary.

Pentru a atribui un număr de rând global, sortat în ordine descrescătoare a salariului, puteți scrie:

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

Această interogare returnează toți angajații, sortați după salariu de la cel mai mare la cel mai mic, cu row_num = 1 pentru cel mai bine plătit angajat, 2 pentru al doilea și așa mai departe, fără goluri și fără valori repetitive. Legăturile salariale sunt rupte arbitrar, cu excepția cazului în care prelungiți ORDER BY cu coloane suplimentare.

Dacă, în schimb, aveți nevoie ca numerotarea rândurilor să repornească în interiorul fiecărui departament, combinați PARTITION BY implementate cu ORDER BY:

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

Aici, fiecare departament are propria secvență 1, 2, 3, ..., astfel încât puteți găsi cu ușurință „persoana cu cele mai mari venituri din fiecare departament” filtrând ulterior după row_num = 1 într-o interogare externă sau CTE. Acest model este coloana vertebrală pentru multe interogări de top N per grup.

Pentru a separa în mod clar logica de numerotare de logica de filtrare, este obișnuit să încapsulezi funcția fereastră într-un CTE sau o subinterogare și apoi să filtrezi numerele de rând generate în SELECT-ul exterior. Acest lucru este deosebit de important deoarece funcțiile ferestrei nu pot fi utilizate direct în WHERE clauză a aceluiași SELECT care le definește.

Utilizarea ROW_NUMBER() pentru paginarea clasică bazată pe pagini

Cea mai simplă metodă de paginare cu ROW_NUMBER() În PostgreSQL, scopul este de a calcula indexul rândului pentru fiecare rând și apoi de a solicita un interval numeric care corespunde paginii dorite. Aceasta este uneori folosită ca alternativă la OFFSET/LIMITși funcționează bine și la portarea codului de paginare din SQL Server sau Oracle.

Să presupunem că doriți o pagină de rezultate cu dimensiunea @PageSize și numărul paginii @PageNumber (indice bazat pe 0). Interogarea T-SQL tipică arată astfel:

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

Aceeași logică se traduce direct în PostgreSQL – adaptezi doar sintaxa parametrilor și, dacă dorești, îi incluzi într-o funcție în loc de o procedură stocată. Esența este: calcularea ROW_NUMBER() o dată, apoi secționați rândurile după un interval numeric care corespunde limitelor paginii.

De exemplu, în PostgreSQL, pentru o pagină fixă ​​ați putea scrie:

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;

Aceasta returnează rândurile 11-20 în ordinea definită de ORDER BY title, id, oferindu-vă practic a doua pagină cu dimensiunea paginii 10. Marele avantaj față de OFFSET simplu este că numerele de rând sunt explicite și pot fi combinate cu logică suplimentară, de exemplu, reunirea, filtrarea sau efectuarea de analize suplimentare.

Paginare în stil cursor atunci când coloana de sortare are duplicate

Paginarea bazată pe offset este ușor de abordat, dar poate cauza probleme de performanță în tabelele mari și devine, de asemenea, fragilă atunci când datele subiacente se modifică între cereri. Paginarea bazată pe cursor (numită și paginare pe seturi de chei) își propune să rezolve această problemă utilizând ultimul element văzut ca ancoră în loc de un offset absolut.

Lucrurile devin complicate atunci când coloana după care sortați conține valori duplicate. Luați în considerare o schemă cu 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
);

Imaginează-ți că construiești mai întâi o interogare care ordonează postările în ordine descrescătoare după numărul de comentarii:

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;

Pentru paginarea bazată pe cursor, cineva ar putea fi tentat să selecteze până la un anumit comments_count prag și apoi aplicați 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;

Problema apare atunci când mai multe postări au același conținut. comments_count. Dacă două postări au ambele un număr de 2, iar cursorul este îndreptat spre una dintre ele, folosind <= include ambele rânduri legate de pe a doua pagină, în timp ce folosește < sare peste toate rândurile cu același număr și sare prea departe, omițând unele postări pe care te așteptai să le vezi.

Acesta este un simptom clasic al ordonării după o cheie neunică în paginarea cursorului - baza de date nu poate secționa determinist setul de date „din mijlocul” unui grup de legături dacă cursorul codifică doar valoarea neunică. Ai nevoie de o ordine unică și stabilă pentru a defini un cursor în siguranță.

O soluție este crearea unei chei de sortare sintetice, unice, prin combinarea numărului de comentarii cu cheia primară, de exemplu prin concatenare:

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;

Prin construirea unei chei compozite precum '2:00000000-...-0003', faci ordinea strict unică și poți spune în siguranță „dă-mi rânduri cu comments_count_id „mai puțin decât ancora” fără ambiguitate. Este aceeași idee ca întotdeauna includerea id în ta ORDER BY ca element de departajare.

În practică, nu trebuie să concatenați cu un șir de caractere; puteți utiliza pur și simplu mai multe coloane în ORDER BY și codificați-le în obiectul cursor din stratul aplicației. Partea importantă din partea bazei de date este că ordinea totală este unică și reproductibilă între apeluri.

Paginare cu ROW_NUMBER() vs LIMIT și OFFSET

PostgreSQL acceptă versiunea clasică LIMIT și OFFSET sintaxă gata de utilizare, iar pentru multe seturi de rezultate mici sau medii, este perfect acceptabilă. Pur și simplu specificați câte rânduri să omiteți și câte să returnați.

Totuși, paginarea bazată pe OFFSET are două mari dezavantaje: performanța și stabilitatea. As OFFSET Pe măsură ce datele cresc, PostgreSQL trebuie să scaneze și să elimine toate rândurile anterioare înainte de a putea începe să returneze rezultate, ceea ce devine costisitor pentru seturi de date mari. Și dacă datele se modifică între solicitări, paginile se pot „deplasa” și pot afișa duplicate sau pot sări peste rânduri.

Utilizarea ROW_NUMBER() `for pagination` vă oferă o modalitate de a materializa indexul rândului o singură dată și apoi de a-l felia curat:

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;

Acest model se citește intuitiv: mai întâi atribuie fiecărui produs poziția sa în lista sortată, apoi interogarea externă preia rândurile de la 11 la 20. Atâta timp cât datele subiacente nu se modifică între execuție și consumarea paginii, obțineți o porțiune stabilă a secvenței logice.

Acestea fiind spuse, ROW_NUMBER()Nici paginarea bazată pe nu este o soluție miraculoasă pentru performanță. Baza de date trebuie în continuare să evalueze funcția window pe toate rândurile eligibile pentru a atribui numere, așa că pentru tabele extrem de mari, acest lucru poate fi la fel de costisitor ca un OFFSET mare. Deosebit de eficient este atunci când este combinat cu o filtrare suplimentară sau atunci când doriți o logică deterministă, bazată pe numărul de rânduri, dincolo de paginarea pură.

Cum se comportă paginarea funcțiilor de fereastră în motoarele de baze de date

Funcții ale ferestrei, cum ar fi ROW_NUMBER() sunt caracteristici SQL standardizate, dar fiecare motor de baze de date le optimizează diferit pentru modele de tip paginare. Unele produse pot recunoaște interogările „top-N cu funcție de fereastră” și pot opri scanarea mai devreme folosind accesul la index; altele vor procesa în liniște întregul set de fiecare dată.

Luați în considerare această interogare tipică de tip top-N / pagination-style folosind ROW_NUMBER peste un index ordonat într-un sales masa:

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;

Aici, ROW_NUMBER numără rândurile conform ordinii definite în OVER clauză și exteriorul WHERE restricționează rezultatul la o anumită pagină (rândurile 11-20). Acest lucru este echivalent logic cu o interogare top-N combinată cu un offset.

Oracle, de exemplu, este capabil să recunoască condiția de oprire și să utilizeze un index pe sale_date și sale_id pentru a implementa un comportament „top-N în pipeline”. Planul său de execuție poate arăta WINDOW NOSORT STOPKEY, indicând faptul că motorul nu are nevoie de o sortare suplimentară și se va opri imediat ce a produs limita superioară a ferestrei solicitate.

Sprijinul pentru acest tip de optimizare nu este universal. Unele versiuni de PostgreSQL și alte motoare precum MySQL, MariaDB și Db2 nu opresc scanările de index devreme în aceste modele bazate pe funcții de fereastră, ceea ce înseamnă că procesează în continuare mai multe rânduri decât este strict necesar pentru a livra pagina solicitată.

Versiunile recente de PostgreSQL (15+ și ulterioare) au îmbunătățit performanța și optimizările funcțiilor ferestrelor, dar comportamentul poate diferi în continuare între versiunile majore. Inspectați întotdeauna planurile de execuție cu EXPLAIN (ANALYZE) pentru a vedea dacă baza de date este capabilă să exploateze indexurile și să se oprească prematur sau dacă scanează și sortează întregul set de rezultate.

Combinarea ROW_NUMBER() cu DISTINCT, CTE-uri și subinterogări

O problemă comună atunci când se utilizează ROW_NUMBER() pe langa DISTINCT este că funcția fereastră este evaluată înainte de pasul de eliminare a duplicatelor. Acest lucru poate duce la rezultate confuze, în care valorile aparent duplicate primesc în continuare numere de rând distincte.

De exemplu, dacă încercați să enumerați prețuri distincte dintr-un products tabel cu o singură interogare, cum ar fi:

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

S-ar putea să fiți surprinși să vedeți mai multe rânduri cu aceleași price dar diferit rn valori, deoarece funcția fereastră a rulat pe toate rândurile anterioare DISTINCT a eliminat duplicatele din proiecția finală.

Modul robust de a gestiona acest lucru este de a materializa mai întâi valorile distincte (printr-un CTE sau o subinterogare), apoi de a aplica ROW_NUMBER() pe lângă asta:

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

Alternativ, puteți utiliza o subinterogare direct în FROM clauză, obținând același efect. Ideea critică este de a decide în mod explicit care set de rezultate este „fereastra” în care ROW_NUMBER() ar trebui să funcționeze, iar dacă aveți nevoie de unicitate, construiți mai întâi acel set.

Acest model este extrem de util pentru sarcini de paginare precum „obțineți produsul cu al treilea cel mai mare preț” sau „enumerați prețuri distincte cu numere de rând și apoi alegeți unul anume”. Mai întâi puteți obține prețuri sortate unice cu ROW_NUMBER() și apoi alătură-te sau filtrează după rangul specific care te interesează.

ROW_NUMBER() pentru clasament, top-N și eliminare duplicate

Deși ne concentrăm pe paginare, ar fi o risipă să nu menționăm că ROW_NUMBER() este un instrument fantastic pentru clasificarea și deduplicarea datelor. Multe dintre aceleași modele pe care le folosești pentru paginare servesc și ca logică de clasare.

Pentru a obține primele N înregistrări per categorie, puteți partiționa după categorie și clasifica rândurile după o metrică, cum ar fi descrescătorul de preț:

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;

Aceasta returnează cele mai scumpe două produse din fiecare categorie. Apoi te poți alătura unui categories tabel folosind USING (category_id) sau o joncțiune explicită pentru a afișa nume lizibile de către om.

Pentru eliminarea duplicatelor, ROW_NUMBER() este adesea folosit în combinație cu PARTITION BY pentru a marca toate aparițiile din fiecare grup, cu excepția primei, ca duplicate. Luați în considerare un tabel simplu:

CREATE TABLE items (
id INT,
name VARCHAR
);

Să presupunem că introduceți mai multe nume duplicate și doriți să eliminați copiile suplimentare, păstrând în același timp cel mai mic ID pentru fiecare nume. Mai întâi puteți identifica duplicatele:

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

Orice rând cu row_number > 1 este un duplicat. Puteți utiliza apoi un CTE și un DELETE declarație pentru a le elimina:

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

După rularea acesteia, selectând dintre items va afișa doar nume distincte, cu un rând reprezentativ păstrat pentru fiecare valoare. Aceasta este o modalitate clară și declarativă de a elimina duplicatele, controlând în același timp exact ce rând păstrați.

ROW_NUMBER() vs RANK() vs DENSE_RANK() în scenarii de paginare

PostgreSQL oferă mai multe funcții pentru fereastra de clasificare: ROW_NUMBER(), RANK() și DENSE_RANK(). Deși toate atribuie numere ordonate, se comportă diferit atunci când există legături în coloanele sortate.

Diferențele importante sunt:

• ROW_NUMBER() atribuie întotdeauna un număr întreg unic fiecărui rând, chiar și atunci când apar egalități; numerele sunt strict secvențiale (1, 2, 3, 4, ...).
• RANK() acordă același rang valorilor identice, dar omite numerele după egalități (de exemplu, 1, 2, 2, 4 – rangul 3 lipsește).
• DENSE_RANK() acordă același rang și pentru egalități, dar nu omite numerele (1, 2, 2, 3).

Pentru paginare, ROW_NUMBER() este de obicei cea mai sigură alegere, deoarece garantează exact un rând per număr, ceea ce se potrivește în mod natural cu intervale de pagini precum 1-10, 11-20 etc. Daca ai folosit RANK() or DENSE_RANK(), ați putea ajunge la pagini care au mai puține sau mai multe rânduri decât era de așteptat din cauza legăturilor.

Pe de altă parte, pentru raportarea cazurilor de utilizare precum rezultatele competițiilor în care valorile egale trebuie să împartă aceeași poziție, RANK() or DENSE_RANK() reprezintă intenția mai bine decât ROW_NUMBER(). Puteți pagina în continuare acele rezultate, dar trebuie să fiți conștienți că „poziția” nu mai corespunde direct numărului fizic al rândului.

Sfaturi practice, capcane și considerații privind performanța

Atunci când se utilizează ROW_NUMBER() Pentru paginare și analiză, câteva bune practici vă vor scuti de erori subtile și dureri de cap inutile legate de performanță. Majoritatea se rezumă la a fi explicite și deterministe.

Definește întotdeauna clar ORDER BY în interiorul OVER() clauză. Fără aceasta, PostgreSQL este liber să returneze rânduri în orice ordine în scopul funcției window, iar numerele rândurilor se pot schimba între execuții chiar dacă datele subiacente sunt identice.

Ori de câte ori este posibil, includeți o coloană unică (adesea cheia primară) la sfârșitul ORDER BY listă. Aceasta transformă ordinea într-o ordine totală și evită ambiguitatea cu legături, ceea ce este esențial pentru paginarea bazată pe cursor și pentru rezultate previzibile din primele N elemente.

Nu vă așteptați să utilizați funcțiile ferestrei direct în WHERE clauză a aceleiași instrucțiuni SELECT. În schimb, încadrați-le într-un CTE sau o subinterogare și filtrați în funcție de coloana derivată din interogarea externă. Acest model este simplu, reutilizabil și menține codul SQL lizibil.

Când paginați, preferați ordonarea pe coloane indexate ori de câte ori este posibil. Ambele ORDER BY și ROW_NUMBER() se bazează pe sortare sau scanări de index; indexarea corectă poate face diferența dintre milisecunde și secunde în tabelele mari.

Fiți atenți când combinați PARTITION BY cu paginare în unele motoare. În anumite produse și versiuni de baze de date, utilizarea funcțiilor ferestrelor partiționate în interiorul vizualizărilor sau subinterogărilor poate dezactiva optimizările stopkey altfel disponibile, determinând motorul să proceseze mai multe rânduri decât este necesar. Testarea cu date realiste și citirea planurilor de interogare este esențială.

Pentru seturi de date foarte mari și date extrem de dinamice, luați în considerare combinarea ROW_NUMBER() paginare pentru vizualizări „în stil administrator” cu paginare a setului de chei bazată pe cursor pentru endpoint-uri orientate spre utilizator. În acest fel, obțineți atât interogări deterministe pentru instrumente, cât și o navigare eficientă și stabilă în API sau UI.

Privit în ansamblu, ROW_NUMBER() nu este doar un truc de paginare: este un element analitic puternic care vă permite să numerotați, să clasificați, să segmentați și să curățați seturile de rezultate în PostgreSQL și în alte motoare SQL importante cu aceeași logică fundamentală. Stăpânirea acesteia – împreună cu o înțelegere solidă a OVER(), PARTITION BY, și diferențele față de RANK() și DENSE_RANK() – vă oferă un set de instrumente foarte flexibil pentru paginare eficientă, interogări de top N și deduplicare robustă în aplicații din lumea reală.