3 способа удалить дубликаты строк в SQL

Введение

Дублирующиеся записи – это “обычная” проблема качества данных, которая встречается в любых базах данных. Если их не обрабатывать должным образом, они исказят точность ваших анализов, нарушат целостность данных и зря потратят ценное место в хранилище.

В этой статье я расскажу, как можно удалить дубликаты строк в SQL. Если у вас есть дубликаты записей в таблице и вы хотите удалить их, вы можете воспользоваться одним из трех методов, о которых я расскажу. Для иллюстрации мы будем использовать таблицу ниже (под названием “Страна”). В этой таблице есть 3 столбца, и в столбцах название страны и столица города есть несколько дублирующихся строк. Что же можно сделать, чтобы удалить дубликаты?

Найдите дублирующиеся строки

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

Предложение HAVING гарантирует, что будут показаны только строки с количеством больше 1. Столбцы в предложении SELECT и GROUP BY указывают на то, что ряд является дубликатом, в данном случае это названиестраны и столицагорода. В данном примере, если строка имеет то же название страны и столицы, что и другая строка, то она является дубликатом. В таблице ниже показаны записи, в которых есть дубликаты. Теперь давайте удалим их!

Метод 1: использование оператора DELETE

Первый способ – использовать оператор DELETE. Мы можем удалить из одной таблицы и присоединиться к той же таблице, где ID меньше, чем ID присоединяемой таблицы. Мы соединяемся по полям, которые используются для определения того, является ли строка дубликатом. В данном случае это названиестраны и столицагорода. А столбец ID – это ваш первичный ключ или уникальный идентификатор строки. Должен быть способ удалить все строки, кроме одной. И если у вас есть поле ID или какой-то первичный ключ, вы можете использовать его здесь.

Если вы выполните приведенный выше запрос, вы увидите, что некоторые строки (дубликаты) удалены. Чтобы проверить это, можно использовать тот же запрос, который мы использовали для поиска дубликатов строк, чтобы посмотреть, остались ли дубликаты в таблице. Результаты должны показать, что дубликатов нет. Но этот метод может не сработать, если у вашей таблицы нет идентификатора или если в операторе DELETE не допускается использование двух таблиц.

Метод 2: добавление уникальных или DISTINCT-записей

Другой способ удаления дублирующихся записей – добавить уникальные записи в новую таблицу и использовать ее для замены старой. При использовании этого метода мы начинаем с создания новой таблицы. Мы создаем новую таблицу в виде оператора select, и этот оператор select будет извлекать отдельные записи из другой таблицы. В данном случае мы хотим вставить только одну строку для каждой из записей, и для этого нам нужно использовать GROUP BY.

Другой подход заключается в использовании ключевого слова DISTINCT в операторе select.

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

Метод 3: использование функции ROW_NUMBER()

Третий способ – использовать функцию ROW_NUMBER(). Вы можете использовать функцию ROW_NUMBER() внутри подзапроса, чтобы найти номер строки и удалить все строки, кроме первой.

Приведенный выше запрос представляет собой оператор DELETE, который удаляет дубликаты записей из таблицы country, ID которых найден в подзапросе. В подзапросе для обнаружения дубликатов можно использовать функцию ROW_NUMBER(), а в предложении PARTITION BY нужно указать столбцы, которые делают их дубликатами, в нашем примере это названиестраны и столицагорода. Выполнив приведенный выше запрос, вы удалите все дублирующиеся строки в таблице.

Заключение

Вот как можно удалить дубликаты записей в SQL. Безусловно, есть и другие методы, которые вы можете использовать, но для меня лучшим подходом является метод 3, использующий функцию ROW_NUMBER(). Помните, что всегда нужно проверять наличие дубликатов записей в конвейере данных, делать резервные копии перед модификацией таблиц и удалять их по мере необходимости.

Спасибо за чтение и давайте подключаться!