Oracle Self-Join для нескольких возможных совпадений столбцов - CONNECT BY?
У меня есть требование запроса от ----. Пытаясь решить это с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
Есть два источника пользовательских данных (datafeeds) ... Я должен следить за ошибками в любом из них при обновлении информации.
Сценарии:
Мне нужно знать, что один и тот же пользователь, и я могуне полагайтесь на имя или другие поля ... они отличаются между соответствующими записями:
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
поле. Переименование произошло до того, как появились глобальные идентификаторы персонала, поэтомуEX0T1100
Безразлично»не один.EX000005
получил новый идентификатор пользователяGL110456'и эти два связаны только с одним и тем же глобальным идентификатором HR.
Очистка данных нет вариант.
Вопрос до сих пор: я
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))
ОБЪЕДИНЕНИЕ двух запросов CONNECT BY не делаетне работает ... я неполучить уровень.
Вот что я хотел бы увидеть ...все в порядке с набором результатов, которые я должен различать и использовать в качестве подзапроса. Я'Я также согласен с любым из трех идентификаторов пользователей в столбце ROOTUSER ... Мне просто нужно знать, что оните же пользователи.
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
Идеи?
Обновить
Николас, твой код очень похож на правильный трек ... на данный момент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
я часто хотел рассматривать нули как отдельные записи в разделе, но ямы никогда не находили способ сделатьignore nulls
Работа. Это сделал то, что я хотел:
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 секунд ... долго, но управляемо. Возвращает 93 тыс. Записей, потому что я неу вас есть хороший способ фильтрации попаданий 2 уровня из корня - у вас естьstart with global_hr_id is null
но, к сожалению, это неЭто всегда так. Я'Придется еще немного подумать о том, как их отфильтровать.
раньше мы пытались добавить более сложное начало с предложениями, но я обнаружил, что по отдельности они запускаются < 1 секунда ... вместе, они занимают 90 минут>. <
Еще раз спасибо за вашу помощь.