LAG w SQL – czyli jak znaleźć poprzedni rekord w bazie danych

Potrzebujesz znaleźć poprzedni rekord w bazie danych? Nie wiesz jak zestawić miesiąc z miesiącem poprzednim? Chcesz policzyć wzrost procentowy w kolejnym miesiącu? Jesteś w dobrym miejscu. Pokażę Ci jak to zrobić. Opanujemy funkcję LAG w SQL. Przy okazji zobaczysz również zastosowanie frazy partition by.

Sortowanie w SQL

Zacznijmy od tego, że przyjrzymy się jak wyglądają dane. Pisząc prosty SELECT nie dostaniemy nawet rekordów po kolei. Dlatego, my od razu sobie dane posortujemy używając order by.

select * from monthly_sales order by order_month 

Rezultat:

LAG w SQL – czyli magia window functions

Funkcja LAG w SQL jest jedną z kilku funkcji analitycznych. Częściej mówi się o nich jako o window functions, gdyż działają one na pewnym okienku danych. Okienko wyznacza się względem danego rekordu.

Przykład. Chcemy dodać poprzedni miesiąc do naszego zestawienia.

select 
	order_month 
	,sales 
	,lag(sales) over (order by order_month asc) as previous_sales
from monthly_sales

Rezultat:

Do każdego miesiąca, funkcja LAG w SQL dodaje sales z poprzedniego rekordu, uwzględniając sortowanie po miesiącu. W tym przypadku order by po słowie from nie ma już znaczenia. Nawet jeżeli posortujemy cały rezultat odwrotnie, styczeń 2020 dostanie null, bo wcześniejszej daty nie ma.

select 
	order_month 
	,sales 
	,lag(sales) over (order by order_month asc) as previous_sales
from monthly_sales
order by order_month desc

Można powiedzieć, że znajdujemy poprzedni rekord w bazie danych, zgodnie z tym co dla nas znaczy słowo “poprzedni”.

Partition by

No dobrze, a gdybyśmy mieli taką tabelkę?

Wtedy poprzedni sposób nie zadziała, widać to już po 5 rekordach:

select 
	order_month 
	,sales 
	,product_type
	,lag(sales) over (order by order_month asc) as previous_sales
from monthly_sales_2
limit 5

Dlaczego? Ano dlatego, że musimy sobie nasz poprzedni rekord w bazie danych ustalić – przypisać do odpowiedniego okna (dlatego też lag jest jedną z window functions w SQL – punkt nr 7 mojego planu nauki SQL).

Robimy to za pomocą słów partition by, która dzieli nam zestawy danych na osobne sety.

select 
	order_month 
	,sales 
	,product_type
	,lag(sales) over (partition by product_type order by order_month asc) as previous_sales
from monthly_sales_2

Nie jest to co prawda tak, proste jak tematy z cyklu where i group by, ale po kilku testach samemu powinieneś załapać o co chodzi 😉

Na koniec całość możemy sobie wrzucić do CTE i w finalnym selectcie policzyć wzrost procentowy.

with
data_needed as 
	(
	select 
		order_month 
		,sales 
		,product_type
		,lag(sales) over (partition by product_type order by order_month asc) as previous_sales
	from monthly_sales_2
	)
select
dn.*
,round((sales - previous_sales)/previous_sales,2) as growth 
from data_needed dn

Podsumowanie

Funkcja LAG w SQL pozwala nam znaleźć poprzedni rekord w bazie danych (a w zasadzie w wynikach kwerendy), zgodnie z ustalonymi przez nas zasadami. Kolejność zapewnia nam użycie order by wewnątrz funkcji, a podział na grupy robimy za pomocą partition by. Pamiętamy również o słowie over – charakterystycznym dla funkcji analitycznych.

Przykładowa funkcja lag może zatem wyglądać tak:

lag(sales) over (partition by product_type order by order_month asc)

Jeżeli ten temat był dla Ciebie trochę za trudny, to może na początek warto wrócić do podstaw np. kolejności operacji w SQL?

To tyle w tym temacie. Analizujcie w pokoju!  📊📈

Podobał Ci się ten artykuł?
Podziel się nim w Social Mediach:
>>> udostępnij go na LinkedIn i pokaż, że codziennie uczysz się czegoś nowego 💻
>>> wrzuć go na Facebooka, to się może przydać któremuś z Twoich znajomych 💗
>>> Przypnij sobie tą stronkę to zakładek, może się przydać w przyszłości 💡

Wolisz oglądać 📺 niż czytać – nie ma problemu 😉
>>> Obserwuj i oglądaj KajoData na YouTube

A jeśli chcesz się dowiedzieć czegoś więcej o SQL – zajrzyj tutaj.