Forberedelse til SQL-interview · Lektion

OVER, PARTITION BY og ORDER BY

Anatomien i en vinduesspecifikation, og hvordan partitioner nulstiller beregningen

Lektion 1 af 413 trin

OVER, PARTITION BY og ORDER BY 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.

Hvorfor interviewere vælger vinduesfunktioner

En vinduesfunktion udfører en beregning på et sæt rækker, der er relateret til den aktuelle række, uden at samle dem til færre rækker, sådan som GROUP BY gør. Denne ene egenskab er grunden til, at interviewere er så glade for dem: Du bevarer alle detaljerækker og får stadig et aggregat, en rangering eller en løbende sum ved siden af.

GROUP BY returnerer én række pr. gruppe.
Vinduesfunktion returnerer hver indgående række med en ekstra beregnet kolonne.

Når en interviewer siger "vis hver medarbejder og den gennemsnitlige løn i vedkommendes afdeling på samme række", undersøger de, om du vælger en vinduesfunktion frem for en selvforbindelse.

Anatomien i OVER-klausulen

Alle vinduesfunktioner efterfølges af en OVER (...)-klausul. Klausulen har tre valgfrie dele, og det imponerer interviewere, når du kan benævne dem præcist:

PARTITION BY — opdeler rækker i grupper; funktionen starter forfra i hver gruppe.
ORDER BY — ordner rækker i hver partition (nødvendigt til rangering og løbende summer).
Ramme — begrænser, hvilke rækker der indgår i beregningen (ROWS/RANGE).

Et tomt OVER () behandler hele resultatsættet som én partition.

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

Vinduesfunktion kontra aggregat: Samme funktion, forskelligt resultat

Den samme aggregatfunktion opfører sig forskelligt som vinduesfunktion. Sammenlign de to forespørgsler nedenfor på et overordnet plan.

AVG(salary) med GROUP BY department returnerer én række pr. afdeling.
AVG(salary) OVER (PARTITION BY department) returnerer hver medarbejder med afdelingens gennemsnit som ekstra kolonne.

Tip til interviewet: Fremhæv, at vinduesversionen ikke kræver GROUP BY og ikke fjerner de duplikerede detaljerækker.

-- 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: Nulstilling af beregningen

PARTITION BY er for vinduesfunktioner, hvad GROUP BY er for aggregater, bortset fra at det ikke sammenlægger rækker. Hver særskilt partitionsværdi får sin egen uafhængige beregning.

I eksemplet begynder rækkenummereringen forfra ved 1 for hver afdeling. Uden PARTITION BY ville nummereringen fortsætte på tværs af alle medarbejdere.

Du kan partitionere efter én kolonne eller flere.
Uden PARTITION BY får du én stor partition, der omfatter hele datasættet.

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 forespørgslens afsluttende ORDER BY. Det definerer kun rækkefølgen af rækker inden for hver partition, som funktionen arbejder på.

Rangeringsfunktioner som ROW_NUMBER og RANK kræver det, fordi de skal have en rækkefølge at rangere efter.
Almindelige aggregatfunktioner over en partition behøver det ikke, medmindre du ønsker en løbende beregning.

En almindelig fejl i jobsamtaler er at forveksle vinduets ORDER BY med den rækkefølge, som resultatet vises i.

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

Kombination af PARTITION BY og ORDER BY

Den klassiske rangeringsvinduesfunktion kombinerer begge dele: PARTITION BY opdeler i grupper, hvorefter ORDER BY ordner rækkerne i hver gruppe.

Læs specifikationen nedenfor sådan: "Inden for hver afdeling skal medarbejderne sorteres efter løn i faldende rækkefølge og nummereres." Den højest lønnede medarbejder i hver afdeling får rækkenummer 1.

Denne ene specifikation er grundlaget for de mest almindelige spørgsmål om vinduesfunktioner i jobsamtaler, herunder top-N-pr.-gruppe.

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

ORDER BY ændrer aggregatets adfærd

Her er et subtilt punkt, som interviewere undersøger: Når du føjer ORDER BY til et aggregat i en vinduesfunktion, bliver det til en løbende beregning, fordi en implicit ramme, "fra partitionens begyndelse til den aktuelle række", træder i kraft.

SUM(x) OVER (PARTITION BY g) → den samme gruppesum på hver række.
SUM(x) OVER (PARTITION BY g ORDER BY d) → en løbende sum frem til den aktuelle række.

At vide, at ORDER BY implicit tilføjer en ramme, adskiller kandidater på mellemniveau 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 vinduesfunktioner er tilladt

Vinduesfunktioner må kun stå på SELECT-listen og i ORDER BY-klausulen. De er ikke tilladt i WHERE, GROUP BY eller HAVING.

Årsagen hænger sammen med den logiske rækkefølge for udførelsen: Vinduesfunktioner evalueres efter, at WHERE, GROUP BY og HAVING er kørt. Rækkerne er allerede valgt, før vinduesfunktionen overhovedet ser dem.

Derfor kræver filtrering baseret på rangering en underforespørgsel eller en CTE — et punkt, der gennemgås fuldt ud i en senere lektion.

-- 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 vinduesfunktioner i én forespørgsel

Du kan bruge flere vinduesfunktioner i samme SELECT, hver med sin egen eller en fælles specifikation. Databasen beregner dem i én gennemgang af de partitionerede data.

Det er nyttigt i jobsamtaler, når du har brug for både en rang og et afdelingsgennemsnit. Hvis to funktioner deler en specifikation, kan du i nogle SQL-dialekter navngive den med en WINDOW-klausul for at undgå gentagelser.

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

Gennemgået eksempel: Løn kontra afdelingsgennemsnit

Et hyppigt spørgsmål til en analytiker er: "Vis alle medarbejdere med deres løn, afdelingsgennemsnittet og forskellen." Én vinduesudtryk står for det meste af arbejdet; aritmetikken klarer resten.

Bemærk, at der ikke er nogen GROUP BY, og at hver medarbejderrække bevares. dept_avg gentages for alle i samme afdeling, hvilket netop gør sammenligningen mulig række for række.

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;

Almindelige fejl, interviewere holder øje med

Undgå disse faldgruber, når vinduesfunktioner kommer på tale:

At placere en vinduesfunktion i WHERE eller HAVING — det er ulovligt; brug en underforespørgsel.
At glemme ORDER BY på en rangeringsfunktion — resultaterne bliver vilkårlige.
At antage, at PARTITION BY reducerer antallet af rækker — det gør den aldrig.
At forveksle vinduets ORDER BY med den endelige rækkefølge af resultatet.
At føje ORDER BY til et aggregat i et vindue uden at opdage, at det blev til en løbende total.

Hurtigt tjek

Test din forståelse af vinduesspecifikationen.

Besvar den i den interaktive lektion

Opsummering: Vinduesspecifikationen

Du kender nu opbygningen af OVER (...):

Vinduesfunktioner bevarer alle rækker, mens de beregner på tværs af relaterede rækker.
PARTITION BY opdeler i grupper og nulstiller beregningen; det fjerner aldrig rækker.
ORDER BY ordner rækker inden for en partition; rangeringsfunktioner kræver det, og det gør aggregater til løbende beregninger.
Vinduesfunktioner er kun tilladt i SELECT og ORDER BY — aldrig i WHERE/HAVING.

Dernæst tildeler du entydige sekvensnumre med ROW_NUMBER.

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 gratis Se hele kurset

Ofte stillede spørgsmål

Er lektionen “OVER, PARTITION BY og ORDER BY” gratis?

Ja — hele teksten til “OVER, PARTITION BY og ORDER BY” 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 “OVER, PARTITION BY og ORDER BY”?

Anatomien i en vinduesspecifikation, og hvordan partitioner nulstiller beregningen 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 “OVER, PARTITION BY og ORDER BY”?

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

← Tilbage til Forberedelse til SQL-interview