SQL OFFSET funkce – LAG, LEAD, FIRST_VALUE, LAST_VALUE

OFFSET funkce jsou v MS SQL Server relativně nové, jsou dostupné teprve od verze SQL Server 2012. Tyto funkce umožňují „listovat“ mezi řádky tabulky. Lépe řečeno na řádku aktuálním si můžete prostředníctvím takových funkcí sáhnout na řádek minulý nebo následující.

Tyto funkce řadíme do tzv Window funkcí a do stejné skupiny patří kromě nich také agregační funkce a Ranking funkce (funkce na pořadí).

Seznam funkcí pro přístup na předchodí nebo následující hodnoty:

LAG() – Najde a vrátí předchozí hodnotu aktuálního řádku
LEAD() – Najde a vrátí následnou hodnotu aktuálního řádku
FIRST_VALUE() – Najde a vrátí nejmenší hodnotu vzhledem k aktuálnímu řádku
LAST_VALUE() – Najde a vrátí největší hodnotu vzhledem k aktuálnímu řádku

Další možností jak stránkovat je klauzule OFFSET <počet řádků> FETCH NEXT <počet řádků>, která se píše za ORDER BY.To ale není funkce, takže sem nepatří.

Příklad použití funkcí:

Založíme se tabulku, ve které budou Tržby za rok 2015 po jednotlivých měsících. Následně na ní ukážeme stránkovací funkce a nakonec si zkusiíme pomocí funkcí napsat SQL skript na index=> měziměsíční změnu tržeb [%].

USE tempdb;
CREATE TABLE Ukazka_ranking
(ID INT IDENTITY(1,1),
Produkt VARCHAR(100),
Produktova_Skupina VARCHAR(100),
Trzby NUMERIC;

INSERT INTO Ukazka_ranking (Produkt, Produktova_Skupina, Trzby)
VALUES ('Židle','Kuchyne',110),
(
'Stul','Kuchyne',200),
(
'Skrín','Kuchyne',410),
(
'Postel','Ložnice',200),
(
'Vana','Koupelny',100),
(
'Pracka','Koupelny',400);

Tabulka vypadá takto:

SQL OFFSET funkce

Vstupní data

Skript:

SELECT ID ,Rok, Mesic, Trzby,
  FIRST_VALUE(Trzby) OVER(ORDER BY Rok, Mesic)    AS Prvni,
  LAST_VALUE(Trzby) OVER(ORDER BY Rok, Mesic)     AS Posledni,
  LAG(Trzby) OVER(ORDER BY Rok, Mesic)            AS Predchazejici,
  LEAD(Trzby) OVER(ORDER BY Rok, Mesic)           AS Nasledujici
FROM Ukazka_strankovani;

Výsledek:

SQL OFFSET funkce ukázka

Příklad použítí FIRST, LAST, LAG, LEAD

Jak udělat index pomocí SQL Offset funkcí?

Často se dostaneme do situace kdy potřebujeme spočítat míru růstu mezi jednotlivými členy časové řady. Tady je příklad jak na to:

SELECT ID ,Rok, Mesic,
LAG(Trzby) OVER(ORDER BY Rok,Mesic)        AS Predchazejici_Mesic,
Trzby                                      AS Aktualni_mesic,
CONVERT(NUMERIC(10,2),
     100*(Trzby - LAG(Trzby) OVER(ORDER BY Rok,Mesic))
/
     LAG(Trzby) OVER(ORDER BY Rok,Mesic
)
) AS [Index %]
FROM Ukazka_strankovani

tadá:

SQL OFFSET funkce výsledek dotazu

Porovnání dvou po sobe následujících řádků v SQL

Reagovat na příspěvek