Coding Interview Prep · Lekcja

Druga najwyższa pensja na pięć sposobów

Porównanie rozwiązań z podzapytaniem, LIMIT/OFFSET i funkcjami okienkowymi

Druga najwyższa pensja na pięć sposobów 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, które pada na każdej rozmowie

„Znajdź drugą najwyższą pensję” to najczęściej zadawane pytanie SQL na rozmowach rekrutacyjnych. Rekruterzy je uwielbiają, ponieważ istnieje wiele poprawnych odpowiedzi i kilka subtelnych pułapek.

Załóżmy, że tabela employee zawiera kolumny id i salary. Zadaniem jest zwrócenie drugiej najwyższej odrębnej wartości wynagrodzenia.

Jeśli wynagrodzenia wynoszą 300, 200, 200, 100, odpowiedzią jest 200, a nie drugi wiersz.
Jeśli nie istnieje druga odrębna wartość wynagrodzenia, oczekiwaną odpowiedzią jest zazwyczaj NULL.

W kolejnych scenach rozwiążemy ten problem na pięć różnych sposobów i omówimy, kiedy każdy z nich jest najlepszy.

CREATE TABLE employee (
  id     INT PRIMARY KEY,
  salary INT
);

Sposób 1: MAX wartości poniżej MAX

Najbardziej intuicyjne rozwiązanie: drugie najwyższe wynagrodzenie to największa wartość wynagrodzenia, która jest ściśle mniejsza od ogólnego maksimum.

To rozwiązanie jest niemal tak czytelne jak zdanie po angielsku i działa w każdym dialekcie SQL. Podzapytanie wewnętrzne znajduje najwyższą wartość, a zewnętrzna funkcja MAX znajduje największą wartość poniżej niej.

Dodatkowa zaleta: jeśli nie istnieje druga odrębna wartość wynagrodzenia, zewnętrzna funkcja MAX agreguje zero wierszy i automatycznie zwraca NULL. To automatycznie uzyskane NULL jest dokładnie tym, czego oczekują rekruterzy.

SELECT MAX(salary) AS second_highest
FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);

Dlaczego podzapytanie poprawnie obsługuje duplikaty

Zauważ, że w sposobie 1 nie użyliśmy ani razu DISTINCT, a mimo to duplikaty są poprawnie obsługiwane.

Jeśli trzy osoby zarabiają po 200, a osoba z najwyższym wynagrodzeniem zarabia 300, zapytanie wewnętrzne zwróci 300. Filtr zewnętrzny zachowa każdy wiersz o wartości mniejszej niż 300, a funkcja MAX zwróci spośród nich 200 — niezależnie od tego, ile występuje wartości 200.

To najważniejszy wniosek: agregaty automatycznie eliminują wpływ duplikatów. Wielu kandydatów niepotrzebnie komplikuje rozwiązanie, używając DISTINCT, mimo że agregat już wykonuje właściwą pracę.

Sposób 2: LIMIT z OFFSET

W MySQL i PostgreSQL można posortować różne wynagrodzenia malejąco i pominąć pierwsze.

OFFSET 1 pomija najwyższe wynagrodzenie.
LIMIT 1 zachowuje tylko następne.

DISTINCT jest tu niezbędne. W przeciwnym razie powtarzające się najwyższe wynagrodzenia sprawią, że OFFSET 1 wskaże ponownie maksimum zamiast rzeczywiście drugiego najwyższego wynagrodzenia.

Pułapka: jeśli nie istnieje druga różna wartość, zapytanie zwróci zero wierszy, a nie NULL. Ten przypadek brzegowy naprawimy w lekcji 4.

SELECT DISTINCT salary
FROM employee
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

Sposób 3: FETCH w SQL Server i Oracle

SQL Server i nowoczesne wersje Oracle nie obsługują składni LIMIT ... OFFSET. Zamiast niej używają składni standardu ANSI OFFSET ... FETCH.

Logika jest identyczna jak w sposobie 2: posortować różne wynagrodzenia malejąco, pominąć jeden wiersz i pobrać jeden. Znajomość składni stosowanej w różnych dialektach pokazuje rekruterowi doświadczenie z rzeczywistymi projektami.

SELECT DISTINCT salary
FROM employee
ORDER BY salary DESC
OFFSET 1 ROWS
FETCH NEXT 1 ROWS ONLY;

Sposób 4: funkcja okna DENSE_RANK

Nowoczesne i skalowalne rozwiązanie wykorzystuje funkcję okna. DENSE_RANK przypisuje rangę 1 do najwyższego wynagrodzenia, rangę 2 do następnego różnego wynagrodzenia, a wartościom równym nadaje tę samą rangę bez przerw.

Obliczamy rangę w podzapytaniu, a następnie w zapytaniu zewnętrznym filtrujemy wiersze o randze 2. Należy pamiętać, że nie można bezpośrednio filtrować funkcji okna w klauzuli WHERE, dlatego opakowanie w podzapytanie jest konieczne.

SELECT salary AS second_highest
FROM (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employee
) ranked
WHERE rnk = 2;

Dlaczego DENSE_RANK, a nie RANK ani ROW_NUMBER

Wybór funkcji rankingowej ma znaczenie dla interpretacji różnych wartości:

ROW_NUMBER nadaje każdemu wierszowi unikalny numer, więc dwie osoby zarabiające 300 otrzymałyby numery 1 i 2, a ranga 2 byłaby powtórzeniem najwyższego wynagrodzenia. To błędne rozwiązanie.
RANK pozostawia przerwy po remisach: dwie osoby zarabiające 300 otrzymują rangę 1, a następne wynagrodzenie przeskakuje do rangi 3. W ten sposób zostanie pominięte przy filtrowaniu rangi 2. To błędne rozwiązanie.
DENSE_RANK nadaje wartościom równym tę samą rangę i nie pozostawia przerw, więc ranga 2 zawsze oznacza drugie różne wynagrodzenie. To poprawne rozwiązanie.

Sposób 5: zliczanie za pomocą skorelowanego podzapytania

Klasyczny trik stosowany przed wprowadzeniem funkcji okna: wynagrodzenie jest N-tym najwyższym, jeśli istnieje dokładnie N-1 różnych wynagrodzeń ściśle od niego wyższych.

W przypadku drugiego najwyższego wynagrodzenia chcemy, aby dokładnie jedno różne wynagrodzenie było od niego wyższe. To eleganckie rozwiązanie może jednak działać wolno dla dużych tabel, ponieważ wewnętrzne zliczanie wykonuje się dla każdego wiersza zapytania zewnętrznego.

Łatwo uogólnić je na N-te najwyższe wynagrodzenie, zmieniając wartość zliczania na N - 1, dlatego rekruterzy lubią, gdy kandydaci je znają.

SELECT salary AS second_highest
FROM employee e
WHERE 1 = (
  SELECT COUNT(DISTINCT e2.salary)
  FROM employee e2
  WHERE e2.salary > e.salary
);

Przykład od początku do końca

Przyjmijmy wynagrodzenia: 500, 500, 350, 350, 100.

Sposób 1: MAX wynosi 500, a największa wartość mniejsza niż 500 to 350. Odpowiedź: 350.
Sposób 4 (DENSE_RANK): 500 -> ranga 1, 350 -> ranga 2, 100 -> ranga 3. Ranga 2 oznacza 350.
Sposób 5: dla wynagrodzenia 350 istnieje dokładnie jedno różne wynagrodzenie (500) od niego wyższe. Warunek jest spełniony. Odpowiedź: 350.

Wszystkie pięć metod daje ten sam wynik: drugim najwyższym różnym wynagrodzeniem jest 350, nawet jeśli w danych występują duplikaty.

Po który sposób sięgnąć

Wskazówki dotyczące rozmowy kwalifikacyjnej:

Najpierw doprecyzować pytanie: „Czy chodzi o różne wynagrodzenia i wartość NULL, jeśli żadne nie istnieje?”. Takie doprecyzowanie jest dodatkowym atutem.
DENSE_RANK to najsilniejsza odpowiedź domyślna; łatwo uogólnić ją na N-te wynagrodzenie i zastosować osobno w każdej grupie.
MAX poniżej MAX to najlepsze rozwiązanie jednolinijkowe, które automatycznie zwraca NULL.
LIMIT/OFFSET jest zwięzłe, ale zależne od dialektu i w przypadku brzegowym nie zwraca żadnych wierszy.

Głośne omówienie kompromisów odróżnia odpowiedź na poziomie mid od odpowiedzi juniorskiej.

Pułapki, których należy unikać

Należy uważać na następujące pułapki, które rekruterzy celowo umieszczają w zadaniach:

Użycie ROW_NUMBER zamiast DENSE_RANK, przez co najwyższe wynagrodzenie zostaje zwrócone dwukrotnie.
Pominięcie DISTINCT w wersji z LIMIT/OFFSET, gdy najwyższe wynagrodzenie występuje wielokrotnie.
Założenie, że ORDER BY salary DESC LIMIT 1,1 zwraca różną wartość — tak nie jest.
Zwrócenie drugiego wiersza zamiast drugiej wartości.

Szybki test

Sprawdź, czy rozumiesz wybór funkcji rankingowej.

Podsumowanie

Znają już Państwo pięć sposobów znajdowania drugiego najwyższego wynagrodzenia:

MAX poniżej MAX - rozwiązanie przenośne, które automatycznie zwraca NULL.
LIMIT/OFFSET oraz OFFSET/FETCH - rozwiązania zwięzłe, ale zależne od dialektu.
DENSE_RANK - skalowalne rozwiązanie domyślne, które poprawnie obsługuje remisy.
Skorelowane zliczanie - eleganckie rozwiązanie, które można uogólnić na N-te wynagrodzenie.

Najważniejsze wnioski: należy ustalić, czy potrzebne są różne wartości, preferować DENSE_RANK przy remisach oraz pamiętać, które metody zwracają NULL, a które nie zwracają żadnych wierszy, gdy druga wartość nie istnieje.

Często zadawane pytania

Czy lekcja „Druga najwyższa pensja na pięć sposobów” jest bezpłatna?

Tak — pełny tekst „Druga najwyższa pensja na pięć sposobów” 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 „Druga najwyższa pensja na pięć sposobów”?

Porównanie rozwiązań z podzapytaniem, LIMIT/OFFSET i funkcjami okienkowymi Ć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 „Druga najwyższa pensja na pięć sposobów”?

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

Druga najwyższa pensja na pięć sposobów
N-ta najwyższa wartość za pomocą DENSE_RANK
Najlepiej zarabiająca osoba w dziale
Zwracanie NULL, gdy nie istnieje n-ta wartość

← Powrót do Coding Interview Prep