Forberedelse til kodeintervjuer · leksjon

OVER, PARTITION BY og ORDER BY

Anatomien til en window-spesifikasjon, og hvordan partisjoner nullstiller beregningen.

Leksjon 1 av 413 trinn

OVER, PARTITION BY og ORDER BY er en gratis leksjon i Forberedelse til kodeintervjuer på CoddyKit. Dette er leksjon 1 av 4. Du kan lese hele leksjonen gratis nedenfor – og deretter øve praktisk i nettleseren med en innebygd kodeeditor og en AI-veileder som er tilgjengelig døgnet rundt. Den er en del av læringsløpet i Forberedelse til kodeintervjuer, og fremdriften din synkroniseres mellom nettet og CoddyKit-appen. Kurset i Forberedelse til kodeintervjuer inneholder totalt 4 leksjoner.

Hvorfor intervjuere velger vindusfunksjoner

En vindusfunksjon utfører en beregning over et sett med rader som er relatert til gjeldende rad, uten å slå dem sammen slik GROUP BY gjør. Det er nettopp denne egenskapen som gjør dem populære hos intervjuere: alle detaljradene beholdes, samtidig som et aggregat, en rangering eller en løpende totalsum kan vises ved siden av dem.

GROUP BY returnerer én rad per gruppe.
Vindusfunksjon returnerer hver inndata-rad, med en ekstra beregnet kolonne.

Når en intervjuer sier «vis hver ansatt og den gjennomsnittlige lønnen i avdelingen på samme rad», undersøkes det om en velger en vindusfunksjon i stedet for en self-join.

Anatomien til OVER-klausulen

Alle vindusfunksjoner etterfølges av en OVER (...)-klausul. Klausulen har tre valgfrie deler, og det å navngi dem presist imponerer intervjuere:

PARTITION BY — deler radene inn i grupper; funksjonen starter på nytt i hver gruppe.
ORDER BY — sorterer radene i hver partisjon (nødvendig for rangering og løpende totalsummer).
ramme — begrenser hvilke rader som brukes i beregningen (ROWS/RANGE).

Et tomt OVER () behandler hele resultatsettet som én partisjon.

SELECT
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

Vindusfunksjon kontra aggregat: Samme funksjon, ulikt resultat

Den samme aggregatfunksjonen oppfører seg annerledes som vindusfunksjon. Sammenlign de to spørringene nedenfor på et overordnet nivå.

AVG(salary) med GROUP BY department returnerer én rad per avdeling.
AVG(salary) OVER (PARTITION BY department) returnerer hver ansatt, med avdelingsgjennomsnittet som ekstra informasjon.

Tips til intervjuet: understrek at vindusversjonen ikke krever GROUP BY og ikke fjerner dupliserte detaljrader.

-- Aggregate: collapses
SELECT department, AVG(salary)
FROM employees
GROUP BY department;

-- Window: preserves every row
SELECT department, name, AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

PARTITION BY: Starte beregningen på nytt

PARTITION BY er for vindusfunksjoner det GROUP BY er for aggregater, bortsett fra at det ikke slår sammen rader. Hver unike partisjonsverdi får sin egen uavhengige beregning.

I eksemplet starter radnummeret på 1 for hver avdeling. Uten PARTITION BY ville nummereringen fortsatt kontinuerlig på tvers av alle ansatte.

Du kan partisjonere etter én eller flere kolonner.
Uten PARTITION BY får du én stor partisjon (hele datasettet).

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

ORDER BY i OVER

ORDER BY i OVER er ikke det samme som spørringens endelige ORDER BY. Det definerer bare rekkefølgen på radene innenfor hver partisjon som funksjonen skal arbeide med.

Rangeringsfunksjoner (ROW_NUMBER, RANK) krever det – de trenger en rekkefølge å rangere etter.
Vanlige aggregater over en partisjon trenger det ikke, med mindre du ønsker en løpende beregning.

En vanlig feil i intervjuer er å forveksle vinduets ORDER BY med visningsrekkefølgen i resultatet.

SELECT
  name,
  hire_date,
  ROW_NUMBER() OVER (ORDER BY hire_date) AS seniority_rank
FROM employees
ORDER BY name;  -- output order is independent of the window order

Kombinere PARTITION BY og ORDER BY

Den klassiske rangeringsfunksjonen for vinduer kombinerer begge: PARTITION BY grupperer, og ORDER BY ordner radene i hver gruppe.

Les spesifikasjonen nedenfor slik: "Innenfor hver avdeling skal ansatte ordnes etter synkende lønn og nummereres." Den best betalte personen i hver avdeling får radnummer 1.

Denne ene spesifikasjonen er grunnlaget for de vanligste intervjuoppgavene om vindusfunksjoner, inkludert top-N-per-gruppe.

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

ORDER BY endrer aggregatenes oppførsel

Her er et subtilt poeng intervjuere tester: Når du legger til ORDER BY i et aggregatvindu, blir det en løpende beregning fordi en implisitt ramme ("fra starten av partisjonen til gjeldende rad") aktiveres.

SUM(x) OVER (PARTITION BY g) → samme gruppesum på hver rad.
SUM(x) OVER (PARTITION BY g ORDER BY d) → løpende sum frem til gjeldende rad.

Å vite at ORDER BY implisitt legger til en ramme, skiller kandidater på mellomnivå fra juniorkandidater.

SELECT
  account_id,
  txn_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY account_id
    ORDER BY txn_date
  ) AS running_balance
FROM transactions;

Hvor vindusfunksjoner er tillatt

Vindusfunksjoner kan bare brukes i SELECT-listen og ORDER BY-leddet. De er ikke tillatt i WHERE, GROUP BY eller HAVING.

Årsaken henger sammen med den logiske kjørerekkefølgen: Vindusfunksjoner evalueres etter at WHERE, GROUP BY og HAVING er kjørt. Radene er allerede valgt før vinduet i det hele tatt ser dem.

Derfor krever filtrering på en rangering en underforespørsel eller CTE – et poeng som blir behandlet grundig i en senere leksjon.

-- This FAILS: window function in WHERE
-- SELECT name FROM employees
-- WHERE ROW_NUMBER() OVER (ORDER BY salary) = 1;

-- This works: window in SELECT, filter outside
SELECT * FROM (
  SELECT name, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
  FROM employees
) t
WHERE rn = 1;

Flere vindusfunksjoner i én spørring

Du kan bruke flere vindusfunksjoner i samme SELECT, hver med sin egen eller en delt spesifikasjon. Databasen beregner dem i én gjennomgang av de partisjonerte dataene.

Dette er nyttig i intervjuer når du trenger både en rangering og et avdelingsgjennomsnitt. Hvis to funksjoner deler en spesifikasjon, lar enkelte SQL-dialekter deg navngi den med et WINDOW-ledd for å unngå gjentakelser.

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

Gjennomgått eksempel: Lønn sammenlignet med avdelingsgjennomsnitt

Et vanlig spørsmål til analytikere er: "List opp alle ansatte med lønn, avdelingsgjennomsnitt og differansen." Ett vindusuttrykk gjør det meste av arbeidet; aritmetikken gjør resten.

Legg merke til at det ikke finnes noen GROUP BY, og at hver rad for en ansatt beholdes. dept_avg gjentas for alle i samme avdeling, og det er nettopp dette som gjør sammenligningen mulig rad for rad.

SELECT
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg,
  salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees
ORDER BY department, salary DESC;

Vanlige feil intervjuere ser etter

Unngå disse fellene når vindusfunksjoner dukker opp:

Å plassere en vindusfunksjon i WHERE eller HAVING – ulovlig; bruk en underforespørsel.
Å glemme ORDER BY for en rangeringsfunksjon – resultatene blir vilkårlige.
Å anta at PARTITION BY reduserer antallet rader – det gjør det aldri.
Å forveksle vinduets ORDER BY med den endelige rekkefølgen på resultatet.
Å legge til ORDER BY i et aggregatvindu uten å innse at det ble en løpende sum.

Rask kontroll

Test hvor godt du har forstått vindusspesifikasjonen.

Besvar den i den interaktive leksjonen

Oppsummering: Vindusspesifikasjonen

Du behersker nå oppbygningen av OVER (...):

Vindusfunksjoner beholder alle radene mens de beregner over relaterte rader.
PARTITION BY grupperer og starter beregningen på nytt; det fjerner aldri rader.
ORDER BY ordner radene innenfor en partisjon; rangeringsfunksjoner krever det, og det gjør aggregater om til løpende beregninger.
Vindusfunksjoner er bare tillatt i SELECT og ORDER BY – aldri i WHERE/HAVING.

Deretter skal du tilordne deterministiske sekvensnumre med ROW_NUMBER.

Gratis å komme i gang

Lær deg Forberedelse til kodeintervjuer med en AI-veileder – gratis

Skriv og kjør ekte kode i nettleseren, få umiddelbar hjelp fra en AI-veileder som er tilgjengelig døgnet rundt, og fortsett der du slapp – på nettet eller i appen.

Kurs: 90
Leksjoner: 360

Start læringen gratis Se hele kurset

Ofte stilte spørsmål

Er leksjonen «OVER, PARTITION BY og ORDER BY» gratis?

Ja – hele teksten i «OVER, PARTITION BY og ORDER BY» er gratis å lese her på nettet. For å øve interaktivt med en innebygd kodeeditor og en AI-veileder som er tilgjengelig døgnet rundt, og for å låse opp resten av Forberedelse til kodeintervjuer-kurset, kan du oppgradere til CoddyKit PRO. Kurset i Forberedelse til kodeintervjuer inneholder totalt 4 leksjoner.

Hva lærer jeg i «OVER, PARTITION BY og ORDER BY»?

Anatomien til en window-spesifikasjon, og hvordan partisjoner nullstiller beregningen. Du øver på Forberedelse til kodeintervjuer med praktisk kode som du kjører direkte i nettleseren, mens en AI-veileder som er tilgjengelig døgnet rundt, svarer på spørsmålene dine mens du jobber deg gjennom leksjonen.

Trenger jeg erfaring for å begynne med Forberedelse til kodeintervjuer?

Ingen tidligere erfaring er nødvendig. Forberedelse til kodeintervjuer på CoddyKit er lagt opp for både nybegynnere og viderekomne, så De kan begynne her eller helt fra start og lære i Deres eget tempo. Dette er leksjon 1 av 4.

Hvor lang tid tar leksjonen «OVER, PARTITION BY og ORDER BY»?

De fleste CoddyKit-leksjoner tar omtrent 5–10 minutter. Hver leksjon er kort og interaktiv, slik at De gjør jevne fremskritt og kan fortsette akkurat der De slapp – både på nettet og i appen.

Kan jeg skrive og kjøre kode i denne Forberedelse til kodeintervjuer-leksjonen?

Ja. Alle Forberedelse til kodeintervjuer-leksjoner har en innebygd kodeeditor, slik at De kan skrive og kjøre ekte kode direkte i nettleseren og få umiddelbar tilbakemelding fra AI – uten lokal konfigurering.

Alle leksjonene i dette kurset

← Tilbake til Forberedelse til kodeintervjuer