MS SQL - okenné funkcie: ROW_NUMBER, LAG a priebežný súčet
Okenné funkcie (window functions) počítajú hodnotu z viacerých riadkov, ale na rozdiel od GROUP BY riadky nezlučujú – každý riadok zostane vo výsledku a pribudne k nemu vypočítaný stĺpec. Hodia sa na poradie, porovnanie s predchádzajúcim riadkom či priebežné súčty, teda na úlohy, ktoré sa inak riešia zložitými poddotazmi.
Testovacie dáta
CREATE TABLE Objednavky (
Id INT IDENTITY PRIMARY KEY,
ZakaznikId INT NOT NULL,
Datum DATE NOT NULL,
Suma DECIMAL(10,2) NOT NULL
);
INSERT INTO Objednavky (ZakaznikId, Datum, Suma) VALUES
(1, '2026-01-05', 120.00),
(1, '2026-02-11', 80.00),
(1, '2026-03-20', 200.00),
(2, '2026-01-15', 50.00),
(2, '2026-03-02', 50.00),
(3, '2026-02-28', 310.00);
Klauzula OVER
Okennú funkciu spoznáte podľa klauzuly OVER. V nej PARTITION BY rozdelí riadky do skupín (okien) a ORDER BY určí poradie riadkov v rámci skupiny.
funkcia() OVER (PARTITION BY stlpec_skupiny ORDER BY stlpec_poradia)
ROW_NUMBER – posledná objednávka každého zákazníka
ROW_NUMBER() očísluje riadky v každej skupine od 1. Typické použitie je výber „prvého“ alebo „posledného“ riadka za skupinu:
WITH Ocislovane AS (
SELECT Id, ZakaznikId, Datum, Suma,
ROW_NUMBER() OVER (PARTITION BY ZakaznikId ORDER BY Datum DESC, Id DESC) AS Poradie
FROM Objednavky
)
SELECT Id, ZakaznikId, Datum, Suma
FROM Ocislovane
WHERE Poradie = 1;
Okenná funkcia sa nedá použiť priamo vo WHERE, preto je dotaz zabalený do CTE (WITH).
RANK a DENSE_RANK – poradie s rovnakými hodnotami
Pri zhodných hodnotách dá ROW_NUMBER riadkom rôzne čísla, RANK rovnaké a ďalšie poradie preskočí, DENSE_RANK rovnaké bez preskočenia.
SELECT Id, Suma,
ROW_NUMBER() OVER (ORDER BY Suma) AS CisloRiadka,
RANK() OVER (ORDER BY Suma) AS Poradie,
DENSE_RANK() OVER (ORDER BY Suma) AS HustePoradie
FROM Objednavky;
| Suma | CisloRiadka | Poradie | HustePoradie |
|---|---|---|---|
| 50.00 | 1 | 1 | 1 |
| 50.00 | 2 | 1 | 1 |
| 80.00 | 3 | 3 | 2 |
| 120.00 | 4 | 4 | 3 |
| 200.00 | 5 | 5 | 4 |
| 310.00 | 6 | 6 | 5 |
LAG a LEAD – porovnanie s predchádzajúcim riadkom
LAG vráti hodnotu z predchádzajúceho riadka, LEAD z nasledujúceho. Druhý parameter je posun, tretí predvolená hodnota, ak taký riadok neexistuje.
SELECT ZakaznikId, Datum, Suma,
LAG(Suma) OVER (PARTITION BY ZakaznikId ORDER BY Datum, Id) AS PredoslaSuma,
Suma - LAG(Suma, 1, Suma) OVER (PARTITION BY ZakaznikId ORDER BY Datum, Id) AS Rozdiel,
DATEDIFF(DAY,
LAG(Datum) OVER (PARTITION BY ZakaznikId ORDER BY Datum, Id),
Datum) AS DniOdPoslednej
FROM Objednavky
ORDER BY ZakaznikId, Datum;
Pri prvej objednávke zákazníka je PredoslaSuma aj DniOdPoslednej NULL, Rozdiel je vďaka predvolenej hodnote 0.
Priebežný súčet
Agregačné funkcie (SUM, AVG, COUNT, MIN, MAX) s klauzulou OVER a ORDER BY počítajú priebežne – od začiatku okna po aktuálny riadok.
SELECT ZakaznikId, Datum, Suma,
SUM(Suma) OVER (PARTITION BY ZakaznikId
ORDER BY Datum, Id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS PriebezneSpolu
FROM Objednavky
ORDER BY ZakaznikId, Datum;
Pozor na rámec okna. Ak ROWS BETWEEN … vynecháte, použije sa predvolený rámec RANGE, ktorý riadky s rovnakou hodnotou v ORDER BY berie ako jeden celok – dve objednávky z toho istého dňa by dostali rovnaký priebežný súčet. Rámec ROWS je navyše spravidla rýchlejší.
Rovnakým spôsobom sa počíta kĺzavý priemer, napríklad z aktuálneho a dvoch predchádzajúcich riadkov:
SELECT Datum, Suma,
AVG(Suma) OVER (ORDER BY Datum, Id
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS KlzavyPriemer
FROM Objednavky;
Podiel na celku
Bez ORDER BY v klauzule OVER sa agregácia počíta cez celé okno, takže ku každému riadku dostaneme súčet za jeho skupinu:
SELECT ZakaznikId, Datum, Suma,
SUM(Suma) OVER (PARTITION BY ZakaznikId) AS SpoluZakaznik,
CAST(Suma * 100.0 / SUM(Suma) OVER (PARTITION BY ZakaznikId) AS DECIMAL(5,1)) AS PodielPercent
FROM Objednavky
ORDER BY ZakaznikId, Datum;
Zhrnutie
ROW_NUMBER,RANK,DENSE_RANK– poradie riadkov v skupine.LAG,LEAD– hodnota z predchádzajúceho alebo nasledujúceho riadka.SUM/AVG … OVER (ORDER BY …)– priebežné súčty a kĺzavé priemery; rámec uvádzajte cezROWS.SUM … OVER (PARTITION BY …)– súčet za skupinu pri každom riadku.
Príklady fungujú v MS SQL Server 2012 a novšom; rovnaká syntax platí aj v MySQL 8, PostgreSQL a Oracle (okrem DATEDIFF a IDENTITY).