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řednictví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 SQL funkcí pro přístup na předchodí nebo následující řádky:
LAG() – Najde a vrátí předchozí hodnotu v tabulce
LEAD() – Najde a vrátí následnou hodnotu v tabulce
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 u klauzule ORDER BY. To ale není funkce, takže sem nepatří.
Příklad použití funkcí LAG, LEAD, FIRST_VALUE, LAST_VALUE
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) ,[Rok] INT ,[Mesic] INT ,[Trzby] NUMERIC);
INSERT INTO [Ukazka_ranking] ( [Rok] ,[Mesic] ,[Trzby])VALUES (2015, 1, 50)
,(2015, 2, 60)
,(2015, 3, 30)
,(2015, 4, 120)
,(2015, 5, 150)
,(2015, 6, 70)
,(2015, 7, 50)
,(2015, 8, 90)
,(2015, 9, 90)
,(2015, 10, 100)
,(2015, 11, 90)
,(2015, 12, 150);
Tabulka vypadá takto:
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_ranking];
Výsledek:
Jak udělat index (meziměsíční změna) pomocí LAG()?
Pomocí stránkovacích funkcí můžeme spočítat fůru věcí – třeba míru růstu mezi jednotlivými členy časové řady tj. index. 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_ranking]
výsledek:
