SQL Interview Prep · Lekcja

FIRST_VALUE, LAST_VALUE i krawędzie ramki

Pobieranie wartości granicznych oraz pułapka związana z ramką LAST_VALUE

FIRST_VALUE, LAST_VALUE i krawędzie ramki to bezpłatna lekcja SQL Interview Prep na CoddyKit. To lekcja 4 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.

Pobieranie wartości granicznych

Rekruterzy pytają: „Pokaż każdy wiersz wraz z pierwszą i ostatnią wartością w jego grupie”. Może to być na przykład data pierwszego logowania użytkownika albo najnowsza cena w partycji wyświetlana obok każdego szczegółowego wiersza.

Służą do tego funkcje FIRST_VALUE i LAST_VALUE. Wyglądają prosto, ale LAST_VALUE kryje jedną z najsłynniejszych pułapek dotyczących ramek okna w SQL. W tej lekcji pokażemy, jak niezawodnie używać obu funkcji.

Podstawy FIRST_VALUE

FIRST_VALUE(col) zwraca wartość col z pierwszego wiersza okna i dołącza ją do każdego wiersza. Przy uporządkowaniu według daty każdy wiersz otrzymuje najwcześniejszą wartość ze swojej partycji.

Ponieważ domyślna ramka zaczyna się od pierwszego wiersza partycji, FIRST_VALUE zazwyczaj działa dokładnie tak, jak oczekują tego użytkownicy.

SELECT
  user_id,
  login_date,
  FIRST_VALUE(login_date) OVER (
    PARTITION BY user_id
    ORDER BY login_date
  ) AS first_login
FROM logins;

Domyślna ramka okna

Oto sedno sprawy. Po dodaniu ORDER BY do okna domyślna ramka to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Oznacza to, że okno dla każdego wiersza obejmuje zakres tylko od początku partycji do bieżącego wiersza, a nie do jej końca. FIRST_VALUE pozostaje bez zmian (pierwszy wiersz zawsze mieści się w zakresie), ale LAST_VALUE bardzo na tym traci.

Pułapka LAST_VALUE

Uruchamiając LAST_VALUE z samym ORDER BY, większość kandydatów oczekuje końcowej wartości partycji. Tymczasem ponieważ ramka kończy się na bieżącym wierszu, „ostatnia wartość w ramce” jest po prostu wartością bieżącego wiersza.

To zapytanie zwraca więc na każdym wierszu samo login_date, co wygląda jak błąd. To najczęściej spotykana pułapka związana z funkcjami okna.

SELECT
  user_id,
  login_date,
  LAST_VALUE(login_date) OVER (
    PARTITION BY user_id
    ORDER BY login_date
  ) AS wrong_last_login
FROM logins;

Naprawianie LAST_VALUE za pomocą pełnej ramki

Rozwiązanie polega na rozszerzeniu ramki tak, aby obejmowała całą partycję: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Teraz okno każdego wiersza obejmuje całą partycję, więc LAST_VALUE zwraca rzeczywistą końcową wartość. Podczas rozmowy kwalifikacyjnej proszę wyraźnie wskazać tę poprawkę; pokaże to, że rozumieją Państwo ramki, a nie tylko nazwy funkcji.

SELECT
  user_id,
  login_date,
  LAST_VALUE(login_date) OVER (
    PARTITION BY user_id
    ORDER BY login_date
    ROWS BETWEEN UNBOUNDED PRECEDING
             AND UNBOUNDED FOLLOWING
  ) AS last_login
FROM logins;

Łatwiejsza alternatywa

Wielu inżynierów całkowicie omija ramkę: aby uzyskać ostatnią wartość, używa FIRST_VALUE z odwróconym porządkiem sortowania.

FIRST_VALUE(login_date) OVER (... ORDER BY login_date DESC) zwraca najpóźniejszą datę bez konieczności podawania klauzuli ramki. To prosta i łatwa do zapamiętania sztuczka, o której warto wspomnieć.

SELECT
  user_id,
  login_date,
  FIRST_VALUE(login_date) OVER (
    PARTITION BY user_id
    ORDER BY login_date DESC
  ) AS last_login
FROM logins;

ROWS a RANGE w ramkach

Ramki występują w dwóch wariantach. ROWS zlicza wiersze fizyczne, a RANGE grupuje wiersze według równych wartości ORDER BY (wierszy równorzędnych).

Domyślna ramka używa RANGE, dlatego wiersze z remisową wartością sortowania mają wspólną granicę ramki. Przy poprawkach dla LAST_VALUE warto używać jawnego ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, aby uniknąć niespodzianek związanych z remisami.

NTH_VALUE dla dowolnych pozycji

Oprócz pierwszej i ostatniej wartości funkcja NTH_VALUE(col, n) pobiera wartość z pozycji n w obrębie ramki, na przykład cenę drugą od najwyższej.

Obowiązują ją te same zasady dotyczące ramek co funkcję LAST_VALUE, dlatego gdy potrzebują Państwo n-tej wartości z całej partycji, należy połączyć ją z pełną ramką zamiast ograniczać wynik do bieżącego wiersza.

SELECT
  product_id,
  price,
  NTH_VALUE(price, 2) OVER (
    PARTITION BY product_id
    ORDER BY price DESC
    ROWS BETWEEN UNBOUNDED PRECEDING
             AND UNBOUNDED FOLLOWING
  ) AS second_highest_price
FROM prices;

Przykład: FIRST_VALUE i LAST_VALUE razem

W typowym raporcie każda transakcja jest pokazana obok kwoty pierwszej i ostatniej transakcji klienta. Należy połączyć obie funkcje, pamiętając o jawnej ramce dla LAST_VALUE.

Teraz każdy wiersz zawiera pierwszą i ostatnią wartość z całej partycji, gotowe do obliczenia różnicy lub wykonania etapu etykietowania.

SELECT
  customer_id,
  txn_date,
  amount,
  FIRST_VALUE(amount) OVER w AS first_amt,
  LAST_VALUE(amount) OVER w AS last_amt
FROM transactions
WINDOW w AS (
  PARTITION BY customer_id
  ORDER BY txn_date
  ROWS BETWEEN UNBOUNDED PRECEDING
           AND UNBOUNDED FOLLOWING
);

Nazwane okna pomagają zachować zasadę DRY

Proszę zauważyć, że poprzednie zapytanie korzystało z klauzuli WINDOW w AS (...) i dwukrotnie odwoływało się do OVER w. Zdefiniowanie okna raz pozwala uniknąć powtarzania długiej specyfikacji ramki i zapobiega rozbieżnościom między obiema funkcjami.

Większość głównych baz danych obsługuje nazwane okna. Jest to eleganckie rozwiązanie, które rekruterzy doceniają, gdy kilka kolumn korzysta z tego samego okna.

Przykład: różnica od pierwszej do ostatniej transakcji

Częstym pytaniem dodatkowym jest zmiana od pierwszej do ostatniej transakcji klienta. Gdy obie wartości graniczne są dostępne w każdym wierszu, należy je od siebie odjąć, a następnie w razie potrzeby usunąć duplikaty, pozostawiając jeden wiersz na klienta.

Łączy to poprawkę dotyczącą pełnej ramki z prostą arytmetyką — właśnie taki kompletny sposób rozwiązania od początku do końca rekruterzy chcą zobaczyć.

SELECT DISTINCT
  customer_id,
  LAST_VALUE(amount) OVER w - FIRST_VALUE(amount) OVER w AS first_to_last_delta
FROM transactions
WINDOW w AS (
  PARTITION BY customer_id
  ORDER BY txn_date
  ROWS BETWEEN UNBOUNDED PRECEDING
           AND UNBOUNDED FOLLOWING
);

Szybki test

Klasyczna pułapka związana z LAST_VALUE.

Podsumowanie

Funkcje zwracające wartości graniczne zależą od ramki:

FIRST_VALUE działa z domyślną ramką, natomiast LAST_VALUE nie.
Domyślna ramka kończy się na bieżącym wierszu, dlatego LAST_VALUE należy poprawić za pomocą ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING albo odwrócić sortowanie i użyć FIRST_VALUE.
NTH_VALUE(col, n) pobiera wartości z dowolnych pozycji, a nazwane okna pomagają zachować zasadę DRY przy specyfikacjach obejmujących wiele kolumn.

To kończy zestaw narzędzi obejmujący LAG, LEAD, NTILE oraz funkcje zwracające wartości graniczne.

Często zadawane pytania

Czy lekcja „FIRST_VALUE, LAST_VALUE i krawędzie ramki” jest bezpłatna?

Tak — pełny tekst „FIRST_VALUE, LAST_VALUE i krawędzie ramki” 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 „FIRST_VALUE, LAST_VALUE i krawędzie ramki”?

Pobieranie wartości granicznych oraz pułapka związana z ramką LAST_VALUE Ć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 4 z 4.

Ile czasu zajmuje lekcja „FIRST_VALUE, LAST_VALUE i krawędzie ramki”?

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