Slovník | Vyhledávání | Mapa webu
 
Základy informatiky pro biologyDatabázové systémy v biomedicíně Analytické a statistické funkce SQL Funkce pro pro přístup k předchozím a následným řádkům - LAG (), LEAD ()

Logo Matematická biologie

Funkce pro pro přístup k předchozím a následným řádkům - LAG (), LEAD ()

Další speciální operací, která je ve standardním SQL obtížné proveditelná, je práce s jiným než aktuálně zpracovávaným řádkem v setříděném seznamu. Připomeňme, že standardní funkce a operátory jako SUBSTR(), LN (), TRUNC() atd. pracují vždy s hodnotami aktuálně zpracovávaného řádku. Co když ale chceme například porovnat číselnou hodnotu jednoho řádku s předchozím řádkem, například sledujeme u pacientů změnu v počtu leukocytů od předchozího vyšetření. Ve standardním SQL bychom museli pomocí JOIN operace spojit tabulku vysetreni se sebou samou, abychom dostali na jeden řádek hodnotu předchozího a následného vyšetření. Výrazně snadnější a přehlednější je využití speciálních funkcí LAG() nebo LEAD(). Funkce LAG() nám umožňuje pracovat s předchozími záznamy, funkce LEAD() s následnými záznamy. Syntaxe obou funkcí je shodná.

LAG (sloupec1, n, hodnota) OVER (ORDER BY sloupec2)

Prvním parametrem je výraz, nejčastěji název sloupce, jehož předchozí nebo následující hodnota nás zajímá. Výraz může obsahovat libovolný operátor či standardní funkci.  Druhým parametrem je celé číslo, které udává, o kolik řádků se chceme vrátit nebo posunout vpřed. Třetí nepovinným parametrem je hodnota, kterou chceme, aby funkce vrátila, pokud se posune mimo hranice vybrané množiny řádků (tedy před první nebo za poslední řádek). Výchozí hodnotou třetího parametru je NULL. Následuje výraz OVER s definicí setřídění a případně seskupení zpracovávané množiny výsledků. Touto klauzulí určíme funkci LAG() či LEAD(), co míníme předchozím a následným řádkem. Výraz ORDER BY v klauzuli OVER nijak nesouvisí s finálním setříděním výsledku SQL dotazu, i když pro kontrolu správnosti našeho výsledku bude nejčastěji výraz ORDER BY v klazuli OVER stejný jako na konci celého SQL dotazu. 
Mějme tabulku vysetreni se sloupci patient_id, datum_vysetreni a pocet_leukocytu. Zajímá nás změna počtu leukocytů u každého pacienta oproti předchozímu vyšetření. 

SELECT patient_id, datum_vysetreni, pocet_leukocytu,
            LAG(pocet_leukocytu, 1) OVER
            (PARTITION BY patient_id ORDER BY datum_vysetreni) predchozi_pocet,
     pocet_leukocytu -
            LAG(pocet_leukocytu, 1) OVER
            (PARTITION BY patient_id ORDER BY datum_vysetreni)  zmena

     FROM vysetreni

Tab. 3: Ukázka výsledku funkce LAG()

patient_id

datum_vysetreni

pocet_leukocytu

predchozi_pocet

zmena

PAT_1

12. 5. 2012

7,4

 

 

PAT_2

12. 5. 2012

5,3

 

 

PAT_2

23. 9. 2012

2,4

5,3

-2,9

PAT_2

4. 2. 2013

3,7

2,3

1,4

PAT_3

15. 3. 2012

1,9

 

 

PAT_3

6. 9. 2012

4,5

1,9

2,6

 

 
vytvořil Institut biostatistiky a analýz Masarykovy univerzity | | zpětné odkazy | validní XHTML 1.0 Strict