PricingLoginStart free
Forberedelse til SQL-interview · Lektion

Top-N-rækker pr. gruppe med ROW_NUMBER

Det klassiske partition-og-rangér-mønster til top 3 pr. kategori

Lektion 1 af 413 trin

Top-N-rækker pr. gruppe med ROW_NUMBER er en gratis Forberedelse til SQL-interview-lektion på CoddyKit. Dette er lektion 1 af 4. Du kan læse hele lektionen gratis nedenfor — og derefter øve dig praktisk i browseren med en indbygget kodeeditor og en AI-vejleder, der er tilgængelig døgnet rundt. Den er en del af læringsforløbet i Forberedelse til SQL-interview, og dine fremskridt synkroniseres på tværs af nettet og CoddyKit-appen. Forberedelse til SQL-interview-kurset indeholder 4 lektioner i alt.

Spørgsmålet om de N bedste pr. gruppe

En af de mest almindelige SQL-opgaver til jobsamtaler lyder enkel: "Returnér de 3 bedst lønnede medarbejdere i hver afdeling." Kandidater, der straks griber til LIMIT, svarer forkert, fordi LIMIT begrænser hele resultatsættet, ikke hver gruppe.

Intervieweren undersøger, om du kender vinduesfunktioner. Det kanoniske svar er: Nummerér rækkerne inden for hver gruppe, og behold derefter de rækker, hvis nummer er ≤ N. Denne lektion opbygger mønstret trin for trin.

Hvorfor LIMIT ikke kan løse opgaven

Antag, at du skriver forespørgslen nedenfor. Den returnerer kun 3 rækker i alt fra hele tabellen, ikke 3 pr. afdeling.

LIMIT (eller TOP eller FETCH FIRST) fungerer på det endelige resultatsæt. Der findes ikke en LIMIT pr. gruppe i standard-SQL. Når en interviewer hører dig foreslå LIMIT 3 til et problem pr. gruppe, tyder det på, at du ikke har forstået partitionering fuldt ud.

-- WRONG: only 3 rows total, not 3 per department
SELECT department, name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

Mød ROW_NUMBER

ROW_NUMBER() er en vinduesfunktion, der tildeler hvert række et entydigt heltal uden huller i henhold til en sortering. I sig selv nummererer den hele resultatet.

Den afgørende ingrediens er PARTITION BY: Nummereringen starter forfra ved 1 for hver gruppe. Kombinér PARTITION BY department med ORDER BY salary DESC, så får hver afdeling sin egen rangering fra 1, 2, 3 ... efter løn.

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

Læsning af det nummererede resultat

Efter at du har kørt den forrige forespørgsel, har hver række en værdi for rn. I hver afdeling får den højeste løn rn = 1, den næsthøjeste får 2 og så videre. Når en ny afdeling begynder, nulstilles nummereringen til 1.

  • Salg: Ana (1), Bo (2), Cal (3), Dee (4)
  • Udvikling: Eve (1), Fin (2), Gus (3)

Nu betyder "de 3 bedste pr. afdeling" ganske enkelt "behold rækker, hvor rn <= 3".

Du kan ikke filtrere rn i WHERE

Det naturlige næste skridt er WHERE rn <= 3, men det virker ikke. Vinduesfunktioner beregnes efter WHERE-sætningen i den logiske udførelsesrækkefølge, så aliaset rn findes ikke endnu, når WHERE udføres.

Interviewere elsker denne fælde. Løsningen er at beregne vinduesfunktionen i en underforespørgsel eller CTE og derefter filtrere resultatet af den indre forespørgsel i en ydre forespørgsel.

-- ERROR: rn does not exist in WHERE
SELECT name, department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees
WHERE rn <= 3;

Den kanoniske CTE-løsning

Pak nummereringen ind i en CTE med navnet ranked, og vælg derefter fra den med filteret i den ydre WHERE. Det er det svar, interviewere gerne vil se, og det er nemt at læse.

Husk denne skabelon: Brug PARTITION BY til gruppen, ORDER BY til målekolonnen, og filtrér rn ≤ N i den ydre forespørgsel. Den kan bruges til den bedste, de 5 bedste eller et hvilket som helst N ved at ændre ét tal.

WITH ranked AS (
  SELECT
    name, department, salary,
    ROW_NUMBER() OVER (
      PARTITION BY department
      ORDER BY salary DESC
    ) AS rn
  FROM employees
)
SELECT name, department, salary
FROM ranked
WHERE rn <= 3
ORDER BY department, rn;

Underforespørgselsformen

Hvis interviewerens SQL-dialekt er ældre, eller intervieweren foretrækker underforespørgsler, kan den samme logik placeres i en afledt tabel i FROM. Husk, at en afledt tabel skal have et alias (r her), ellers får du en syntaksfejl.

CTE-formen og formen med en afledt tabel kan bruges om hinanden i dette problem. Vælg den, intervieweren finder mest læselig; begge er lige korrekte.

SELECT name, department, salary
FROM (
  SELECT name, department, salary,
         ROW_NUMBER() OVER (
           PARTITION BY department ORDER BY salary DESC
         ) AS rn
  FROM employees
) AS r
WHERE rn <= 3;

Den bedste pr. gruppe

"Find den bedst lønnede medarbejder i hver afdeling" betyder blot N = 1. Sæt filteret til rn = 1.

Hvorfor ikke MAX(salary) sammen med GROUP BY department? Fordi MAX giver dig lønværdien, men ikke resten af medarbejderens række, f.eks. navn og ansættelsesdato. ROW_NUMBER bevarer hele den valgte række, hvilket som regel er det, spørgsmålet faktisk kræver.

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY department ORDER BY salary DESC
         ) AS rn
  FROM employees
)
SELECT name, department, salary, hire_date
FROM ranked
WHERE rn = 1;

Tilføjelse af en entydig sekundær sorteringsnøgle

ROW_NUMBER returnerer altid præcis N rækker, selv når lønningerne er ens. Men hvilken af de ligeplacerede rækker der får rn = 1, er vilkårligt, medmindre du afgør rækkefølgen. Hvis to personer tjener 90000, og du kun beholder rn = 1, kan den valgte række variere fra kørsel til kørsel.

Tilføj en sekundær, entydig sorteringsnøgle som employee_id, så resultatet bliver stabilt og reproducerbart. Interviewere værdsætter kandidater, der nævner determinisme uden at blive spurgt.

ROW_NUMBER() OVER (
  PARTITION BY department
  ORDER BY salary DESC, employee_id ASC
) AS rn

Et konkret gennemarbejdet eksempel

Givet en sales-tabel med region, product og revenue skal du returnere de 2 produkter med størst omsætning pr. region. Samme opskrift: partitionér efter region, sortér efter revenue DESC, og behold rn <= 2.

Bemærk, at kun partitionskolonnen og målekolonnen ændres. Strukturen er identisk uanset forretningsområdet.

WITH ranked AS (
  SELECT region, product, revenue,
         ROW_NUMBER() OVER (
           PARTITION BY region ORDER BY revenue DESC, product
         ) AS rn
  FROM sales
)
SELECT region, product, revenue
FROM ranked
WHERE rn <= 2
ORDER BY region, rn;

Ydeevne og pointer til jobsamtalen

Hvis du vil imponere ud over blot at have et korrekt svar, så nævn:

  • Et indeks på (department, salary DESC) hjælper databasemotoren med effektivt at producere sorterede rækker pr. partition.
  • Vinduestilgangen gennemløber tabellen én gang og er langt bedre end en korreleret underforespørgsel, der køres for hver række.
  • Til meget store datasæt med den bedste række pr. gruppe understøtter nogle motorer DISTINCT ON (Postgres) som en genvej, men ROW_NUMBER er den portable standard.

Angiv altid din sekundære sorteringsnøgle, og bekræft det ønskede N.

Hurtigt tjek

Afprøv, hvor godt du forstår mønstret med de N bedste pr. gruppe.

Besvar den i den interaktive lektion

Opsummering: De N bedste pr. gruppe

Mønstret kort fortalt: Brug PARTITION BY til gruppen, ORDER BY til målekolonnen, tildel ROW_NUMBER, og behold rn ≤ N i en ydre forespørgsel.

  • LIMIT begrænser hele sættet, aldrig pr. gruppe.
  • Du kan ikke filtrere vinduesaliaset i WHERE; pak det ind i en CTE eller underforespørgsel.
  • Tilføj en entydig sekundær sorteringsnøgle for deterministiske resultater.
  • Den bedste pr. gruppe bevarer hele den vindende række, modsat MAX + GROUP BY.

Skift ét tal, så løser den samme forespørgsel problemet med den bedste, de 5 bedste eller et hvilket som helst N.

Gratis at komme i gang

Lær SQL med en AI-underviser — gratis

Skriv og kør rigtig kode i din browser, få øjeblikkelig hjælp fra en AI-underviser døgnet rundt, og fortsæt, hvor du slap, på web eller i appen.

Kurser
30
Lektioner
120
Start gratisSe hele kurset

Ofte stillede spørgsmål

Er lektionen “Top-N-rækker pr. gruppe med ROW_NUMBER” gratis?

Ja — hele teksten til “Top-N-rækker pr. gruppe med ROW_NUMBER” kan læses gratis her på nettet. Hvis du vil øve dig interaktivt med en indbygget kodeeditor og en AI-vejleder døgnet rundt og få adgang til resten af Forberedelse til SQL-interview-kurset, skal du opgradere til CoddyKit PRO. Forberedelse til SQL-interview-kurset indeholder 4 lektioner i alt.

Hvad lærer jeg i “Top-N-rækker pr. gruppe med ROW_NUMBER”?

Det klassiske partition-og-rangér-mønster til top 3 pr. kategori Du øver dig i Forberedelse til SQL-interview med praktisk kode, som du kører direkte i browseren, og en AI-vejleder døgnet rundt besvarer dine spørgsmål, mens du arbejder dig gennem lektionen.

Skal jeg have erfaring for at begynde på Forberedelse til SQL-interview?

Der kræves ingen tidligere erfaring. Forberedelse til SQL-interview på CoddyKit er tilrettelagt for både begyndere og øvede, så du kan starte her eller fra begyndelsen og lære i dit eget tempo. Dette er lektion 1 af 4.

Hvor lang tid tager lektionen “Top-N-rækker pr. gruppe med ROW_NUMBER”?

De fleste CoddyKit-lektioner tager cirka 5–10 minutter. Hver lektion er kort og interaktiv, så du gør løbende fremskridt og kan fortsætte, hvor du slap – på både web og app.

Kan jeg skrive og køre kode i denne Forberedelse til SQL-interview-lektion?

Ja. Alle Forberedelse til SQL-interview-lektioner har en indbygget kodeeditor, så du kan skrive og køre rigtig kode direkte i din browser og få øjeblikkelig feedback fra AI – uden lokal opsætning.

Alle lektioner i dette kursus

  1. Top-N-rækker pr. gruppe med ROW_NUMBER
  2. Håndtering af ligheder i Top-N
  3. Sikker fjernelse af dubletter
  4. Bevar den seneste række pr. nøgle
← Tilbage til Forberedelse til SQL-interview