Tengo un requisito de consulta de ----. Tratando de resolverlo conCONNECT BY, pero parece que no puede obtener los resultados que necesito.

Tabla (simplificada):

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

Hay dos fuentes de datos de usuario (fuentes de datos) ... Tengo que estar atento a los errores en cualquiera de ellos al actualizar la información.

Escenarios:

A un usuario se le asigna una nueva ID de usuario ... El registro anterior se configura en consecuencia y se desactiva (generalmente, se trata de un cambio de nombre para los contratistas que se convierten a tiempo completo)Un usuario se va y vuelve un poco más tarde. HR no nos envía la ID de usuario anterior para que podamos conectar las cuentas.El sistema se arruinó y no estableció la nueva ID de usuario en el registro anterior.Los datos pueden ser malos de otras cien maneras.

Necesito saber que los siguientes son los mismos usuarios, y no puedo confiar en el nombre u otros campos ... difieren entre los registros coincidentes:

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 yEX000005 están conectados correctamente por elNEW_USER_ID campo. El cambio de nombre ocurrió antes de que existieran las ID de HR globales, por lo queEX0T1100 no tiene unoEX000005 recibió una nueva ID de usuario, 'GL110456', y las dos solo se conectan al tener la misma ID global de HR.

La limpieza de los datos no es una opción.

La consulta hasta el momento:

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);

He probado variosCONNECT BY cláusulas, pero ninguna de ellas es del todo correcta:

-- 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))

UNIONing dos consultas CONECTAR POR no funciona ... No obtengo la nivelación.

Esto es lo que me gustaría ver ... Estoy de acuerdo con un conjunto de resultados que debo distinguir y usar como una subconsulta. También estoy de acuerdo con cualquiera de las tres ID de usuario en la columna ROOTUSER ... Solo necesito saber que son los mismos usuarios.

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    

Ideas?

Actualizar

Nicholas, tu código se parece mucho al camino correcto ... en este momento, ellead(user_id) over (partition by global_hr_id) obtiene golpes falsos cuando elglobal_hr_id es nulo. Por ejemplo:

USER_ID   NEW_USER_ID   CHAINNEWUSER   GLOBAL_HR_ID   LAST_NAME   FIRST_NAME
FP004468                FP004469                      AARON       TIMOTHY
FP004469                                              FOONG       KOK WAH

A menudo he querido tratar los nulos como registros separados en una partición, pero nunca he encontrado una manera de hacerignore nulls trabajo. Esto hizo lo que quería:

decode(global_hr_id,null,null,lead(cud.user_id ignore nulls) over (partition by global_hr_id order by user_id)

... pero tiene que haber una mejor manera No he podido obtener la consulta para terminar todavía en los datos de usuario completos (unos 40,000 usuarios). Ambosglobal_hr_id ynew_user_id están indexados.

Actualizar

La consulta vuelve después de unos 750 segundos ... largo, pero manejable. Devuelve 93k registros, porque no tengo una buena manera de filtrar los hits de nivel 2 fuera de la raíz;start with global_hr_id is null, pero desafortunadamente, ese no es siempre el caso. Tendré que pensar un poco más sobre cómo filtrarlos.

He intentado agregar más comienzo complejo con cláusulas antes, pero me parece que por separado, se ejecutan <1 segundo ... juntos, toman 90 minutos>. <

Gracias de nuevo por su ayuda ... trabajando en esto.