Spłaszczenie Hierarchii Listy Pomocniczej Do Listy Wszystkich Ścieżek
Mam tabelę, która przechowuje hierarchiczne informacje za pomocą modelu listy przyległości. (używa klucza SELF referential - przykład poniżej. Ta tabela może wyglądać znajomo):
category_id name parent
----------- -------------------- -----------
1 ELECTRONICS NULL
2 TELEVISIONS 1
3 TUBE 2
4 LCD 2
5 PLASMA 2
6 PORTABLE ELECTRONICS 1
7 MP3 PLAYERS 6
8 FLASH 7
9 CD PLAYERS 6
10 2 WAY RADIOS 6
Jaka jest najlepsza metoda na "spłaszczenie" powyższych danych w coś takiego?
category_id lvl1 lvl2 lvl3 lvl4
----------- ----------- ----------- ----------- -----------
1 1 NULL NULL NULL
2 1 2 NULL NULL
6 1 6 NULL NULL
3 1 2 3 NULL
4 1 2 4 NULL
5 1 2 5 NULL
7 1 6 7 NULL
9 1 6 9 NULL
10 1 6 10 NULL
8 1 6 7 8
Każdy wiersz jest jedną" ścieżką " przez hierarchię, z wyjątkiem tego, że jest wiersz dla każdego węzła (nie tylko dla każdego węzła liścia ). Kolumna category_id reprezentuje bieżący node i kolumny " lvl " są jego przodkami. Wartość dla bieżącego węzła musi również znajdować się w najdalszej prawej kolumnie lvl. Wartość w kolumnie lvl1 zawsze będzie reprezentować węzeł główny, wartości w lvl2 zawsze będą reprezentować bezpośrednie potomstwo lvl1, i tak dalej.
Jeśli to możliwe, metoda generowania tego wyniku będzie w SQL i będzie działać dla N-warstwowych hierarchii.
4 answers
Wykonywanie wielopoziomowych zapytań na prostej liście przyległości zawsze wiąże się z samo-lewymi połączeniami. Łatwo jest utworzyć tabelę wyrównaną do prawej:
SELECT category.category_id,
ancestor4.category_id AS lvl4,
ancestor3.category_id AS lvl3,
ancestor2.category_id AS lvl2,
ancestor1.category_id AS lvl1
FROM categories AS category
LEFT JOIN categories AS ancestor1 ON ancestor1.category_id=category.category_id
LEFT JOIN categories AS ancestor2 ON ancestor2.category_id=ancestor1.parent
LEFT JOIN categories AS ancestor3 ON ancestor3.category_id=ancestor2.parent
LEFT JOIN categories AS ancestor4 ON ancestor4.category_id=ancestor3.parent;
Do left-align to jak twój przykład jest nieco trudniejsze. Przychodzi mi to na myśl:
SELECT category.category_id,
ancestor1.category_id AS lvl1,
ancestor2.category_id AS lvl2,
ancestor3.category_id AS lvl3,
ancestor4.category_id AS lvl4
FROM categories AS category
LEFT JOIN categories AS ancestor1 ON ancestor1.parent IS NULL
LEFT JOIN categories AS ancestor2 ON ancestor1.category_id<>category.category_id AND ancestor2.parent=ancestor1.category_id
LEFT JOIN categories AS ancestor3 ON ancestor2.category_id<>category.category_id AND ancestor3.parent=ancestor2.category_id
LEFT JOIN categories AS ancestor4 ON ancestor3.category_id<>category.category_id AND ancestor4.parent=ancestor3.category_id
WHERE
ancestor1.category_id=category.category_id OR
ancestor2.category_id=category.category_id OR
ancestor3.category_id=category.category_id OR
ancestor4.category_id=category.category_id;
Będzie działać dla n-warstwowych hierarchii.
Przepraszamy, zapytania o dowolne głębokości nie są możliwe w modelu adjacency-list. Jeśli często wykonujesz tego typu zapytania, powinieneś zmienić schemat na jeden z innych modeli przechowuje informacje hierarchiczne: pełną relację przylegającą( przechowuje wszystkie relacje przodek-potomek), zmaterializowaną ścieżkę lub zagnieżdżone zestawy.
Jeśli Kategorie nie poruszają się zbyt często (co zwykle ma miejsce w przypadku sklepu, takiego jak twój przykład), skłaniałbym się ku zagnieżdżonym zestawom.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-10-23 13:57:23
Jak wspomniano, SQL nie ma czystego sposobu na implementację tabel z dynamicznie zmieniającą się liczbą kolumn. Jedyne dwa rozwiązania, z których korzystałem wcześniej, to: 1. Stała liczba Samorozłączeń, dająca stałą liczbę kolumn (jak na BobInce) 2. Generowanie wyników w postaci ciągu znaków w jednej kolumnie
Drugi brzmi groteskowo na początku; przechowywanie identyfikatorów jako ciąg znaków?! Ale kiedy wyjście jest sformatowane jako XML lub coś takiego, ludzie nie mają nic przeciwko tak bardzo.
Podobnie, jest to bardzo niewielkie użycie, jeśli chcesz dołączyć do wyników w SQL. Jeśli wynik ma być dostarczony do aplikacji, może być bardzo odpowiedni. Osobiście jednak wolę zrobić spłaszczenie w aplikacji, a nie SQL
Utknąłem tutaj na 10 calowym ekranie bez dostępu do SQL, więc nie mogę podać testowanego kodu, ale podstawową metodą byłoby wykorzystanie rekurencji w jakiś sposób;
- Funkcja skalarna rekurencyjna może to zrobić
- MS SQL może to zrobić za pomocą rekurencyjnego z statement (more efficient)
Funkcja skalarna (coś w rodzaju):
CREATE FUNCTION getGraphWalk(@child_id INT)
RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE @graph VARCHAR(4000)
-- This step assumes each child only has one parent
SELECT
@graph = dbo.getGraphWalk(parent_id)
FROM
mapping_table
WHERE
category_id = @child_id
AND parent_id IS NOT NULL
IF (@graph IS NULL)
SET @graph = CAST(@child_id AS VARCHAR(16))
ELSE
SET @graph = @graph + ',' + CAST(@child_id AS VARCHAR(16))
RETURN @graph
END
SELECT
category_id AS [category_id],
dbo.getGraphWalk(category_id) AS [graph_path]
FROM
mapping_table
ORDER BY
category_id
Dawno nie używałem rekurencyjnego z, ale podam składnię, mimo że nie mam tu SQL ' a do testowania czegokolwiek:)
Rekurencyjny z
WITH
result (
category_id,
graph_path
)
AS
(
SELECT
category_id,
CAST(category_id AS VARCHAR(4000))
FROM
mapping_table
WHERE
parent_id IS NULL
UNION ALL
SELECT
mapping_table.category_id,
CAST(result.graph_path + ',' + CAST(mapping_table.category_id AS VARCHAR(16)) AS VARCHAR(4000))
FROM
result
INNER JOIN
mapping_table
ON result.category_id = mapping_table.parent_id
)
SELECT
*
FROM
result
ORDER BY
category_id
EDIT-OUTPUT dla obu jest taki sam:
1 '1'
2 '1,2'
3 '1,2,3'
4 '1,2,4'
5 '1,2,5'
6 '1,6'
7 '1,6,7'
8 '1,6,7,8'
9 '1,6,9'
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-04-21 19:44:45
Przemierzanie drzewa o dowolnej głębokości zazwyczaj wymaga rekurencyjnego kodu proceduralnego, chyba że korzystasz ze specjalnych funkcji niektórych DBMS.
W Oracle klauzula CONNECT BY pozwoli Ci przejść głębiej drzewo w pierwszej kolejności, jeśli użyjesz listy przyległości, tak jak to zrobiłeś tutaj.
Jeśli używasz zagnieżdżonych zestawów, lewy numer sekwencji zapewni Ci kolejność odwiedzania węzłów.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-04-19 02:57:08
W rzeczywistości można to zrobić za pomocą dynamicznego SQL w ramach procedury stores. Następnie stają się ograniczone do tego, co można zrobić sith procedury składowanej. Oczywiście staje się wyzwaniem, aby EXEC wyniki do tymczasowej tabeli nie wiedząc, ile kolumn się spodziewać. Jeśli jednak celem jest wyjście na stronę internetową lub inny interfejs użytkownika, może to być warte wysiłku...
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-07-22 00:18:12