Эффективно MERGE ON составной ключ при выводе удаления на основе ключа переопределения и даты
Я пытаюсь объединить данные из промежуточной таблицы источника в целевую таблицу на основе составного ключа из 3 разных столбцов. У каждой таблицы также есть последняя обновленная дата, которую я хочу принять во внимание, то есть я должен объединять данные только там, где исходные строки более свежие, чем целевые строки.
Один из столбцов в моем составном ключе является своего рода "ключом переопределения" (дайте мне знать, если есть более подходящее слово для этого), что означает, что мы хотим удалить все строки в нашей цели, которые совпадают на ключе переопределения, но не на другом части составного ключа. Я могу сделать это, не проверяя даты, используя CTE в качестве цели и используя оператор удаления WHEN NOT MATCHED BY Target, но мне нужно проверить, что минимальная последняя обновленная дата в цели для этого ключа переопределения меньше минимальной последней обновлена дата в источнике для этого ключа переопределения.
Пример исходной таблицы с Field1 в качестве ключа переопределения и Field1, Field2 и Field3 в качестве составного ключа:
+--------+--------+--------+--------+-------------------------+
| Field1 | Field2 | Field3 | Field4 | LastUpdatedDate |
+--------+--------+--------+--------+-------------------------+
| 1 | 2 | 3 | 8 | 2000-12-31 00:00:00.000 |
+--------+--------+--------+--------+-------------------------+
| 1 | 2 | 4 | 8 | 2000-12-31 00:00:00.000 | --No 1, 2, 5 row
+--------+--------+--------+--------+-------------------------+
| 1 | 2 | 6 | 8 | 2000-12-31 00:00:00.000 | --Skips right to 1, 2, 6
+--------+--------+--------+--------+-------------------------+
| 2 | 1 | 1 | 8 | 2000-12-31 00:00:00.000 |
+--------+--------+--------+--------+-------------------------+
| 2 | 1 | 2 | 8 | 2000-12-31 00:00:00.000 |
+--------+--------+--------+--------+-------------------------+
| 3 | 1 | 1 | 1 | 2000-12-31 00:00:00.000 |
+--------+--------+--------+--------+-------------------------+
| 4 | 1 | 1 | 2 | 2000-12-31 00:00:00.000 |
+--------+--------+--------+--------+-------------------------+
Пример целевой таблицы с Field1 в качестве ключа переопределения и Field1, Field2 и Field3 в качестве составного ключа:
+--------+--------+--------+--------+-------------------------+
| Field1 | Field2 | Field3 | Field4 | LastUpdatedDate |
+--------+--------+--------+--------+-------------------------+
| 1 | 2 | 3 | 7 | 2010-06-15 00:00:00.000 |
+--------+--------+--------+--------+-------------------------+
| 1 | 2 | 4 | 7 | 2010-06-15 00:00:00.000 |
+--------+--------+--------+--------+-------------------------+
| 1 | 2 | 5 | 7 | 2010-06-15 00:00:00.000 | --There is a 1, 2, 5 row
+--------+--------+--------+--------+-------------------------+
| 1 | 2 | 6 | 7 | 2010-06-15 00:00:00.000 |
+--------+--------+--------+--------+-------------------------+
| 2 | 1 | 1 | 7 | 2010-06-15 00:00:00.000 |
+--------+--------+--------+--------+-------------------------+
| 2 | 1 | 2 | 7 | 2010-06-15 00:00:00.000 |
+--------+--------+--------+--------+-------------------------+
Таблица желаемых результатов. Обратите внимание, что хотя строка с Field1, Field2 и Field3 как 1, 2 и 5 отсутствует в исходной таблице, она все еще находится в выходных данных, поскольку минимальная дата последнего обновления для Field1 = 1 в источнике меньше минимальной дата последнего обновления поля 1 = 1 в целевом объекте:
+--------+--------+--------+--------+--------------------+
| Field1 | Field2 | Field3 | Field4 | LastUpdatedDate |
+--------+--------+--------+--------+--------------------+
| 1 | 2 | 3 | 7 | 2010-06-15 0:00:00 |
+--------+--------+--------+--------+--------------------+
| 1 | 2 | 4 | 7 | 2010-06-15 0:00:00 |
+--------+--------+--------+--------+--------------------+
| 1 | 2 | 5 | 7 | 2010-06-15 0:00:00 | --Still here
+--------+--------+--------+--------+--------------------+
| 1 | 2 | 6 | 7 | 2010-06-15 0:00:00 |
+--------+--------+--------+--------+--------------------+
| 2 | 1 | 1 | 7 | 2010-06-15 0:00:00 |
+--------+--------+--------+--------+--------------------+
| 2 | 1 | 2 | 7 | 2010-06-15 0:00:00 |
+--------+--------+--------+--------+--------------------+
| 3 | 1 | 1 | 1 | 2000-12-31 0:00:00 |
+--------+--------+--------+--------+--------------------+
| 4 | 1 | 1 | 2 | 2000-12-31 0:00:00 |
+--------+--------+--------+--------+--------------------+
Текущий оператор слияния с закомментированным битом неправильного синтаксиса предложением delete, чтобы дать вам представление о том, что я пытаюсь сделать:
WITH TargetQuery AS ( SELECT *
FROM TargetTable
WHERE EXISTS ( SELECT 1
FROM SourceTable
WHERE SourceTable.Field1 = TargetTable.Field1 ) )
MERGE TargetQuery AS Target
USING SourceTable AS Source
ON ( Target.Field1 = Source.Field1
AND Target.Field2 = Source.Field2
AND Target.Field3 = Source.Field3 )
WHEN NOT MATCHED BY Target THEN
INSERT ( Field1,
Field2,
Field3,
Field4,
LastUpdatedDate )
VALUES ( Field1,
Field2,
Field3,
Field4,
Source.LastUpdatedDate )
WHEN MATCHED AND Source.LastUpdatedDate >= Target.LastUpdatedDate THEN
UPDATE SET Target.Field4 = Source.Field4
WHEN NOT MATCHED BY Source --AND MIN( Source.LastUpdatedDate) OVER( PARTITION BY Source.Field1 )
-- >= MIN( Target.LastUpdatedDate ) OVER( PARTITION BY Target.Field1 )
THEN DELETE;
Похоже, я могу использовать этот сложный исходный запрос для выполнения работы, но производительность на больших наборах данных ужасна:
WITH SourceQuery AS ( SELECT COALESCE( SourceTable.Field1, TargetTable.Field1 ) Field1,
COALESCE( SourceTable.Field2, TargetTable.Field2 ) Field2,
COALESCE( SourceTable.Field3, TargetTable.Field3 ) Field3,
COALESCE( SourceTable.Field4, TargetTable.Field4 ) Field4,
IsDeleted = CASE WHEN SourceTable.Field1 IS NULL THEN 1
ELSE 0 END,
MinSourceLastUpdateTime,
MinTargetLastUpdateTime = MIN( TargetTable.LastUpdatedDate ) OVER( PARTITION BY TargetTable.Field1 ),
SourceLastUpdateTime = SourceTable.LastUpdatedDate,
TargetLastUpdateTime = TargetTable.LastUpdatedDate
FROM dbo.TargetTable
FULL OUTER JOIN dbo.SourceTable
ON SourceTable.Field1 = TargetTable.Field1
AND SourceTable.Field2 = TargetTable.Field2
AND SourceTable.Field3 = TargetTable.Field3
LEFT OUTER JOIN ( SELECT Field1,
MIN( LastUpdatedDate ) MinSourceLastUpdateTime
FROM dbo.SourceTable
GROUP BY Field1 ) GroupedSource
ON TargetTable.Field1 = GroupedSource.Field1 )
MERGE dbo.TargetTable AS Target
USING SourceQuery AS Source
ON ( Source.Field1 = Target.Field1
AND Source.Field2 = Target.Field2
AND Source.Field3 = Target.Field3 )
WHEN MATCHED AND Source.IsDeleted = 0 AND Source.SourceLastUpdateTime >= Source.TargetLastUpdateTime
THEN UPDATE SET Target.Field4 = Source.Field4
WHEN NOT MATCHED BY Target
THEN INSERT ( Field1,
Field2,
Field3,
Field4 )
Values ( Source.Field1,
Source.Field2,
Source.Field3,
Source.Field4 )
WHEN MATCHED AND Source.IsDeleted = 1 AND Source.MinSourceLastUpdateTime >= Source.MinTargetLastUpdateTime
THEN DELETE;