Paginering i PostgreSQL med ROW_NUMBER forklaret

Hjem » Python » Paginering i PostgreSQL med ROW_NUMBER forklaret

Paginering af store resultatsæt i PostgreSQL kan virke simpelt ved første øjekast, men at gøre det effektivt og korrekt – især når der er ligheder i din rækkefølgekolonne – kræver lidt mere end blot at sætte en LIMIT/OFFSET på en forespørgsel. Vinduesfunktionen ROW_NUMBER() er et af de mest alsidige værktøjer, du har til at løse dette problem, samtidig med at det åbner op for en række ekstra analytiske anvendelsesscenarier som rangering, top-N-forespørgsler eller duplikatdetektion.

Denne guide dykker ned i, hvordan man bruger den ROW_NUMBER() for paginering i PostgreSQL, hvordan det rent faktisk fungerer under motorhjelmen, hvordan det adskiller sig fra andre rangeringsfunktioner, hvilke ydeevnemæssige konsekvenser man kan forvente, og hvordan andre større databasemotorer opfører sig med lignende mønstre. Vi vil også se på vanskelige scenarier fra den virkelige verden, såsom markørbaseret paginering, når sorteringskolonnen indeholder dubletter, og hvordan man kombinerer dem. ROW_NUMBER() med CTE'er, joins og underforespørgsler til ren, produktionsklar SQL.

Hvad PostgreSQL ROW_NUMBER() vinduesfunktionen rent faktisk gør

I sin kerne ROW_NUMBER() er en vinduesfunktion, der tildeler et unikt, sekventielt heltal til hver række i et resultatsæt, startende ved 1 og stigende med 1 uden mellemrum. Denne nummerering kan anvendes på hele resultatet eller genstartes for hver gruppe af rækker, afhængigt af hvordan du definerer vinduet.

Den generiske syntaks i PostgreSQL ser sådan ud:

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

To dele inde i OVER klausul styrer, hvordan rækkenumre tildeles: PARTITION BY (valgfrit) opdeler resultatsættet i uafhængige grupper, og ORDER BY (obligatorisk) definerer rækkefølgen inden for hver partition, der bestemmer hvilken række der får hvilket nummer.

Hvis du undlader PARTITION BY, behandler funktionen det komplette resultatsæt som en enkelt partition og nummererer blot alle rækker i henhold til den angivne rækkefølge. Når du har tilføjet PARTITION BY, rækkenummereringen starter forfra fra 1 i hver partition, hvilket er yderst nyttigt til rangeringer pr. kategori, top-N-pr. gruppe-forespørgsler og grupperet deduplikering.

I modsætning til rangeringsfunktioner som f.eks. RANK() or DENSE_RANK(), ROW_NUMBER() ignorerer fuldstændigt ligheder og gentager aldrig et tal, selv når rækker har identiske værdier i sorteringskolonnerne. Det gør den ideel til deterministisk pagination og præcis rækkeopdeling.

Grundlæggende ROW_NUMBER() eksempler til at opbygge intuition

Før du bruger ROW_NUMBER() For pagination hjælper det at se det i aktion på simple eksempler, hvor målet blot er at nummerere rækker på en kontrolleret måde. Forestil dig et bord employees med kolonner id, name, department og salary.

For at tildele et globalt rækkenummer sorteret efter faldende løn, kan du skrive:

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

Denne forespørgsel returnerer alle medarbejdere, sorteret efter løn fra højeste til laveste, med row_num = 1 for den bedst betalte medarbejder, 2 for den anden, og så videre, uden mellemrum og uden gentagne værdier. Lønbindinger brydes vilkårligt, medmindre du forlænger ORDER BY med yderligere kolonner.

Hvis du i stedet har brug for rækkenummerering for at genstarte inden for hver afdeling, kombinerer du PARTITION BY med ORDER BY:

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

Her får hver afdeling sin egen 1, 2, 3, ... sekvens, så du nemt kan finde "den højestlønnede i hver afdeling" ved senere at filtrere efter row_num = 1 i en ekstern forespørgsel eller CTE. Dette mønster er rygraden for mange top-N-pr. gruppe-forespørgsler.

For at adskille nummereringslogik fra filtreringslogik tydeligt er det almindeligt at pakke vinduesfunktionen ind i en CTE eller underforespørgsel og derefter filtrere de genererede rækkenumre i den ydre SELECT. Dette er især vigtigt, fordi vinduesfunktioner ikke kan bruges direkte i WHERE klausul i den samme SELECT, der definerer dem.

Brug af ROW_NUMBER() til klassisk sidebaseret paginering

Den mest ligefremme måde at lave paginering med ROW_NUMBER() I PostgreSQL er det at beregne rækkeindekset for hver række og derefter bede om et numerisk område, der svarer til den ønskede side. Dette bruges nogle gange som et alternativ til OFFSET/LIMIT, og fungerer også fint, når man porterer paginationskode fra SQL Server eller Oracle.

Antag, at du ønsker en side med resultater med størrelse @PageSize og sidetal @PageNumber (0-baseret indeks). En typisk T-SQL-forespørgsel ser sådan ud:

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

Den samme logik oversættes direkte til PostgreSQL – du tilpasser kun parametersyntaksen og, hvis du vil, pakker den ind i en funktion i stedet for en lagret procedure. Essensen er: beregning ROW_NUMBER() én gang, og opdel derefter rækker med et numerisk interval, der svarer til dine sidegrænser.

For eksempel, i PostgreSQL, kan du for en fast side skrive:

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;

Dette returnerer rækkerne 11-20 i den rækkefølge, der er defineret af ORDER BY title, id, hvilket effektivt giver dig den anden side med sidestørrelse 10. Den store fordel i forhold til almindelig OFFSET er, at rækkenumrene er eksplicitte og kan kombineres med yderligere logik, for eksempel tilbagekobling, filtrering eller yderligere analyser.

Paginering i markørstil, når sorteringskolonnen har dubletter

Offset-baseret paginering er let at ræsonnere omkring, men det kan forårsage ydeevneproblemer på store tabeller og bliver også skrøbeligt, når de underliggende data ændres mellem anmodninger. Markørbaseret paginering (også kaldet keyset-paginering) sigter mod at løse dette ved at bruge det sidst sete element som et anker i stedet for en absolut forskydning.

Tingene bliver vanskelige, når den kolonne, du sorterer efter, indeholder dubletter. Overvej et skema med posts og 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
);

Forestil dig, at du først opretter en forespørgsel, der sorterer indlæg efter antal kommentarer i faldende rækkefølge:

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;

For markørbaseret paginering kan man være fristet til at vælge op til en bestemt comments_count tærskel og derefter anvende en 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;

Problemet opstår, når flere opslag deler det samme comments_count. Hvis to indlæg begge har en tæller på 2, og din markør peger på et af dem, ved hjælp af <= inkluderer begge de bundne rækker på den anden side, mens man bruger < springer alle rækker med samme antal over og hopper for langt, hvilket går glip af nogle indlæg, du forventede at se.

Dette er et klassisk symptom på sortering efter en ikke-unik nøgle i markørens paginering – databasen kan ikke deterministisk opdele datasættet "midt i" en gruppe af bånd, hvis din markør kun koder den ikke-unikke værdi. Du har brug for en unik og stabil rækkefølge for at definere en markør sikkert.

En løsning er at oprette en syntetisk, unik sorteringsnøgle ved at kombinere antallet af kommentarer med den primære nøgle, for eksempel via sammenkædning:

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;

Ved at bygge en sammensat nøgle som '2:00000000-...-0003', du gør rækkefølgen helt unik og kan trygt sige "giv mig rækker med comments_count_id mindre end ankeret” uden tvetydighed. Det er den samme idé som altid at inkludere id i din ORDER BY som en tiebreaker.

I praksis behøver du ikke at sammenkæde til en streng; du kan bare bruge flere kolonner i ORDER BY og indkode dem i dit markørobjekt i applikationslaget. Det vigtige fra databasesiden er, at den samlede rækkefølge er unik og reproducerbar mellem kald.

Paginering med ROW_NUMBER() vs. LIMIT og OFFSET

PostgreSQL understøtter den klassiske LIMIT og OFFSET syntaks lige fra starten, og for mange små til mellemstore resultatsæt er den helt fin at bruge. Du angiver blot, hvor mange rækker der skal springes over, og hvor mange der skal returneres.

OFFSET-baseret paginering har dog to store ulemper: ydeevne og stabilitet. As OFFSET vokser, men PostgreSQL skal stadig scanne og kassere alle de foregående rækker, før den kan begynde at returnere resultater, hvilket bliver dyrt på store datasæt. Og hvis data ændrer sig mellem anmodninger, kan siderne "forskyde" og vise dubletter eller springe rækker over.

Ved brug af ROW_NUMBER() for paginering giver dig en måde at materialisere rækkeindekset én gang og derefter opdele det rent:

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;

Dette mønster læses intuitivt: det tildeler først hvert produkt sin position i den sorterede liste, derefter henter den ydre forespørgsel rækkerne 11 til 20. Så længe de underliggende data ikke ændrer sig mellem udførelse og forbrug af siden, får du et stabilt udsnit af den logiske sekvens.

Det er sagt, ROW_NUMBER()-baseret pagination er heller ikke en mirror kugle til ydeevne. Databasen skal stadig evaluere vinduesfunktionen over alle berettigede rækker for at tildele numre, så for ekstremt store tabeller kan dette være lige så dyrt som en stor OFFSET. Det er en fordel, når det kombineres med yderligere filtrering, eller når du ønsker deterministisk, rækkenummerbaseret logik ud over ren paginering.

Hvordan vinduesfunktionspagination opfører sig på tværs af databasemotorer

Vinduesfunktioner som f.eks. ROW_NUMBER() er standardiserede SQL-funktioner, men hver databasemotor optimerer dem forskelligt til paginationslignende mønstre. Nogle produkter kan genkende "top-N med vinduesfunktion"-forespørgsler og stoppe scanningen tidligt ved hjælp af indeksadgang; andre vil behandle hele sættet i stilhed hver gang.

Overvej denne typiske top-N / pagineringsstil-forespørgsel ved hjælp af ROW_NUMBER over et ordnet indeks i en sales bord:

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;

Her, ROW_NUMBER tæller rækker i henhold til den rækkefølge, der er defineret i OVER klausul, og den ydre WHERE begrænser resultatet til en bestemt side (række 11-20). Dette er logisk set ækvivalent med en top-N-forespørgsel kombineret med en offset.

Oracle er for eksempel i stand til at genkende stopbetingelsen og bruge et indeks på sale_date og sale_id at implementere en "top-N i pipeline"-adfærd. Dens udførelsesplan kan vise WINDOW NOSORT STOPKEY, hvilket indikerer, at motoren ikke behøver en ekstra sortering og stopper, så snart den har produceret den øvre grænse af det ønskede vindue.

Understøttelse af denne type optimering er ikke universel. Nogle versioner af PostgreSQL og andre motorer som MySQL, MariaDB og Db2 stopper ikke indeksscanninger tidligt i disse vinduesfunktionsbaserede mønstre, hvilket betyder, at de stadig behandler flere rækker end strengt nødvendigt for at levere den ønskede side.

Nyere PostgreSQL-versioner (15+ og fremefter) har forbedret ydeevne og optimeringer for vinduesfunktioner, men adfærden kan stadig variere mellem de større versioner. Kontroller altid udførelsesplaner med EXPLAIN (ANALYZE) for at se, om din database er i stand til at udnytte indekser og stoppe tidligt, eller om den scanner og sorterer hele resultatsættet.

Kombination af ROW_NUMBER() med DISTINCT, CTE'er og underforespørgsler

En almindelig fejltagelse ved brug ROW_NUMBER() langs med DISTINCT er at vinduesfunktionen evalueres før duplikat-elimineringstrinnet. Dette kan føre til forvirrende resultater, hvor tilsyneladende duplikerede værdier stadig modtager forskellige rækkenumre.

Hvis du for eksempel forsøger at opregne forskellige priser fra en products tabel med en enkelt forespørgsel som:

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

Du vil måske blive overrasket over at se flere rækker med det samme price men anderledes rn værdier, fordi vinduesfunktionen kørte på alle rækker før DISTINCT fjernede dubletter fra den endelige projektion.

Den robuste måde at håndtere dette på er først at materialisere de forskellige værdier (via en CTE eller underforespørgsel) og derefter anvende ROW_NUMBER() oven i det:

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

Alternativt kan du bruge en underforespørgsel direkte i FROM klausul, der opnår samme effekt. Den afgørende idé er eksplicit at bestemme, hvilket resultatsæt der er det "vindue", hvor ROW_NUMBER() skal fungere, og hvis du har brug for unikhed, så byg det sæt først.

Dette mønster er yderst praktisk til pagineringsopgaver som "find produktet med den 3. højeste pris" eller "opregn forskellige priser med rækkenumre og vælg derefter en specifik". Du kan først få unikke sorterede priser med ROW_NUMBER() og tilmeld dig eller filtrer derefter efter den specifikke rang, du er interesseret i.

ROW_NUMBER() til fjernelse af rangering, top-N og dubletter

Selvom vores fokus er paginering, ville det være spild af tid ikke at nævne det. ROW_NUMBER() er et fantastisk værktøj til rangering og deduplikering af data. Mange af de samme mønstre, som du bruger til pagination, fungerer også som rangeringslogik.

For at få de N bedste poster pr. kategori kan du opdele efter kategori og rangere rækker efter en metrik, f.eks. pris faldende:

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;

Dette returnerer de to dyreste produkter i hver kategori. Du kan derefter deltage i en categories tabel ved hjælp af USING (category_id) eller en eksplicit join for at vise menneskelæsbare navne.

For fjernelse af dubletter, ROW_NUMBER() bruges ofte i kombination med PARTITION BY for at markere alle undtagen den første forekomst i hver gruppe som dubletter. Overvej en simpel tabel:

CREATE TABLE items (
id INT,
name VARCHAR
);

Antag, at du indsætter flere dubletter af navne og vil fjerne de ekstra kopier, mens du beholder det laveste id for hvert navn. Du kan først identificere dubletter:

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

Enhver række med row_number > 1 er en duplikat. Du kan derefter bruge en CTE og en DELETE erklæring om at fjerne dem:

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

Efter at have kørt dette, skal du vælge fra items vil kun vise forskellige navne, med én repræsentativ række pr. værdi. Dette er en ren, deklarativ måde at deduplikere på, samtidig med at du kontrollerer præcis, hvilken række du bevarer.

ROW_NUMBER() vs. RANK() vs. DENSE_RANK() i pagineringsscenarier

PostgreSQL tilbyder adskillige funktioner i rangeringsvinduet: ROW_NUMBER(), RANK()og DENSE_RANK(). Selvom de alle tildeler ordnede tal, opfører de sig forskelligt, når der er ligheder i de sorterede kolonner.

De vigtige forskelle er:

• ROW_NUMBER() tildeler altid et unikt heltal til hver række, selv når der er uafgjort; tallene er strengt fortløbende (1, 2, 3, 4, ...).
• RANK() giver samme rang til identiske værdier, men springer tal over efter uafgjorte (for eksempel 1, 2, 2, 4 – rang 3 mangler).
• DENSE_RANK() giver også samme rang for uafgjorte, men springer ikke tal (1, 2, 2, 3) over.

For paginering, ROW_NUMBER() er normalt det sikreste valg, fordi det garanterer præcis én række pr. tal, hvilket naturligt knyttes til sideintervaller som 1-10, 11-20 osv. Hvis du brugte RANK() or DENSE_RANK(), kan du ende med sider, der har færre eller flere rækker end forventet på grund af bindinger.

På den anden side, til rapportering af brugsscenarier som konkurrenceresultater, hvor uafgjorte værdier skal dele samme position, RANK() or DENSE_RANK() repræsenterer intentionen bedre end ROW_NUMBER(). Du kan stadig paginere disse resultater, men du skal være opmærksom på, at "position" ikke længere direkte svarer til det fysiske rækkenummer.

Praktiske tips, faldgruber og præstationsovervejelser

Når du bruger ROW_NUMBER() For paginering og analyser vil en håndfuld gode fremgangsmåder spare dig for subtile fejl og unødvendige præstationsproblemer. De fleste af dem koger ned til at være eksplicitte og deterministiske.

Definer altid en klar ORDER BY Inde i OVER() klausul. Uden den kan PostgreSQL frit returnere rækker i en hvilken som helst rækkefølge med henblik på window-funktionen, og dine rækkenumre kan ændre sig mellem udførelser, selvom de underliggende data er identiske.

Når det er muligt, skal du inkludere en unik kolonne (ofte den primære nøgle) i slutningen af ORDER BY liste. Dette gør rækkefølgen til en samlet rækkefølge og undgår tvetydighed med bindinger, hvilket er afgørende for markørbaseret paginering og for forudsigelige top-N-resultater.

Forvent ikke at kunne bruge vinduesfunktioner direkte i WHERE klausul i den samme SELECT. I stedet skal du pakke dem ind i en CTE eller underforespørgsel og filtrere på den afledte kolonne i den ydre forespørgsel. Dette mønster er simpelt, genanvendeligt og holder din SQL læsbar.

Ved paginering, foretræk rækkefølge efter indekserede kolonner, når det er praktisk muligt. Både ORDER BY og ROW_NUMBER() Stol på sortering eller indeksscanninger; korrekt indeksering kan gøre forskellen mellem millisekunder og sekunder på store tabeller.

Vær forsigtig, når du kombinerer PARTITION BY med paginering i nogle motorer. I visse databaseprodukter og -versioner kan brugen af ​​partitionerede vinduesfunktioner i visninger eller underforespørgsler deaktivere ellers tilgængelige stoptastoptimeringer, hvilket får systemet til at behandle flere rækker end nødvendigt. Test med realistiske data og læsning af forespørgselsplaner er afgørende.

For meget store datasæt og meget dynamiske data, overvej at kombinere ROW_NUMBER() Paginering til "admin-stil"-visninger med markørbaseret nøglesæt-paginering til brugervendte slutpunkter. På den måde får du både deterministiske værktøjsforespørgsler og effektiv, stabil navigation i din API eller brugergrænseflade.

Set som en helhed, ROW_NUMBER() er ikke bare et pagineringstrick: det er en kraftfuld analytisk byggesten, der giver dig mulighed for at nummerere, rangere, opdele og rydde op i resultatsæt i PostgreSQL og på tværs af andre større SQL-motorer med den samme underliggende logik. At mestre det – sammen med en solid forståelse af OVER(), PARTITION BY, og forskellene fra RANK() og DENSE_RANK() – giver dig et meget fleksibelt værktøjssæt til effektiv paginering, top-N-forespørgsler og robust deduplikering i virkelige applikationer.