0Pricing
SQL Interview Prep · Lekcja

Przechodzenie po strukturze organizacyjnej

Przechodzenie przez hierarchię pracownik–przełożony na dowolną głębokość

Przechodzenie po strukturze organizacyjnej to bezpłatna lekcja SQL Interview Prep na CoddyKit. To lekcja 2 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.

Pytanie o schemat organizacyjny

„Mając tabelę employees z kolumnami id, name i manager_id, wyświetl wszystkie osoby podlegające wskazanemu menedżerowi, na dowolnej głębokości”. To jedno z najczęstszych pytań rekrutacyjnych dotyczących rekurencyjnych CTE.

Tabela ma strukturę samoodwołującą: manager_id wskazuje na wartość id innego wiersza. W tej lekcji przejdą Państwo po niej zarówno w dół (podwładni), jak i w górę (łańcuch podległości).

Tabela przykładowa

Proszę wyobrazić sobie takie dane. Dyrektor generalny ma menedżera o wartości NULL. Wszyscy pozostali podlegają komuś wyżej w hierarchii.

  • 1 Ada (menedżer NULL)
  • 2 Ben (menedżer 1)
  • 3 Cleo (menedżer 1)
  • 4 Dan (menedżer 2)
  • 5 Eve (menedżer 4)

Zatem głębokość przedstawia się następująco: Ada → Ben → Dan → Eve. Proszę o tym pamiętać podczas przechodzenia po strukturze.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT REFERENCES employees(id)
);

Przechodzenie w dół od menedżera

Aby wyświetlić wszystkich podwładnych wybranego menedżera, człon kotwiczący wybiera tego menedżera (lub jego bezpośrednich podwładnych), a człon rekurencyjny przechodzi w dół po manager_id.

W tym przykładzie zaczynamy od Bena (id 2) i zbieramy wszystkie osoby podlegające mu w hierarchii.

WITH RECURSIVE subtree AS (
    SELECT id, name, manager_id, 1 AS depth
    FROM employees WHERE id = 2
    UNION ALL
    SELECT e.id, e.name, e.manager_id, s.depth + 1
    FROM employees e
    JOIN subtree s ON e.manager_id = s.id
)
SELECT name, depth FROM subtree ORDER BY depth;

Odczytywanie wyników

Powyższe zapytanie zwraca Bena na poziomie głębokości 1, Dana na poziomie 2 i Eve na poziomie 3. Człon kotwiczący zainicjował Bena; pierwsza iteracja znalazła Dana (którego menedżerem jest Ben); druga iteracja znalazła Eve (której menedżerem jest Dan); trzecia iteracja nie znalazła nikogo, więc rekurencja się zatrzymała.

Jeśli osoba prowadząca rozmowę zapyta „o ile poziomów niżej od Bena znajduje się Eve?”, kolumna depth udziela bezpośredniej odpowiedzi: 3 minus 1 równa się 2 poziomy.

Przechodzenie w górę do dyrektora generalnego

Odwrotne pytanie jest równie częste: „Pokaż pełny łańcuch podległości Eve aż do dyrektora generalnego”. Należy odwrócić kierunek złączenia — człon rekurencyjny przechodzi teraz od wartości manager_id bieżącego wiersza w górę do elementu nadrzędnego.

WITH RECURSIVE chain AS (
    SELECT id, name, manager_id, 1 AS lvl
    FROM employees WHERE id = 5
    UNION ALL
    SELECT e.id, e.name, e.manager_id, c.lvl + 1
    FROM employees e
    JOIN chain c ON e.id = c.manager_id
)
SELECT name, lvl FROM chain ORDER BY lvl;

W dół czy w górę: zmienia się złączenie

Jedyną strukturalną różnicą między przechodzeniem w dół i w górę jest warunek złączenia:

  • W dół (wyszukiwanie podwładnych): e.manager_id = cte.id — dopasowuje pracowników, których menedżerem jest osoba znajdująca się już w wyniku.
  • W górę (wyszukiwanie menedżerów): e.id = cte.manager_id — dopasowuje pracownika, którego identyfikator jest menedżerem bieżącego wiersza.

Umiejętność jasnego wyjaśnienia tej zmiany kierunku robi na osobach prowadzących rozmowy dobre wrażenie.

Budowanie drzewa z wcięciami

Dopracowana odpowiedź formatuje wynik jako drzewo z wcięciami, wykorzystując depth do powtarzania spacji. Pokazuje to, że potrafią Państwo nie tylko obliczać wyniki hierarchiczne, lecz także je prezentować.

WITH RECURSIVE org AS (
    SELECT id, name, 1 AS depth
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, o.depth + 1
    FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT REPEAT('  ', depth - 1) || name AS tree
FROM org
ORDER BY depth;

Gromadzenie ścieżki

Aby pokazać pełną drogę od dyrektora generalnego do każdej osoby, należy przechowywać ciąg znaków path. Jest to ta sama technika co w poprzedniej lekcji, zastosowana do schematu organizacyjnego.

WITH RECURSIVE org AS (
    SELECT id, name, CAST(name AS VARCHAR(500)) AS path
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, o.path || ' / ' || e.name
    FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT name, path FROM org ORDER BY path;

Zliczanie podwładnych każdego menedżera

Częste pytanie dodatkowe brzmi: „Ile osób podlega każdemu menedżerowi bezpośrednio lub pośrednio?”. Należy użyć rekurencyjnego poddrzewa dla każdego menedżera, a następnie wykonać agregację. Typowy wzorzec polega na uruchomieniu rekurencji raz dla każdego korzenia i użyciu GROUP BY dla menedżera będącego punktem początkowym.

W tym przykładzie zliczamy wszystkich podwładnych pośrednich Ady (dyrektora generalnego), przechodząc po całym drzewie i zliczając wiersze poniżej korzenia.

WITH RECURSIVE org AS (
    SELECT id, name, manager_id, 0 AS depth
    FROM employees WHERE id = 1
    UNION ALL
    SELECT e.id, e.name, e.manager_id, o.depth + 1
    FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT COUNT(*) - 1 AS total_reports FROM org;

Częste błędy

Proszę uważać na następujące pułapki zastawiane podczas rozmów:

  • Nieprawidłowy kierunek złączenia — użycie e.manager_id = cte.id, gdy zamierzali Państwo przejść w górę, zwróci niewłaściwy zestaw.
  • Pominięcie filtra członu kotwiczącego — pominięcie WHERE id = X zainicjuje każdy wiersz, zwracając cały las.
  • Błąd o jeden w głębokości — należy zdecydować, czy punkt początkowy ma poziom 0 czy 1, i konsekwentnie stosować tę konwencję.

Dlaczego nie użyć tylko self-join

Self-join może pobrać ustaloną liczbę poziomów: jedno złączenie dla bezpośrednich podwładnych, dwa dla podwładnych drugiego poziomu i tak dalej. Trzeba jednak z góry znać głębokość i napisać osobne złączenie dla każdego poziomu.

Rekurencyjne CTE obsługuje dowolną, nieznaną z góry głębokość w jednym zapytaniu. Gdy osoba prowadząca rozmowę mówi, że „hierarchia może mieć dowolną liczbę poziomów”, wyklucza to zwykłe self-joiny i wskazuje na rekurencję.

Szybkie sprawdzenie

Proszę upewnić się, że potrafią Państwo odwrócić kierunek przechodzenia.

Podsumowanie

Przechodzenie po schemacie organizacyjnym opiera się na rekurencyjnym szkielecie stosowanym do tabeli z odwołaniami do samej siebie:

  • W dół: jako punkt początkowy wybierz menedżera, a następnie wykonaj złączenie e.manager_id = cte.id.
  • W górę: jako punkt początkowy wybierz pracownika, a następnie wykonaj złączenie e.id = cte.manager_id.
  • Przenoś depth do wcięć oraz path do przechowywania pełnego łańcucha.
  • Rekurencja obsługuje dowolną, nieznaną głębokość, czego nie potrafi self-join.

Dalej: użycie rekurencji do generowania ciągów liczb i dat.

Często zadawane pytania

Czy lekcja „Przechodzenie po strukturze organizacyjnej” jest bezpłatna?

Tak — pełny tekst „Przechodzenie po strukturze organizacyjnej” 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 „Przechodzenie po strukturze organizacyjnej”?

Przechodzenie przez hierarchię pracownik–przełożony na dowolną głębokość Ć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 2 z 4.

Ile czasu zajmuje lekcja „Przechodzenie po strukturze organizacyjnej”?

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. Elementy zakotwiczający i rekurencyjny
  2. Przechodzenie po strukturze organizacyjnej
  3. Generowanie szeregów liczb i dat
  4. Unikanie nieskończonej rekurencji
← Powrót do SQL Interview Prep