Как синтезировать атрибут для объединенных таблиц

У меня есть представление, определенное следующим образом:

CREATE VIEW [dbo].[PossiblyMatchingContracts] AS
SELECT 
    C.UniqueID,
    CC.UniqueID AS PossiblyMatchingContracts
FROM  [dbo].AllContracts AS C
    INNER JOIN [dbo].AllContracts AS CC
        ON C.SecondaryMatchCodeFB = CC.SecondaryMatchCodeFB
            OR C.SecondaryMatchCodeLB = CC.SecondaryMatchCodeLB
            OR C.SecondaryMatchCodeBB = CC.SecondaryMatchCodeBB
            OR C.SecondaryMatchCodeLB = CC.SecondaryMatchCodeBB
            OR C.SecondaryMatchCodeBB = CC.SecondaryMatchCodeLB
WHERE C.UniqueID NOT IN
    (
        SELECT UniqueID FROM [dbo].DefinitiveMatches
    )
    AND C.AssociatedUser IS NULL
    AND C.UniqueID <> CC.UniqueID

Что в основном заключается в поиске контрактов, где f.e. первое имя и день рождения совпадают. Это отлично работает. Теперь я хочу добавить синтетический атрибут к каждой строке со значением только из одной строки источника.

Позвольте мне привести вам пример, чтобы сделать его более ясным. Предположим, что у меня есть следующая таблица:

UniqueID  | FirstName | LastName  | Birthday

1         | Peter     | Smith     | 1980-11-04
2         | Peter     | Gray      | 1980-11-04
3         | Peter     | Gray-Smith| 1980-11-04
4         | Frank     | May       | 1985-06-09
5         | Frank-Paul| May       | 1985-06-09
6         | Gina      | Ericson   | 1950-11-04

Полученный вид должен выглядеть следующим образом:

UniqueID | PossiblyMatchingContracts | SyntheticID

1        | 2                         | PeterSmith1980-11-04
1        | 3                         | PeterSmith1980-11-04
2        | 1                         | PeterSmith1980-11-04
2        | 3                         | PeterSmith1980-11-04
3        | 1                         | PeterSmith1980-11-04
3        | 2                         | PeterSmith1980-11-04
4        | 5                         | FrankMay1985-06-09
5        | 4                         | FrankMay1985-06-09
6        | NULL                      | NULL [or] GinaEricson1950-11-04

Обратите внимание, что столбец SyntheticID использует ТОЛЬКО значения из одной из соответствующих строк источника. Неважно, какой из них. Я экспортирую это представление в другое приложение и должен иметь возможность идентифицировать каждую "группу соответствия" впоследствии.

Ясно ли это, что я имею в виду? Любые идеи, как это можно сделать в sql?

Возможно, это помогает немного разобраться в фактическом прецеденте:

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

Целевая система использует многозначные ссылочные атрибуты для хранения этих отношений. Конечной целью является создание пользовательских объектов для этих контрактов. Первый улов состоит в том, что он должен быть только одним пользовательским объектом для нескольких согласующих контрактов. Таким образом, я создаю эти соответствия в представлении. Второй улов заключается в том, что создание пользовательских объектов происходит по рабочим потокам, которые выполняются параллельно для каждого контракта. Чтобы избежать создания нескольких пользовательских объектов для согласования контрактов, каждый рабочий процесс должен проверять, если уже есть соответствующий пользовательский объект или другой рабочий процесс, который собирается создать указанный пользовательский объект. Поскольку механизм рабочего процесса чрезвычайно медленный по сравнению с sql, рабочие процессы не должны повторять весь соответствующий тест. Поэтому идея состоит в том, чтобы позволить рабочему процессу проверять только "синтетический идентификатор".

Ответ 1

Я решил это с помощью многошагового подхода:

Создайте список возможных совпадений 1-го уровня для каждого контракта
Создайте список базовых групп, назначив другую группу для каждый контракт (как если бы они не были связаны ни с кем)
Итерировать список совпадений, обновляя список групп, когда требуется больше контрактов быть добавлен в группу
Рекурсивно создать SyntheticID из списка конечных групп
Результаты вывода

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

1) совпадение распространяется в "каскаде"

Я имею в виду, что если "Питер Смит" сгруппирован с "Петром Грей", это означает, что все Смит и все Серые связаны (если у них одинаковая дата рождения), так что Люк Смит может быть в той же группе Джона Серый

2) Я не понял, что вы имеете в виду под названием "Имя рождения"

Вы говорите, что контракты совпадают с "первым, последним или именем рождения", извините, я итальянский, я думал, что имя рождения и первое были одинаковыми, также в ваших данных нет такого столбца. Может быть, это связано с символом символа между именами?
Когда FirstName является Frank-Paul, это означает, что он должен совпадать с Франком и Павлом?
Когда LastName Gray-Smith означает, что он должен совпадать как с Gray, так и с Smith?

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

Шаг Zero: некоторое объявление и подготовка базовых данных

declare @cli as table (UniqueID int primary key, FirstName varchar(20), LastName varchar(20), Birthday varchar(20))
declare @comb as table (id1 int, id2 int, done bit)
declare @grp as table (ix int identity primary key, grp int, id int, unique (grp,ix))
declare @str_id as table (grp int primary key, SyntheticID varchar(1000))
declare @id1 as int, @g int

;with
t as (
    select *
    from (values
    (1         , 'Peter'     , 'Smith'     , '1980-11-04'),
    (2         , 'Peter'     , 'Gray'      , '1980-11-04'),
    (3         , 'Peter'     , 'Gray-Smith', '1980-11-04'),
    (4         , 'Frank'     , 'May'       , '1985-06-09'),
    (5         , 'Frank-Paul', 'May'       , '1985-06-09'),
    (6         , 'Gina'      , 'Ericson'   , '1950-11-04')
    ) x (UniqueID  , FirstName , LastName  , Birthday)
)
insert into @cli
select * from t

Шаг первый: создайте список возможных совпадений 1-го уровня для каждого контракта

;with
p as(select UniqueID, Birthday, FirstName, LastName from @cli),
m as (
    select p.UniqueID UniqueID1, p.FirstName FirstName1, p.LastName LastName1, p.Birthday Birthday1, pp.UniqueID UniqueID2, pp.FirstName FirstName2, pp.LastName LastName2, pp.Birthday Birthday2
    from p
    join p pp on (pp.Birthday=p.Birthday) and (pp.FirstName = p.FirstName or pp.LastName = p.LastName)
    where p.UniqueID<=pp.UniqueID
)
insert into @comb
select UniqueID1,UniqueID2,0
from m

Шаг второй: создайте список базовых групп

insert into @grp
select ROW_NUMBER() over(order by id1), id1 from @comb where id1=id2

Шаг третий: Итерируйте список совпадений, обновляя список групп Только цикл по контрактам, которые могут иметь возможные совпадения и обновления только при необходимости

set @id1 = 0
while not(@id1 is null) begin
    set @id1 = (select top 1 id1 from @comb where id1<>id2 and done=0)

    if not(@id1 is null) begin

        set @g = (select grp from @grp where [email protected])
        update g set grp= @g
        from @grp g
        inner join @comb c on g.id = c.id2
        where c.id2<>@id1 and [email protected]
        and grp<>@g

        update @comb set done=1 where [email protected]
    end
end

Шаг четвертый: создайте SyntheticID Рекурсивно добавьте ВСЕ (отличные) имена и фамилии группы в SyntheticID.
Я использовал "_" в качестве разделителя для даты рождения, имен и фамилий и "," в качестве разделителя для списка имен, чтобы избежать конфликтов.

;with
c as(
    select c.*, g.grp
    from @cli c
    join @grp g on g.id = c.UniqueID
),
d as (
    select *, row_number() over (partition by g order by t,s) n1, row_number() over (partition by g order by t desc,s desc) n2
    from (
        select distinct c.grp g, 1 t, FirstName s from c
        union 
        select distinct c.grp, 2, LastName from c 
        ) l
),
r as (
    select d.*, cast(CONVERT(VARCHAR(10), t.Birthday, 112) + '_' + s as varchar(1000)) Names, cast(0 as bigint) i1, cast(0 as bigint) i2
    from d
    join @cli t on t.UniqueID=d.g
    where n1=1
    union all
    select d.*, cast(r.names + IIF(r.t<>d.t,'_',',') +  d.s as varchar(1000)), r.n1, r.n2
    from d
    join r on r.g = d.g and r.n1=d.n1-1 
)
insert into @str_id 
select g, Names
from r
where n2=1

Шаг пятый: результаты вывода

select c.UniqueID, case when id2=UniqueID then id1 else id2 end PossibleMatchingContract, s.SyntheticID
from @cli c
left join @comb cb on c.UniqueID in(id1,id2) and id1<>id2
left join @grp g on c.UniqueID = g.id
left join @str_id s on s.grp = g.grp

Вот результаты

UniqueID    PossibleMatchingContract    SyntheticID
1           2                           1980-11-04_Peter_Gray,Gray-Smith,Smith
1           3                           1980-11-04_Peter_Gray,Gray-Smith,Smith
2           1                           1980-11-04_Peter_Gray,Gray-Smith,Smith
2           3                           1980-11-04_Peter_Gray,Gray-Smith,Smith
3           1                           1980-11-04_Peter_Gray,Gray-Smith,Smith
3           2                           1980-11-04_Peter_Gray,Gray-Smith,Smith
4           5                           1985-06-09_Frank,Frank-Paul_May
5           4                           1985-06-09_Frank,Frank-Paul_May
6           NULL                        1950-11-04_Gina_Ericson

Я думаю, что таким образом полученный SyntheticID также должен быть "уникальным" для каждой группы

Ответ 2

Это создает синтетическое значение и легко изменяется в соответствии с вашими потребностями.

DECLARE @T TABLE (
    UniqueID INT
    ,FirstName VARCHAR(200)
    ,LastName  VARCHAR(200)
    ,Birthday DATE
)

INSERT INTO @T(UniqueID,FirstName,LastName,Birthday) SELECT 1,'Peter','Smith','1980-11-04'
INSERT INTO @T(UniqueID,FirstName,LastName,Birthday) SELECT 2,'Peter','Gray','1980-11-04'
INSERT INTO @T(UniqueID,FirstName,LastName,Birthday) SELECT 3,'Peter','Gray-Smith','1980-11-04'
INSERT INTO @T(UniqueID,FirstName,LastName,Birthday) SELECT 4,'Frank','May','1985-06-09'
INSERT INTO @T(UniqueID,FirstName,LastName,Birthday) SELECT 5,'Frank-Paul','May','1985-06-09'
INSERT INTO @T(UniqueID,FirstName,LastName,Birthday) SELECT 6,'Gina','Ericson','1950-11-04'

DECLARE @PossibleMatches TABLE (UniqueID INT,[PossibleMatch] INT,SynKey VARCHAR(2000)
)

INSERT INTO @PossibleMatches
    SELECT t1.UniqueID [UniqueID],t2.UniqueID [Possible Matches],'Ln=' + t1.LastName + ' Fn=' +  + t1.FirstName + ' DoB=' + CONVERT(VARCHAR,t1.Birthday,102) [SynKey]
    FROM @T t1
    INNER JOIN @T t2 ON t1.Birthday=t2.Birthday
        AND t1.FirstName=t2.FirstName
        AND t1.LastName=t2.LastName
        AND t1.UniqueID<>t2.UniqueID

INSERT INTO @PossibleMatches
    SELECT t1.UniqueID [UniqueID],t2.UniqueID [Possible Matches],'Fn=' + t1.FirstName + ' DoB=' + CONVERT(VARCHAR,t1.Birthday,102) [SynKey]
    FROM @T t1
    INNER JOIN @T t2 ON t1.Birthday=t2.Birthday
        AND t1.FirstName=t2.FirstName
        AND t1.UniqueID<>t2.UniqueID

INSERT INTO @PossibleMatches
    SELECT t1.UniqueID,t2.UniqueID,'Ln=' + t1.LastName + ' DoB=' + CONVERT(VARCHAR,t1.Birthday,102) [SynKey]
    FROM @T t1
    INNER JOIN @T t2 ON t1.Birthday=t2.Birthday
        AND t1.LastName=t2.LastName
        AND t1.UniqueID<>t2.UniqueID

INSERT INTO @PossibleMatches
    SELECT t1.UniqueID,pm.UniqueID,'Ln=' + t1.LastName + ' Fn=' +  + t1.FirstName + ' DoB=' + CONVERT(VARCHAR,t1.Birthday,102) [SynKey]
    FROM @T t1
    LEFT JOIN @PossibleMatches pm on pm.UniqueID=t1.UniqueID
    WHERE pm.UniqueID IS NULL

SELECT *
FROM @PossibleMatches
ORDER BY UniqueID,[PossibleMatch]

Ответ 3

Я думаю, что это сработает для вас.

SELECT 
    C.UniqueID,
    CC.UniqueID AS PossiblyMatchingContracts,
    FIRST_VALUE(CC.FirstName+CC.LastName+CC.Birthday) 
          OVER (PARTITION BY C.UniqueID ORDER BY CC.UniqueID) as SyntheticID
FROM 
    [dbo].AllContracts AS C INNER JOIN
    [dbo].AllContracts AS CC ON
        C.SecondaryMatchCodeFB = CC.SecondaryMatchCodeFB OR
        C.SecondaryMatchCodeLB = CC.SecondaryMatchCodeLB OR
        C.SecondaryMatchCodeBB = CC.SecondaryMatchCodeBB OR
        C.SecondaryMatchCodeLB = CC.SecondaryMatchCodeBB OR
        C.SecondaryMatchCodeBB = CC.SecondaryMatchCodeLB
WHERE 
    C.UniqueID NOT IN(
    SELECT UniqueID FROM [dbo].DefinitiveMatches)
AND C.AssociatedUser IS NULL

Ответ 4

Вы можете попробовать следующее:

SELECT 
    C.UniqueID,
    CC.UniqueID AS PossiblyMatchingContracts,
    FIRST_VALUE(CC.FirstName+CC.LastName+CC.Birthday) 
          OVER (PARTITION BY C.UniqueID ORDER BY CC.UniqueID) as SyntheticID
FROM 
    [dbo].AllContracts AS C
INNER JOIN
    [dbo].AllContracts AS CC
ON
        C.SecondaryMatchCodeFB = CC.SecondaryMatchCodeFB
    OR
        C.SecondaryMatchCodeLB = CC.SecondaryMatchCodeLB
    OR
        C.SecondaryMatchCodeBB = CC.SecondaryMatchCodeBB
    OR
        C.SecondaryMatchCodeLB = CC.SecondaryMatchCodeBB
    OR
        C.SecondaryMatchCodeBB = CC.SecondaryMatchCodeLB
WHERE 
    C.UniqueID NOT IN
    (
        SELECT UniqueID FROM [dbo].DefinitiveMatches
    )
AND
    C.AssociatedUser IS NULL

Это создаст одну дополнительную строку (потому что мы оставили C.UniqueID < > CC.UniqueID), но дадим вам хорошую душу.

Ответ 5

Следуя примеру с некоторыми примерами, извлеченными из вашего исходного сообщения. Идея: Сгенерировать все SyntheticID в CTE, запросить все записи с помощью "Возможного макета" и объединить его со всеми записями, которые еще не включены:

DECLARE @t TABLE(
  UniqueID int
 ,FirstName nvarchar(20)
 ,LastName nvarchar(20)
 ,Birthday datetime
)

INSERT INTO @t VALUES (1, 'Peter', 'Smith', '1980-11-04');
INSERT INTO @t VALUES (2, 'Peter', 'Gray', '1980-11-04');
INSERT INTO @t VALUES (3, 'Peter', 'Gray-Smith', '1980-11-04');
INSERT INTO @t VALUES (4, 'Frank', 'May', '1985-06-09');
INSERT INTO @t VALUES (5, 'Frank-Paul', 'May', '1985-06-09');
INSERT INTO @t VALUES (6, 'Gina', 'Ericson', '1950-11-04');


WITH ctePrep AS(
SELECT UniqueID, FirstName, LastName, BirthDay,
       ROW_NUMBER() OVER (PARTITION BY FirstName, BirthDay ORDER BY FirstName, BirthDay) AS k,
       FirstName+LastName+CONVERT(nvarchar(10), Birthday, 126) AS SyntheticID
  FROM @t
),
cteKeys AS(
SELECT FirstName, BirthDay, SyntheticID
  FROM ctePrep
  WHERE k = 1
),
cteFiltered AS(
SELECT 
    C.UniqueID,
    CC.UniqueID AS PossiblyMatchingContracts,
    keys.SyntheticID
FROM @t AS C
JOIN @t AS CC ON C.FirstName = CC.FirstName
              AND C.Birthday = CC.Birthday
JOIN cteKeys AS keys ON keys.FirstName = c.FirstName
                  AND keys.Birthday = c.Birthday
WHERE C.UniqueID <> CC.UniqueID
)
SELECT UniqueID, PossiblyMatchingContracts, SyntheticID
  FROM cteFiltered
UNION ALL
SELECT UniqueID, NULL, FirstName+LastName+CONVERT(nvarchar(10), Birthday, 126) AS SyntheticID
  FROM @t
  WHERE UniqueID NOT IN (SELECT UniqueID FROM cteFiltered)

Надеюсь, это поможет. Результат выглядел ОК для меня:

UniqueID    PossiblyMatchingContracts   SyntheticID
---------------------------------------------------------------
2           1                           PeterSmith1980-11-04
3           1                           PeterSmith1980-11-04
1           2                           PeterSmith1980-11-04
3           2                           PeterSmith1980-11-04
1           3                           PeterSmith1980-11-04
2           3                           PeterSmith1980-11-04
4           NULL                        FrankMay1985-06-09
5           NULL                        Frank-PaulMay1985-06-09
6           NULL                        GinaEricson1950-11-04

Ответ 6

Протестировано в SSMS, оно отлично работает.:)

--create table structure
create table #temp
(
    uniqueID int,
    firstname varchar(15),
    lastname varchar(15),
    birthday date
)

--insert data into the table
insert #temp
select 1, 'peter','smith','1980-11-04'
union all
select 2, 'peter','gray','1980-11-04'
union all
select 3, 'peter','gray-smith','1980-11-04'
union all
select 4, 'frank','may','1985-06-09'
union all
select 5, 'frank-paul','may','1985-06-09'
union all
select 6, 'gina','ericson','1950-11-04'

select * from #temp

--solution is as below

select ab.uniqueID
, PossiblyMatchingContracts
, c.firstname+c.lastname+cast(c.birthday as varchar) as synID
from
(
    select a.uniqueID
            , case 
                when  a.uniqueID < min(b.uniqueID)over(partition by a.uniqueid)
                    then a.uniqueID
                else min(b.uniqueID)over(partition by a.uniqueid)
            end as SmallestID
            , b.uniqueID as PossiblyMatchingContracts
        from #temp a
        left join #temp b
        on (a.firstname = b.firstname OR a.lastname = b.lastname) AND a.birthday = b.birthday AND a.uniqueid <> b.uniqueID
) as ab
left join #temp c
on ab.SmallestID = c.uniqueID

Захват результатов прилагается ниже:

Ответ 7

Скажем, у нас есть следующая таблица (VIEW в вашем случае):

UniqueID    PossiblyMatchingContracts   SyntheticID
1           2                           G1
1           3                           G2
2           1                           G3
2           3                           G4
3           1                           G4
3           4                           G6
4           5                           G7
5           4                           G8
6           NULL                        G9

В вашем случае вы можете установить начальный SyntheticID как строку типа PeterSmith1980-11-04, используя UniqueID для каждой строки. Вот рекурсивный запрос CTE, который делит все строки на несвязанные группы и выбирает MAX(SyntheticId) в текущей группе как новый SyntheticID для всех строк в этой группе.

WITH CTE AS 
(
  SELECT CAST(','+CAST(UniqueID AS Varchar(100)) +','+ CAST(PossiblyMatchingContracts as Varchar(100))+',' as Varchar(MAX)) as GroupCont,
        SyntheticID
  FROM PossiblyMatchingContracts
  UNION ALL
  SELECT CAST(GroupCont+CAST(UniqueID AS Varchar(100)) +','+ CAST(PossiblyMatchingContracts as Varchar(100))+',' AS Varchar(MAX)) as GroupCont,
         pm.SyntheticID 
  FROM CTE 
  JOIN PossiblyMatchingContracts as pm
  ON 
     (
       CTE.GroupCont LIKE '%,'+CAST(pm.UniqueID AS Varchar(100))+',%'
       OR 
       CTE.GroupCont LIKE '%,'+CAST(pm.PossiblyMatchingContracts AS Varchar(100))+',%'
     )
     AND NOT  
     (
       CTE.GroupCont LIKE '%,'+CAST(pm.UniqueID AS Varchar(100))+',%'
       AND 
       CTE.GroupCont LIKE '%,'+CAST(pm.PossiblyMatchingContracts AS Varchar(100))+',%'
     )
)
SELECT pm.UniqueID,
       pm.PossiblyMatchingContracts,  
       ISNULL(
        (SELECT MAX(SyntheticID) FROM CTE WHERE 
        (
           CTE.GroupCont LIKE '%,'+CAST(pm.UniqueID AS Varchar(100))+',%'
           OR 
           CTE.GroupCont LIKE '%,'+CAST(pm.PossiblyMatchingContracts AS Varchar(100))+',%'
        ))
        ,pm.SyntheticID) as SyntheticID
FROM PossiblyMatchingContracts pm