Paginering i PostgreSQL med ROW_NUMBER forklart

Home » Python » Paginering i PostgreSQL med ROW_NUMBER forklart

Paginering av store resultatsett i PostgreSQL kan virke enkelt ved første øyekast, men å gjøre det effektivt og riktig – spesielt når det er koblinger i rekkefølgekolonnen – krever litt mer enn bare å sette en LIMIT/OFFSET på en spørring. Vindusfunksjonen ROW_NUMBER() er et av de mest allsidige verktøyene du har for å løse dette problemet, samtidig som det låser opp en rekke ekstra analytiske bruksområder som rangering, topp-N-spørringer eller duplikatdeteksjon.

Denne guiden går i dybden på hvordan du bruker den ROW_NUMBER() for paginering i PostgreSQL, hvordan det egentlig fungerer under panseret, hvordan det skiller seg fra andre rangeringsfunksjoner, hvilke ytelsesimplikasjoner man kan forvente, og hvordan andre store databasemotorer oppfører seg med lignende mønstre. Vi vil også se på vanskelige virkelige scenarier som markørbasert paginering når sorteringskolonnen inneholder duplikater og hvordan man kombinerer dem. ROW_NUMBER() med CTE-er, koblinger og underspørringer for ren, produksjonsklar SQL.

Hva PostgreSQL ROW_NUMBER()-vindusfunksjonen faktisk gjør

I kjernen, ROW_NUMBER() er en vindusfunksjon som tilordner et unikt, sekvensielt heltall til hver rad i et resultatsett, startende på 1 og økende med 1 uten mellomrom. Denne nummereringen kan brukes på hele resultatet eller startes på nytt for hver gruppe med rader, avhengig av hvordan du definerer vinduet.

Den generiske syntaksen i PostgreSQL ser slik ut:

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

To deler inni OVER klausulen styrer hvordan radnumre tildeles: PARTITION BY (valgfritt) deler resultatsettet inn i uavhengige grupper, og ORDER BY (obligatorisk) definerer rekkefølgen innenfor hver partisjon som avgjør hvilken rad som får hvilket nummer.

Hvis du utelater PARTITION BY, behandler funksjonen hele resultatsettet som én partisjon og nummererer ganske enkelt alle rader i henhold til den angitte rekkefølgen. Når du har lagt til PARTITION BY, radnummereringen starter på nytt fra 1 i hver partisjon, noe som er ekstremt nyttig for rangeringer per kategori, topp-N-per-gruppe-spørringer og gruppert deduplisering.

I motsetning til rangeringsfunksjoner som RANK() or DENSE_RANK(), ROW_NUMBER() ignorerer fullstendig koblinger og gjentar aldri et tall, selv når rader har identiske verdier i sorteringskolonnene. Det gjør den ideell for deterministisk paginering og presis radinndeling.

Grunnleggende ROW_NUMBER()-eksempler for å bygge intuisjon

Før du bruker ROW_NUMBER() Når det gjelder paginering, hjelper det å se det i aksjon på enkle eksempler, der målet bare er å nummerere rader på en kontrollert måte. Tenk deg et bord employees med kolonner id, name, department og salary.

For å tilordne et globalt radnummer sortert etter synkende lønn, kan du skrive:

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

Denne spørringen returnerer alle ansatte, sortert etter lønn fra høyeste til laveste, med row_num = 1 for den best betalte ansatte, 2 for den andre, og så videre, uten mellomrom og uten gjentatte verdier. Lønnsbindinger brytes vilkårlig med mindre du forlenger ORDER BY med ekstra kolonner.

Hvis du i stedet trenger radnummerering for å starte på nytt i hver avdeling, 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 avdeling sin egen 1, 2, 3, …-sekvens, slik at du enkelt kan finne «den som tjener mest i hver avdeling» ved å filtrere senere etter row_num = 1 i en ekstern spørring eller CTE. Dette mønsteret er ryggraden for mange topp-N-per-gruppe-spørringer.

For å skille nummereringslogikk fra filtreringslogikk på en tydelig måte, er det vanlig å pakke vindusfunksjonen inn i en CTE eller delspørring og deretter filtrere de genererte radnumrene i den ytre SELECT-verdien. Dette er spesielt viktig fordi vindusfunksjoner ikke kan brukes direkte i WHERE klausulen i den samme SELECT-en som definerer dem.

Bruk av ROW_NUMBER() for klassisk sidebasert paginering

Den enkleste måten å gjøre paginering med ROW_NUMBER() I PostgreSQL går det ut på å beregne radindeksen for hver rad, og deretter be om et numerisk område som tilsvarer siden du ønsker. Dette brukes noen ganger som et alternativ til OFFSET/LIMIT, og fungerer også fint når man porterer pagineringskode fra SQL Server eller Oracle.

Anta at du ønsker en side med resultater med størrelse @PageSize og sidetall @PageNumber (0-basert indeks). En typisk T-SQL-spørring ser slik ut:

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 logikken oversettes direkte til PostgreSQL – du tilpasser bare parametersyntaks og, hvis du vil, pakker den inn i en funksjon i stedet for en lagret prosedyre. Essensen er: beregning ROW_NUMBER() én gang, og segmenter deretter rader med et numerisk intervall som tilsvarer sidegrensene dine.

For eksempel, i PostgreSQL, for en fast side kan du 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 radene 11–20 i rekkefølgen definert av ORDER BY title, id, noe som effektivt gir deg den andre siden med sidestørrelse 10. Den store fordelen i forhold til vanlig OFFSET er at radnumrene er eksplisitte og kan kombineres med tilleggslogikk, for eksempel tilbakekobling, filtrering eller ytterligere analyser.

Markørstil-paginering når sorteringskolonnen har duplikater

Offsetbasert paginering er lett å resonnere rundt, men det kan forårsake ytelsesproblemer på store tabeller og blir også skjørt når de underliggende dataene endres mellom forespørsler. Markørbasert paginering (også kalt keyset-paginering) har som mål å løse dette ved å bruke det sist sett elementet som et anker i stedet for en absolutt forskyvning.

Ting blir vanskelige når kolonnen du sorterer etter inneholder dupliserte verdier. Tenk deg et skjema 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
);

Tenk deg at du først lager en spørring som sorterer innlegg etter antall kommentarer i synkende rekkefø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ørbasert paginering kan man bli fristet til å velge opptil en viss comments_count terskel og deretter bruke 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 oppstår når flere innlegg deler det samme comments_count. Hvis to innlegg begge har en telling på 2, og markøren peker på et av dem, bruk <= inkluderer begge de bundne radene på den andre siden, mens du bruker < hopper over alle rader med samme antall og hopper for langt, og går glipp av noen innlegg du forventet å se.

Dette er et klassisk symptom på sortering etter en ikke-unik nøkkel i markørpaginering – databasen kan ikke deterministisk dele datasettet «midt i» en gruppe med bånd hvis markøren bare koder den ikke-unike verdien. Du trenger en unik og stabil rekkefølge for å definere en markør på en sikker måte.

En løsning er å lage en syntetisk, unik sorteringsnøkkel ved å kombinere kommentarantall med primærnøkkelen, for eksempel via sammenkobling:

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 å bygge en sammensatt nøkkel som '2:00000000-...-0003', du gjør bestillingen helt unik og kan trygt si «gi meg rader med comments_count_id mindre enn ankeret» uten tvetydighet. Det er den samme ideen som alltid, inkludert id i ORDER BY som en tie-breaker.

I praksis trenger du ikke å sammenkoble til en streng; du kan bare bruke flere kolonner i ORDER BY og kode dem i markørobjektet ditt i applikasjonslaget. Det viktige fra databasesiden er at den totale rekkefølgen er unik og reproduserbar mellom kall.

Paginering med ROW_NUMBER() vs. LIMIT og OFFSET

PostgreSQL støtter den klassiske LIMIT og OFFSET syntaks rett ut av boksen, og for mange små til mellomstore resultatsett er den helt grei å bruke. Du angir bare hvor mange rader som skal hoppes over, og hvor mange som skal returneres.

OFFSET-basert paginering har imidlertid to store ulemper: ytelse og stabilitet. As OFFSET vokser, men PostgreSQL må fortsatt skanne og forkaste alle de foregående radene før den kan begynne å returnere resultater, noe som blir dyrt på store datasett. Og hvis data endres mellom forespørsler, kan sidene «forskyve» seg og vise duplikater eller hoppe over rader.

Ved hjelp av ROW_NUMBER() for paginering gir deg en måte å materialisere radindeksen én gang og deretter dele den opp 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ønsteret leses intuitivt: det tilordner først hvert produkt sin plassering i den sorterte listen, deretter henter den ytre spørringen rad 11 til 20. Så lenge de underliggende dataene ikke endres mellom utførelse og konsumering av siden, får du en stabil del av den logiske sekvensen.

Med det sagt, ROW_NUMBER()-basert paginering er heller ikke en mirakelkur for ytelse. Databasen må fortsatt evaluere vindusfunksjonen over alle kvalifiserte rader for å tildele tall, så for ekstremt store tabeller kan dette være like kostbart som en stor OFFSET. Der det skinner er når det kombineres med ekstra filtrering eller når du ønsker deterministisk, radnummerbasert logikk utover ren paginering.

Hvordan paginering med vindusfunksjon oppfører seg på tvers av databasemotorer

Vindusfunksjoner som ROW_NUMBER() er standardiserte SQL-funksjoner, men hver databasemotor optimaliserer dem forskjellig for pagineringslignende mønstre. Noen produkter kan gjenkjenne «top-N med vindusfunksjon»-spørringer og stoppe skanning tidlig ved hjelp av indekstilgang; andre vil behandle hele settet i stillhet hver gang.

Tenk på denne typiske top-N / pagineringsstil-spørringen ved å bruke ROW_NUMBER over en ordnet indeks i en sales tabell:

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 teller rader i henhold til rekkefølgen som er definert i OVER klausulen, og den ytre WHERE begrenser resultatet til en bestemt side (rad 11–20). Dette er logisk sett ekvivalent med en top-N-spørring kombinert med en forskyvning.

Oracle, for eksempel, er i stand til å gjenkjenne stoppbetingelsen og bruke en indeks på sale_date og sale_id å implementere en «topp-N i pipeline»-oppførsel. Utførelsesplanen kan vise WINDOW NOSORT STOPKEY, som indikerer at motoren ikke trenger en ekstra sortering og vil stoppe så snart den har produsert den øvre grensen for det forespurte vinduet.

Støtte for denne typen optimalisering er ikke universell. Noen versjoner av PostgreSQL og andre motorer som MySQL, MariaDB og Db2 stopper ikke indeksskanninger tidlig i disse vindusfunksjonsbaserte mønstrene, noe som betyr at de fortsatt behandler flere rader enn strengt tatt nødvendig for å levere den forespurte siden.

Nyere PostgreSQL-versjoner (15+ og nyere) har forbedret ytelse og optimaliseringer for vindusfunksjoner, men oppførselen kan fortsatt variere mellom større versjoner. Kontroller alltid utførelsesplaner med EXPLAIN (ANALYZE) for å se om databasen din er i stand til å utnytte indekser og stoppe tidlig, eller om den skanner og sorterer hele resultatsettet.

Kombinering av ROW_NUMBER() med DISTINCT, CTE-er og delspørringer

En vanlig misforståelse når du bruker ROW_NUMBER() sammen DISTINCT er at vindusfunksjonen evalueres før duplikat-elimineringstrinnet. Dette kan føre til forvirrende resultater der tilsynelatende dupliserte verdier fortsatt får forskjellige radnumre.

Hvis du for eksempel prøver å liste opp forskjellige priser fra en products tabell med en enkelt spørring som:

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

Du kan bli overrasket over å se flere rader med det samme price men annerledes rn verdier, fordi vindusfunksjonen kjørte på alle radene før DISTINCT fjernet duplikater fra den endelige projeksjonen.

Den robuste måten å håndtere dette på er å først materialisere de distinkte verdiene (via en CTE eller delspørring), deretter bruke ROW_NUMBER() i tillegg til det:

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

Alternativt kan du bruke en delspørring direkte i FROM klausul, som oppnår samme effekt. Den kritiske ideen er å bestemme eksplisitt hvilket resultatsett som er «vinduet» der ROW_NUMBER() skal fungere, og hvis du trenger unikhet, bygg det settet først.

Dette mønsteret er ekstremt nyttig for pagineringsoppgaver som «finn produktet med den tredje høyeste prisen» eller «list opp distinkte priser med radnumre og velg deretter en spesifikk». Du kan først få unike sorterte priser med ROW_NUMBER() og bli deretter med eller filtrer etter den spesifikke rangen du bryr deg om.

ROW_NUMBER() for fjerning av rangering, topp-N og duplikat

Selv om fokuset vårt er paginering, ville det være bortkastet å ikke nevne det. ROW_NUMBER() er et fantastisk verktøy for rangering og deduplisering av data. Mange av de samme mønstrene du bruker for paginering fungerer også som rangeringslogikk.

For å få de N øverste postene per kategori, kan du dele opp etter kategori og rangere rader etter en beregning, for eksempel synkende pris:

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 gir de to dyreste produktene i hver kategori. Du kan deretter bli med i en categories tabell ved bruk av USING (category_id) eller en eksplisitt sammenføyning for å vise navn som er lesbare av mennesker.

For fjerning av duplikat, ROW_NUMBER() brukes ofte i kombinasjon med PARTITION BY for å markere alle unntatt den første forekomsten i hver gruppe som duplikater. Tenk deg en enkel tabell:

CREATE TABLE items (
id INT,
name VARCHAR
);

Anta at du setter inn flere dupliserte navn og vil fjerne de ekstra kopiene samtidig som du beholder den laveste ID-en for hvert navn. Du kan først identifisere duplikater:

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

Enhver rad med row_number > 1 er en duplikat. Du kan deretter bruke en CTE og en DELETE erklæring om å 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
);

Etter å ha kjørt dette, velg fra items vil bare vise forskjellige navn, med én representativ rad per verdi. Dette er en ren, deklarativ måte å deduplisere på, samtidig som du kontrollerer nøyaktig hvilken rad du bevarer.

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

PostgreSQL tilbyr flere rangeringsvindufunksjoner: ROW_NUMBER(), RANK()og DENSE_RANK(). Selv om alle tildeler ordnede tall, oppfører de seg forskjellig når det er bånd i de sorterte kolonnene.

De viktige forskjellene er:

• ROW_NUMBER() tilordner alltid et unikt heltall til hver rad, selv når det er uavgjort; tallene er strengt sekvensielle (1, 2, 3, 4, …).
• RANK() gir samme rang til identiske verdier, men hopper over tall etter uavgjort (for eksempel 1, 2, 2, 4 – rang 3 mangler).
• DENSE_RANK() gir også samme rangering for uavgjort, men hopper ikke over tall (1, 2, 2, 3).

For paginering, ROW_NUMBER() er vanligvis det sikreste valget fordi det garanterer nøyaktig én rad per tall, noe som naturlig tilordnes sideområder som 1–10, 11–20 osv. Hvis du brukte RANK() or DENSE_RANK(), kan du ende opp med sider som har færre eller flere rader enn forventet på grunn av koblinger.

På den annen side, for rapportering av brukstilfeller som konkurranseresultater der like verdier må dele samme posisjon, RANK() or DENSE_RANK() representerer intensjonen bedre enn ROW_NUMBER(). Du kan fortsatt paginere disse resultatene, men du må være oppmerksom på at «posisjon» ikke lenger direkte samsvarer med det fysiske radnummeret.

Praktiske tips, fallgruver og ytelseshensyn

Når du bruker ROW_NUMBER() For paginering og analyse vil en håndfull gode fremgangsmåter spare deg for subtile feil og unødvendige ytelsesproblemer. De fleste av dem koker ned til å være eksplisitte og deterministiske.

Definer alltid en tydelig ORDER BY Inne i OVER() klausul. Uten den kan PostgreSQL returnere rader i hvilken som helst rekkefølge for vindusfunksjonen, og radnumrene dine kan endres mellom kjøringer selv om de underliggende dataene er identiske.

Når det er mulig, inkluder en unik kolonne (ofte primærnøkkelen) på slutten av ORDER BY liste. Dette gjør rekkefølgen til en total rekkefølge og unngår tvetydighet med bindinger, noe som er kritisk for markørbasert paginering og forutsigbare top-N-resultater.

Ikke forvent å bruke vindusfunksjoner direkte i WHERE klausulen i den samme SELECT. Pakk dem heller inn i en CTE eller delspørring og filtrer på den avledede kolonnen i den ytre spørringen. Dette mønsteret er enkelt, gjenbrukbart og holder SQL-en din lesbar.

Når du paginerer, foretrekk å bruke indekserte kolonner når det er praktisk mulig. Begge ORDER BY og ROW_NUMBER() stol på sortering eller indeksskanninger; riktig indeksering kan utgjøre forskjellen mellom millisekunder og sekunder på store tabeller.

Vær forsiktig når du kombinerer PARTITION BY med paginering i noen motorer. I visse databaseprodukter og -versjoner kan bruk av partisjonerte vindusfunksjoner i visninger eller delspørringer deaktivere ellers tilgjengelige stopptastoptimaliseringer, noe som fører til at motoren behandler flere rader enn nødvendig. Testing med realistiske data og lesing av spørreplaner er viktig.

For svært store datasett og svært dynamiske data, vurder å kombinere ROW_NUMBER() paginering for visninger i «administratorstil» med markørbasert tastesett-paginering for brukervendte endepunkter. På den måten får du både deterministiske verktøyspørringer og effektiv, stabil navigasjon i API-et eller brukergrensesnittet ditt.

Sett som en helhet, ROW_NUMBER() er ikke bare et pagineringstriks: det er en kraftig analytisk byggestein som lar deg nummerere, rangere, dele opp og rydde opp i resultatsett i PostgreSQL og på tvers av andre store SQL-motorer med samme underliggende logikk. Å mestre det – sammen med en solid forståelse av OVER(), PARTITION BY, og forskjellene fra RANK() og DENSE_RANK() – gir deg et svært fleksibelt verktøysett for effektiv paginering, top-N-spørringer og robust deduplisering i virkelige applikasjoner.