У меня есть запрос от ----. Пытаюсь решить это с помощью 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
Есть два источника пользовательских данных (фиды данных) ... Я должен следить за ошибками в любом из них при обновлении информации.
Сценарии:
- Пользователю дается новый идентификатор пользователя ... Старая запись устанавливается соответствующим образом и деактивируется (обычно это переименование для подрядчиков, которые переходят на полный рабочий день)
- Пользователь уходит и возвращается через некоторое время. HR не может отправить нам старый идентификатор пользователя, чтобы мы могли подключить учетные записи.
- Система облажалась и не установила новый идентификатор пользователя на старую запись.
- Данные могут быть плохими по сотне других причин
Мне нужно знать, что это один и тот же пользователь, и я не могу полагаться на имя или другие поля ... они различаются между совпадающими записями:
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, поэтому у 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 минут>.‹
Еще раз спасибо за вашу помощь ...
