Artykuł
26.04.2019

Akademia SQL - Część 17: Dane hierarchiczne w SQL Server 2008 – informacje podstawowe

Autor: Damian Widera

Opublikowano: 30 października 2009

Zawartość strony

	Laboratorium
	Referencje

Ze strukturami hierarchicznymi mamy do czynienia na co dzień i serwer baz danych musi umieć efektywnie i wydajnie zapisać i przetwarzać takie informacje. Generalnie rzecz biorąc, związki hierarchiczne to takie, w których jeden element jest nadrzędny w stosunku do drugiego – podrzędnego. Związek taki można określić jako relację rodzic – dziecko (ang. parent – child relationship).

Elementy hierarchii zwane węzłami (ang. node) tworzą „odwrócone” drzewo. Zawiera ono wiele węzłów, ale w jego strukturze są dwa specyficzne.Znajdujący się na górze drzewa, czyli nieposiadający „rodzica”, nazywany jest korzeniem (ang. root), usytuowane u dołu drzewa (w jego gałęziach) nazywane są liśćmi (ang. leaf), a pozostałe –elementami drzewa.

Na rysunku poniżej element ken0 to korzeń, wszystkie elementy oprócz diane1 oraz gigi0 to węzły (elementy) , natomiast rob0, gail0 oraz jossef0 to liście. Diane1 oraz gigi0 nie są w hierarchii (nie są połączone) z innymi elementami:

Rysunek 1: Elementy drzewa.

Przykłady danych hierarchicznych:

struktura organizacyjna firmy,
system katalogów i plików,
struktura projektu informatycznego, np.: projekt, grupa zadań, zadania.

We wcześniejszych wersjach serwera dane hierarchiczne można było przechowywać na kilka sposobów, ale najpopularniejszy były trzy:

utworzenie w tabeli nowej kolumny, której wartość w rekordzie wskazuje na rekord nadrzędny w tej samej lub innej tabeli,
utworzenie w tabeli nowej kolumny, w której dla każdego rekordu przechowywana jest ścieżka określająca pozycję elementu w hierarchii,
przechowywanie danych hierarchicznych w formacie XML zapisanych w osobnej kolumnie.

SQL Server 2008 posiada nowy typ danych do składowania i obsługi danych hierarchicznych. Wspomniany typ to HIERARCHYID, który można opisać w następujący sposób:

a) przechowuje dane w postaci binarnej,

b) jest typem CLR i udostępnia wiele metod wspomagających pracę programistów,

c) umożliwia zapisywanie pozycji węzła w tzw. kanonicznej postaci tekstowej.

W kanonicznej postaci tekstowej zapisuje się pozycję węzła za pomocą ukośników (/) oraz liczb. Ukośnik oddziela poziomy hierarchii, a liczby całkowite oddzielone kropkami decydują, którą pozycję na danym poziomie zajmuje węzeł.

Porównywanie dwóch wartości typu HIERARCHYID może odbywać się zgodnie z zasadą „najpierw w pionie” lub „najpierw w poziomie”.

Stosowanie zasady „najpierw w pionie” powoduje, że węzły w pierwszej gałęzi (przykładowo /1/.../) mają mniejszą wartość, niż znajdujące się w drugiej gałęzi (przykładowo /2/.../). SQL Server 2008 stosuje tę zasadę domyślnie.

Natomiast przyjęcie zasady „najpierw w poziomie” powoduje, że węzeł na wyższym poziomie ma mniejszą wartość, niż znajdujący się na poziomie niższym, niezależnie od położenia obu węzłów w gałęziach hierarchii. W celu uzyskania porządku sortowania zgodnego z tą zasadą trzeba do badania poziomu węzła użyć metody GetLevel i w pierwszej kolejności sortować rekordy według poziomu, a dopiero w dalszej kolejności według wartości z kolumny typu HIERARCHYID.

Laboratorium

Celem laboratorium jest praktyczne zapoznanie się z typem danych HIERARCHYID. W pierwszym zadaniu zostanie pokazane, w jaki sposób można pracować z danymi hierarchicznymi, korzystając z metod dostępnych w starszej wersji systemu. W drugim zadaniu natomiast podamy sposoby korzystania z metod oferowanych przez typ HIERARCHYID. Do wykonania obydwu zadań potrzebna będzie baza danych AdventureWorks.

Zadanie 1.

1. Uruchom konsolę SQL Server Management Studio (SSMS) i połącz się z wybranym serwerem SQL:

Rysunek 2: Konsola SQL Server Management Studio (SSMS).

2. Otwórz nowe okno zapytania, wybierając opcję New Query z głównego menu.

3. Wpisz i uruchom poniższe zapytanie, które wyświetli informacje o pracownikach firmy AdventureWorks. Każdy z pracowników posiada unikalny identyfikator zapisany w kolumnie EmployeeID oraz identyfikator przełożonego (w kolumnie ManagerID). Osoba, która w kolumnie ManagerID ma zapisaną wartość NULL, jest właścicielem firmy, czyli nie posiada przełożonego.

use AdventureWorks

go

SELECT EmployeeID,ManagerID,LoginID

FROM HumanResources.Employee

4. Kolejny fragment kodu tworzy hierarchię pracowników, korzystając z Common Table Expressions:

WITH CTE_Employee AS

(

    SELECT EmployeeID,ManagerID,LoginID, 0 as POZIOM

    FROM HumanResources.Employee 

    WHERE ManagerID IS NULL

    UNION ALL

    SELECT HR.EmployeeID,HR.ManagerID,HR.LoginID, POZIOM+1

    FROM HumanResources.Employee HR JOIN CTE_Employee C

    ON HR.ManagerID = C.EmployeeID

)

SELECT * FROM CTE_Employee

ORDER BY POZIOM,ManagerID,EmployeeID

5. Zmienna POZIOM określa, na jakim poziomie drzewa znajduje się aktualny pracownik. W tym przypadku drzewo ma pięć poziomów. Na pierwszym jest właściciel (ken0), jego można uznać za korzeń drzewa. Na ostatnim poziomie znajduje się 190 pracowników, którzy są liśćmi. Sprawdzić to można, wykonując następujące zapytanie:

WITH CTE_Employee AS

(

    SELECT EmployeeID,ManagerID,LoginID, 0 as POZIOM

    FROM HumanResources.Employee 

    WHERE ManagerID IS NULL

    UNION ALL

    SELECT HR.EmployeeID,HR.ManagerID,HR.LoginID, POZIOM+1

    FROM HumanResources.Employee HR JOIN CTE_Employee C

    ON HR.ManagerID = C.EmployeeID

)

SELECT COUNT(EmployeeID) as [Liczba pracownikow],POZIOM FROM CTE_Employee

GROUP BY POZIOM

ORDER BY POZIOM

Liczba pracownikow POZIOM

------------------ -----------

1                  0

6                  1

27                 2

66                 3

190                4



(5 row(s) affected)

6. Omawiane rozwiązanie charakteryzuje się zwięzłym zapisem i czytelnością kodu. Brakuje w nim jednak informacji o ścieżce od każdego pracownika do właściciela firmy. Innymi słowy: pracownik adventure-works\ben0 o identyfikatorze 261 ma przełożonego o numerze 274 (adventure-works\sheela0), który z kolei jest podwładnym pracownika o numerze 71 (adventure-works\wendy0). Ten ma przełożonego o numerze 140 (adventure-works\laura1), który jest bezpośrednim podwładnym właściciela firmy. Istotna byłaby więc informacja zapisana przy pierwszym z wymienionych pracowników: 1/140/71/274/261.

7. Poniższe zapytanie prezentuje, w jaki sposób dynamicznie zbudować ścieżkę, o której mowa w poprzednim punkcie:

DECLARE @root AS INT;

SET @root = 1;



WITH CTE_Employee

AS

(

  SELECT EmployeeID, ManagerID,LoginID, 0 AS poziom,

    CAST('.' + CAST(EmployeeID AS VARCHAR(10)) + '.'

         AS VARCHAR(MAX)) AS sciezka

  FROM HumanResources.Employee 

  WHERE ManagerID IS NULL



  UNION ALL



  SELECT HR.EmployeeID, HR.ManagerID,HR.LoginID, poziom + 1,

    CAST(C.sciezka + CAST(HR.EmployeeID AS VARCHAR(10)) + '.'

         AS VARCHAR(MAX)) AS sciezka

  FROM HumanResources.Employee  HR JOIN CTE_Employee AS C

    ON HR.ManagerID = C.EmployeeID

)

SELECT *

FROM CTE_Employee

ORDER BY POZIOM,ManagerID,EmployeeID,sciezka;

8. Dla pracownika adventure-works\ben0 o identyfikatorze 261 otrzymujemy następujący wynik:

EmployeeID  ManagerID   LoginID               poziom      sciezka

----------- ----------- ------------------    ----------- -------------------

261         274         adventure-works\ben0  4           .109.140.71.274.261.

Zadanie 2.

1. Podobne operacje dotyczące hierarchii pracowników w firmie wykonane zostaną za pomocą typu danych HIERARCHYID.
2. Otwórz nowe okno zapytania, wybierając opcję New Query z głównego menu.

3. Utwórz tabelę dbo.Organizacja, korzystając z podanego niżej kodu. Informacje o hierarchii pracowników będą w niej przechowywane w polu EmployeeID typu HIERARCHYID:

use AdventureWorks

GO

CREATE TABLE dbo.Organizacja

(

    Pracownik hierarchyid NOT NULL,

    Nazwa nvarchar(50) NOT NULL

)

4. Do tabeli zostanie dodana kolumna Poziom, w której przechowywane będą informacje o poziomie pracownika w hierarchii:

ALTER TABLE dbo.Organizacja 

ADD Poziom AS Pracownik.GetLevel()

5. Dodaj klucz główny do tabeli dbo.Organizacja, który utworzony jest na polu Pracownika. Operacja ta jest konieczna, ponieważ w następnym kroku będzie tworzona kolumna wyliczeniowa Przelozony:

ALTER TABLE dbo.Organizacja ADD CONSTRAINT

    PK_Organizacja PRIMARY KEY

    (

        Pracownik

    )

6. Utwórz kolumnę wyliczeniową Przelozony, która będzie kolumną wyliczeniową trwałą (jej wartość będzie przechowywana w tabeli). Wartość w tej kolumnie jest pobierana z metody GetAncestor(), która pozwala obliczyć osobę nadrzędną w hierarchii i jako parametr przyjmuje liczbę poziomów, o którą należy się przesunąć „w górę”, żeby uzyskać wynik:

ALTER TABLE dbo.Organizacja

ADD Przelozony AS Pracownik.GetAncestor(1) PERSISTED

    REFERENCES dbo.Organizacja(Pracownik)

7. Spójrz na poniższe zapytanie. Jest ono oparte na

WITH CTE_Hierarchia 

AS

(

    SELECT EmployeeID, LoginID, hierarchyid::GetRoot() AS Pracownik

    FROM HumanResources.Employee

    WHERE ManagerID IS NULL

    UNION ALL

    SELECT HR.EmployeeID, HR.LoginID, C.Pracownik.GetDescendant(null, null)

    FROM HumanResources.Employee AS HR

        INNER JOIN CTE_Hierarchia AS C

            ON HR.ManagerID= C.EmployeeID

)

Select *

From CTE_Hierarchia



EmployeeID  LoginID                       Pracownik

----------- -----------------------------------------

109         adventure-works\ken0          0x

6           adventure-works\david0        0x58

12          adventure-works\terri0        0x58

42          adventure-works\jean0         0x58

140         adventure-works\laura1        0x58

148         adventure-works\james1        0x58

273         adventure-works\brian3        0x58

268         adventure-works\stephen0      0x5AC0

284         adventure-works\amy0          0x5AC0

288         adventure-works\syed0         0x5AC0

290         adventure-works\lynn0         0x5AD6



------------------------------------------------

8. Dane w kolumnie Pracownik nie są unikalne i nie mogą posłużyć do zbudowania klucza głównego w tabeli dbo.Organizacja, w której ten klucz miał być oparty tylko na kolumnie Pracownik. Można jednak zmienić sposób wyznaczania kolumny Pracownik, dodając do niej informację o kolumnie EmployeeID, która na pewno zawiera dane unikalne:

WITH CTE_Hierarchia 

AS

(

    SELECT EmployeeID, LoginID, hierarchyid::GetRoot() AS Node

    FROM HumanResources.Employee

    WHERE ManagerID IS NULL

    UNION ALL

    SELECT HR.EmployeeID, HR.LoginID, hierarchyId::Parse(c.Node.ToString() + Cast(HR.EmployeeID as varchar(5)) + '/')

    FROM HumanResources.Employee AS HR

        INNER JOIN CTE_Hierarchia AS C

            ON HR.ManagerID= C.EmployeeID

)

INSERT INTO dbo.Organizacja(Pracownik,Nazwa)

Select Node, LoginID 

From CTE_Hierarchia

9. Sprawdźmy zatem uzyskane wyniki. Zwróć uwagę, że dane typu HIERARCHYID są rzeczywiście w zapisane w formacie binarnym – kolumny Pracownik i Przelozony. Jeśli chcesz zobaczyć, jak wygląda taka informacja w formacie tekstowym, wywołaj na odpowiedniej kolumnie metodę ToString():

SELECT Pracownik,Nazwa,Poziom,Przelozony,Pracownik.ToString() as Sciezka

FROM dbo.Organizacja

ORDER BY Poziom

Rysunek 3: Wynik działania zapytania.

10. Jeśli chciałbyś się dowiedzieć, kto jest szefem firmy, możesz wykonać zapytanie, w którym skorzystasz ze statycznej metody HIERARCHYID::GetRoot():

SELECT *

FROM dbo.Organizacja

WHERE Pracownik = HIERARCHYID::GetRoot();

Rysunek 4: Wynik działania zapytania.

11. Jeśli chcesz wyświetlić informacje o tym, kto jest przełożonym danego pracownika, musisz skorzystać z metody IsDescendantOf():

Declare @Pracownik As HierarchyId

Select @Pracownik = Pracownik From dbo.Organizacja

Where Nazwa = 'adventure-works\grant0'



Select *

From dbo.Organizacja

Where @Pracownik.IsDescendantOf(Pracownik) = 1

Rysunek 5: Wynik działania zapytania.

12. Typ danych HIERARCHYID umożliwia wykonywanie w zasadzie wszystkich operacji, które są potrzebne podczas manipulowania strukturami drzewiastymi. Nie są to jednak operacje proste, wymagają doświadczenia i dlatego nie zostaną zaprezentowane w tej lekcji. W literaturze pokazano miejsca, gdzie można odnaleźć praktyczne przykłady zastosowań typu danych HIERARCHYID.

Do początku strony

Referencje

Dodatkowe informacje na temat obiektów o zróżnicowanych atrybutach można znaleźć w następujących miejscach:

[1] HierarchyID, czyli drzewa po nowemu – artykuł Marcina Goła (SQL Server MVP) na polskich stronach TechNet

[2] „Serwer SQL 2008. Administracja i programowanie”, Wydawnictwo Helion SA

[3] Artykuł na portalu www.sqlservercentral.com

Damian Widera, Project Manager & Team Lead (MCT, MCITP – DBA, MCSD.NET)
Od 8 lat zajmuje się projektowaniem, tworzeniem i wdrażaniem aplikacji wykorzystujących platformę .NET, SQL Server oraz Oracle. Obecnie pracuje jako project manager dla LGBS Polska. Pracował także jako trener, programista, administrator baz danych, twórca domumentacji oraz analityk biznesowy. Aktywnie współpracuje z polskim oddziałem Microsoft publikując atykuły, webcasty oraz porady z zakresu SQL Server na stronach TechNet. Jest współautorem książki „Serwer SQL 2008. Administracja i programowanie”.

Speaker na wielu konferencjach, m.in. Microsoft Heroes Happen Here, C2C, European PASS Conference, Microsoft Technology Summit, Energy Launch, TechED. Od 2004 r. posiada certyfikaty firmy Microsoft: MCT, MCITP–DBA oraz MCSD.NET. Jest współtwórcą oraz liderem jednej z najwiekszych grup pasjonatów SQL Server w Polsce – Śląskiej Regionalnej Grupy Microsoft (PLSSUG Katowice). Od listopada 2008 jest prezesem Polish SQL Server Users Group (PLSSUG) w Polsce. W styczniu 2009 nagrodzony tytułem MVP w kategorii SQL Server.