多个可能的列匹配上的 Oracle Self-Join - CONNECT BY？

Question

我有来自 ---- 的查询需求。尝试用 CONNECT BY 解决它，但似乎无法获得我需要的结果。

表格(简化):

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

用户数据(数据源)有两个来源......我在更新信息时必须注意其中任何一个的错误。

场景:

1. 用户获得一个新的用户 ID...旧记录相应地设置并停用(通常是为成为全职承包商的重命名)
2. 用户离开并稍后返回。 HR 未能向我们发送旧用户 ID，因此我们无法关联帐户。
3. 系统搞砸了，没有在旧记录上设置新用户 ID。
4. 数据可能以其他一百种方式出现问题

我需要知道以下是同一用户，我不能依赖名称或其他字段......它们在匹配记录中有所不同:

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 和 EX000005 通过 NEW_USER_ID 字段正确连接。重命名发生在全局 HR ID 出现之前，因此 EX0T1100 没有。 EX000005 获得了一个新的用户 ID“GL110456”，两者仅通过具有相同的全局 HR ID 连接。

清理数据不是一种选择。

到目前为止的查询:

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

我尝试了各种 CONNECT BY 子句，但没有一个是完全正确的:

-- 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 两个 CONNECT BY 查询不起作用......我没有得到调平。

这是我希望看到的...我可以接受必须区分并用作子查询的结果集。我也接受 ROOTUSER 列中的三个用户 ID 中的任何一个...我只需要知道它们是相同的用户。

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    

想法？

更新

Nicholas，您的代码看起来非常像正确的轨道...目前，lead(user_id) over (partition by global_hr_id) 在 global_hr_id 为空。例如:

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

我经常想将空值视为分区中的单独记录，但我从未找到使忽略空值 起作用的方法。这做了我想要的:

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

...但必须有更好的方法。我还没有能够完成对完整用户数据(大约 40,000 名用户)的查询。 global_hr_id 和 new_user_id 都已编入索引。

更新

查询在大约 750 秒后返回……很长，但易于管理。它返回 93k 条记录，因为我没有从根目录中过滤 2 级命中的好方法 - 你有 start with global_hr_id is null，但不幸的是，情况并非总是如此。我将不得不考虑更多关于如何过滤掉它们的问题。

我之前尝试过添加更复杂的 start with 子句，但我发现它们单独运行 < 1 秒...一起，它们需要 90 分钟>。<

再次感谢您的帮助...正在努力解决这个问题。

Answer 1

您只为一位用户提供了数据样本。多吃一点会更好。不管怎样，让我们​​看看这样的东西。

SQL> with user_desc(USER_ID, NEW_USER_ID,  GLOBAL_HR_ID)as(
  2    select 'EX0T1100',  'EX000005',  null          from dual union all
  3    select 'EX000005',   null,       00126121      from dual union all
  4    select 'GL110456',   null,       00126121      from dual
  5  )
  6  select connect_by_root(user_id) rootuser
  7       , count(connect_by_root(user_id)) over(partition by connect_by_root(user_id)) numroot
  8       , level nodlevel
  9       , connect_by_isleaf
 10       , user_id
 11       , new_user_id
 12       , global_hr_id
 13    from (select user_id
 14               , coalesce(new_user_id, usr) new_user_id1
 15               , new_user_id
 16               , global_hr_id
 17            from ( select user_id
 18                        , new_user_id
 19                        , global_hr_id
 20                        , decode(global_hr_id,null,null,lead(user_id) over (partition by global_hr_id order by user_id)) usr
 21                    from user_desc
 22                 )
 23         )
 24  start with global_hr_id is null
 25  connect by prior new_user_id1 = user_id
 26  ;

结果:

ROOTUSER    NUMROOT   NODLEVEL CONNECT_BY_ISLEAF USER_ID  NEW_USER_ID GLOBAL_HR_ID
-------- ---------- ---------- ----------------- -------- ----------- ------------
EX0T1100          3          1                 0 EX0T1100 EX000005    
EX0T1100          3          2                 0 EX000005                   126121
EX0T1100          3          3                 1 GL110456                   126121