0Pricing
SQL Interview Prep · Lekcja

Niezawodne zwracanie wierszy Top-N

Dlaczego ORDER BY wraz z LIMIT może dawać niedeterministyczne wyniki bez dodatkowego kryterium rozstrzygającego remisy

Niezawodne zwracanie wierszy Top-N to bezpłatna lekcja SQL Interview Prep na CoddyKit. To lekcja 3 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.

Ukryty błąd w zapytaniach Top-N

Prośba „pokaż 5 najlepiej zarabiających pracowników” wydaje się prosta: ORDER BY salary DESC LIMIT 5. Jednak osoby przeprowadzające rozmowy kwalifikacyjne często zastawiają tu pułapkę. Co jeśli na granicy wyniku sześć osób ma takie samo wynagrodzenie? Co jeśli takich remisów jest wiele?

Kluczowym problemem jest determinizm: gdy klucz sortowania zawiera remisy, LIMIT dokonuje arbitralnego cięcia, a dokładne zwrócone wiersze mogą zmieniać się między uruchomieniami. Ta lekcja pokazuje, jak uzyskać niezawodne wyniki Top-N.

Dlaczego ORDER BY + LIMIT może dawać niedeterministyczne wyniki

Rozważmy wynagrodzenia, w przypadku których osoby na miejscach 4, 5 i 6 mają po 50000. ORDER BY salary DESC LIMIT 5 musi zwrócić dokładnie 5 wierszy, więc zachowa dwa z trzech remisujących wierszy i odrzuci jeden, ale nie wiadomo, które dwa.

Po dwukrotnym uruchomieniu zapytania albo po zmianie planów przez optymalizator można otrzymać inne osoby. Ten brak determinizmu jest błędem, który należy zauważyć podczas rozmowy kwalifikacyjnej.

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

Naprawa 1: dodaj unikatowy klucz rozstrzygający

Najprostszym rozwiązaniem jest utworzenie pełnego porządku sortowania przez dodanie kolumny, która jest unikatowa, zwykle klucza głównego. Wtedy żadne dwa wiersze nie są równe względem całego klucza, więc cięcie wyniku jest deterministyczne i powtarzalne.

Nie zmienia to zestawu występujących wynagrodzeń, ale sprawia, że wybór spośród remisujących wierszy jest stabilny między uruchomieniami.

SELECT id, name, salary
FROM employees
ORDER BY salary DESC, id ASC
LIMIT 5;

Naprawa 2: uwzględnij wszystkie remisy za pomocą WITH TIES

Czasami wymaganie brzmi: „uwzględnij wszystkie osoby remisujące na granicy wyniku”, a nie „zwróć dokładnie N wierszy”. Standard SQL i SQL Server oferują WITH TIES, które zwraca dodatkowe wiersze mające taką samą wartość sortowania ORDER BY jak ostatni wiersz.

Jeśli piąte najwyższe wynagrodzenie otrzymują trzy osoby, zapytanie zwróci 7 wierszy. Należy pamiętać, że WITH TIES wymaga użycia ORDER BY.

SELECT name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS WITH TIES;

Najpierw doprecyzuj wymaganie

Przed rozpoczęciem implementacji należy zapytać osobę przeprowadzającą rozmowę: „Jeśli na granicy wyniku wystąpi remis, czy mają zostać zwrócone dokładnie N wierszy, czy wszystkie remisujące wiersze?” To jedno pytanie doprecyzowujące pokazuje doświadczenie.

  • Dokładnie N, w stabilny sposób: dodaj unikatowy klucz rozstrzygający.
  • Uwzględnij wszystkie remisy: użyj WITH TIES lub RANK.
  • Różne wartości: użyj DENSE_RANK.

Przenośne podejście z funkcją okna

Wiele silników nie obsługuje WITH TIES. Przenośny i elastyczny wzorzec polega na użyciu rankingowej funkcji okna w podzapytaniu lub CTE, a następnie odfiltrowaniu wyników według rangi. ROW_NUMBER zwraca dokładnie N wierszy przy deterministycznym kluczu sortowania.

Funkcję okna należy umieścić w zapytaniu zewnętrznym, ponieważ nie można odwołać się do niej bezpośrednio w WHERE.

SELECT name, salary
FROM (
  SELECT name, salary,
         ROW_NUMBER() OVER (ORDER BY salary DESC, id ASC) AS rn
  FROM employees
) ranked
WHERE rn <= 5;

RANK do zachowania remisów

Należy zamienić ROW_NUMBER na RANK, gdy mają zostać zachowane wszystkie remisujące wiersze, a numeracja ma zawierać luki. Jeśli trzy wiersze zajmują ex aequo 4. miejsce, wszystkie otrzymają rangę 4, a następna ranga będzie wynosić 7.

Filtrowanie według rank <= 5 zwróci każdy wiersz należący do pięciu najwyższych pozycji wynagrodzeń, wraz z remisami.

SELECT name, salary
FROM (
  SELECT name, salary,
         RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk <= 5;

DENSE_RANK dla N najwyższych różnych wartości

„3 najwyższe poziomy wynagrodzeń” (a nie 3 najlepiej zarabiające osoby) oznaczają różne wartości. DENSE_RANK przypisuje remisującym wartościom tę samą rangę i nie pomija numerów, więc dense_rnk <= 3 zwróci wszystkie osoby zarabiające jedno z trzech najwyższych różnych wynagrodzeń.

Umiejętność rozpoznania, która funkcja rankingowa odpowiada danemu sformułowaniu, jest klasycznym sposobem wyróżnienia się.

SELECT name, salary
FROM (
  SELECT name, salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
  FROM employees
) ranked
WHERE drnk <= 3;

Szczególny przypadek Top-1

W przypadku pojedynczego najwyższego wiersza ORDER BY ... LIMIT 1 działa, ale nadal nie rozstrzyga remisów. Jeśli mają zostać zwrócone wszystkie wiersze o maksymalnej wartości, można porównać je z maksimum zwróconym przez podzapytanie albo użyć RANK() = 1.

Wersja z podzapytaniem zwracającym maksimum jest przejrzysta i działa w każdym dialekcie.

SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

Porównanie podejść

Podsumowanie tego, kiedy używać poszczególnych narzędzi do niezawodnego Top-N:

  • LIMIT + unikatowy klucz rozstrzygający: dokładnie N wierszy, stabilny wynik, najprostsze rozwiązanie.
  • FETCH ... WITH TIES: dokładnie N wierszy oraz remisy na granicy wyniku, standard SQL.
  • ROW_NUMBER: dokładnie N wierszy, deterministyczny wynik, pełna przenośność.
  • RANK: N najwyższych pozycji wraz ze wszystkimi remisami.
  • DENSE_RANK: N najwyższych różnych wartości.

Zapowiedź Top-N w grupach

Podejście z funkcją okna można bardzo łatwo uogólnić. Dodaj PARTITION BY, aby uzyskać N najwyższych wyników w każdej grupie, na przykład 2 najlepiej zarabiające osoby w każdym dziale. Po podziale na grupy stosuje się ten sam filtr rn <= n.

Top-N dla każdej grupy to jeden z najczęściej spotykanych rzeczywistych problemów na rozmowach kwalifikacyjnych. Opiera się dokładnie na poznanym przed chwilą wzorcu.

SELECT department, name, salary
FROM (
  SELECT department, name, salary,
         ROW_NUMBER() OVER (PARTITION BY department
                            ORDER BY salary DESC, id ASC) AS rn
  FROM employees
) ranked
WHERE rn <= 2;

Szybki test

Dopasuj wymaganie do właściwej funkcji.

Podsumowanie

Aby niezawodnie zwracać wyniki Top-N:

  • Samodzielne ORDER BY ... LIMIT daje niedeterministyczne wyniki, gdy klucz sortowania zawiera remisy.
  • Dodaj unikatowy klucz rozstrzygający, aby uzyskać stabilny wynik zawierający dokładnie N wierszy.
  • Użyj WITH TIES lub RANK, aby zachować remisy na granicy wyniku.
  • Użyj DENSE_RANK dla N najwyższych różnych wartości.
  • Zawsze doprecyzuj, czy osoba przeprowadzająca rozmowę oczekuje dokładnie N wierszy, czy wszystkich remisów.

Często zadawane pytania

Czy lekcja „Niezawodne zwracanie wierszy Top-N” jest bezpłatna?

Tak — pełny tekst „Niezawodne zwracanie wierszy Top-N” 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 „Niezawodne zwracanie wierszy Top-N”?

Dlaczego ORDER BY wraz z LIMIT może dawać niedeterministyczne wyniki bez dodatkowego kryterium rozstrzygającego remisy Ć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 3 z 4.

Ile czasu zajmuje lekcja „Niezawodne zwracanie wierszy Top-N”?

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

  1. Sortowanie według wielu kolumn i rozmieszczanie wartości NULL
  2. LIMIT, OFFSET i FETCH FIRST
  3. Niezawodne zwracanie wierszy Top-N
  4. Sortowanie według wyrażeń i aliasów
← Powrót do SQL Interview Prep