[SQL] Niveaus in Tree berekenen.

[4of9]

Hallo,

Ik probeer een in een database opgeslagen structuur, de niveaus te achterhalen.

ik heb bijvoorbeeld een tabel met data in de vorm van:

code:

id  parent_id
1   null
2   1
3   1
5   2
10  5

Dit moet omgezet worden in een tabel met een extra veld niveau waarbij het niveau aangeeft hoeveel parents een node heeft.

Is dit op te lossen met SQL?

Ik heb al een SP geschreven die door alle records van de eerste tabel heen loopt met een cursor, maar ik krijg het niet voor elkaar om het aantal niveau te tellen.

ik hoop dat jullie mij op weg kunnen helpen.

alvast bedankt.

[mithras]

Moet het in SQL? Je kan in php dan een recursieve routine opstellen die een teller bijhoud. Je maakt een query die zoekt op een bepaalde parent bestaat. Zo ja, roep je dezelde formule aan een verhoog je de teller met 1.

Je moet dan wel nog kijken naar id's met dezelfde parent. Een algoritme opstellen die dan de "jongste" parent bekijkt moet er dan nog tussen, en dat wordt denk ik lastiger.

4of9 schreef op maandag 08 mei 2006 @ 15:28:
Het moet helaas wel in SQL (TSQL om precies te zijn, ik gebruik MSSQL)

Dan kan ik je niet helpen

[ Voor 23% gewijzigd door mithras op 08-05-2006 15:32 ]

[4of9]

Het moet helaas wel in SQL (TSQL om precies te zijn, ik gebruik MSSQL)

[RobIII]

4of9 schreef op maandag 08 mei 2006 @ 15:28:
Het moet helaas wel in SQL (TSQL om precies te zijn, ik gebruik MSSQL)

Maak een user-defined function die de diepte teruggeeft?

Even uit de blote bol:

SQL:

CREATE FUNCTION [dbo].[GetDepthForMyNode] 
(
    @intID as int
)
RETURNS Int
AS
BEGIN
    Declare @intDepth as int
    Set @intDepth = 0

    While not (@intID is null)
    BEGIN
        Select @intID = parent_id
        from mytable
        where id = @intID 

        Set @intDepth = @intDepth + 1
    END

    Return @intDepth 
END

Daarna kun je met een

SQL:

Select id, dbo.GetDepthForMyNode(id) as diepte from mytable

de diepte voor die node ophalen.

Je begrijpt dat dit wel een (redelijk/behoorlijk) dure operatie is op veel records of veel "diepte".

Janoz schreef op maandag 08 mei 2006 @ 15:33:
Als je de volgende dingen hebt:

* Een conditionele lus
* Het achterhalen van het aantal geupdate velden

Zou je het volgende kunnen doen:

Zorg dat alle niveau velden NULL zijn. en zet het niveau van de parrent op 0. Draai een query waarbij je twee niveau's joint en filter deze op parent niveau != null en eigen niveau null en ken aan het eigen niveau dan parentniveau + 1 toe. Dit doe je net zo lang tot het aantal geupdate rijen 0 is.

Origineel, maar als nodes van parent wisselen zou dit wel eens gevaarlijk kunnen zijn (vergeten niveau te updaten enzo, maar daar heb je SP's voor ). Daarentegen is jouw oplossing waarschijnlijk wel sneller als je vaak het niveau moet hebben van een node.

TallManNL schreef op maandag 08 mei 2006 @ 15:36:
Ik heb overigens niet gecheckt of stored procs recursie mogen toepassen.

Bij mijn weten kan dat niet, of je moet met EXEC gaan rommelen enzo... Weet overigens ook niet of dat bij functions wel kan... dat zou mijn oplossing nog iets kunnen versimpelen wellicht.

[ Voor 122% gewijzigd door RobIII op 08-05-2006 15:49 ]

[Janoz]

Als je de volgende dingen hebt:

* Een conditionele lus
* Het achterhalen van het aantal geupdate velden

Zou je het volgende kunnen doen:

Zorg dat alle niveau velden NULL zijn. en zet het niveau van de parrent op 0. Draai een query waarbij je twee niveau's joint en filter deze op parent niveau != null en eigen niveau null en ken aan het eigen niveau dan parentniveau + 1 toe. Dit doe je net zo lang tot het aantal geupdate rijen 0 is.

[TallManNL]

stored proc die je aanroept met parent nivo en alle childs daarvan het meegegeven childnivo geeft. Per child nog eens dezelfde stored proc aanroepen met nivo+1 als dat nivo.

Ik heb overigens niet gecheckt of stored procs recursie mogen toepassen.

[4of9]

Die userdefined function werkt prima.
Ik moet wel even afvangen dat als de parent_id null is dat dan het niveau = 0

code:

Alter Function GetNodeDepth 
(
    @intID as int
)
Returns Int
     
As
Begin
    Declare @intDepth int
    
    Set @intDepth = 0
   
    While not (@intID is null)
    Begin
        SELECT @intID = parent_id --, parent_id
        FROM test_boom_old
        WHERE id = @intID 

    If(NOT @intId IS NULL)
    Begin
      Set @intDepth = @intDepth + 1
    End
    End

    Return @intDepth 
End

En dan met de volgende update functie wordt de tweede tabel gevuld met niveau's

code:

update test_boom set niveau = dbo.GetNodeDepth(t.id)
from test_boom t,test_boom_old tbo
where t.id = tbo.id

ik ga de boel nog even testen!

super bedankt, ik vreesde al voor recursieve functies etc...

[RobIII]

4of9 schreef op maandag 08 mei 2006 @ 15:55:
Die userdefined function werkt prima.
En dan met de volgende update functie wordt de tweede tabel gevuld met niveau's

code:

update test_boom set niveau = dbo.GetNodeDepth(t.id)
from test_boom t,test_boom_old tbo
where t.id = tbo.id

ik ga de boel nog even testen!

super bedankt, ik vreesde al voor recursieve functies etc...

Als je die diepte maar "sporadisch" nodig hebt ben je gek als je deze "hard" in de tabel gaat opslaan. Je kunt deze functie in iedere query gebruiken wanneer je 'm nodig hebt en dan is 't altijd kloppend. Heb je de diepte veel en vaak nodig dan kun je 'm inderdaad beter bij de node opslaan, maar vergeet 'm dan niet bij te werken op het moment dat de node van parent wijzigt. Erger: Wat als de parent van parent wijzigt? Dan moet je dus ook alle childnodes bijwerken. Al met al een boel administratieve taken die je dan dus NIET moet vergeten, wil je je DB consistent houden.

[Janoz]

Als het je om efficientie gaat, en je zet het sowieso in een nieuwe tabel, dan kun je beter mijn algoritme gebruiken ipv de getNodeDepth functie. In mijn functie worden er (3 + max diepte) queries uitgevoerd terwijl de andere oplossing (aantal elementen * gemiddelde diepte) queries uitvoert. Dat laatste aantal is significant hoger dan het eerste.

Betreft het echter een eenmalige omzetting, dan zou ik me er niet zo druk over maken ..

[RobIII]

Janoz schreef op maandag 08 mei 2006 @ 17:11:
Als het je om efficientie gaat, en je zet het sowieso in een nieuwe tabel, dan kun je beter mijn algoritme gebruiken ipv de getNodeDepth functie. In mijn functie worden er (3 + max diepte) queries uitgevoerd terwijl de andere oplossing (aantal elementen * gemiddelde diepte) queries uitvoert. Dat laatste aantal is significant hoger dan het eerste.

Betreft het echter een eenmalige omzetting, dan zou ik me er niet zo druk over maken ..

Uiteraard eensch is. Ik zei het misschien wat krom