Pchełki SQL: LAG

Operatory analityczne są często traktowane po macoszemu przez padawanów sztuki SQL-owej. Czasem jednak potrafią one zaoszczędzić mnóstwo czasu. Dziś pokażę, jak działa operator LAG.

Jednym z klasycznych problemów, przed którymi staje większość programistów SQL, jest wykonanie zestawienia, w którym chcemy pokazać wartości z rekordu bieżącego wraz z wartościami z rekordu poprzedniego, w jednym rekordzie.

Typowym podejściem jest tu utworzenie CTE z ROW_NUMBER(), a następnie wykonanie JOIN-a tego CTE z samym sobą po kolumnie ROW_NUMBER() z nią samą zmniejszoną o jeden. Czyli po naszemu tzw. self-join.

Rozwiązanie działa i jest względnie proste do zrozumienia (i napisania), ma jednak jedną zasadniczą wadę: wymusza na silniku bazy danych wykonanie kosztownej operacji JOIN, która w przypadku większych zbiorów danych (miliony rekordów? miliardy?) może „chwilkę” zająć, nawet przy założeniu optymalnej strategii indeksowania.

Począwszy od SQL Server 2012 mamy jednak do dyzpozycji serię operatorów analitycznych umożliwiających operacje na „oknach” danych. „Okno” oznacza tu coś w rodzaju kursora, obejmującego swoim zasięgiem kilka (-naście / -dziesiąt / -set itd.) bieżących rekordów bez konieczności uciekania się do kosztownych self-JOIN-ów.

Jednym z nich jest tytułowy LAG.

To może ja pokażę na przykładzie:

Najpierw stworzymy sobie tabelę:

CREATE TABLE dbo.sales_monthly (
      ymonth INT NOT NULL
    , sales MONEY NOT NULL
    , CONSTRAINT PK_sales_monthly PRIMARY KEY CLUSTERED ( ymonth )
    )

Następnie wypełnimy ją testowymi danymi:

INSERT  dbo.sales_monthly
VALUES  ( 201501, 57553 ),
        ( 201502, 47944 ),
        ( 201503, 41947 ),
        ( 201504, 59524 ),
        ( 201505, 37307 ),
        ( 201506, 63031 ),
        ( 201507, 50572 ),
        ( 201508, 60783 ),
        ( 201509, 38902 ),
        ( 201510, 57650 ),
        ( 201511, 57140 ),
        ( 201512, 50831 ),
        ( 201601, 45473 ),
        ( 201602, 42949 )

Mamy wartości sprzedaży z każdego miesiąca, począwszy od stycznia 2015 aż do lutego 2016.

Czas na magię właściwą:

SELECT  ymonth
      , sales
      , LAG(sales, 1, 0) OVER ( ORDER BY ymonth ) sales_prev
FROM    dbo.sales_monthly

Wynik:

Jak widać, kolumna [sales_prev] wyświetla wartości z poprzedniego miesiąca – wszystko za pomocą pojedynczego SELECT-a, bez dodatkowych fikołków.

Trochę o samym operatorze LAG:

  • przyjmuje trzy parametry: (1) nazwę kolumny, (2) przesunięcie (czyli długość okna – ile rekordów wstecz mamy patrzeć) oraz (3) wartość domyślną (parametr opcjonalny), mówiącą co należy zwrócić w razie gdyby w poprzednim rekordzie nic nie było (na przykład, kiedy jesteśmy w pierwszym rekordzie w partycji albo kiedy poprzedni rekord jest NULL)
  • wartość domyślna (3) może być skalarem lub dowolnym podzapytaniem zwracającym pojedynczą wartość o typie danych zgodnym z kolumną (1) – z jednym ograniczeniem: nie może to być podzapytanie używające operatorów analitycznych
  • wewnętrznie, operator LAG używa operacji „window spool”, a więc czym większe okno (parametr (2)), tym więcej pamięci będzie potrzebnej na przechowanie bieżącej zawartości okna
  • operator LAG jest niedeterministyczny, a więc nie może być używany w funkcjach

Operatorem działającym w drugą stronę (a więc: do przodu zamiast wstecz) jest LEAD. Składnia jest identyczna, jedyna różnica to „kierunek działania”.

Autor: xpil

Po czterdziestce. Żonaty. Dzieciaty. Komputerowiec. Krwiodawca. Emigrant. Rusofil. Lemofil. Sarkastyczny. Uparty. Mól książkowy. Ateista. Apolityczny. Nie oglądam TV. Uwielbiam matematykę. Walę prosto z mostu. Gram na paru instrumentach. Lubię planszówki. Słucham bluesa, poezji śpiewanej i kapel a’capella. || Kliknij tutaj po więcej szczegółów ||

Dodaj komentarz

Bądź pierwszy!

Powiadom o
avatar