Verweis auf übergeordnete Spalten in Oracle CONNECT BY hierarchische Abfrage

Ich habe Daten, die wie folgt aussieht:

KEY1   KEY2      KEY3   LKEY1  LKEY2     LKEY3  FLAG
====== ========= ====== ====== ========= ====== =====
09/10  10000     A1234  09/10  AU000123  A1234  1
09/10  10000     A1234  09/10  AU000456  A1234  1
09/10  10000     A1234  09/10  AX000001  A1234  1
09/10  AX000001  A1234  09/10  AE000010  A1234  0
09/10  AX000001  A1234  09/10  AE000020  A1234  0
09/10  AX000001  A1234  09/10  AE000030  A1234  0
09/10  10000     A1234  09/10  AX000002  A1234  0
09/10  AX000002  A1234  09/10  AE000040  A1234  0
09/10  10000     A1234  09/10  AU000789  A1234  0

Dies ist hierarchische Daten, wobei ich würde die Abfrage gegen die Wurzel zusammengesetzten Schlüssel (in diesem Fall 09/10 10000 A1234); die FLAG Feld bezieht sich auf das 'Objekt', identifiziert durch die LKEYx Tasten. Es kann eine beliebige Anzahl von Ebenen verschachteln. (Beachten Sie, dass die KEY1 und KEY3 Felder müssen nicht sein-Invarianten, wie in dem Beispiel oben, solange die Hierarchie gewahrt wird.)

Was ich abrufen möchten, sind die leaf-Knoten, aber wenn ein Blatt übergeordnete KEY2 ist die gleiche Länge wie LKEY2 oder enthält eine X als zweiten Buchstaben, dann den unmittelbar übergeordneten. In diesem Fall müssen wir auch die Markierung des Datensatzes als optionale... So etwas wie dieses:

KEY1   KEY2      KEY3   OPTION  FLAG
====== ========= ====== ======= =====
09/10  AU000123  A1234  0       1
09/10  AU000456  A1234  0       1
09/10  AX000001  A1234  1       1
09/10  AX000002  A1234  1       0
09/10  AU000789  A1234  0       0

Ich geschrieben habe eine Abfrage, die dies tut, aber es ist nicht schön. Darüber hinaus macht es die Annahme, dass alle Blattknoten sind auf der gleichen Ebene der Baumstruktur, um zu unterscheiden, dass die optionale Datensätze werden; dies ist jedoch nicht unbedingt wahr. Meine Abfrage lautet wie folgt:

with queryKeys as (
  select '09/10' key1,
         '10000' key2,
         'A1234' key3,
  from   dual
),
subTree as (
  select     tree.key1,
             tree.key2,
             tree.key3,

             tree.lkey1,
             tree.lkey2,
             tree.lkey3,

             tree.flag,

             connect_by_isleaf isLeaf,
             level thisLevel

  from       tree,
             queryKeys

  start with tree.key1 = queryKeys.key1
  and        tree.key2 = queryKeys.key2
  and        tree.key3 = queryKeys.key3

  connect by tree.key1 = prior tree.lkey1
  and        tree.key2 = prior tree.lkey2
  and        tree.key3 = prior tree.lkey3
),
maxTree as (
  select max(thisLevel) maxLevel
  from   subTree
)
select lkey1 key1,
       lkey2 key2,
       lkey3 key3,
       1 - isLeaf option,
       flag

from   subTree,
       maxTree
where (isLeaf = 1 or thisLevel = maxLevel - 1)
and   (length(key2) != length(lkey2) or substr(lkey2, 2, 1) != 'X');

Den Grund für queryKeys wird verwendet, da Sie anderswo in einer größeren Abfrage und kann mehr als einen Datensatz. Die maxTree Teil ist das problem, über seine Allgemeine Schrulligkeit!

Nun, der Grund für den Titel von diesem post ist da diese Abfrage gemacht werden konnte viel einfacher, wenn ich könnte finden, um die Eltern FLAG Feld. Ich habe versucht, ein JOIN Annäherung zu dieser Idee ist-joining Baum mit sich auf die jeweiligen Tasten -- aber, wenn ich mich nicht Irre, wäre das Ergebnis in einem rekursiven problem, wo würde Sie immer wieder zu Durchlaufen, bis der Baum zu finden, den richtigen übergeordneten Schlüssel (sowohl die KEYx und LKEYx Felder definieren Sie die vollständigen zusammengesetzten Schlüssel für den Datensatz).

(P. S. Mit Oracle 10gR2, ob es einen Unterschied macht.)

InformationsquelleAutor Xophmeister | 2012-04-30

Verwenden Sie einfach:

PRIOR FLAG

es wird Ihnen genau, was Sie wollen - flag-Feld des übergeordneten Zeile.

subTree as (            
select     tree.key1,            
         tree.key2,            
         tree.key3,            
         tree.lkey1,            
         tree.lkey2,            
         tree.lkey3,            
         tree.flag,            
         PRIOR TREE.FLAG PRIOR_FLAG
         connect_by_isleaf isLeaf,            
         level thisLevel            

from       tree,            
         queryKeys 
(...)

Schön! Danke 🙂

InformationsquelleAutor arturro

Ich habe angenommen, dein post läuft darauf hinaus, die Frage, "Wie kann ich mich auf die FLAG - Attribut des übergeordneten Zeile in eine hierarchische Abfrage?"

Ich weiß nicht, die SQL -, die ich mir ausgedacht habe, richtig ist. Ich entschuldige mich, wenn es nicht so ist. Im Allgemeinen, obwohl, hier war mein Ansatz:

Auf jeder Ebene in der Hierarchie, ich vorstellen, die zusammen alle Tasten (SYS_CONNECT_BY_PATH). Dann mit SUBSTR, INSTR und LEVEL ich SUBSTRinged heraus, welche Mengen der übergeordneten Ebene Schlüssel. Schließlich, in der definition von PARENT_FLAG wähle ich die FLAG einer Reihe, deren Schlüssel passt zu dieser SUBSTRinged-out-Taste.

Setup:

CREATE TABLE tree (
    key1        VARCHAR2(5)
,   key2        VARCHAR2(10)
,   key3        VARCHAR2(5)
,   lkey1       VARCHAR2(5)
,   lkey2       VARCHAR2(10)
,   lkey3       VARCHAR2(5)
,   flag        VARCHAR2(1)
);
INSERT INTO tree VALUES ('09/10','10000','A1234','09/10','AU000123','A1234','1');
INSERT INTO tree VALUES ('09/10','10000','A1234','09/10','AU000456','A1234','1');
INSERT INTO tree VALUES ('09/10','10000','A1234','09/10','AX000001','A1234','1');
INSERT INTO tree VALUES ('09/10','AX000001','A1234','09/10','AE000010','A1234','0');
INSERT INTO tree VALUES ('09/10','AX000001','A1234','09/10','AE000020','A1234','0');
INSERT INTO tree VALUES ('09/10','AX000001','A1234','09/10','AE000030','A1234','0');
INSERT INTO tree VALUES ('09/10','10000','A1234','09/10','AX000002','A1234','0');
INSERT INTO tree VALUES ('09/10','AX000002','A1234','09/10','AE000040','A1234','0');
INSERT INTO tree VALUES ('09/10','10000','A1234','09/10','AU000789','A1234','0');

Abfrage:

COL flag          FOR A4
COL same_length   FOR A11
COL has_x_2nd     FOR A9
COL full_key_path FOR A50
COL parent_key    FOR A30
COL parent_flag   FOR A11
WITH querykeys AS (
  SELECT '09/10' key1
  ,      '10000' key2
  ,      'A1234' key3
  FROM   DUAL
)
, subtree1 AS (
  SELECT     tree.key1
  ,          tree.key2
  ,          tree.key3
  ,          tree.lkey1
  ,          tree.lkey2
  ,          tree.lkey3
  ,          tree.flag
  ,          CONNECT_BY_ISLEAF isleaf
  ,          LEVEL thislevel
  ,          DECODE(LENGTH(tree.key2)
             ,      LENGTH(tree.lkey2), '1'
             ,      '0') same_length
  ,          DECODE(UPPER(SUBSTR(tree.key2,2,1))
             ,      'X', '1'
             ,      '0') has_x_2nd
  ,          SYS_CONNECT_BY_PATH(tree.key1 || '|' || tree.key2 || '|' || tree.key3,'\')
             || '\'
             || tree.lkey1 || '|' || tree.lkey2 || '|' || tree.lkey3 || '\' full_key_path
  FROM       tree
  ,          querykeys
  START WITH tree.key1 = querykeys.key1
  AND        tree.key2 = querykeys.key2
  AND        tree.key3 = querykeys.key3
  CONNECT BY tree.key1 = PRIOR tree.lkey1
  AND        tree.key2 = PRIOR tree.lkey2
  AND        tree.key3 = PRIOR tree.lkey3
)
subtree2 AS (
    SELECT  st1.key1
    ,       st1.key2
    ,       st1.key3
    ,       st1.lkey1
    ,       st1.lkey2
    ,       st1.lkey3
    ,       st1.flag
    ,       st1.isleaf
    ,       st1.thislevel
    ,       st1.same_length
    ,       st1.has_x_2nd
    ,       st1.full_key_path
    ,       SUBSTR(st1.full_key_path
            ,      INSTR(st1.full_key_path,'\',1,st1.thislevel) + 1
            ,      INSTR(st1.full_key_path,'\',1,st1.thislevel + 1)
                   - INSTR(st1.full_key_path,'\',1,st1.thislevel) - 1) parent_key
    FROM    subtree1    st1
)
SELECT  st2.key1
      st2.key2
      st2.key3
      st2.lkey1
      st2.lkey2
      st2.lkey3
      st2.flag
      st2.isleaf
      st2.thislevel
      st2.same_length
      st2.has_x_2nd
     (SELECT t_prime.flag
        FROM   tree   t_prime
        WHERE  t_prime.key1 = SUBSTR(st2.parent_key
                              ,      1
                              ,      INSTR(st2.parent_key,'|',1,1) - 1)
        AND    t_prime.key2 = SUBSTR(st2.parent_key
                              ,      INSTR(st2.parent_key,'|',1,1) + 1
                              ,      INSTR(st2.parent_key,'|',1,2)
                                   - INSTR(st2.parent_key,'|',1,1) - 1)
        AND    t_prime.key3 = SUBSTR(st2.parent_key
                              ,      INSTR(st2.parent_key,'|',1,2) + 1)
        -- Following assumes all rows with parent keys have same flag value.
        -- Avoids ORA-01427: single-row subquery returns more than one row.
        AND    ROWNUM = 1) parent_flag
FROM    subtree2    st2
;

Ergebnis:

KEY1  KEY2       KEY3  LKEY1 LKEY2      LKEY3 FLAG     ISLEAF  THISLEVEL SAME_LENGTH HAS_X_2ND PARENT_FLAG
----- ---------- ----- ----- ---------- ----- ---- ---------- ---------- ----------- --------- -----------
09/10 10000      A1234 09/10 AU000123   A1234 1             1          1 0           0         1
09/10 10000      A1234 09/10 AU000456   A1234 1             1          1 0           0         1
09/10 10000      A1234 09/10 AU000789   A1234 0             1          1 0           0         1
09/10 10000      A1234 09/10 AX000001   A1234 1             0          1 0           0         1
09/10 AX000001   A1234 09/10 AE000010   A1234 0             1          2 1           1         0
09/10 AX000001   A1234 09/10 AE000020   A1234 0             1          2 1           1         0
09/10 AX000001   A1234 09/10 AE000030   A1234 0             1          2 1           1         0
09/10 10000      A1234 09/10 AX000002   A1234 0             0          1 0           0         1
09/10 AX000002   A1234 09/10 AE000040   A1234 0             1          2 1           1         0

9 rows selected.

SQL>

Wie gesagt, ich bin mir nicht 100% sicher, dass ich grokked des Datenmodells vollständig, aber ich hoffe, dass Sie in der Lage sind, Folgen Sie meinem Ansatz.

Ich wählte @arturro Antwort für seine Einfachheit Analyse über den Weg, aber +1 würde für deine Mühe. Vielen Dank 🙂

InformationsquelleAutor Tebbe

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.