Найти объединенные данные (мое решение)

На прошлой неделе публиковала задачку по SQL, которая попалась мне на работе — «Найти объединенные данные». Хочу рассказать, как я ее решала.

Перед тем, как строить догадки, я посмотрела на парочку идентификаторов «вживую» и сделала вывод, что часть из них окажется неактуальными. Тогда мне казалось, что все, что от меня надо — доказать свою теорию числами. Пффф!

Но это не такая простая задачка, как оказалось. Сначала я пошла втупую — скопировала первую колонку и попробовала сделать запрос in (1, 2, 3...). Выяснилось, что внутри IN может быть только 1000 параметров, а у меня их 82 000. Облом-с.

Но ведь можно работать с таблицей! То есть главное — запихать наши 82 000 в некую темповую табличку с данными. Тут следующий облом — я умею через Workhbench, а на сервере его нету. Пошла гуглить, как без него, нашла только load data, расстроилась. Уже думала попросить админов мне файлик на сервер закинуть, как коллега услышал про мои страдания и показал, как загружать файлы через sql developer (см пункт 2).

Итак, ура, данные загрузили! Что дальше?

Подготовка тестовых данных

А что, вы думали, что я сразу понесусь тестировать на миллионах данных? Нет, конечно, сначала скрипты надо обкатать на небольшом объеме. А то так запустишь селект, который 2 часа работает, а потом поймешь, что результат невалидный, так как ты про is not null забыл, или другое условие.

Так что сначала я обратилась к своей небольшой тестовой базе и надергала оттуда разных идентификаторов, которые покрывают самые разные ситуации. Номера групп — фактически номер теста.

Сначала начала рисовать табличку, а потом поняла, что мне проще сразу в виде запросов писать тест-кейсы. А если вам интересно само задание, то вернитесь к условию и подумайте, какие тесты вы бы накидали для проверки.

ID_PARTY	GROUPING	Комментарий
3048	1	Уже объединены. Исходный 3048. Его ИД больше ИД золотого
3041	1
5162	2	Уже объединены. Исходный 5155. Его ИД меньше ИД золотого
5155	2
...	...	...

Вот что получилось у меня:

*************************************************************

CREATE TABLE TMP$DUPLICATES (id_party integer, grouping nvarchar2(20)) NOLOGGING;


-- УЖЕ И ТАК ОБЪЕДИНЕНЫ

-- Объединены, ИД исходника 3048 > ИД итогового
insert into TMP$DUPLICATES (id_party, grouping) values (3048, 'A1');
insert into TMP$DUPLICATES (id_party, grouping) values (3041, 'A1');

-- Объединены, ИД исходника 5155 < ИД итогового
insert into TMP$DUPLICATES (id_party, grouping) values (5162, 'B2');
insert into TMP$DUPLICATES (id_party, grouping) values (5155, 'B2');

-- Оба влиты в 6050, оба исходных в одной итоговой
insert into TMP$DUPLICATES (id_party, grouping) values (6044, 'C3');
insert into TMP$DUPLICATES (id_party, grouping) values (6049, 'C3');


-- НА РУЧНОЙ РАЗБОР

-- Были объединены, но связь закрылась (cancelled is not null). Исходный 5180 больше никуда не вливался
insert into TMP$DUPLICATES (id_party, grouping) values (5180, '4');
insert into TMP$DUPLICATES (id_party, grouping) values (2028, '4');

-- Были объединены, но связь закрылась (cancelled is not null). Исходный 14054 потом влит в другого
insert into TMP$DUPLICATES (id_party, grouping) values (14054, '5');
insert into TMP$DUPLICATES (id_party, grouping) values (14065, '5');

-- Оба исходные, но влиты в разные карточки (10047 - 10050, 3024 — 15088)
insert into TMP$DUPLICATES (id_party, grouping) values (10047, '6');
insert into TMP$DUPLICATES (id_party, grouping) values (3024, '6');

select * from party where merged_status = 0;

-- Один никуда не вливался, второй влит (статус 1)
insert into TMP$DUPLICATES (id_party, grouping) values (1024, '7');
insert into TMP$DUPLICATES (id_party, grouping) values (3037, '7');

-- Один никуда не вливался, второй золотой (статус 2)
insert into TMP$DUPLICATES (id_party, grouping) values (4029, '8');
insert into TMP$DUPLICATES (id_party, grouping) values (14097, '8');

commit;

*************************************************************

Анализ

Так как в количестве таблиц меня никто не ограничивает, я решаю разбить одну TMP$DUPLICATES на три разные, по Merged_status карточек. Иначе довольно сложные селекты получатся, когда я не знаю, с какой стороны исходный, с какой объединенный, у кого ИД меньше, у кого больше итд. В общем, прикинув масштабы селектов таблицы саму на себя, разбиваю

*************************************************************

-- Разобьем на таблички поменьше по разным merged_status
CREATE TABLE TMP$DUPLICATES_0 (id_party integer, grouping nvarchar2(20)) NOLOGGING;
CREATE TABLE TMP$DUPLICATES_1 (id_party integer, grouping nvarchar2(20)) NOLOGGING;
CREATE TABLE TMP$DUPLICATES_2 (id_party integer, grouping nvarchar2(20)) NOLOGGING;

-- Переносим данные

-- Этих придется анализировать вручную, так как они еще не объединены. Надо понять почему
insert into TMP$DUPLICATES_0
select t.id_party, t.grouping from TMP$DUPLICATES t join physical_party pp on t.id_party = pp.id_party
where pp.version = 0 and pp.merged_status = 0;
commit;

select * from TMP$DUPLICATES_0;
select count(*) from TMP$DUPLICATES_0;
-- 42 022

-- Исходники
insert into TMP$DUPLICATES_1
select t.id_party, t.grouping from TMP$DUPLICATES t join physical_party pp on t.id_party = pp.id_party
where pp.version = 0 and pp.merged_status = 1;
commit;

select count(*) from TMP$DUPLICATES_1;
-- 4 214

-- Итоговые
insert into TMP$DUPLICATES_2
select t.id_party, t.grouping from TMP$DUPLICATES t join physical_party pp on t.id_party = pp.id_party
where pp.version = 0 and pp.merged_status = 2;
commit;

select count(*) from TMP$DUPLICATES_2;
-- 35 926


-- Расчеты

-- Сколько кейсов, когда записи уже объединены?
select count(*) from TMP$DUPLICATES_1 t1
join TMP$DUPLICATES_2 t2 on t1.grouping = t2.grouping;
-- 2253

-- А те, которые друг в друга?
select count(*) from TMP$DUPLICATES_1 t1
join TMP$DUPLICATES_2 t2 on t1.grouping = t2.grouping
join merged m on t1.id_party = m.id_party
where m.hid_final_scion = t2.id_party and m.canceled is null;

select * from TMP$DUPLICATES_1 t1
join TMP$DUPLICATES_2 t2 on t1.grouping = t2.grouping
join merged m on t1.id_party = m.id_party
where m.hid_final_scion = t2.id_party and m.canceled is null;

-- А те, которые НЕ друг в друга?
select count(*) from TMP$DUPLICATES_1 t1
join TMP$DUPLICATES_2 t2 on t1.grouping = t2.grouping
join merged m on t1.id_party <> m.id_party
where m.hid_final_scion = t2.id_party and m.canceled is null;

-- Сколько было закрыто?
select count(*) from TMP$DUPLICATES_1 t1
join TMP$DUPLICATES_2 t2 on t1.grouping = t2.grouping
join merged m on t1.id_party = m.id_party
where m.hid_final_scion = t2.id_party and m.canceled is not null;

-- Сколько случаев, когда обе записи — исходные?
select grouping, count(grouping) from TMP$DUPLICATES_1
group by grouping
having count(grouping) > 1;

*************************************************************

В итоге я то думала, что просто подтвержу теорию "все уже давно работает, они объединены", ан нет, большинство пар осталось на ручной разбор. А вот тут в анализе данных начинается самое интересное, когда отпала первая теория и надо думать дальше.

А дальше строим новые теории заговора! Делаем селект из таблицы, берем любую группу, находим ее по идентификаторам, изучаем, что да как. Делаем предположение. Например, опечатка в отчестве или изменение фамилии (Назина — Киселева). Смотрим, сколько таких? Потом делаем выборку по оставшимся, смотрим еще пару примеров. Снова строим теории и снова селектим...

Но полное решение оставим за кадром, так как там подключаются новые таблички и новые колонки, это уже наша специфика. Исходно в блоге я ставила задачу «как бы найти тех, кто уже объединен». И вот мое решение чуть выше — заносим данные в оракл, разбиваем на три разные таблички и потом сравниваем их между собой, джойня по совпадению grouping. Очень удобно получается! ツ