Vindusfunksjoner og CTE-er i T-SQL

«Finn den siste ordren per kunde.» «Vis salget per måned med endring fra forrige måned.» «Lag en løpende sum.» Slike oppgaver ble tidligere løst med korrelerte underspørringer og selvjoins som var vanskelige å lese og ofte trege. Med vindusfunksjoner og CTE-er (common table expressions) blir de korte, lesbare spørringer. Denne guiden går gjennom de viktigste funksjonene med eksempler du kan bruke direkte.

Kort fortalt
  • En vindusfunksjon regner ut en verdi for hver rad basert på et «vindu» av andre rader – uten å slå radene sammen slik GROUP BY gjør.
  • PARTITION BY deler opp vinduet, ORDER BY bestemmer rekkefølgen, og ROWS BETWEEN avgrenser rammen.
  • En CTE (WITH ... AS) er en navngitt underspørring som gjør koden lesbar. Den lagres ikke midlertidig.
  • Skriv alltid ROWS eksplisitt for løpende summer – standardrammen RANGE er både tregere og kan gi overraskende resultater.

Eksempeldata

Eksemplene bruker en enkel ordretabell:

CREATE TABLE dbo.Ordre
(
    OrdreId  int           PRIMARY KEY,
    KundeId  int           NOT NULL,
    Dato     date          NOT NULL,
    Beløp    decimal(10,2) NOT NULL
);

CTE: navngitte underspørringer

En CTE lar deg gi en underspørring et navn og bruke den som en tabell i spørringen rett etter. Det gjør lange spørringer mulige å lese ovenfra og ned:

WITH SalgPerKunde AS
(
    SELECT KundeId, SUM(Beløp) AS Total
    FROM dbo.Ordre
    WHERE Dato >= '2026-01-01'
    GROUP BY KundeId
)
SELECT k.Navn, s.Total
FROM SalgPerKunde AS s
JOIN dbo.Kunde AS k ON k.KundeId = s.KundeId
WHERE s.Total > 100000
ORDER BY s.Total DESC;
En CTE er bare syntaks. SQL Server lagrer ikke resultatet midlertidig – refererer du til samme CTE to ganger, kjøres den to ganger. Trenger du et mellomresultat flere ganger, bruk en midlertidig tabell (#tabell). Husk også at setningen før WITH må avsluttes med semikolon.

Nummerering og rangering

FunksjonHva den gjørVed like verdier
ROW_NUMBER()Løpenummer 1, 2, 3 …Alltid unike numre (tilfeldig rekkefølge mellom like)
RANK()Rangering med hopp1, 2, 2, 4
DENSE_RANK()Rangering uten hopp1, 2, 2, 3
NTILE(n)Deler radene i n like store grupperFor eksempel kvartiler med NTILE(4)

Siste ordre per kunde (topp N per gruppe)

Det mest brukte mønsteret av alle:

WITH Nummerert AS
(
    SELECT OrdreId, KundeId, Dato, Beløp,
           ROW_NUMBER() OVER (PARTITION BY KundeId
                              ORDER BY Dato DESC, OrdreId DESC) AS Nr
    FROM dbo.Ordre
)
SELECT OrdreId, KundeId, Dato, Beløp
FROM Nummerert
WHERE Nr = 1;   -- Nr <= 3 gir de tre siste

Ta med en unik kolonne sist i ORDER BY, ellers er det tilfeldig hvilken rad som blir nummer 1 når to ordrer har samme dato. En indeks på (KundeId, Dato DESC) med INCLUDE (Beløp) gjør spørringen rask.

Fjern duplikater

WITH Duplikater AS
(
    SELECT ROW_NUMBER() OVER (PARTITION BY KundeId, Dato, Beløp
                              ORDER BY OrdreId) AS Nr
    FROM dbo.Ordre
)
DELETE FROM Duplikater
WHERE Nr > 1;

Du kan slette gjennom en CTE så lenge den bygger på én tabell. Kjør gjerne SELECT i stedet for DELETE først for å se hva som forsvinner.

LAG og LEAD: forrige og neste rad

WITH Måned AS
(
    SELECT DATEFROMPARTS(YEAR(Dato), MONTH(Dato), 1) AS Måned,
           SUM(Beløp) AS Salg
    FROM dbo.Ordre
    GROUP BY DATEFROMPARTS(YEAR(Dato), MONTH(Dato), 1)
)
SELECT Måned,
       Salg,
       LAG(Salg) OVER (ORDER BY Måned)       AS ForrigeMåned,
       Salg - LAG(Salg) OVER (ORDER BY Måned) AS Endring,
       LAG(Salg, 12) OVER (ORDER BY Måned)   AS SammeMånedIFjor
FROM Måned
ORDER BY Måned;

LAG(kolonne, n, standard) ser n rader bakover, og gir standardverdien (eller NULL) når det ikke finnes noen. LEAD ser fremover. Fra SQL Server 2022 kan du legge til IGNORE NULLS for å hoppe over tomme verdier.

Løpende summer og glidende snitt

Aggregatfunksjoner som SUM, AVG og COUNT kan også brukes med OVER:

SELECT KundeId, Dato, Beløp,
       SUM(Beløp) OVER (PARTITION BY KundeId
                        ORDER BY Dato, OrdreId
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS LøpendeSum,
       AVG(Beløp) OVER (PARTITION BY KundeId
                        ORDER BY Dato, OrdreId
                        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)         AS SnittSiste3,
       SUM(Beløp) OVER (PARTITION BY KundeId)                             AS KundeTotal,
       Beløp * 100.0 / SUM(Beløp) OVER (PARTITION BY KundeId)            AS AndelProsent
FROM dbo.Ordre;

Fellen med RANGE

Skriver du bare SUM(Beløp) OVER (ORDER BY Dato), bruker SQL Server standardrammen RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Det har to konsekvenser:

  • Rader med samme dato får samme løpende sum, fordi de regnes som én «verdi».
  • Det er tregere: RANGE bruker en arbeidstabell på disk, mens ROWS kan beregnes i minnet.

Samme felle gjelder LAST_VALUE, som med standardrammen bare gir verdien på gjeldende rad. Bruk ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, eller FIRST_VALUE med motsatt sortering.

WINDOW-klausulen

Fra SQL Server 2022 (kompatibilitetsnivå 160) kan du navngi et vindu og gjenbruke det:

SELECT KundeId, Dato, Beløp,
       SUM(Beløp) OVER v AS LøpendeSum,
       COUNT(*)   OVER v AS AntallHittil
FROM dbo.Ordre
WINDOW v AS (PARTITION BY KundeId ORDER BY Dato, OrdreId
             ROWS UNBOUNDED PRECEDING);

Rekursive CTE-er

En CTE kan referere til seg selv. Det brukes til hierarkier som organisasjonskart, kategoritrær og stykklister:

WITH Org AS
(
    -- Ankeret: toppen av treet
    SELECT AnsattId, Navn, LederId, 0 AS Nivå
    FROM dbo.Ansatt
    WHERE LederId IS NULL

    UNION ALL

    -- Den rekursive delen: alle som har en leder vi allerede har funnet
    SELECT a.AnsattId, a.Navn, a.LederId, o.Nivå + 1
    FROM dbo.Ansatt AS a
    JOIN Org AS o ON a.LederId = o.AnsattId
)
SELECT REPLICATE('    ', Nivå) + Navn AS Organisasjon
FROM Org
ORDER BY Nivå, Navn
OPTION (MAXRECURSION 100);

Standardgrensen er 100 nivåer. Inneholder dataene en løkke (A er leder for B, B for A), stopper spørringen med feil når grensen nås – det er en nyttig sikring. MAXRECURSION 0 fjerner grensen, men bruk det bare når du vet at dataene er et tre.

Vanlige spørsmål

Kan jeg bruke en vindusfunksjon i WHERE?

Nei. Vindusfunksjoner beregnes etter WHERE, GROUP BY og HAVING. Pakk spørringen i en CTE og filtrer i den ytre spørringen, slik eksemplene over gjør.

Er CTE raskere enn en underspørring?

Nei, de gir normalt samme kjøreplan. Velg det som er lettest å lese. Se hvordan du leser en kjøreplan hvis du vil sjekke.

Hvorfor er spørringen med vindusfunksjon treg?

Se etter en dyr Sort i planen. En indeks som allerede er sortert på PARTITION BY- og ORDER BY-kolonnene fjerner sorteringen. På store tabeller kan batch mode gi en stor forbedring.

Les også