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:

SQL OFFSET funkce

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:

SQL OFFSET funkce ukázka

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:

SQL OFFSET funkce výsledek dotazu

Rate this post

Ing. Jan Zedníček - Data & Finance

Jmenuji se Honza Zedníček a působím jako freelancer. Tuto práci dělám pro různé firmy již přes 10 let. Před tím jsem dlouhou dobu pracoval také jako BI developer, finanční controller a analytik. Vše pro společnosti z oblasti IT, bankovnictví a výroby. Po práci si rád zahraju tenis, volejbal, šachy a zajdu do posilovny. Svoje znalosti a názory se snažím už několik let zapisovat na tento web, aby sloužily i někomu dalšímu - zejména studentům a zájemcům o informace z oblastí Business intelligence, korporátních financí a reportingu.

=> Přihlašte se do naší Excel facebook skupiny (1.4k+ členů), kde si pomáháme Excel CZ/SK diskuse »
=> Pokud vám článek pomohl, sdílejte ho nebo mě uveďte na vašich webových stránkách jako užitečný zdroj informací.

Leave a Reply

Your email address will not be published. Required fields are marked *