arafat
Goto Top

SQL - Durchsuchen einer Baumstruktur

Hallo Forum,

mal wiede eine Frage bei der ich nicht so recht weiter weiss.

Folgendes Problem in einer SQL Datenbank:

Die Daten liegen folgendermasen in der Bank:

Felder:
ID
VaterID
Name
Anzahl


1, NULL, AAAA, NULL
2, 1, BBB, NULL
3, 2, hziui, 887
4, 2, htzs, 776
5, 1, hdtds, NULL
6, 5, jkhasodu, 987958
7, 5, kjoiusdad, 98798729344

Die Daten Daten können also sowohl Knoten als auch Endpunkt sein.
Ich denke ich muss hier mit einer while Schleife arbeiten - die Frage ist wie könnte diese Ausehen?

Danke und Gruß Markus

Content-ID: 148782

Url: https://administrator.de/contentid/148782

Ausgedruckt am: 22.11.2024 um 21:11 Uhr

it-frosch
it-frosch 11.08.2010 um 13:17:26 Uhr
Goto Top
Hallo Markus,

Ich denke ich muss hier mit einer while Schleife arbeiten - die Frage ist wie könnte diese Ausehen?
Die Frage ist, was willst du auswerten? face-smile

grüße vom it-frosch

PS: Es wäre auch recht hilfreich wenn du uns schreibst was das eigentlich für Daten sind.
Netzfetzer
Netzfetzer 11.08.2010 um 14:27:00 Uhr
Goto Top
Hi,

falls das Ziel darin besteht, die Struktur hierarchisch auzugliedern, könnte folgendes Hilfreich sein:

SELECT ID, VaterID
FROM <tabelle>
CONNECT BY PRIOR ID = VaterID
START WITH ID = 1

(nicht getestet aber aus einem Beispiel übernommen und umgeschrieben).

Interessant zum Thema CONNECT BY PRIOR / Treewalking mit SQL ist auch der Link:
http://www.akadia.com/services/ora_treewalking_with_sql.html

Hoffe das Hilft weiter.

Grüße Netzfetzer
Arafat
Arafat 11.08.2010 um 14:30:57 Uhr
Goto Top
Hallo,

Auswerten möchte ich zB die Höchste Zahl im Feld Anzahl.

Was für DAten es sind ist schwer zu beschreiben.

Im Grunde liegen hier Listen und Unterlisten vor, die in einer Bank zusammengefasst sind. Die kann sowohl ein Artikel als auch eine "Liste" darstellen
Also:

Liste 1 ohne VaterID
Liste 2 mit VaterID 1
Liste 3 mit VaterID 1
Liste 4 ist ein Artikel mit VaterID 2 und Anzahl 8768653
Liste 5 ist ein Artikel ohne VaterID und ANzahl 9873946

Die Bank kann in der Art weitergeführt sein. Ich möchte im Grunde den "Artikel" mit der höchsten Anzahl finden der zur Gesamtliste 1 gehört.
In der Bank können aber auch Artikel liegen die nichts mit der Liste 1 zu tun haben, diese möchte ich natürlcih nicht auswerten.

Herzlichen DAnk.

Gruß Markus
Netzfetzer
Netzfetzer 11.08.2010 um 14:39:38 Uhr
Goto Top
Hi,

evtl. ist das ein Lösungsansatz:

SELECT ID, count(VaterID)
FROM <tabelle>
CONNECT BY PRIOR ID = VaterID
START WITH ID = 1
group by ID
Arafat
Arafat 11.08.2010 um 14:43:20 Uhr
Goto Top
Hallo,

ich glaube der Link ist genau das was ich gesucht habe!
Der Suchbegriff wäre Treewalking gewesen!
DANKE!

Ich werde es mal auf meinen Fall anpassen, anwenden und schauen ob es klappt.

Vorab DANKE!

Gruß Markus
it-frosch
it-frosch 11.08.2010 um 14:45:13 Uhr
Goto Top
Hallo Markus,

wenn ich dich richtig verstanden habe willst du die Artikel mit der höchsten Anzahl herausfinden.
Es kann sein das diese Artikel Kinder eines Vaterartikels sind (Mitglied einer Artikelliste).

Ich habe allerdings nicht verstanden ob du nur die reinen Artikel haben willst oder auch die Artikel die Mitglied einer Artikelliste sind.

Schick doch mal jeweils einen Datensatz für jedes Beispiel damit man den Aufbau sehen kann.

it-frosch
dog
dog 11.08.2010 um 23:06:37 Uhr
Goto Top
Für einfache Baumstrukturen empfiehlt sich die Modified Preorder Tree Traversal Technik.
Arafat
Arafat 12.08.2010 um 09:14:56 Uhr
Goto Top
Hallo,

das Problem ist mir dann doch noch zu komplex und ich bin noch nicht so ganz im sql drin.

ich habe 2 Tabellen:

a.ID
a.ID2

b.ID
b.Tage

InTablle a.ID steht eine ID in a.ID2 stehen die "Söhnen"
Die Söhne stehen Wiederum in a.ID mit eigenen "Söhnen" in a.ID2

In b.ID stehen alle aus a.ID, aber nur bei den "Söhnen" die keine weiteren "Söhne" haben stehen Tage.
( In b.ID stehen aber auch Daten die nicht mit dem zutun haben was ich siche - daher kann ichnicht nur die Tabelle b nehmen und gut is)

Beispiel:
a.ID -- a.ID2 -- b.ID -- b.Tage

1 -- 2 -- 1 -- NULL
2 -- 3 -- 3 -- NULL
NULL -- NULL -- 3 --8762387462
2 -- 4 -- 4 --NULL
2 -- 5 -- 5 -- NULL
NULL -- NULL -- 4 -- 940895
NULL -- NULL -- 5 -- 268283

und so weiter...


Kann mir hier jemand wieterhelfen?

Herzlichen Dank und Gruß Markus
it-frosch
it-frosch 12.08.2010 um 18:41:05 Uhr
Goto Top
Hallo Markus,

ich muss noch mal drauf nachfragen weil es immer noch nicht klar ist.

Was willst du konkret auswerten?
Und wie sehen Beispieldatensätze (id ohne Sohn mit Tagen, Id mit mehreren Söhen und mit Tagen, Daten die du nicht auswerten willst) aus?
Willst du IDs mit oder/und ohne Söhnen auswerten?
Wenn du IDs mit Söhnen auswerten willst, soll sich Anzahl der Tage auf die "Vater" ID beziehen oder auf den letzten Sohn?

Ich glaube du musst in deinem Kopf dir das ganze erst einmal klar machen.
Nimm dir ein Blatt und schreib dir dir Strukturen auf. Wenn du es dann hast, stelle die Antworten hier rein.

grüße vom it-frosch
Arafat
Arafat 13.08.2010 um 16:10:08 Uhr
Goto Top
Hallo,

grundsätzlich gibt es zweit Tabellen.

Die erste Tabelle umschreibt die Vater/Sohn Beziehung.

Artikelnummer; Ressourcenummer
81400002; 82150003
81400002; 82150018
81500007; 81900018
81900018; 82050001
81900018; 82050004
81900018; 82050005
81900018; 82050006
81900018; 82050007
81900018; 82050010
81900018; 82050011

in der Zweiten Tabelle ist dann die Zeit untergebracht, wobei tabelleA.Ressourcenummer mit tabelleB.Artikelnummer vergleichbar ist.

Artikelnummer; Wiederbeschaffungszeit
82150003; 42
82150018; 58
81900018; 12 ............................=> falscher Wert bitte überlesen
82050001; 25
82050004; 14
82050005; 7
82050006; 77
82050007; 69
82050010; 36
82050011; 50

Als Ergebnis möchte ich die höchste Wiederbeschaffungszeit aus allen Endpunkten des Baumes haben.

Knotenpunkte wie 81900018, 81400002 etc. haben keine Wiederbeschaffungszeit, Sie dienen nur als "Knoten"


Herzlichen Dank für Eure Hilfe!

Gruß Markus
it-frosch
it-frosch 13.08.2010 um 20:02:45 Uhr
Goto Top
Hallo Markus,

unter der Voraussetzung das ein Endpunkt ein Artikel ist der in der Vater-Sohn-Tabelle nur als Ressource auftaucht würde ich sagen.

select tabelle2.artikelnummer,max(tabelle2.wiederbeschaffungszeit) from tabelle2
where
tabelle2.artikelnr in (select tabelle1.ressourcennummer from tabelle1)
and
tabelle2.artikelnr not in (select tabelle1.artikelnr from tabelle1)
group by tabelle2.artikelnummer
order by 2 desc;


Damit erhälst du eine Liste aus Artikelnummer und höchster Wiederbeschaffungszeit pro Artikel sortiert nach der größten Wiederbeschaffungszeit.

grüße vom it-frosch

PS: In der Hoffnung dich nun richtig verstanden zu haben. face-wink
Arafat
Arafat 15.08.2010 um 21:53:53 Uhr
Goto Top
Hallo it-frosch,

das Problem ist, dass in tabelleB auch Artikell zu finden sind, die nichts mit tabelleA zu tun haben.

Ich denke, man müsste den Baum von der Spitze durchwandern. Das war aber noch nie meine Stärke.

Danke für deine Aantworten!

Gruß Markus
Netzfetzer
Netzfetzer 16.08.2010 um 10:01:17 Uhr
Goto Top
ich glaube ich habe dein Problem verstanden.

Du hast eine Artikel-Stücklisten-Struktur und willst die maximale Wiederbeschaffungszeit/Durchlaufzeit erhalten. Dabei wird in Tabelle A die Zusammensetzung (Artikel / Komponente bzw. Ressource) und in Tabelle B die WBZ/DLZ der einzelnen Ressourcen festgelegt.

Dann hätte ich als Lösungsvorschlag sowas in der Art (nicht getestet):

1. Struktur des Artikels
SELECT TAB_A.Artikelnummer as ART_NR ,
TAB_A.Ressourcenummer as RES_NR,
max(TAB_B.Wiederbeschaffungszeit) as WBZ
FROM TAB_A left join TAB_B on TAB_A.Ressourcennummer = TAB_B.Artikelnummer
CONNECT BY PRIOR TAB_A.Artikelnummer = TAB_A.Ressourcenummer
GROUP BY ART_NR, RES_NR

ich weiß nicht genau wie bei dir dann das Ergebnis aussieht, aber du könntest sicher dann eine Abfrage um diese herum "bauen", wo du dir die WBZ pro "Kopfartikel" summieren lässt...

Gruß Netzfetzer
Arafat
Arafat 16.08.2010 um 15:03:30 Uhr
Goto Top
Hallo,

auch hier ist leider das Problem, dass es in der TabelleB Artikelgibt die nichts mit Tabelle A zu tun haben.
Bzw. gibt es in TabelleA auch andere "Stücklisten".
Und ich würde gerne nur eine dieser Listen durchsuchen.
Im obrigen Beispiel z.B. alles unterhalb von 81400002.

Herzlichen Dank.

Gruß Markus
Netzfetzer
Netzfetzer 16.08.2010 um 15:15:14 Uhr
Goto Top
Naja das in Tab_B Einträge vorkommen, die mit Tab_A nichts zu tun haben, ist für die Abfrage ja eigentlich egal, da sie die Artikel und Ressourcen aus der Tab_A nimmt und nur die WBZ's aus Tab_B zuordnet...

Dein Beispiel würde doch so aussehen

81400002
-> 82150003 -> 42d WBZ
-> 82150018 -> 58d WBZ

Was willst du als Ergebnis erhalten?
81400002 - 58???
Arafat
Arafat 16.08.2010 um 15:38:27 Uhr
Goto Top
Hallo,

danke für deine Antwort.

In meinem Beispiel sollte die Lösung 82050006; 77 sein.

Es könnte aber sein, dass es in der obersten Ebene neben der 81400002 noch einen weiteren Baum gibt, den ich nicht berücksichtigen möchte.
Daher muss ich eigentlich eine Routine haben der man die 81400002 angibt und von der aus man die Ästen nach unten wandert.

Aber wie schreib ich die denn?

Danke und Gruß Markus
Netzfetzer
Netzfetzer 16.08.2010 um 16:20:43 Uhr
Goto Top
Du willst also eine Routine, der du 81400002 mitgibst...

wie soll dann das Ergebnis aussehen??? Weil aus dem oben genannten Beispiel würde ich folgendes Schlussfolgern:

Artikel = 81400002
-> Ressource = 82150003 -> WBZ = 42
-> Ressource = 82150018 -> WBZ = 58

Ergebnis:
max(WBZ) = 58

ist das richtig oder nicht???

--damit solltest du das ergebnis erhalten:
select max(tab_b.wbz)
from tab_b, tab_a
where tab_b.artikelnummer in
(select ressource
from tab_a
where artikelnummer = :artikelnummer -- z.B. 81400002)
Arafat
Arafat 16.08.2010 um 17:05:53 Uhr
Goto Top
Hallo irgendwie bin ich aus der Spur.

im Grunde ist das ganze ein Baum.

Unterhalb der 81400002 könnte es noch weitere Zweige und darunter nochmal weiter ... und so weiter geben.

Sprich:

Artikel = 81400002
-> Ressource = 82150003 -> WBZ = 42
-> Ressource = 82150018 -> WBZ = 58
-> Ressource = 81700001
->-> Artikel= 8700001
->->->Ressource = 82150099 -> WBZ = 77

Ich glaub ich hab mich in dem Beispiel mit TabelleA und B verhauen - sorry.

Gruß Markus
Arafat
Arafat 16.08.2010 um 17:17:21 Uhr
Goto Top
... noch mal:

Im Grunde könnten in TabelleA x-verschiedenen Stücklisten mit Vater/Sohn Beziehung beschrieben werden. Das Beispiel wäre eine einzelne Liste deren UrVater 81400002 ist. Das Verwandschaftverhältnis muss nicht auf Vater/Sohn beschränkt sein - Söhne können natürlich selber wieder Väter sein

In TabelleB stehen alle Artikel mit einer WBZ. Diese Artikel können einer x-beliebigen STRückliste zugeordnet sein, oder Sie können auch keiner Stückliste zugeordnet sein.

Ich denke ich muss den UrVater übergeben und dann über eine "while" Schleife den Baum an der Äster hinabsteigen, bis zum Ende und den nächsten Ast ... und so weiter.

Genau da hänge ich Schleifen waren noch nie mein Ding.

Vielleicht fällt noch jemanden von Euch was ein - ich bin für jede Anregung Dankbar.

Herzlichen Dank Markus
Netzfetzer
Netzfetzer 17.08.2010 um 10:00:45 Uhr
Goto Top
ok so langsam verstehe ich das Problem =D

ich bin dennoch der Meinung, das dir hier ein Treewalker am besten hilft...

SELECT ressource
FROM tab_a
CONNECT BY PRIOR artikelnummer = ressource
START WITH artikelnummer = 81400002

hiermit (oder so ähnlich) müsstest du die ressourcen auslesen können, welche ganz unten in deiner hierarchie stehen. mit einem select um diesen select könntest du dir dann aus tabelle B noch die WBZ's dazu lesen.

Du kannst ja auch mal versuchen, in das select die wbz mit einzubinden. evtl. kannst du dir dann mit einem weiteren select die max(WBZ) auslesen lassen...
Arafat
Arafat 17.08.2010 um 11:15:57 Uhr
Goto Top
Hallo,

danke für deine Antwort.

Da heb ich allerdings das nächste Problem.
CONNECT BY PRIOR ist Oracle Syntax. Wie bekomme ich die in MSSql?

Herzlichen Dank und Gruß

Markus
Netzfetzer
Netzfetzer 17.08.2010 um 11:34:26 Uhr
Goto Top
hmm ok das ist ein gutes argument...
kann dir folgendes anbieten:
http://phpperformance.de/nested-sets-hierarchische-strukturen-und-baeum ...
http://forums.mysql.com/read.php?98,38047,38089#msg-38089

wenn das nicht hilft, dann müssen wir uns einen neuen ansatz ausdenken...
Arafat
Arafat 17.08.2010 um 16:35:56 Uhr
Goto Top
Hallo,

darüber bin auch gestolpert - müsste aber dann vor jeder Abfrage der WBZ die "nested sets"-Tabelle aktualisieren, da es zwischenzeitlich neue Knoten geben könnten.
(Bzw. müsste ich die Struktur im "Kopf" habe, da die "nested sets" nur ein Abbild der Struktur ist?)

Mir schwebte halt vor, das es eine Funktion oder Schleife gibt die mich durch den Baum leitet und mir den Endpunkt ausgibt.

von der Logik her etwas wie

Einstieg bei 81400002

suche alle Söhne von 81400002

suche ob die Söhne wiederum Söhne haben, wenn nicht sind sie Endpunkte und werden ausgegeben

Hat man den Endpunkt erreicht geht es wieder nach ober und einen "Schritt zur Seite"

Ich kann mich ganz dunkel daran erinnern, dass ich sowas mal beim Thema Algorithmen und Datenstrukturen gelesen habe - das ist aber ewig her.

Ich such und denke weiter...

Danke und Gruß Markus
Netzfetzer
Netzfetzer 18.08.2010 um 13:22:53 Uhr
Goto Top
Evtl. hilft da sowas in der Art:
Quelle: http://www.ms-office-forum.net/forum/showthread.php?t=267959

innerhalb der Schleife muss man dann eine weitere Schleife durchgehen, da es ja mehr als einen Sohn geben kann. ist man ganz am ende, müsste man sich den letzten wert in ein array schreiben.

evtl. kannst du dir einen view oder eine temp. tabelle aufbauen um das ganze zu vereinfachen?!

eine weitere alternative wäre evtl., mittels PL/SQL eine Funktion zu schreiben, die sich selbst wieder aufruft und checkt, ob söhne vorhanden sind...wenn nein dann weißt du, das du am ende bist, und wenn ja dann rufst du die selbe funktion wieder auf um nach söhnen zu schauen.

Gruß Netzfetzer
Biber
Biber 18.08.2010 um 14:00:41 Uhr
Goto Top
Öhmm, Netzfetzer,

Zitat von @Netzfetzer:
eine weitere alternative wäre evtl., mittels PL/SQL eine Funktion zu schreiben, die sich selbst wieder aufruft und checkt, ob
söhne vorhanden sind...wenn nein dann weißt du, das du am ende bist, und wenn ja dann rufst du die selbe funktion
wieder auf um nach söhnen zu schauen.
Sagen wir so... wenn Arafats Datenbankblech PL/SQL verstehen würde, dann würde es auch "CONNECT BY PRIOR" verstehen.

Aber ansonsten natürlich... die Rekursivität lässt sich natürlich auch mit einer Stored Procedure nachbilden..

Grüße
Biber
Netzfetzer
Netzfetzer 18.08.2010 um 14:06:03 Uhr
Goto Top
Hi Biber,

naja ich gehe davon aus das es was äquivalentes zu pl/sql in mssql gibt...ich meinte damit ja auch nur was ich mir noch vorstellen könnte.

würde ich die lösung kennen hätte ich sie schon gepostet ;)

Gruß Netzfetzer
Arafat
Arafat 18.08.2010 um 14:13:56 Uhr
Goto Top
Hallo,

das ist das Problem PL/SQL ist Oracle hier geht es um MSSQL...

Ich habe das Ganze mal mit den nested sets versucht - allerdings muss ich jetzt jedesmal mitbekommen ob es einen neuen Ast gibt.


Tabelle generieren

CREATE TABLE tree (
id INT NOT NULL IDENTITY(1,1) ,
Artikelnummer VARCHAR(50) NOT NULL,
lft INT NOT NULL,
rgt INT NOT NULL,
PRIMARY KEY (id),
);

EIngabe des Urvaters

INSERT INTO tree (Artikelnummer,lft,rgt) VALUES ('80310001',1,2);

weiter Kinder

DECLARE @rgt int
DECLARE @lft int
SET @lft = 1
SET @rgt = 2
UPDATE tree SET rgt=rgt+2 WHERE rgt >= @rgt;
UPDATE tree SET lft=lft+2 WHERE lft > @rgt;
INSERT INTO tree (Artikelnummer,lft,rgt) VALUES ('XXXXXXXXXXXXXXXX', @rgt,@RGT+1)
;

*


Den Link mit der Schleife schau ich mir nachher mal an.

Gruß Markus
it-frosch
it-frosch 19.08.2010 um 22:01:43 Uhr
Goto Top
Hallo Markus,

allerdings muss ich jetzt jedesmal mitbekommen ob es einen neuen Ast gibt.

Du könntest auf die Tabelle einen AFTER INSERT TRIGGER setzen der eine Stored Procedure aufruft die dir diese Tabelle neu generiert.

Grüße vom it-frosch
Arafat
Arafat 20.08.2010 um 09:10:52 Uhr
Goto Top
Hallo,

da habe ich das Problem, dass der Baum in einem anderen Programm (Sage) generiert wird face-sad ... Hier möchte ich nicht in deren Tabelle "rumpfuschen".

Gruß Markus
Netzfetzer
Netzfetzer 20.08.2010 um 09:37:39 Uhr
Goto Top
Hi,

wie wäre es denn mit einer rekursiven schleife?

also so in der art (keine SQL-Syntax - nur als Denkansatz gedacht!):

function FINDSONS (artnr in varchar2)
declare
artnr as varchar2;
begin
while ( (select count(*) from tab_a where artikelnummer = artnr) > 0 ) begin
--noch nicht am ende
select artikelnummer from tab_a where artikelnummer = artnr;
-- die ergebnismenge müsste man in einer schleife durchgehen und hier wieder die Funktion FINDSONS aufrufen (mit übergebener neuer Artikelnummer)
end
--wenn count(*) = 0 dann bist du in der struktur am ende, heißt du hast deinen untersten knoten und kannst mit einem select die WBZ für die Artikelnummer abfragen und diese dir in ein array oder eine tabelle o.ä. schreiben...
end;

also wie gesagt, ist jetzt nur so nen denkansatz...ich habe auch vorerst keinen wert auf syntax o.ä. gelegt...

Gruß Netzfetzer
it-frosch
it-frosch 20.08.2010 um 19:45:30 Uhr
Goto Top
Hallo Markus,

da habe ich das Problem, dass der Baum in einem anderen Programm (Sage) generiert wird face-sad ... Hier möchte ich nicht in deren Tabelle "rumpfuschen".
Mmh, verstehe ich obwohl ein Trigger auf einer Programm-Tabelle der in einer nicht zum Programm gehörenden Tabelle Aktionen auslöst für mich noch nicht unbedingt "rumpfuschen" ist. face-wink
Prinzipiell hast du aber recht.

grüße vom it-frosch