Oracle Self-Join bei mehreren möglichen Spaltenübereinstimmungen - CONNECT BY?
Ich habe eine Abfrageanforderung von ----. Ich versuche es zu lösen mitCONNECT BY
, aber ich kann nicht die Ergebnisse erzielen, die ich brauche.
Tabelle (vereinfacht):
create table CSS.USER_DESC (
USER_ID VARCHAR2(30) not null,
NEW_USER_ID VARCHAR2(30),
GLOBAL_HR_ID CHAR(8)
)
-- USER_ID is the primary key
-- NEW_USER_ID is a self-referencing key
-- GLOBAL_HR_ID is an ID field from another system
Es gibt zwei Quellen für Benutzerdaten (Datenfeeds) ... Ich muss beim Aktualisieren der Informationen auf Fehler in beiden achten.
Szenarien:
Ich muss wissen, dass die folgenden Benutzer identisch sind und ich mich nicht auf Namen oder andere Felder verlassen kann. Sie unterscheiden sich zwischen übereinstimmenden Datensätzen:
ROOTUSER NUMROOTS NODELEVEL ISLEAF USER_ID NEW_USER_ID GLOBAL_HR_ID USERTYPE LAST_NAME FIRST_NAME
-----------------------------------------------------------------------------------------------------------------------------
EX0T1100 2 1 0 EX0T1100 EX000005 CONTRACTOR VON DER HAAVEN VERONICA
EX0T1100 2 2 1 EX000005 00126121 EMPLOYEE HAAVEN, VON DER VERONICA
GL110456 1 1 1 GL110456 00126121 EMPLOYEE VONDERHAAVEN VERONICA
EXOT1100
undEX000005
sind durch die richtig verbundenNEW_USER_ID
Feld. Die Umbenennung erfolgte, bevor es globale HR-IDs gabEX0T1100
hat keinen.EX000005
wurde eine neue Benutzer-ID, 'GL110456', zugewiesen, und beide sind nur mit derselben globalen HR-ID verbunden.
Das Aufräumen der Daten ist keine Option.
Die Abfrage bisher:
select connect_by_root cud.user_id RootUser,
count(connect_by_root cud.user_id) over (partition by connect_by_root cud.user_id) NumRoots,
level NodeLevel, connect_by_isleaf IsLeaf, --connect_by_iscycle IsCycle,
cud.user_id, cud.new_user_id, cud.global_hr_id,
cud.user_type_code UserType, ccud.last_name, cud.first_name
from css.user_desc cud
where cud.user_id in ('EX000005','EX0T1100','GL110456')
-- Using this so I don't get sub-users in my list of root users...
-- It complicates the matches with GLOBAL_HR_ID, however
start with cud.user_id not in (select cudsub.new_user_id
from css.user_desc cudsub
where cudsub.new_user_id is not null)
connect by nocycle (prior new_user_id = user_id);
Ich habe verschiedene ausprobiertCONNECT BY
Klauseln, aber keine von ihnen ist ganz richtig:
-- As a multiple CONNECT BY
connect by nocycle (prior global_hr_id = global_hr_id)
connect by nocycle (prior new_user_id = user_id)
-- As a compound CONNECT BY
connect by nocycle ((prior new_user_id = user_id)
or (prior global_hr_id = global_hr_id
and user_id != prior user_Id))
Das UNIONieren von zwei CONNECT BY-Abfragen funktioniert nicht ... Ich verstehe das Leveling nicht.
Hier ist, was ich sehen möchte ... Ich bin mit einer Ergebnismenge einverstanden, die ich unterscheiden und als Unterabfrage verwenden muss. Ich bin auch mit einer der drei Benutzer-IDs in der ROOTUSER-Spalte einverstanden ... Ich muss nur wissen, dass sie dieselben Benutzer sind.
ROOTUSER NUMROOTS NODELEVEL ISLEAF USER_ID NEW_USER_ID GLOBAL_HR_ID USERTYPE LAST_NAME FIRST_NAME
-----------------------------------------------------------------------------------------------------------------------------
EX0T1100 3 1 0 EX0T1100 EX000005 CONTRACTOR VON DER HAAVEN VERONICA
EX0T1100 3 2 1 EX000005 00126121 EMPLOYEE HAAVEN, VON DER VERONICA
EX0T1100 3 (2 or 3) 1 GL110456 00126121 EMPLOYEE VONDERHAAVEN VERONICA
Ideen?
Aktualisieren
Nicholas, dein Code sieht sehr nach dem richtigen Track aus ... im Moment ist derlead(user_id) over (partition by global_hr_id)
Erhält falsche Treffer, wenn dieglobal_hr_id
ist Null. Zum Beispiel:
USER_ID NEW_USER_ID CHAINNEWUSER GLOBAL_HR_ID LAST_NAME FIRST_NAME
FP004468 FP004469 AARON TIMOTHY
FP004469 FOONG KOK WAH
Ich wollte Nullen oft als separate Datensätze in einer Partition behandeln, aber ich habe nie einen Weg gefunden, dies zu tunignore nulls
Arbeit. Das hat getan, was ich wollte:
decode(global_hr_id,null,null,lead(cud.user_id ignore nulls) over (partition by global_hr_id order by user_id)
... aber es muss einen besseren Weg geben. Ich konnte die Abfrage für die vollständigen Benutzerdaten (ca. 40.000 Benutzer) noch nicht abschließen. Beideglobal_hr_id
undnew_user_id
sind indiziert.
Aktualisieren
Die Abfrage wird nach ca. 750 Sekunden zurückgegeben ... lang, aber überschaubar. Es werden 93.000 Datensätze zurückgegeben, da ich keine gute Möglichkeit habe, Treffer der Ebene 2 aus dem Stammverzeichnis herauszufiltern - das haben Siestart with global_hr_id is null
das ist aber leider nicht immer der fall. Ich muss noch etwas darüber nachdenken, wie ich diese herausfiltern kann.
Ich habe zuvor versucht, komplexere Start-with-Klauseln hinzuzufügen, aber ich finde, dass sie getrennt <1 Sekunde laufen ... zusammen dauern sie 90 Minuten>
Nochmals vielen Dank für Ihre Hilfe.