0Pricing
Coding Interview Prep · Lekcja

Najlepiej zarabiająca osoba w dziale

Łączenie partycjonowania z rankingiem w problemach dotyczących najwyższych N wynagrodzeń w grupach

Najlepiej zarabiająca osoba w dziale to bezpłatna lekcja Coding 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 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.

Od rankingu globalnego do rankingu w grupach

Kolejne rozszerzenie zadania brzmi: „Znajdź najlepiej zarabiającego pracownika w każdym dziale”. To połączenie rankingu z grupowaniem i typowe pytanie na poziomie mid.

Załóżmy, że istnieje tabela employee z kolumnami id, name, department_id i salary. Chcemy znaleźć najlepiej zarabiającego pracownika (lub kilku w przypadku remisu) z każdego działu, a nie tylko globalne maksimum.

Najważniejszym nowym narzędziem jest PARTITION BY, które rozpoczyna ranking od nowa w każdym dziale.

CREATE TABLE employee (
  id            INT PRIMARY KEY,
  name          VARCHAR(100),
  department_id INT,
  salary        INT
);

PARTITION BY resetuje ranking

Dodanie PARTITION BY department_id do definicji okna informuje bazę danych, że ranking należy obliczać niezależnie w obrębie każdego działu.

Każdy dział rozpoczyna własną rangę 1. Dlatego najlepiej zarabiający pracownik w dziale 1 i najlepiej zarabiający pracownik w dziale 5 otrzymają rangę 1. Bez podziału tylko jedno globalne maksimum otrzymałoby rangę 1.

SELECT name, department_id, salary,
       DENSE_RANK() OVER (
         PARTITION BY department_id
         ORDER BY salary DESC
       ) AS rnk
FROM employee;

Filtrowanie do rangi 1

Aby zachować tylko najlepiej zarabiających pracowników, należy opakować zapytanie z rankingiem i odfiltrować rangę 1. Jak zawsze, funkcję okna trzeba obliczyć w podzapytaniu lub CTE, zanim będzie można filtrować jej wynik.

Użycie DENSE_RANK (lub RANK) oznacza, że jeśli dwóch pracowników ma najwyższe wynagrodzenie w danym dziale, zostaną zwróceni obaj. Zwykle jest to właściwa interpretacja określenia „najlepiej zarabiający pracownik”.

SELECT name, department_id, salary
FROM (
  SELECT name, department_id, salary,
         DENSE_RANK() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
         ) AS rnk
  FROM employee
) t
WHERE rnk = 1;

ROW_NUMBER, gdy potrzebny jest dokładnie jeden wiersz

Czasami osoba przeprowadzająca rozmowę chce otrzymać dokładnie jeden wiersz dla każdego działu, nawet w przypadku remisu. Wtedy należy użyć ROW_NUMBER i dodać deterministyczne rozstrzygnięcie remisu, na przykład według najmniejszej wartości id.

Bez takiego rozstrzygnięcia remisy są rozwiązywane arbitralnie, a wynik nie jest deterministyczny. Dodanie , id ASC sprawia, że wybór można powtarzalnie odtworzyć.

SELECT name, department_id, salary
FROM (
  SELECT name, department_id, salary,
         ROW_NUMBER() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC, id ASC
         ) AS rn
  FROM employee
) t
WHERE rn = 1;

DENSE_RANK a ROW_NUMBER i RANK w tym przypadku

Wybór zależy od dokładnego sformułowania wymagania:

  • DENSE_RANK = 1: wszyscy pracownicy, którzy remisują pod względem najwyższego wynagrodzenia w danym dziale.
  • RANK = 1: wynik identyczny jak w przypadku DENSE_RANK dla pierwszej pozycji (luki mają znaczenie dopiero poniżej pozycji 1).
  • ROW_NUMBER = 1: dokładnie jeden pracownik z każdego działu, a remisy są rozstrzygane zgodnie z klauzulą ORDER BY.

Wskazanie, której funkcji użyto i dlaczego, jest właśnie tym elementem, który oceniają osoby przeprowadzające rozmowę.

Podejście skorelowane sprzed wprowadzenia funkcji okna

Zanim pojawiły się funkcje okna, standardowym rozwiązaniem było podzapytanie skorelowane: należy zachować wiersz tylko wtedy, gdy nikt w tym samym dziale nie zarabia więcej.

W ten sposób naturalnie otrzymuje się wszystkich najlepiej zarabiających pracowników remisujących ze sobą. Rozwiązanie jest przenośne między systemami, ale może działać wolno, ponieważ wewnętrzne MAX jest obliczane dla każdego wiersza zapytania zewnętrznego, chyba że optymalizator przepisze zapytanie.

SELECT e.name, e.department_id, e.salary
FROM employee e
WHERE e.salary = (
  SELECT MAX(e2.salary)
  FROM employee e2
  WHERE e2.department_id = e.department_id
);

Podejście ze złączeniem po GROUP BY

Inny przenośny wzorzec polega na obliczeniu maksymalnego wynagrodzenia w każdym dziale za pomocą GROUP BY, a następnie wykonaniu złączenia z powrotem, aby pobrać pasujących pracowników.

To rozwiązanie jest wydajne i czytelne. Złączenie zwraca każdego pracownika, którego wynagrodzenie jest równe maksymalnemu wynagrodzeniu w jego dziale, dzięki czemu remisy zostają zachowane.

SELECT e.name, e.department_id, e.salary
FROM employee e
JOIN (
  SELECT department_id, MAX(salary) AS max_sal
  FROM employee
  GROUP BY department_id
) m
  ON e.department_id = m.department_id
 AND e.salary = m.max_sal;

N najlepszych pracowników w każdym dziale

Wzorzec można rozszerzyć do przypadku „3 najlepiej zarabiających pracowników w każdym dziale” bez wprowadzania nowych koncepcji. Wystarczy zmienić filtr na zakres.

W przypadku DENSE_RANK warunek rnk <= 3 zwraca trzy najwyższe różne poziomy wynagrodzeń (w przypadku remisów może to oznaczać więcej niż trzy wiersze). W przypadku ROW_NUMBER warunek rn <= 3 zwraca dokładnie trzy wiersze z każdego działu.

SELECT name, department_id, salary
FROM (
  SELECT name, department_id, salary,
         DENSE_RANK() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
         ) AS rnk
  FROM employee
) t
WHERE rnk <= 3;

Przykład rozwiązania

Dział 1: Ana 120, Bob 120, Cara 90. Dział 2: Dan 200, Eve 150.

  • DENSE_RANK = 1: Ana (120) i Bob (120) z działu 1 oraz Dan (200) z działu 2. Trzy wiersze.
  • ROW_NUMBER = 1 z rozstrzygnięciem remisu według id: jedna z osób Ana/Bob (ta, która ma mniejsze id) oraz Dan. Dwa wiersze.

Te same dane mogą dać różną liczbę wierszy w zależności od użytej funkcji. Należy wybrać funkcję odpowiednią do treści pytania.

Uwzględnianie działów i dołączanie nazw

Osoby przeprowadzające rozmowę często dodają tabelę department i proszą o zwrócenie nazwy działu. Wystarczy złączyć ją dopiero po utworzeniu rankingu.

Ranking należy utworzyć na tabeli employee, a tabelę słownikową złączyć na końcu, aby partycjonowanie nadal odbywało się na właściwym poziomie szczegółowości.

SELECT d.name AS department, t.name AS employee, t.salary
FROM (
  SELECT name, department_id, salary,
         DENSE_RANK() OVER (
           PARTITION BY department_id ORDER BY salary DESC
         ) AS rnk
  FROM employee
) t
JOIN department d ON d.id = t.department_id
WHERE t.rnk = 1;

Błędy, których należy unikać

Typowe błędy podczas tworzenia rankingów w grupach:

  • Pomijanie PARTITION BY i tworzenie rankingu globalnego, co zwraca tylko najlepiej zarabiającego pracownika w całej firmie.
  • Użycie ROW_NUMBER, gdy treść pytania sugeruje, że należy uwzględnić wszystkie remisy, co po cichu pomija pracowników wspólnie zajmujących pierwsze miejsce.
  • Próba umieszczenia funkcji okna bezpośrednio w klauzuli WHERE zamiast opakowania zapytania.
  • Złączenie tabeli działów przed utworzeniem rankingu i przypadkowa zmiana poziomu szczegółowości partycji.

Szybki test

Należy wybrać właściwą funkcję rankingu zgodnie z wymaganiem.

Podsumowanie

Najlepiej zarabiający pracownik w każdym dziale to wzorzec rankingu globalnego rozszerzony o PARTITION BY department_id:

  • DENSE_RANK = 1 zwraca wszystkich najlepiej zarabiających pracowników remisujących ze sobą w każdym dziale.
  • ROW_NUMBER = 1 z rozstrzygnięciem remisu zwraca dokładnie jednego pracownika z każdego działu.
  • Przenośne alternatywy to skorelowane MAX dla każdego działu albo maksymalne wartości obliczone za pomocą GROUP BY i złączone z powrotem z tabelą.

Aby uzyskać N najlepszych wyników, należy zmienić = 1 na <= N. Warto wyraźnie powiedzieć, w jaki sposób rozstrzygany jest remis.

Często zadawane pytania

Czy lekcja „Najlepiej zarabiająca osoba w dziale” jest bezpłatna?

Tak — pełny tekst „Najlepiej zarabiająca osoba w dziale” 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 „Najlepiej zarabiająca osoba w dziale”?

Łączenie partycjonowania z rankingiem w problemach dotyczących najwyższych N wynagrodzeń w grupach Ć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 3 z 4.

Ile czasu zajmuje lekcja „Najlepiej zarabiająca osoba w dziale”?

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

  1. Druga najwyższa pensja na pięć sposobów
  2. N-ta najwyższa wartość za pomocą DENSE_RANK
  3. Najlepiej zarabiająca osoba w dziale
  4. Zwracanie NULL, gdy nie istnieje n-ta wartość
← Powrót do Coding Interview Prep