SQL OFFSET funkce – LAG, LEAD, FIRST_VALUE, LAST_VALUE

Poslední aktualizace:

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 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)
  ,[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_ranking];

Výsledek:

SQL OFFSET funkce ukázka

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

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

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

Jak se vám líbil článek?

Jmenuji se Honza Zedníček a nejčastěji se se mnou v civilu můžete potkat v kancelářích Kentico Software v Brně, kde působím jako BI Developer. Částečně pracuji také jako freelancer. Tuto práci dělám pro různé firmy již přes 5 let, ale Kentico je moje srdcovka ♡. Před tím jsem pracoval jako finanční controller třeba v Aero Vodochody a Sberbank nebo jako manažer dluhopisového programu v investiční skupině Unicapital. Po práci si rád zahraju tenis, volejbal, šachy, zajdu do posilovny a rád ochutnávám dobré rumy. Svoje znalosti se snažím zapisovat na tento web - abych je nezapomněl (působením rumu ^^) a sloužily i někomu dalšímu. Nebojte se položit dotaz nebo reagovat do komentáře. Přihlašte se do naší nové Excel facebook skupiny Excel CZ/SK diskuse »

Category: SQL Funkce Užitečné SQL skripty

About Ing. Jan Zedníček - BI Developer, Finance controller

Jmenuji se Honza Zedníček a nejčastěji se se mnou v civilu můžete potkat v kancelářích Kentico Software v Brně, kde působím jako BI Developer. Částečně pracuji také jako freelancer. Tuto práci dělám pro různé firmy již přes 5 let, ale Kentico je moje srdcovka ♡. Před tím jsem pracoval jako finanční controller třeba v Aero Vodochody a Sberbank nebo jako manažer dluhopisového programu v investiční skupině Unicapital. Po práci si rád zahraju tenis, volejbal, šachy, zajdu do posilovny a rád ochutnávám dobré rumy. Svoje znalosti se snažím zapisovat na tento web - abych je nezapomněl (působením rumu ^^) a sloužily i někomu dalšímu. Nebojte se položit dotaz nebo reagovat do komentáře. Přihlašte se do naší nové Excel facebook skupiny Excel CZ/SK diskuse »

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.