Wiersze Top-N dla każdej grupy za pomocą ROW_NUMBER
Klasyczny wzorzec partycjonowania i rankingu dla problemów typu „3 najlepsze pozycje w kategorii”
Wiersze Top-N dla każdej grupy za pomocą ROW_NUMBER to bezpłatna lekcja Coding 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 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.
Pytanie o pierwsze N elementów w każdej grupie
Jedno z najczęstszych pytań podczas rozmów kwalifikacyjnych dotyczących SQL brzmi prosto: "Zwróć 3 najlepiej opłacanych pracowników w każdym dziale." Kandydaci, którzy od razu sięgają po LIMIT, odpowiadają błędnie, ponieważ LIMIT ogranicza cały zbiór wyników, a nie każdą grupę osobno.
Osoba przeprowadzająca rozmowę sprawdza, czy zna Pan/Pani funkcje okna. Standardowa odpowiedź brzmi: ponumerować wiersze wewnątrz każdej grupy, a następnie zachować te, których numer jest ≤ N. W tej lekcji ten schemat zostanie zbudowany krok po kroku.
Dlaczego LIMIT nie rozwiązuje tego problemu
Załóżmy, że napisze Pan/Pani zapytanie przedstawione poniżej. Zwróci ono tylko 3 wiersze łącznie z całej tabeli, a nie 3 wiersze z każdego działu.
LIMIT (podobnie jak TOP lub FETCH FIRST) działa na końcowym zbiorze wyników. W standardzie SQL nie istnieje LIMIT działający osobno dla każdej grupy. Gdy podczas rozmowy kwalifikacyjnej zaproponuje Pan/Pani LIMIT 3 dla problemu dotyczącego poszczególnych grup, będzie to sygnał, że koncepcja partycjonowania nie została jeszcze dobrze przyswojona.
-- WRONG: only 3 rows total, not 3 per department
SELECT department, name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;Poznajmy ROW_NUMBER
ROW_NUMBER() to funkcja okna, która przypisuje każdemu wierszowi unikatową, ciągłą liczbę całkowitą zgodnie z określoną kolejnością. Użyta samodzielnie, numeruje cały wynik.
Kluczowym elementem jest PARTITION BY: numerowanie zaczyna się od 1 ponownie dla każdej grupy. Połączenie PARTITION BY department z ORDER BY salary DESC sprawia, że każdy dział otrzymuje własne numery 1, 2, 3, ... odpowiadające miejscom uszeregowanym według wynagrodzenia.
SELECT
name,
department,
salary,
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS rn
FROM employees;Odczytywanie ponumerowanego wyniku
Po uruchomieniu poprzedniego zapytania każdy wiersz zawiera wartość rn. W obrębie każdego działu najwyższe wynagrodzenie otrzymuje rn = 1, kolejne 2 i tak dalej. Dla nowego działu numeracja zaczyna się ponownie od 1.
- Sales: Ana (1), Bo (2), Cal (3), Dee (4)
- Engineering: Eve (1), Fin (2), Gus (3)
„Pierwsze 3 elementy w każdym dziale” oznacza teraz po prostu „zachować wiersze, dla których rn <= 3”.
Nie można filtrować rn w WHERE
Naturalnym kolejnym krokiem byłoby użycie WHERE rn <= 3, ale to nie zadziała. Funkcje okna są obliczane po klauzuli WHERE w logicznej kolejności wykonywania, więc alias rn nie istnieje jeszcze w momencie wykonywania WHERE.
Osoby przeprowadzające rozmowy kwalifikacyjne często wykorzystują tę pułapkę. Rozwiązaniem jest obliczenie funkcji okna w podzapytaniu lub CTE, a następnie przefiltrowanie wyniku tego wewnętrznego zapytania w zapytaniu zewnętrznym.
-- ERROR: rn does not exist in WHERE
SELECT name, department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees
WHERE rn <= 3;Standardowe rozwiązanie z CTE
Należy umieścić numerowanie w CTE o nazwie ranked, a następnie wybrać z niego dane, stosując filtr w zewnętrznej klauzuli WHERE. To rozwiązanie, którego osoby przeprowadzające rozmowy kwalifikacyjne oczekują, a jego zapis jest przejrzysty.
Warto zapamiętać ten schemat: partycjonować według grupy, sortować według miary, a następnie filtrować rn ≤ N w zapytaniu zewnętrznym. Można go zastosować do pierwszego, pierwszych pięciu lub dowolnych N elementów, zmieniając tylko jedną liczbę.
WITH ranked AS (
SELECT
name, department, salary,
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS rn
FROM employees
)
SELECT name, department, salary
FROM ranked
WHERE rn <= 3
ORDER BY department, rn;Wersja z podzapytaniem
Jeśli używany dialekt jest starszy albo osoba przeprowadzająca rozmowę preferuje podzapytania, identyczną logikę można umieścić w tabeli pochodnej w klauzuli FROM. Należy pamiętać, że tabela pochodna musi mieć alias (tutaj r), w przeciwnym razie wystąpi błąd składni.
Wersje z CTE i tabelą pochodną są w tym problemie równoważne. Należy wybrać tę, którą osoba przeprowadzająca rozmowę uzna za czytelniejszą; obie są równie poprawne.
SELECT name, department, salary
FROM (
SELECT name, department, salary,
ROW_NUMBER() OVER (
PARTITION BY department ORDER BY salary DESC
) AS rn
FROM employees
) AS r
WHERE rn <= 3;Pierwszy element: najlepszy w każdej grupie
„Znajdź najlepiej opłacanego pracownika w każdym dziale” oznacza po prostu N = 1. Należy ustawić filtr na rn = 1.
Dlaczego nie użyć MAX(salary) z GROUP BY department? Ponieważ MAX zwraca wartość wynagrodzenia, ale nie pozostałe dane tego pracownika, takie jak imię i nazwisko czy data zatrudnienia. ROW_NUMBER zachowuje cały zwycięski wiersz, czego zazwyczaj naprawdę wymaga pytanie.
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY department ORDER BY salary DESC
) AS rn
FROM employees
)
SELECT name, department, salary, hire_date
FROM ranked
WHERE rn = 1;Dodawanie deterministycznego rozstrzygnięcia remisu
ROW_NUMBER zawsze zwraca dokładnie N wierszy, nawet gdy wynagrodzenia są takie same. Jednak to, który z remisujących wierszy otrzyma rn = 1, jest przypadkowe, jeśli remis nie zostanie rozstrzygnięty. Jeśli dwie osoby zarabiają 90000, a zachowany ma zostać tylko wiersz z rn = 1, wybrana osoba może różnić się między uruchomieniami.
Należy dodać dodatkowy, unikatowy klucz sortowania, taki jak employee_id, aby wynik był stabilny i powtarzalny. Osoby przeprowadzające rozmowy kwalifikacyjne doceniają kandydatów, którzy bez dodatkowej zachęty wspominają o determinizmie.
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY salary DESC, employee_id ASC
) AS rnKonkretny przykład
Załóżmy, że tabela sales zawiera kolumny region, product i revenue. Należy zwrócić 2 produkty o najwyższym przychodzie w każdym regionie. Schemat jest taki sam: partycjonować według region, sortować według revenue DESC, zachować wiersze, dla których rn <= 2.
Warto zauważyć, że zmieniają się tylko kolumna partycjonowania i kolumna miary. Struktura pozostaje identyczna niezależnie od dziedziny biznesowej.
WITH ranked AS (
SELECT region, product, revenue,
ROW_NUMBER() OVER (
PARTITION BY region ORDER BY revenue DESC, product
) AS rn
FROM sales
)
SELECT region, product, revenue
FROM ranked
WHERE rn <= 2
ORDER BY region, rn;Wydajność i najważniejsze kwestie do omówienia
Aby wykazać się czymś więcej niż tylko poprawnością rozwiązania, warto wspomnieć:
- Indeks na
(department, salary DESC)pomaga silnikowi efektywnie tworzyć uporządkowane wiersze dla każdej partycji. - Podejście z funkcją okna skanuje tabelę raz, co jest znacznie lepsze niż podzapytanie skorelowane wykonywane dla każdego wiersza.
- W przypadku bardzo dużych zapytań typu top-N-of-1 niektóre silniki obsługują
DISTINCT ON(Postgres) jako skrót, aleROW_NUMBERjest przenośnym standardem.
Należy zawsze wskazać sposób rozstrzygania remisów i potwierdzić żądaną wartość N.
Szybkie sprawdzenie
Sprawdź, czy rozumie Pan/Pani schemat top-N dla każdej grupy.
Podsumowanie: pierwsze N elementów w każdej grupie
Cały schemat w jednym zdaniu: partycjonować według grupy, sortować według miary, przypisać ROW_NUMBER, a następnie zachować rn ≤ N w zapytaniu zewnętrznym.
LIMITogranicza cały zbiór, nigdy poszczególne grupy.- Nie można filtrować aliasu funkcji okna w
WHERE; należy umieścić go w CTE lub podzapytaniu. - Należy dodać unikatowy klucz rozstrzygający remis, aby uzyskać deterministyczne wyniki.
- Pierwszy element zachowuje cały zwycięski wiersz, w przeciwieństwie do połączenia
MAX+GROUP BY.
Po zmianie jednej liczby to samo zapytanie rozwiązuje problem pierwszego, pierwszych pięciu lub dowolnych N elementów.
Często zadawane pytania
Czy lekcja „Wiersze Top-N dla każdej grupy za pomocą ROW_NUMBER” jest bezpłatna?
Tak — pełny tekst „Wiersze Top-N dla każdej grupy za pomocą ROW_NUMBER” 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 „Wiersze Top-N dla każdej grupy za pomocą ROW_NUMBER”?
Klasyczny wzorzec partycjonowania i rankingu dla problemów typu „3 najlepsze pozycje w kategorii” Ć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 1 z 4.
Ile czasu zajmuje lekcja „Wiersze Top-N dla każdej grupy za pomocą ROW_NUMBER”?
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