Zachowywanie najnowszego wiersza dla każdego klucza
Wzorzec „najnowszy rekord dla każdego klienta” z partycjonowaniem według klucza i porządkowaniem według daty
Zachowywanie najnowszego wiersza dla każdego klucza to bezpłatna lekcja Coding 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 Coding Interview Prep, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs Coding Interview Prep zawiera 4 lekcji w sumie.
Problem najnowszego wiersza dla każdego klucza
„Zwróć najnowsze zamówienie każdego klienta”. „Pobierz najnowszy status każdego urządzenia”. Problem najnowszego wiersza dla każdego klucza jest jednym z najczęściej pojawiających się zadań SQL na rozmowach rekrutacyjnych, ponieważ stale występuje w rzeczywistej pracy analitycznej.
To wyspecjalizowany wariant problemu top-1-per-group: należy utworzyć partycje według klucza, posortować je malejąco według znacznika czasu i zachować pierwszy wiersz. Ta lekcja szczegółowo omawia ten wzorzec oraz jego alternatywy.
Dlaczego samo MAX nie wystarcza
Kuszącą pierwszą odpowiedzią jest MAX(order_date) z grupowaniem według klienta. Zwraca ona najnowszą datę, ale nie pozostałe dane tego zamówienia, takie jak jego identyfikator, kwota czy status.
Jeśli rekruter oczekuje pełnego wiersza najnowszego zamówienia, użycie MAX z GROUP BY wymaga dodatkowego połączenia zwrotnego z tabelą na podstawie klucza i maksymalnej daty. Jest to rozwlekłe rozwiązanie i może nie działać poprawnie w przypadku remisów. Funkcje okna są prostsze.
-- Gives the date, not the full row
SELECT customer_id, MAX(order_date) AS last_order
FROM orders
GROUP BY customer_id;Wzorzec ROW_NUMBER
Należy utworzyć partycje według klucza, posortować je malejąco według znacznika czasu, a najnowszy wiersz otrzyma rn = 1. Po zachowaniu tylko tych wierszy otrzymujemy pełny, najnowszy rekord dla każdego klucza.
To standardowa odpowiedź. Zwraca dokładnie jeden wiersz na klucz nawet wtedy, gdy znaczniki czasu są takie same, co zazwyczaj wynika ze sformułowania „najnowszy wiersz”.
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date DESC
) AS rn
FROM orders
)
SELECT customer_id, order_id, order_date, amount
FROM ranked
WHERE rn = 1;Rozstrzyganie remisów znaczników czasu
Dwa zamówienia tego samego klienta mogą mieć tę samą wartość order_date — na przykład pochodzić z tego samego dnia albo mieć identyczne znaczniki czasu. Bez kryterium rozstrzygającego remis wiersz, który otrzyma rn = 1, jest wybierany arbitralnie i może zmieniać się między uruchomieniami.
Należy dodać unikalny klucz pomocniczy, taki jak order_id DESC, aby wybór najnowszego wiersza był deterministyczny. Rekruterzy sprawdzają w szczególności, czy kandydat zauważył ten przypadek brzegowy.
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date DESC, order_id DESC
) AS rnNajnowszy wiersz czy wszystkie remisy
Należy zdecydować, co oznacza „najnowszy”, gdy znaczniki czasu są takie same:
- Jeśli potrzebny jest dokładnie jeden wiersz na klucz → należy użyć
ROW_NUMBERz kryterium rozstrzygającym remis. - Jeśli potrzebne są wszystkie wiersze o maksymalnym znaczniku czasu → należy użyć
RANK() = 1, które zwróci każdy zremisowany najnowszy wiersz.
Zadanie tego pytania doprecyzowującego sygnalizuje, że rozumieją Państwo semantykę, a nie tylko składnię.
WITH ranked AS (
SELECT *,
RANK() OVER (
PARTITION BY customer_id ORDER BY order_date DESC
) AS rnk
FROM orders
)
SELECT * FROM ranked WHERE rnk = 1;Alternatywa ze skorelowanym podzapytaniem
Zanim funkcje okna stały się powszechnie dostępne, problem najnowszego wiersza dla każdego klucza rozwiązywano za pomocą skorelowanego podzapytania: wiersz zachowywano tylko wtedy, gdy dla tego samego klucza nie istniał żaden inny wiersz z późniejszą datą.
Rozwiązanie działa, ale uruchamia zapytanie wewnętrzne dla każdego wiersza, dlatego w dużych tabelach jest wolniejsze i niezgrabne w przypadku remisów. Warto o nim wspomnieć, aby pokazać szeroki zakres wiedzy, ale ze względów wydajności należy preferować rozwiązanie z funkcją okna.
SELECT o.*
FROM orders o
WHERE o.order_date = (
SELECT MAX(o2.order_date)
FROM orders o2
WHERE o2.customer_id = o.customer_id
);Skrót DISTINCT ON w Postgres
PostgreSQL oferuje zwięzły idiom: DISTINCT ON (key) zachowuje pierwszy wiersz dla każdego klucza zgodnie z elementem ORDER BY. Element ORDER BY musi zaczynać się od tych samych kolumn klucza, a dopiero potem uwzględniać kryterium rozstrzygające remis i znacznik czasu.
To eleganckie i szybkie rozwiązanie w Postgres, ale nie jest przenośne. Warto wspomnieć o nim jako dodatkowym wariancie specyficznym dla danego dialektu, zachowując ROW_NUMBER jako domyślne rozwiązanie przenośne.
SELECT DISTINCT ON (customer_id)
customer_id, order_id, order_date, amount
FROM orders
ORDER BY customer_id, order_date DESC, order_id DESC;Najnowszy wiersz z warunkiem
Rzeczywiste pytania zawierają dodatkowe filtry: „najnowsze zakończone zamówienie każdego klienta”. Filtrowanie należy zastosować przed rankingowaniem, aby numerowane były tylko kwalifikujące się wiersze.
Warunek należy umieścić w klauzuli WHERE zapytania wewnętrznego, ponieważ jest ona wykonywana przed funkcją okna, a następnie w zapytaniu zewnętrznym wybrać rn = 1. Filtrowanie po rankingowaniu zwróciłoby niewłaściwy wiersz.
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY order_date DESC, order_id DESC
) AS rn
FROM orders
WHERE status = 'completed'
)
SELECT * FROM ranked WHERE rn = 1;Przykład: najnowszy status urządzenia
Tabela status_log rejestruje wartości device_id, status i logged_at. Aby pobrać bieżący status każdego urządzenia, należy utworzyć partycje według device_id, posortować je według logged_at DESC i zachować rn = 1.
To mechanizm stojący za panelami pokazującymi „bieżący stan” wielu obiektów na podstawie dziennika zdarzeń, do którego można tylko dopisywać dane. Ten sam wzorzec służy do tworzenia zapytań o najnowszą cenę, lokalizację i wersję.
WITH latest AS (
SELECT device_id, status, logged_at,
ROW_NUMBER() OVER (
PARTITION BY device_id ORDER BY logged_at DESC
) AS rn
FROM status_log
)
SELECT device_id, status, logged_at
FROM latest
WHERE rn = 1;Uwagi dotyczące wydajności
Warto wspomnieć o następujących kwestiach, aby pokazać poziom seniora:
- Indeks na
(customer_id, order_date DESC)pozwala silnikowi efektywnie odczytać najnowszy wiersz dla każdego klucza. - Podejście oparte na funkcji okna skanuje tabelę jednokrotnie, a skorelowane podzapytanie tego nie robi.
DISTINCT ONw Postgres może korzystać z tego samego indeksu i często jest najszybszą opcją dla pojedynczej tabeli.- W przypadku dzienników zdarzeń, do których intensywnie dopisywane są dane, warto rozważyć zmaterializowaną tabelę „latest”, odświeżaną przyrostowo.
Typowe błędy
Należy uważać na następujące kwestie:
- Użycie
MAX(date)i zwrócenie tylko daty zamiast pełnego wiersza. - Brak kryterium rozstrzygającego remis, co prowadzi do niedeterministycznych wyników, gdy daty są takie same.
- Filtrowanie według warunku po rankingowaniu, przez co może zostać wybrany wiersz, który powinien być wykluczony.
- Pomylanie „najnowszego pojedynczego wiersza” (
ROW_NUMBER) ze „wszystkimi najnowszymi wierszami mającymi ten sam znacznik czasu” (RANK).
Szybkie sprawdzenie
Należy wybrać poprawne zapytanie zwracające najnowszy wiersz dla każdego klucza.
Podsumowanie: najnowszy wiersz dla każdego klucza
Wzorzec: PARTITION BY key, ORDER BY timestamp DESC (plus a unique tiebreaker), keep rn = 1.
MAX(date)zwraca datę, a nie pełny wiersz.- Zawsze należy dodać kryterium rozstrzygające remis, aby wynik był deterministyczny.
- Należy użyć
RANK() = 1, jeśli potrzebne są wszystkie wiersze mające ten sam, najnowszy znacznik czasu. - Warunki filtrowania powinny znajdować się w zapytaniu wewnętrznym, przed rankingowaniem.
- Postgres
DISTINCT ONto zwięzła i szybka alternatywa specyficzna dla danego dialektu.
Ucz się Coding Interview Prep dzięki korepetycjom AI — za darmo
Pisz i uruchamiaj kod w przeglądarce, otrzymuj natychmiastową pomoc od korepetytora AI dostępnego 24/7 i kontynuuj naukę w sieci lub w aplikacji.
- Kursy
- 90
- Lekcje
- 360
Często zadawane pytania
Czy lekcja „Zachowywanie najnowszego wiersza dla każdego klucza” jest bezpłatna?
Tak — pełny tekst „Zachowywanie najnowszego wiersza dla każdego klucza” 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 Coding Interview Prep, przejdź na CoddyKit PRO. Kurs Coding Interview Prep zawiera 4 lekcji w sumie.
Co nauczysz się w „Zachowywanie najnowszego wiersza dla każdego klucza”?
Wzorzec „najnowszy rekord dla każdego klienta” z partycjonowaniem według klucza i porządkowaniem według daty Ćwiczysz Coding 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ąć Coding Interview Prep?
Nie wymagamy żadnego doświadczenia. Coding 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 „Zachowywanie najnowszego wiersza dla każdego klucza”?
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 Coding Interview Prep?
Tak. Każda lekcja Coding 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
- Wiersze Top-N dla każdej grupy za pomocą ROW_NUMBER
- Obsługa remisów w Top-N
- Bezpieczne usuwanie duplikatów wierszy
- Zachowywanie najnowszego wiersza dla każdego klucza