Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Слияние записей  [new]
A_Mozolini
Member

Откуда:
Сообщений: 15
Добрый день.

Ситуация, на первый взгляд - простая, но правильное граммотное решение я так и не могу найти.
Есть таблица клиентов с 4 атрибутами:
-Некий суррогатный ID (пусть он будет IDENTITY)
-Сконкотинированное ФИО+ДР
-Сконкотинированные серия+номер паспорта
-Сконкотинированные Предыдущая ФИО+ДР

Задача слить одинаковых клиентов под 1 ID.
Критерий одинаковости:
-ФИО+ДР равны
-Паспорта равны
-Предудщая ФИО+ДР равны.

Причем любое из условий может выполняться независимо от второго.
Иными словами:
1 ФИО1 ПАСП1 NULL
2 ФИО1 ПАСП2 NULL
3 ФИО2 ПАСП1 NULL
4 ФИО3 ПАСП4 ФИО1
5 ФИО2 ПАСП5 NULL
6 ФИО4 ПАСП5 NULL
7 ФИО5 ПАСП6 ФИО4

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

В Таблице около 15 миллионов записей.

В чем собственно трабл:
1. Основной трабл в производительности. UPDATE с 3-мя or превращается в 85% nested loops от которого невозможно избавиться с помощью индексов. К тому же такой запрос очень много ресурсов жрет у сервера и не отрабатывает даже за ночь.
2. Пробовал разбить все на отдельные этапы, т.е сначала проапдейтить только по фио,потом по паспорту, а потом либо циклом либо cte (которым тоже кстати не получается). Тут все в логику уперлось.
3. Самый простой вариант курсор. Но его не имеет смысла смотреть из-за производительности. По расчетам таблица в 12 миллионов записей будет апдейтиться около 4 суток!!! Это не вариант вообще.


Сабж. Если есть у кого-то идеи буду признателен.
2 апр 13, 21:19    [14128463]     Ответить | Цитировать Сообщить модератору
 Re: Слияние записей  [new]
Crimzic
Member

Откуда: Sydney
Сообщений: 59
Непонятно, зачем нужно обновлять ID. Может лучше оставить его уникальным primary key, добавить новое поле Parent_ID и хранить связи в нём?
Если я правильно понял условие, то можно создать три некластерных индекса по FIO, Passp, FIO_Old и разбить ваш запрос на 3 простых, соответствующие трём критериям одинаковости. Скорее всего, тогда вы получите index seek.
Примерный вид запросов такой:
UPDATE c
SET Parent_ID = (SELECT TOP 1 ID FROM Clients c1 WHERE c1.FIO = c.FIO AND c1.ID < c.ID ORDER BY ID)
FROM Clients c

Также можно попробовать запускать это в цикле пачками по TOP (100..N) WHILE @@ROWCOUNT > 0 чтобы избежать длительной блокировки таблицы.
3 апр 13, 05:10    [14129034]     Ответить | Цитировать Сообщить модератору
 Re: Слияние записей  [new]
aleks2
Guest
A_Mozolini
В чем собственно трабл:
1. Основной трабл в производительности. UPDATE с 3-мя or превращается в 85% nested loops от которого невозможно избавиться с помощью индексов. К тому же такой запрос очень много ресурсов жрет у сервера и не отрабатывает даже за ночь.
2. Пробовал разбить все на отдельные этапы, т.е сначала проапдейтить только по фио,потом по паспорту, а потом либо циклом либо cte (которым тоже кстати не получается). Тут все в логику уперлось.

Сабж. Если есть у кого-то идеи буду признателен.


1. Из 1 UPDATE с 3-мя or сделать 3 UPDATE без or.
2. Вы не дали опредления таблицы.
3.

-- исходная таблица
if object_id('tempdb..#Есть_таблица_клиентов') is not null
  drop table #Есть_таблица_клиентов;
create table #Есть_таблица_клиентов (ID int primary key clustered
                  , [ФИО+ДР] nvarchar(256)
                  , [серия+номер паспорта] nvarchar(256)
                  , [Предыдущая ФИО+ДР] nvarchar(256)
-- под это дело надо три индекса на #Есть_таблица_клиентов
-- (ID, [ФИО+ДР])
-- (ID, [серия+номер паспорта])
-- (ID, [Предыдущая ФИО+ДР])
                  , unique(ID, [ФИО+ДР])
                  , unique(ID, [серия+номер паспорта])
                  , unique(ID, [Предыдущая ФИО+ДР])
                 );

insert #Есть_таблица_клиентов
  select 1, 'fio 1', '123456', 'old fio 1'
  union all
  select 2, 'fio 1', '1234567', 'old fio 11'
  union all
  select 3, 'fio 11', '123456', 'old fio 12'
  union all
  select 5, 'fio 1', '123456', 'old fio 1'
  union all
  select 4, 'fio 2', '78910', 'old fio 2'


if object_id('tempdb..#t') is not null
  drop table #t;
-- проще всего сделать НОВУЮ таблицу
-- уникальные клиенты 
create table #t (ID int primary key clustered
                  , [ФИО+ДР] nvarchar(256)
                  , [серия+номер паспорта] nvarchar(256)
                  , [Предыдущая ФИО+ДР] nvarchar(256)
                 )
create UNIQUE index t_1 on #t ([ФИО+ДР]) with IGNORE_DUP_KEY;
create UNIQUE index t_2 on #t ([серия+номер паспорта]) with IGNORE_DUP_KEY;
create UNIQUE index t_3 on #t ([Предыдущая ФИО+ДР]) with IGNORE_DUP_KEY;

-- все неуникальное будет отброшено
insert #t
  select ID, [ФИО+ДР], [серия+номер паспорта], [Предыдущая ФИО+ДР]
    from #Есть_таблица_клиентов T
    order by ID ASC;

select * from #t;

-- теперь колбасим дубли в #tt
if object_id('tempdb..#tt') is not null
  drop table #tt;
create table #tt (ID int , subID int);
create UNIQUE index tt_1 on #tt (ID, subID) with IGNORE_DUP_KEY;

insert #tt
select x.ID, y.ID from #t x inner join #Есть_таблица_клиентов y on y.ID > x.ID and y.[ФИО+ДР] = x.[ФИО+ДР];
insert #tt
select x.ID, y.ID from #t x inner join #Есть_таблица_клиентов y on y.ID > x.ID and y.[серия+номер паспорта] = x.[серия+номер паспорта];
insert #tt
select x.ID, y.ID from #t x inner join #Есть_таблица_клиентов y on y.ID > x.ID and y.[Предыдущая ФИО+ДР] = x.[Предыдущая ФИО+ДР];

select * from #tt
3 апр 13, 06:25    [14129062]     Ответить | Цитировать Сообщить модератору
 Re: Слияние записей  [new]
A_Mozolini
Member

Откуда:
Сообщений: 15
автор
Непонятно, зачем нужно обновлять ID. Может лучше оставить его уникальным primary key, добавить новое поле Parent_ID и хранить связи в нём?
Если я правильно понял условие, то можно создать три некластерных индекса по FIO, Passp, FIO_Old и разбить ваш запрос на 3 простых, соответствующие трём критериям одинаковости.

Ну да, я естественно первоначальный id не трогал.
Я пробовал так. Разбивал на отдельные апдейты. Все уперлось все равно в логику построения запроса.

автор
-- исходная таблица
if object_id('tempdb..#Есть_таблица_клиентов') is not null
drop table #Есть_таблица_клиентов;
create table #Есть_таблица_клиентов (ID int primary key clustered
, [ФИО+ДР] nvarchar(256)
, [серия+номер паспорта] nvarchar(256)
, [Предыдущая ФИО+ДР] nvarchar(256)
-- под это дело надо три индекса на #Есть_таблица_клиентов
-- (ID, [ФИО+ДР])
-- (ID, [серия+номер паспорта])
-- (ID, [Предыдущая ФИО+ДР])
, unique(ID, [ФИО+ДР])
, unique(ID, [серия+номер паспорта])
, unique(ID, [Предыдущая ФИО+ДР])
);

insert #Есть_таблица_клиентов
select 1, 'fio 1', '123456', 'old fio 1'
union all
select 2, 'fio 1', '1234567', 'old fio 11'
union all
select 3, 'fio 11', '123456', 'old fio 12'
union all
select 5, 'fio 1', '123456', 'old fio 1'
union all
select 4, 'fio 2', '78910', 'old fio 2'


if object_id('tempdb..#t') is not null
drop table #t;
-- проще всего сделать НОВУЮ таблицу
-- уникальные клиенты
create table #t (ID int primary key clustered
, [ФИО+ДР] nvarchar(256)
, [серия+номер паспорта] nvarchar(256)
, [Предыдущая ФИО+ДР] nvarchar(256)
)
create UNIQUE index t_1 on #t ([ФИО+ДР]) with IGNORE_DUP_KEY;
create UNIQUE index t_2 on #t ([серия+номер паспорта]) with IGNORE_DUP_KEY;
create UNIQUE index t_3 on #t ([Предыдущая ФИО+ДР]) with IGNORE_DUP_KEY;

-- все неуникальное будет отброшено
insert #t
select ID, [ФИО+ДР], [серия+номер паспорта], [Предыдущая ФИО+ДР]
from #Есть_таблица_клиентов T
order by ID ASC;

select * from #t;

-- теперь колбасим дубли в #tt
if object_id('tempdb..#tt') is not null
drop table #tt;
create table #tt (ID int , subID int);
create UNIQUE index tt_1 on #tt (ID, subID) with IGNORE_DUP_KEY;

insert #tt
select x.ID, y.ID from #t x inner join #Есть_таблица_клиентов y on y.ID > x.ID and y.[ФИО+ДР] = x.[ФИО+ДР];
insert #tt
select x.ID, y.ID from #t x inner join #Есть_таблица_клиентов y on y.ID > x.ID and y.[серия+номер паспорта] = x.[серия+номер паспорта];
insert #tt
select x.ID, y.ID from #t x inner join #Есть_таблица_клиентов y on y.ID > x.ID and y.[Предыдущая ФИО+ДР] = x.[Предыдущая ФИО+ДР];

select * from #tt



Спасибо за идею, но логика немного неверная и данный способ все равно не пройдет.

1.[Предыдущая ФИО+ДР] должно быть равно [ФИО+ДР]. Я немного неправильно написал в условии.
Какой смысл сравнивать предыдущую фамилию с предыдущей.
2.Такой способ прокатит если есть ближайшие связи. Т.е.
ФИО1 ПАСП1
ФИО1 ПАСП2
ФИО3 ПАСП1
при добавлении еще одного клиента
ФИО4 ПАСП2
Который связан с первым через второго он не отберется, поскольку ни фио ни паспорт его не равны с первым.
Если записать данные в таблицу с клиентами из моего первого поста логика все равно будет неверной.
3. Я объясню смысл такой "нетривиальной" логики.
В базе ведется историчность изменения данных по клиенту.
На одного клиента с одним id_natural(реальный id из базы с клиентами) может приходится несколько записей. Ниже приведен пример записей по одному клиенту.
1 Иванов Иван Иванович
1 Иванов Ван Ванович
1 Ванов Иван Иванович

Если есть хотя бы одна запись в базе у которой совпадает одна из вариаций измененных ФИО с другим id_natural, например, то я считаю что это один человек. Т.е клиенты не обязательно могут быть связаны через одну уникальную запись.

Я так пытался сделать:

Моя таблица выглядит следущем образом:

1.ID_SEQ (УНикальный суррогатный ID)
2.СLIENT_ID_NATURAL (Реальный ID клиента)
3.CLIENT_ID_NEW1 (3-7 результаты апдейтов)
4.CLIENT_ID_NEW2
5.CLIENT_ID_NEW3
6.CLIENT_ID_NEW4
7.CLIENT_ID_NEW5
8.str1 (ФИО+др)
9.str2 (Паспорт)
10.Str3 (предыдущая ФИО)

alter table  REPEATED_CLIENTS_TEST rebuild with (data_compression=page)

create nonclustered index ix_1 on REPEATED_CLIENTS_TEST (CLIENT_ID_NATURAL,ID_SEQ)

--- апдейтим все записи суррогатным ID имеющие одинаковые CLIENT_ID_NATURAL
update dbo.REPEATED_CLIENTS_TEST set client_id_new1 = X.ID_SEQ

from dbo.REPEATED_CLIENTS_TEST t join 
(
select CLIENT_ID_NATURAL,ID_SEQ from
(
	select CLIENT_ID_NATURAL, ID_SEQ,
	ROW_NUMBER() over (partition by CLIENT_ID_NATURAL order by ID_SEQ) nm
	from dbo.REPEATED_CLIENTS_TEST
	) q where nm=1
) x on t.CLIENT_ID_NATURAL=x.CLIENT_ID_NATURAL


create nonclustered index ix_1 on REPEATED_CLIENTS_TEST (str1,client_id_new1) 
--апдейтим клиентов с одинаковой ФИО
update dbo.REPEATED_CLIENTS_TEST set client_id_new2 = X.client_id_new1
from dbo.REPEATED_CLIENTS_TEST t join 
(
select STR1,client_id_new1 from
(
	select STR1, client_id_new1,
	ROW_NUMBER() over (partition by STR1 order by client_id_new1) nm
	from dbo.REPEATED_CLIENTS_TEST
	) q where nm=1
) x on t.STR1=x.STR1 /*and t.client_id_new1!=x.client_id_new1*/

create nonclustered index ix_2 on REPEATED_CLIENTS_TEST (STR2, client_id_new2) 

update dbo.REPEATED_CLIENTS_TEST set client_id_new3 = X.client_id_new2
from dbo.REPEATED_CLIENTS_TEST t join 
(
select STR2,client_id_new2from
(
	select STR2, client_id_new2,
	ROW_NUMBER() over (partition by STR2 order by client_id_new2) nm
	from dbo.REPEATED_CLIENTS_TEST
	) q where nm=1
) x on t.STR2=x.STR2 


/*вот тут начинаются проблемы с построением правильной логики
изначально апдейт выглядел вот так
*/

select *
into #tt
from dbo.REPEATED_CLIENTS_TEST t 
where client_id_new2 <> client_id_new3
and client_id_new2 in (select client_id_new2 from dbo.REPEATED_CLIENTS_TEST where client_id_new2 = client_id_new3) 

update dbo.REPEATED_CLIENTS_TEST set client_id_new4 = t.client_id_new3
from #tt t join dbo.REPEATED_CLIENTS_TEST c on t.client_id_new2 = c.client_id_new2
update dbo.REPEATED_CLIENTS_TEST set client_id_new4 = t.client_id_new3
from #tt t join dbo.REPEATED_CLIENTS_TEST c on t.client_id_new3 = c.client_id_new3


/* естественно это неправильно посколько таким способом далеко не все записи апдейтятся.
Идея правильная и частично работает, опять же если есть ближайшие связи. Если появляется связь "через" какого то клиента, то все уже идет наперекосяк
 в идеале, как это вижу я, здесь должна быть cte которая находила бы мне все дочерние записи, но как это реализовать я не знаю
были попытки написать что-то похожее на это

with CTE(client_id_new2,client_id_new3)as
(select w.client_id_new2,w.client_id_new3 from dbo.REPEATED_CLIENTS_TEST w
where client_id_new2=client_id_new3
union all
select  q.client_id_new2,q.client_id_new3 from dbo.REPEATED_CLIENTS_TEST q join CTE 
on Q.client_id_new2!=CTE.client_id_new3
--where q.client_id_new1!=client_id_new2
--and exists (select client_id_new1 from dbo.REPEATED_CLIENTS_TEST q 
--where q.client_id_new1=cte.client_id_new2)
)


select * from cte


но безуспешно.
 */
3 апр 13, 10:15    [14129524]     Ответить | Цитировать Сообщить модератору
 Re: Слияние записей  [new]
aleks2
Guest
A_Mozolini
1.[Предыдущая ФИО+ДР] должно быть равно [ФИО+ДР]. Я немного неправильно написал в условии.
Какой смысл сравнивать предыдущую фамилию с предыдущей.
2.Такой способ прокатит если есть ближайшие связи. Т.е.
ФИО1 ПАСП1
ФИО1 ПАСП2
ФИО3 ПАСП1
при добавлении еще одного клиента
ФИО4 ПАСП2
Который связан с первым через второго он не отберется, поскольку ни фио ни паспорт его не равны с первым.
Если записать данные в таблицу с клиентами из моего первого поста логика все равно будет неверной.


1. Ну, блин, я верю, что вы сможете поправить пример.

2. Про цикл while слыхали? Это и надо применить.

3. Готового вам никто ничего не принесет.
3 апр 13, 10:55    [14129781]     Ответить | Цитировать Сообщить модератору
 Re: Слияние записей  [new]
aleks2
Guest
A_Mozolini
но безуспешно.

Хе-хе? Такой монстр - для столь простой задачи.

1. Отбираем ВСЕ связанные пары ID1, ID2. По любому из ваших критериев. С ограничением ID1 < ID2.
2. Генерируем следующий уровень связей (ID1, ID2) = (ID2, ID3) -> (ID1, ID3).
3. Повторяем пункт 2., пока есть чего генерировать.
3. Все.
3 апр 13, 11:24    [14129963]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить