SQL Interview Prep · Lekcja

LAG i LEAD dla sąsiednich wierszy

Uzyskiwanie wartości z poprzedniego i następnego wiersza bez self-join

LAG i LEAD dla sąsiednich wierszy to bezpłatna lekcja SQL Interview Prep na CoddyKit. To lekcja 1 z 4. Możesz przeczytać całą lekcję poniżej za darmo — a potem ćwiczyć ją interaktywnie w przeglądarce z wbudowanym edytorem kodu i tutorem AI dostępnym 24/7. To część ścieżki edukacyjnej SQL Interview Prep, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs SQL Interview Prep zawiera 4 lekcji w sumie.

Pytanie zadawane przez rekruterów

Jedno z najczęstszych pytań na rozmowach rekrutacyjnych dla analityków brzmi: „Porównaj każdy wiersz z poprzednim bez używania self-join.” Może chodzić o przychód miesiąc do miesiąca, poprzednie logowanie użytkownika lub następne zdarzenie w sekwencji.

Najprostsza odpowiedź to funkcje okna LAG i LEAD. Pozwalają one pobrać wartość z sąsiedniego wiersza, zachowując jednocześnie wszystkie szczegółowe wiersze. W tej lekcji zbuduje Pan/Pani precyzyjny model mentalny tego, jak poruszają się one między sąsiednimi wierszami.

Działanie LAG i LEAD

LAG(col) zwraca wartość col z poprzedniego wiersza. LEAD(col) zwraca wartość z następnego wiersza. To, który wiersz jest „poprzedni” lub „następny”, jest w całości określane przez ORDER BY wewnątrz klauzuli OVER.

LAG spogląda wstecz.
LEAD spogląda wprzód.

Obie funkcje są funkcjami okna z przesunięciem: nigdy nie redukują liczby wierszy, tylko dołączają do bieżącego wiersza wartość sąsiedniego wiersza.

Podstawowa składnia LAG

Oto standardowa postać. Mamy tabelę sales z kolumnami month i revenue. Chcemy, aby każdy wiersz pokazywał również przychód z poprzedniego miesiąca.

Element OVER (ORDER BY month) informuje silnik, jak definiować wartość „poprzednią”. Pierwszy wiersz nie ma poprzednika, dlatego w tym wierszu prev_revenue ma wartość NULL.

SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_revenue
FROM sales
ORDER BY month;

Odczytywanie wyniku

Dla danych 2024-01 = 100, 2024-02 = 130, 2024-03 = 120 zapytanie zwraca:

styczeń: przychód 100, prev_revenue NULL
luty: przychód 130, prev_revenue 100
marzec: przychód 120, prev_revenue 130

Każdy wiersz pobrał wartość z wiersza znajdującego się bezpośrednio nad nim w uporządkowanym zbiorze. Bez self-join, bez podzapytania i bez utraty wierszy.

LEAD spogląda wprzód

LEAD jest lustrzanym odpowiednikiem LAG. Należy go użyć, gdy wiersz musi znać wartość tego, co nastąpi, na przykład datę następnego zakupu potrzebną do obliczenia czasu między zamówieniami.

Ostatni wiersz uporządkowanego zbioru nie ma następnika, dlatego wynik LEAD dla tego wiersza to NULL.

SELECT
  month,
  revenue,
  LEAD(revenue) OVER (ORDER BY month) AS next_revenue
FROM sales
ORDER BY month;

Argument przesunięcia

Obie funkcje przyjmują opcjonalny drugi argument określający liczbę pomijanych wierszy. LAG(col, 2) cofa się o dwa wiersze, a LEAD(col, 3) przeskakuje o trzy wiersze do przodu.

Rekruterzy wykorzystują to, pytając na przykład o „przychód sprzed dwóch miesięcy” lub „wartość trzy wiersze niżej”. Domyślne przesunięcie wynosi 1.

SELECT
  month,
  revenue,
  LAG(revenue, 2) OVER (ORDER BY month) AS revenue_2_months_ago
FROM sales
ORDER BY month;

Argument wartości domyślnej

Trzeci argument dostarcza wartość zastępczą, gdy nie istnieje sąsiedni wiersz, dzięki czemu zamiast NULL otrzymujemy określoną wartość. Sygnatura ma postać LAG(col, offset, default).

Jest to przydatne, gdy kolejne obliczenie nie może obsługiwać wartości NULL, na przykład gdy brakującą poprzednią wartość traktujemy jako 0, aby nadal można było obliczyć różnicę.

SELECT
  month,
  revenue,
  LAG(revenue, 1, 0) OVER (ORDER BY month) AS prev_revenue
FROM sales
ORDER BY month;

PARTITION BY resetuje okno

Rzeczywiste dane rzadko zawierają tylko jeden globalny szereg. Zwykle porównuje się wartości w obrębie klienta, produktu lub regionu. PARTITION BY rozpoczyna obliczenia LAG/LEAD od nowa na początku każdej partycji.

Oznacza to, że pierwszy wiersz każdej partycji otrzymuje z funkcji LAG wartość NULL, a wartość z danych innego klienta nigdy nie przenika przez granicę partycji.

SELECT
  customer_id,
  order_date,
  amount,
  LAG(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS prev_amount
FROM orders;

Przykład praktyczny: liczba dni między zamówieniami

Częstym zadaniem jest zmierzenie odstępu między kolejnymi zamówieniami klienta. Pobierz datę poprzedniego zamówienia za pomocą LAG, a następnie odejmij ją od bieżącej.

W przypadku pierwszego zamówienia każdego klienta otrzymasz NULL, ponieważ nie ma wcześniejszej daty, od której można by odjąć wartość. To właśnie ten rodzaj porównań w obrębie klienta rekruterzy oczekują rozwiązywać za pomocą funkcji okna.

SELECT
  customer_id,
  order_date,
  order_date - LAG(order_date) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS days_since_prev
FROM orders;

Dlaczego nie użyć self-join?

Przed pojawieniem się funkcji okna stosowano skorelowane złączenie tabeli z samą sobą: tabelę łączono z nią samą na podstawie warunku „wiersz, którego data jest największa, ale mniejsza od bieżącej”. Działa to, ale zapis jest rozwlekły, podatny na błędy w przypadku remisów i często wolniejszy.

LAG/LEAD wyrażają zamiar w jednym wierszu.
Są obliczane w jednym uporządkowanym przebiegu.
Remisy są rozstrzygane deterministycznie przez ORDER BY.

Stwierdzenie „użyłbym LAG zamiast self-join” sygnalizuje biegłość.

Częsty błąd: brak ORDER BY

Bez ORDER BY w klauzuli OVER „poprzedni wiersz” nie jest określony. Niektóre silniki odrzucą takie zapytanie, a inne zwrócą nieprzewidywalne wyniki. Zawsze porządkuj dane w oknie.

Pamiętaj również, że porządkowanie wewnątrz OVER jest niezależne od zewnętrznego ORDER BY zapytania. Okno określa, który wiersz jest sąsiedni, a zewnętrzna klauzula określa jedynie kolejność wyświetlania.

Szybkie sprawdzenie

Sprawdź swoją wiedzę na temat funkcji okna z przesunięciem.

Podsumowanie

Znają już Państwo funkcje okna z przesunięciem:

LAG(col) odczytuje poprzedni wiersz, a LEAD(col) następny, zgodnie z kolejnością określoną przez ORDER BY okna.
Opcjonalne argumenty: LAG(col, offset, default).
PARTITION BY rozpoczyna nawigację od nowa dla każdej grupy, dlatego wiersze graniczne mają wartość NULL.
Zastępują nieporęczne złączenia tabeli z samą sobą podczas porównywania sąsiednich wierszy.

W następnej części zastosujemy to do gwarantowanego pytania na rozmowie analitycznej: zmiany względem poprzedniego okresu.

Często zadawane pytania

Czy lekcja „LAG i LEAD dla sąsiednich wierszy” jest bezpłatna?

Tak — pełny tekst „LAG i LEAD dla sąsiednich wierszy” jest dostępny za darmo tutaj w sieci. Aby ćwiczyć ją interaktywnie (wbudowany edytor kodu i tutor AI dostępny 24/7) i odblokować resztę kursu SQL Interview Prep, przejdź na CoddyKit PRO. Kurs SQL Interview Prep zawiera 4 lekcji w sumie.

Co nauczysz się w „LAG i LEAD dla sąsiednich wierszy”?

Uzyskiwanie wartości z poprzedniego i następnego wiersza bez self-join Ćwiczysz SQL Interview Prep z praktycznym kodem, który uruchamiasz bezpośrednio w przeglądarce, a tutor AI dostępny 24/7 odpowiada na Twoje pytania podczas pracy nad lekcją.

Czy potrzebuję doświadczenia, aby zacząć SQL Interview Prep?

Nie wymagamy żadnego doświadczenia. SQL Interview Prep w CoddyKit jest strukturyzowany dla początkujących i zaawansowanych użytkowników, więc możesz zacząć tutaj lub od początku i uczyć się w swoim tempie. To lekcja 1 z 4.

Ile czasu zajmuje lekcja „LAG i LEAD dla sąsiednich wierszy”?

Większość lekcji CoddyKit trwa około 5–10 minut. Każda lekcja to mały, interaktywny krok, dzięki czemu robisz systematyczne postępy i zawsze wracasz dokładnie do tego samego miejsca — na webie i w aplikacji.

Czy mogę pisać i uruchamiać kod w tej lekcji SQL Interview Prep?

Tak. Każda lekcja SQL Interview Prep zawiera wbudowany edytor kodu, więc piszesz i uruchamiasz prawdziwy kod bezpośrednio w przeglądarce i od razu otrzymujesz sprzężenie zwrotne od AI — bez konfiguracji na komputerze.

Wszystkie lekcje w tym kursie

LAG i LEAD dla sąsiednich wierszy
Zmiany okres do okresu
NTILE do tworzenia przedziałów
FIRST_VALUE, LAST_VALUE i krawędzie ramki

← Powrót do SQL Interview Prep