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.
- En vindusfunksjon regner ut en verdi for hver rad basert på et «vindu» av andre rader – uten å slå radene sammen slik
GROUP BYgjør. PARTITION BYdeler opp vinduet,ORDER BYbestemmer rekkefølgen, ogROWS BETWEENavgrenser rammen.- En CTE (
WITH ... AS) er en navngitt underspørring som gjør koden lesbar. Den lagres ikke midlertidig. - Skriv alltid
ROWSeksplisitt for løpende summer – standardrammenRANGEer 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;
#tabell). Husk også at setningen før WITH må avsluttes med semikolon.
Nummerering og rangering
| Funksjon | Hva den gjør | Ved like verdier |
|---|---|---|
ROW_NUMBER() | Løpenummer 1, 2, 3 … | Alltid unike numre (tilfeldig rekkefølge mellom like) |
RANK() | Rangering med hopp | 1, 2, 2, 4 |
DENSE_RANK() | Rangering uten hopp | 1, 2, 2, 3 |
NTILE(n) | Deler radene i n like store grupper | For 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:
RANGEbruker en arbeidstabell på disk, mensROWSkan 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.