Вы когда-нибудь сталкивались с ситуацией, когда вам нужно удалить дублирующиеся строки из таблицы SQL Server, которая не имеет первичного ключа или уникального идентификатора? В этой статье мы рассмотрим, как нам может помочь оператор OVER() решить эту проблему.

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

К сожалению, из-за ошибки в расписании SQL Agent некоторые данные были импортированы дважды, что привело к появлению дублирующихся строк. Это повлияло на точность статистического анализа. Как же мы можем удалить эти дубликаты без первичного ключа?

Решение заключается в использовании оператора OVER() в сочетании с функцией ROW_NUMBER(). Давайте разберемся в процессе:

1. Выгрузите данные во временную таблицу, присвоив каждой строке номер строки с помощью оператора OVER().
2. Удалите все строки, где номер строки больше 1.
3. Очистите целевую таблицу.
4. Вставьте строки из временной таблицы в целевую таблицу.

Вот пример скрипта:

USE DuplicateImports;
DECLARE @TargetRowCount INT, @ActualRowCount INT;

-- Определите, сколько строк мы ожидаем иметь в конце импорта
-- Поскольку каждая строка была импортирована дважды, мы можем просто разделить количество строк на 2
IF EXISTS (SELECT 1 FROM dbo.InsurancePolicy)
BEGIN;
SELECT @TargetRowCount = COUNT(1) / 2 FROM dbo.InsurancePolicy;
END;

-- Выгрузите данные во временную таблицу, присвоив номер строки для идентификации дубликатов
SELECT ROW_NUMBER() OVER (PARTITION BY PolicyId, FirstName, Surname, PolicyStartDate, PolicyEndDate
ORDER BY PolicyId) AS RowNumber, PolicyId, FirstName, Surname, PolicyStartDate, PolicyEndDate
INTO #allpolicy
FROM dbo.InsurancePolicy;

-- Удалите дубликаты из временной таблицы
DELETE FROM #allpolicy WHERE RowNumber > 1;

IF (@TargetRowCount > 0 AND EXISTS(SELECT 1 FROM #allpolicy))
BEGIN;
BEGIN TRANSACTION;
TRUNCATE TABLE dbo.InsurancePolicy;
INSERT INTO dbo.InsurancePolicy
(PolicyId, FirstName, Surname, PolicyStartDate, PolicyEndDate)
SELECT PolicyId, FirstName, Surname, PolicyStartDate, PolicyEndDate FROM #allpolicy;
SELECT @ActualRowCount = COUNT(1) FROM dbo.InsurancePolicy;

IF (@TargetRowCount = @ActualRowCount)
BEGIN;
COMMIT TRANSACTION;
PRINT 'Обновление успешно. Дубликаты удалены.';
END;
ELSE
BEGIN;
ROLLBACK TRANSACTION;
PRINT 'Ошибка обновления - количество строк в целевой таблице не соответствует фактическому количеству строк после обработки.';
END;

DROP TABLE #allpolicy;
END;

Давайте разберемся в скрипте:

Мы начинаем с объявления двух переменных, @TargetRowCount и @ActualRowCount, для хранения ожидаемого и фактического количества строк соответственно. Мы вычисляем ожидаемое количество строк, разделив общее количество строк на 2, поскольку каждая строка была импортирована дважды.

Затем мы выгружаем данные во временную таблицу с именем #allpolicy с использованием оператора ROW_NUMBER() OVER (PARTITION BY ...). Это присваивает уникальный номер строки каждой строке, разделенной по указанным столбцам. Дубликаты будут иметь номера строк больше 1.

Затем мы удаляем все строки из временной таблицы, где номер строки больше 1, эффективно удаляя дубликаты.

Если есть данные для обработки, мы начинаем транзакцию, очищаем целевую таблицу и вставляем строки из временной таблицы в целевую таблицу. Мы также сравниваем ожидаемое и фактическое количество строк, чтобы убедиться, что операция прошла успешно.

Используя оператор OVER() с функцией ROW_NUMBER(), мы легко можем идентифицировать и удалить дублирующиеся строки из таблицы без первичного ключа или уникального идентификатора.

Оператор OVER() – это мощный инструмент в SQL Server, который предоставляет элегантные решения для различных проблем. Он позволяет нам создавать мини-группы данных с использованием компонента PARTITION BY, что помогает нам выделять дубликаты и удалять их. Потратите некоторое время на изучение возможностей оператора OVER() в документации SQL Server – это может пригодиться в ваших будущих проектах!