Oracle удаляет дубликаты в таблице
Обновлено: 20.11.2024
Постановка задачи.
Вы хотите найти и удалить дубликаты из таблицы в Oracle.
Решение. Мы можем использовать внутреннее значение ROWID Oracle для уникальной идентификации строк в таблице. Пример синтаксиса для достижения этого приведен ниже.
Чтобы продемонстрировать использование, мы начнем с создания образцов данных.
Пример
Просматривая данные, которые мы только что создали.
Пример
| player_rank | player_name |
| 4 | ЭНДИ МЮРРЕЙ |
| 3 | НОВАК ДЖОКОВИЧ |
| 3 | НОВАК ДЖОКОВИЧ |
| 2 | РАФАЭЛЬ НАДАЛЬ |
| 2 | РАФАЭЛЬ НАДАЛЬ |
| 1 | РОДЖЕР ФЕДЕРЕР |
| 1 | РОДЖЕР ФЕДЕРЕР< br /> |
Итак, мы вставили 3 дубликата, которые хотели удалить. прежде чем мы продолжим и напишем оператор Delete, давайте разберемся во внутреннем запросе с ROWID.
Пример
Я намеренно добавил столбцы player_rank и player_name в этот самый внутренний подзапрос, чтобы сделать логику понятной. В идеале самый внутренний подзапрос можно было бы написать без них с тем же эффектом. Если мы выполним только этот самый внутренний запрос с дополнительными столбцами, выбранными для ясности, мы увидим следующие результаты.
| ранг_игрока | имя_игрока | rowid | rnk |
| 4 | ЭНДИ МЮРРЕЙ | AAAPHcAAAAAB/4TAAD | 1 |
| 3 | НОВАК ДЖОКОВИЧ | AAAPHcAAAAAB/4TAAC | 1 |
| 3 | НОВАК ДЖОКОВИЧ | AAAPHcAAAAAB/4TAAG | 2 |
| 2 < /td> | РАФАЭЛЬ НАДАЛЬ | AAAPHcAAAAAB/4TAAB | 1 |
| РАФАЭЛЬ НАДАЛЬ | AAAPHcAAAAAB/4TAAF | 2 | |
| 1 | РОДЖЕР ФЕДЕРЕР | AAAPHcAAAAAB/4TAAE | 1 |
| 1 | РОДЖЕР ФЕДЕРЕР | AAAPHcAAAAAB/4TAAA | 2 |
SQL возвращает идентификатор строки для всех строк в таблице. Затем функция ROW_NUMBER() работает с наборами id и player_name, управляемыми инструкцией PARTITION BY. Это означает, что для каждого уникального player_rank и player_name ROW_NUMBER начнет подсчет строк, которые мы назвали rnk. Когда наблюдается новая комбинация player_rank и player_name, счетчик rank сбрасывается на 1.
Теперь мы можем применить оператор DELETE для удаления повторяющихся значений, как показано ниже.
Вывод: в этом руководстве вы шаг за шагом узнаете, как удалять повторяющиеся записи в базе данных Oracle с помощью оператора DELETE с подзапросом.
После того как вы обнаружили дубликаты записей в таблице, вам часто требуется удалить ненужные копии, чтобы сохранить данные в чистоте.
Если в таблице есть несколько повторяющихся строк, вы можете сделать это вручную, одну за другой, с помощью простого оператора DELETE. Однако делать это вручную, если в таблице много повторяющихся записей, занимает много времени. Лучше создать единый оператор, удаляющий все нежелательные копии за один раз.
Прежде чем удалять повторяющиеся записи, вы должны решить, какие экземпляры вы хотите сохранить. Например, вы можете сохранить самую новую или самую старую строку. В этом случае вам нужен столбец в таблице, такой как столбец идентификатора, который не является частью группы, используемой для оценки дубликатов.
Рассмотрите следующую таблицу фруктов:
Предположим, вы хотите сохранить строку с наибольшим значением fruit_id и удалить все остальные копии. Следующий запрос возвращает последнюю запись для каждой комбинации fruit_name и color :
Вы используете следующий оператор DELETE с подзапросом для удаления строк, значения которых в столбце fruit_id не являются самыми высокими.
Три строки были удалены, как мы и ожидали:
Аналогичным образом, если вы хотите сохранить строку с наименьшим идентификатором, вы используете функцию MIN() вместо функции MAX():
Описанный выше метод работает, если у вас есть столбец, не входящий в группу для оценки дубликатов. Если все значения во всех столбцах могут иметь копии, вы больше не сможете использовать столбец fruit_id.
Давайте создадим таблицу Fruits с новой структурой и данными следующим образом:
В таблице фруктов значения во всех столбцах fruit_id , fruit_name и color имеют копии.
В этом случае вы можете использовать rowid, который является физическим локатором, указывающим, где в хранилище Oracle хранит строку. Поскольку идентификатор строки уникален для каждой строки, вы можете использовать его для удаления дубликатов, как показано ниже:
Я тестирую что-то в Oracle и заполняю таблицу образцами данных, но в процессе я случайно загрузил повторяющиеся записи, поэтому теперь я не могу создать первичный ключ, используя некоторые столбцы.
Как удалить все повторяющиеся строки и оставить только одну из них?
24 ответа 24
Используйте псевдостолбец rowid.
Где столбец1 , столбец2 и столбец3 составляют идентификационный ключ для каждой записи. Вы можете перечислить все свои столбцы.
+1 Мне пришлось найти два повторяющихся телефонных номера в более чем 12 000 записей. Изменил DELETE на SELECT, и это нашло их за считанные секунды. Спасибо, я сэкономил кучу времени.
Мне этот подход не помог. Я не знаю почему. Когда я заменил «DELETE» на «SELECT *», он вернул строки, которые я хотел удалить, но когда я выполнил команду «DELETE», он просто завис на неопределенный срок.
Если выбор работает, а удаление нет, это может быть связано с размером результирующего подзапроса. Может быть интересно сначала создать таблицу с результатом подзапроса, построить индекс для столбца min(rowid), а затем запустить оператор удаления.
(исправлена отсутствующая скобка)
Где столбец1, столбец2 и т. д. — ключ, который вы хотите использовать.
создать таблицу t2 как select *, отличную от t1;
не является ответом - в отличие от * будут приняты все записи, отличающиеся хотя бы 1 символом в 1 столбце. Все, что вам нужно, это выбрать отдельные значения только из тех столбцов, которые вы хотите сделать первичными ключами. Ответ Билла является отличным примером такого подхода.
Еще одним недостатком этого метода является необходимость создания копии таблицы. Для огромных таблиц это подразумевает предоставление дополнительного табличного пространства и удаление или уменьшение табличного пространства после копирования. У метода Билла больше преимуществ и нет дополнительных недостатков.
Вы должны сделать небольшой блок pl/sql, используя курсор для цикла, и удалить строки, которые вы не хотите сохранять. Например:
Я полагаю, что голосование против этого связано с тем, что вы используете PL/SQL, когда вы можете сделать это на SQL, если вам интересно.
То, что вы можете сделать это на SQL, не означает, что это единственное решение. Я опубликовал это решение после того, как увидел решение только для SQL. Я думал, что отрицательные голоса были за неправильные ответы.
Для выбора дубликатов может использоваться только формат запроса:
Итак, правильный запрос согласно другому предложению:
В этом запросе будет сохранена самая старая запись в базе данных для критериев, выбранных в предложении WHERE CLAUSE .
Сертифицированный партнер Oracle (2008 г.)
Самый быстрый способ для очень больших таблиц
Создайте таблицу исключений со следующей структурой: exceptions_table
Попробуйте создать уникальное ограничение или первичный ключ, которые будут нарушаться дубликатами. Вы получите сообщение об ошибке, потому что у вас есть дубликаты. Таблица исключений будет содержать идентификаторы повторяющихся строк.
Присоединиться к вашей таблице с exceptions_table по rowid и удалить дубликаты
Если количество удаляемых строк велико, создайте новую таблицу (со всеми грантами и индексами), противодействующую соединению с exceptions_table по rowid, и переименуйте исходную таблицу в original_dups, а new_table_with_no_dups переименуйте в исходную таблицу
Использование самостоятельного соединения
плотный ранг с разделением по дает ранг для повторяющихся строк с одинаковым номером, например, три строки, имеющие ранг 1, 1, 1 и идентификатор строки, создаются для каждой строки как уникальные, и мы пытаемся удалить те идентификаторы строк, которые не совпадают.< /p> <р>1. решение
<р>2. sloution3.решение
<р>4. решение <р>5. решение
и вы также можете удалить повторяющиеся записи другим способом
Это сообщение в блоге было действительно полезно для общих случаев:
Если строки полностью дублируются (все значения во всех столбцах могут иметь копии), столбцов для использования нет! Но чтобы сохранить его, вам все равно нужен уникальный идентификатор для каждой строки в каждой группе. К счастью, у Oracle уже есть то, что вы можете использовать. Ряд. Все строки в Oracle имеют rowid. Это физический локатор. То есть он указывает, где на диске Oracle хранит строку. Это уникально для каждой строки. Таким образом, вы можете использовать это значение для идентификации и удаления копий. Для этого замените min() на min(rowid) в некоррелированном удалении:
Для лучшей производительности я написал следующее:
(см. план выполнения)
Проверьте приведенные ниже сценарии -
Вы увидите, что повторяющиеся записи удалены.
Надеюсь, это решит ваш вопрос. Спасибо :)
Я не видел ответов, в которых используются общие табличные выражения и оконные функции. Это то, с чем мне легче всего работать.
На что обратить внимание:
1) Мы проверяем наличие дубликатов только в полях в предложении о разделе.
2) Если у вас есть причина выбрать один дубликат вместо других, вы можете использовать предложение order by, чтобы эта строка имела row_number() = 1
3) Вы можете изменить сохраняемый дубликат номера, изменив финальное предложение where на «Where RN > N» с N >= 1 (я думал, что N = 0 удалит все строки, в которых есть дубликаты, но он просто удалит все строки).
4) Добавлено поле Sum partition запроса CTE, которое будет помечать каждую строку номером строки в группе. Таким образом, чтобы выбрать строки с дубликатами, включая первый элемент, используйте «WHERE cnt > 1».
Эта статья написана в соавторстве с сотрудниками wikiHow. Наша обученная команда редакторов и исследователей проверяет статьи на точность и полноту. Команда управления контентом wikiHow тщательно следит за работой нашей редакции, чтобы гарантировать, что каждая статья подкреплена достоверными исследованиями и соответствует нашим высоким стандартам качества.
Эта статья была просмотрена 296 576 раз.
При работе в Oracle вы можете обнаружить, что некоторые записи дублируются. Вы можете удалить эти повторяющиеся строки, идентифицируя их и используя их RowID или адрес строки. Прежде чем начать, вам следует создать резервную таблицу на тот случай, если вам понадобится ссылаться на них после удаления записей.
Определите дубликат. В этом случае укажите дубликат примера «Алан». Убедитесь, что записи, которые вы пытаетесь удалить, на самом деле дублируются, введя приведенный ниже SQL.
Идентификация по столбцу "Имена". В случае столбца с именем «Имена» вы должны заменить «column_name» на «Имена».
Идентификация из других столбцов. Если вы пытаетесь идентифицировать дубликат по другому столбцу, например по возрасту Алана, а не по его имени, вы должны ввести «Возраст» вместо «column_name» и т. д.
Выберите "имя из имен". После «SQL», что означает стандартный язык запросов, введите «выбрать имя из имен».
Удалите все строки с повторяющимся именем. После «SQL» введите «удалить из имен, где имя = «Алан»;». Обратите внимание, что здесь важно использовать заглавные буквы, поэтому будут удалены все строки с именем «Алан». После «SQL» введите «commit». [1] X Источник исследования
Введите строку без дубликатов. Теперь, когда вы удалили все строки с примерным именем «Алан», вы можете вставить одну обратно, введя «вставить в значения имени («Алан»);». После «SQL» введите «commit», чтобы создать новую строку.
Посмотрите свой новый список. Выполнив вышеуказанные шаги, вы можете убедиться, что у вас больше нет повторяющихся записей, введя «выбрать * из имен».
Удалить дубликат. После "SQL" введите "удалить из имен a, где rowid > (выберите min(rowid) из имен b, где b.name=a.name);" для удаления повторяющихся записей. [2] X Источник исследования
Проверить наличие дубликатов. После того, как вы выполнили все вышеперечисленное, команды проверяют наличие повторяющихся записей, введя «выбрать rowid, имя из имен;» а затем "зафиксировать".
Удалите повторяющиеся строки, указав их столбец. После «SQL» введите «удалить из имен a, где rowid > (выберите min(rowid) из имен b, где b.name=a.name и b.age=a.age);» для удаления повторяющихся записей. [3] X Источник исследования
Проверить наличие дубликатов. После того, как вы выполнили вышеуказанные шаги, введите «выбрать * из имен;» а затем "зафиксировать", чтобы убедиться, что вы успешно удалили повторяющиеся записи.
Создайте резервную таблицу в своей собственной учетной записи, которую вы можете использовать, чтобы показать, что было там до того, как произошло какое-либо удаление (на случай возникновения вопросов).
Вам также может понравиться
Как упорядочить в алфавитном порядке в SQL
Об этой статье
Эта статья написана в соавторстве с сотрудниками wikiHow. Наша обученная команда редакторов и исследователей проверяет статьи на точность и полноту. Команда управления контентом wikiHow тщательно следит за работой нашей редакции, чтобы гарантировать, что каждая статья подкреплена надежными исследованиями и соответствует нашим высоким стандартам качества. Эта статья была просмотрена 296 576 раз.
Чтобы удалить повторяющиеся записи в Oracle, сначала убедитесь, что записи действительно дублируются, введя стандартный язык запросов или SQL. После ввода «SQL» найдите то, что вы хотите удалить, например «удалить из имен, где имя =« Алан »». Затем введите «commit», чтобы эта команда вступила в силу. После того, как вы удалили все строки с примерным именем «Алан», создайте новую строку, введя «вставить в значения имени («Алан»)», а затем «зафиксировать». Выполнив эти шаги, убедитесь, что у вас больше нет дубликатов, введя «выбрать * из имен». Чтобы узнать, как удалить несколько дубликатов, продолжайте читать!
Читайте также: