SQL Academy · Lekcja

Przechodzenie drzewa kategorii

W pełni rozwijaj drzewa nadrzędny–podrzędny

Lekcja 2 z 413 kroki

Przechodzenie drzewa kategorii to bezpłatna lekcja SQL Academy 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 Academy, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs SQL Academy zawiera 4 lekcji w sumie.

Czym jest drzewo kategorii?

Wiele rzeczywistych zbiorów danych ma relację rodzic-dziecko. Katalog produktów może zawierać kategorie takie jak Elektronika → Telefony → Smartfony. Każdy węzeł ma rodzica, co tworzy strukturę drzewa.

W SQL jest to zazwyczaj przechowywane w tabeli odwołującej się do samej siebie: każdy wiersz ma kolumny id i parent_id, przy czym parent_id wskazuje inny wiersz tej samej tabeli.

CREATE TABLE categories (
  id       INT PRIMARY KEY,
  name     VARCHAR(100) NOT NULL,
  parent_id INT REFERENCES categories(id)
);

Przykładowe dane kategorii

Wypełnijmy niewielkie drzewo kategorii. Węzeł główny ma parent_id = NULL, ponieważ nie ma rodzica. Każdy pozostały węzeł wskazuje swojego rodzica za pomocą niepustego identyfikatora parent_id.

INSERT INTO categories (id, name, parent_id) VALUES
  (1, 'Electronics',   NULL),
  (2, 'Phones',         1),
  (3, 'Laptops',        1),
  (4, 'Smartphones',    2),
  (5, 'Feature Phones', 2),
  (6, 'Gaming Laptops', 3),
  (7, 'Ultrabooks',     3);

Problem ze zwykłymi zapytaniami

Zwykła instrukcja SELECT może pobrać tylko jeden poziom naraz. Aby dotrzeć do trzech poziomów w głąb, potrzebne byłyby trzy oddzielne zapytania lub trzy self joiny, co staje się nie do opanowania wraz z rozrostem drzewa.

WITH RECURSIVE rozwiązuje ten problem, umożliwiając zapytaniu odwoływanie się do własnego wyniku i przechodzenie przez kolejne poziomy aż do znalezienia wszystkich wierszy.

-- This only shows direct children of Electronics (level 1)
SELECT id, name
FROM   categories
WHERE  parent_id = 1;

Budowa WITH RECURSIVE

Rekurencyjne CTE ma dwie części rozdzielone przez UNION ALL:

1. Człon zakotwiczający — zwykły SELECT dostarczający wiersze początkowe.

2. Człon rekurencyjny — SELECT, który łączy CTE z nim samym i podczas każdej iteracji generuje kolejny poziom.

Silnik powtarza człon rekurencyjny, aż przestanie on zwracać wiersze.

WITH RECURSIVE cte AS (
  -- Anchor: starting rows
  SELECT ...
  UNION ALL
  -- Recursive: join cte to base table
  SELECT ... FROM base_table JOIN cte ON ...
)
SELECT * FROM cte;

Przechodzenie przez całe drzewo od korzenia

Należy rozpocząć od korzenia (gdzie parent_id IS NULL) i przejść w dół do każdego potomka. Człon rekurencyjny łączy każdy zgromadzony wiersz z categories na podstawie relacji rodzic–dziecko.

WITH RECURSIVE category_tree AS (
  -- Anchor: root nodes
  SELECT id, name, parent_id, 1 AS depth
  FROM   categories
  WHERE  parent_id IS NULL

  UNION ALL

  -- Recursive: children of current level
  SELECT c.id, c.name, c.parent_id, ct.depth + 1
  FROM   categories      c
  JOIN   category_tree   ct ON ct.id = c.parent_id
)
SELECT id, name, depth
FROM   category_tree
ORDER  BY depth, id;

Śledzenie ścieżki

Warto zapisywać pełną ścieżkę od korzenia do każdego węzła. Można zbudować tekst path, łącząc nazwy przodków w miarę schodzenia coraz głębiej.

Ułatwia to wyświetlanie ścieżek nawigacyjnych, takich jak Elektronika / Telefony / Smartfony.

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id,
         name AS path
  FROM   categories
  WHERE  parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id,
         ct.path || ' / ' || c.name
  FROM   categories    c
  JOIN   category_tree ct ON ct.id = c.parent_id
)
SELECT id, name, path
FROM   category_tree
ORDER  BY path;

Rozpoczynanie od określonego węzła

Nie trzeba rozpoczynać od korzenia. Zmieniając klauzulę WHERE członu zakotwiczającego, można przejść przez poddrzewo dowolnego węzła. Tutaj rozpoczynamy od Telefonów (id = 2) i pobieramy wszystkich jego potomków.

WITH RECURSIVE subtree AS (
  SELECT id, name, parent_id, 0 AS depth
  FROM   categories
  WHERE  id = 2          -- start at Phones

  UNION ALL

  SELECT c.id, c.name, c.parent_id, s.depth + 1
  FROM   categories c
  JOIN   subtree    s ON s.id = c.parent_id
)
SELECT id, name, depth
FROM   subtree
ORDER  BY depth, id;

Przechodzenie w górę: znajdowanie wszystkich przodków

Drzewo można również przeglądać w odwrotnym kierunku — od liścia w górę do korzenia. Wystarczy odwrócić połączenie, aby podążać w górę za parent_id, zamiast schodzić w dół. Jest to przydatne, gdy potrzebna jest pełna ścieżka nawigacyjna dla znanego węzła liściowego.

WITH RECURSIVE ancestors AS (
  SELECT id, name, parent_id
  FROM   categories
  WHERE  id = 4          -- start at Smartphones

  UNION ALL

  SELECT c.id, c.name, c.parent_id
  FROM   categories c
  JOIN   ancestors  a ON a.parent_id = c.id
)
SELECT id, name
FROM   ancestors
ORDER  BY id;

Dodawanie widoku z wcięciami

Częstym wzorcem interfejsu użytkownika jest wizualne wcinanie węzłów potomnych. Można użyć REPEAT (lub LPAD) wraz z kolumną depth, aby poprzedzić każdą nazwę spacjami i utworzyć tekstowy widok drzewa.

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, 0 AS depth
  FROM   categories
  WHERE  parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id, ct.depth + 1
  FROM   categories    c
  JOIN   category_tree ct ON ct.id = c.parent_id
)
SELECT
  REPEAT('    ', depth) || name AS indented_name,
  depth
FROM   category_tree
ORDER  BY path;

Ochrona przed nieskończonymi pętlami

Jeśli dane zawierają cykl (A jest rodzicem B, a B jest rodzicem A), rekurencja będzie wykonywana bez końca i spowoduje awarię. Można temu zapobiec, śledząc odwiedzone identyfikatory w tablicy i przerywając, gdy bieżący identyfikator już w niej występuje.

WITH RECURSIVE safe_tree AS (
  SELECT id, name, parent_id,
         ARRAY[id] AS visited
  FROM   categories
  WHERE  parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id,
         st.visited || c.id
  FROM   categories c
  JOIN   safe_tree  st ON st.id = c.parent_id
  WHERE  c.id <> ALL(st.visited)   -- stop if already seen
)
SELECT id, name FROM safe_tree;

Zliczanie potomków dla każdego węzła

Po uzyskaniu pełnego drzewa można je agregować. Tutaj zliczamy, ilu potomków ma każdy węzeł, grupując wiersze dzieci względem listy przodków. Jest to przydatne do wyświetlania liczby elementów obok nazw kategorii w menu nawigacyjnym.

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, id AS root_id
  FROM   categories
  WHERE  parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id, ct.root_id
  FROM   categories    c
  JOIN   category_tree ct ON ct.id = c.parent_id
)
SELECT
  root_id,
  COUNT(*) - 1 AS descendant_count
FROM   category_tree
GROUP  BY root_id
ORDER  BY root_id;

Szybki test

Sprawdź swoją wiedzę na temat rekurencyjnych zapytań dotyczących drzewa kategorii.

Odpowiedz w interaktywnej lekcji

Podsumowanie lekcji

W tej lekcji nauczyli się Państwo, jak przechodzić przez tabelę kategorii odwołującą się do samej siebie za pomocą WITH RECURSIVE.

Najważniejsze informacje:

- Człon zakotwiczający wybiera węzły początkowe (zwykle korzeń).

- Człon rekurencyjny łączy CTE z tabelą bazową, aby znaleźć kolejny poziom.

- Należy dodać kolumnę depth, aby śledzić liczbę poziomów dzielących każdy węzeł od korzenia.

- Należy zbudować tekst path, aby generować ścieżki nawigacyjne.

- Należy przechodzić w górę, odwrotnie podążając za parent_id, aby znaleźć wszystkich przodków.

- Należy użyć tablicy visited, aby zabezpieczyć się przed cyklami w nieuporządkowanych danych.

Bezpłatny start

Ucz się SQL dzięki korepetycjom AI — za darmo

Pisz i uruchamiaj kod w przeglądarce, otrzymuj natychmiastową pomoc od korepetytora AI dostępnego 24/7 i kontynuuj naukę w sieci lub w aplikacji.

Kursy: 46
Lekcje: 183

Zacznij naukę bezpłatnie Wyświetl pełny kurs

Często zadawane pytania

Czy lekcja „Przechodzenie drzewa kategorii” jest bezpłatna?

Tak — pełny tekst „Przechodzenie drzewa kategorii” 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 Academy, przejdź na CoddyKit PRO. Kurs SQL Academy zawiera 4 lekcji w sumie.

Co nauczysz się w „Przechodzenie drzewa kategorii”?

W pełni rozwijaj drzewa nadrzędny–podrzędny Ćwiczysz SQL Academy 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 Academy?

Nie wymagamy żadnego doświadczenia. SQL Academy 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 drzewa kategorii”?

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 Academy?

Tak. Każda lekcja SQL Academy 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

← Powrót do SQL Academy