Oracle удалить дубликаты в таблице
До Oracle 11.2 я использовал пользовательскую агрегатную функцию для объединения столбца в строку. 11.2 Добавлена LISTAGG функция, поэтому я пытаюсь использовать ее вместо этого. Моя проблема в том, что мне нужно устранить дубликаты в результатах и, похоже, я не могу этого сделать.
То, что я хочу увидеть, это:
Вот listagg версия, которая близка, но не устраняет дубликаты.
У меня есть решение, но это хуже, чем продолжать использовать пользовательскую функцию агрегирования.
Должно order by null быть order by Num2 или я запутался? @ Джек - Это не имеет значения для устранения дубликатов. В зависимости от вашего использования, это может быть желательно. вздох LISTAGG продолжает отставать от Тома Кайта STRAGG , с которым это так же легко, как STRAGG(DISTINCT . )Вы можете использовать регулярные выражения и regexp_replace удалить дубликаты после объединения listagg :
Это может быть проще, если Oracle поддерживает регулярные выражения или группы без захвата, но это не так .
Однако это решение позволяет избежать сканирования источника более одного раза.
Обратите внимание, что для того, чтобы этот метод REGEX_REPLACE работал для удаления дубликатов, все дублированные значения должны быть рядом друг с другом в агрегированной строке. Вот чего ORDER BY Num2 добивается, не правда ли (см. Здесь ). Или вы просто пытаетесь указать, что вам нужен ORDER BY, чтобы он работал?Насколько я вижу, с доступной на данный момент спецификацией языка это самый короткий путь для достижения того, чего вы хотите, если это необходимо сделать listagg .
Каким было ваше решение, которое было хуже, чем нестандартное совокупное решение ?
Это работает, но нужно сделать два полных сканирования таблицы. Если у вас небольшая таблица, которую нужно агрегировать (<100000 строк), производительность будет более чем приемлемой для простого извлечения. Это было мое решение выбора после почти часа тестирования всех возможных способов! Это также работает, когда дубликаты помещают промежуточное значение более 4000 символов. Это делает его более безопасным, чем regexp решение.Под строками- дубликатами понимаются строки таблицы, значения одного или нескольких полей которых одинаковы, неуникальны. Требуется найти такие строки и удалить лишние дубликаты, оставив только уникальные значения.
Создание и заполнение таблицы с дублирующимися значениями.
Поиск строк - дубликатов:
Удаление строк дубликатов:
Если в таблице нет уникального ключа или индекса, по которому можно отсеивать дубликаты (в примере используется уникальность поля TEST_DUPLICATE.duplicate_id), то можно использовать псевдо столбец ROWID, значения которого гарантированно уникальны, т.е., например, первый запрос на удаление дубликатов через NOT IN может выглядеть так:
Добавление от Деев И. Удаление дубликатов через EXCEPTIONS INTO :
Подскажите,пожалуйста подробно что делает этот кусочек
"row_number() over (partition by value order by duplicate_id"
Прочитайте пару статеек про аналитические функции(Можно во 2-ом томе Кайта)
Подскажите,пожалуйста подробно что делает этот кусочек
"row_number() over (partition by value order by duplicate_id"
можно исп-ть либо min() либо first_value() в зависимости от задачи
delete from table t
where t.rowid in (select rw from (select rowid as rw
min(rowid) over(partition by <list unique colums>) as min_rw
from table
) where rw <> min_rw)
delete from shit where rowid not in (select min(rowid) from shit group by value)
delete from shit
where rowid in
(select a.rid
from (select rownum rn, rowid rid, s.* from shit s) a
where exists (select 1
from (select rownum rn, rowid rid, t.* from shit t) b
where b.value = a.value
and b.rid <> a.rid
and b.rn > a.rn))
delete from shit where rowid not in (select min(rowid) from shit group by value)
delete from shit
where rowid in
(select a.rid
from (select rownum rn, rowid rid, s.* from shit s) a
where exists (select 1
from (select rownum rn, rowid rid, t.* from shit t) b
where b.value = a.value
and b.rid <> a.rid
and b.rn > a.rn))
попробовал последнее решение от Деев И.
insert into drop_test select object_id, object_name from user_objects where rownum <301
ORA-00001: нарушено ограничение уникальности (SYSMAN.DROP_TEST_PK)
еще на генерации
где то надо подкрутить настройку
наверно чтобы давало вставлять записи с всеми одинаковыми полями -
но где вот ?
Можно использовать гибрид с row_number и rowid
delete from
Table1 t1
where t1.rowid in (select ri
from
(select rowid as ri, t1.ROW_ID, row_number() over (partition by t1.ROW_ID order by t1.column1) as rw
from Table1 t1
where t1.column1 = value)
where rw > 1);
Проблема интересная - я с ней столкнулся и только благодаря delete TEST_DUPLICATE
where rowid not in (select min(rowid)
from TEST_DUPLICATE
group by value);
смог ее решить.
Остальные решения при реальной проверке на 34 тыс
записей не работают :(.
Извините, но перед тем как себя хвалить (твёрдая пятёрка)))) нужно бы проверить.
А, в целом, большое спасибо - помощь реальная.
С уважением, Tolik_lv.
а что за функция такая LAG ?
и за чем в оптимизированном методе поиска дубликатов используется (t1.rowid!=t2.rowid). на сколько я знаю rowid уникальна в пределах всей базы данных.
Мой метод поиска.
DELETE FROM tmp_data_change
WHERE ROWID IN (
SELECT LAG (ROWID) OVER (PARTITION BY market_key, data_key ORDER BY market_key,
data_key)
FROM tmp_data_change)
Почти аналогичен методу с row_number()
select *
from table_name t1
where exists (
select *
from table_name t2
where ( (t1.column_name = t2.column_name) and (t1.rowid != t2.rowid) )
)
order by group --для наглядности :-)
/
метод поиска строк дубликатов находящийся выше - не оптимизирован, сегодня я родил метод оптимизированный по времени исполнения (он не использует group by):
select *
from table_name t1
where (
select *
from table_name t2
where ( (t1.column_name = t2.column_name) and (t1.rowid != t2.rowid) )
)
oreder by group --для наглядности :-)
/
твёрдая пятёрка)))
*хотя коментиков моно и поболе)))
-1 (или +1) популярный запрос на собеседовании :-)
2gerf
Такого не бывает. Хотя бы исходя из теории реляционных таблиц.
Я тестирую что-то в Oracle и заполняю таблицу некоторыми образцами данных, но в процессе я случайно загрузил дубликаты записей, поэтому теперь я не могу создать первичный ключ, используя некоторые столбцы.
Как удалить все повторяющиеся строки и оставить только одну из них?
использовать rowid псевдостолбцом.
здесь column1 , column2 и column3 составьте идентифицирующий ключ для каждой записи. Вы можете перечислить все свои колонки.
(исправлена отсутствующая скобка)
где столбец1, столбец2 и т. д. это ключ, который вы хотите использовать.
создать таблицу t2 как выбрать distinct * from t1;
для выбора дубликатов только формат запроса может быть:
таким образом, правильный запрос в соответствии с другим предложением:
этот запрос сохранит самую старую запись в базе данных для критериев, выбранных в WHERE CLAUSE .
Oracle Certified Associate (2008)
использование self join -
1. решение
2. натра
3.решение
4. решение
5. решение
и вы также можете удалить дубликаты записей другим способом
вы должны сделать небольшой блок pl / sql, используя курсор для цикла и удалить строки, которые вы не хотите сохранять. Например:
самый быстрый способ для действительно больших таблиц
создать таблицу исключений со структурой ниже: exceptions_table
если количество строк для удаления велико, то создайте новую таблицу (со всеми грантами и индексами) антисоединение с exceptions_table по rowid и переименуйте исходную таблицу в таблицу original_dups и переименуйте new_table_with_no_dups в исходную таблицу
Проверьте ниже сценарии -
вы увидите здесь 6-записи.
4.выполнить запрос ниже -
вы увидите, что дубликаты записей были удалены.
Надеюсь, это решит ваш запрос. Спасибо :)
что-то в примечание:
1)мы проверяем только дублирование полей в предложении partition.
2) Если у вас есть причина выбрать один дубликат над другими, вы можете использовать предложение order by, чтобы эта строка имела row_number () = 1
3) Вы можете изменить номер дубликата, сохраненный изменение предложения final where на "Where RN > N" с N >= 1 (я думал, что N = 0 удалит все строки, которые имеют дубликаты, но он просто удалит все строки).
4) добавлено поле Sum partition запрос CTE, который будет помечать каждую строку числовыми строками в группе. Поэтому для выбора строк с дубликатами, включая первый элемент, используйте "где cnt > 1".
У меня нет первичного ключа в этой таблице .Но у меня уже есть вышеупомянутые записи в моей таблице. Я хочу удалить дубликаты записей, которые имеют одинаковое значение в полях EmpId и EmpSSN.
может ли кто-нибудь помочь мне создать запрос для удаления этих дубликатов записей
добавить первичный ключ (код ниже)
выполнить правильное удаление (код ниже)
подумайте, почему вы не хотите хранить этот первичный ключ.
предполагая MSSQL или совместимость:
Это очень просто. Я пробовал в SQL Server 2008
использовать номер строки, чтобы различать повторяющиеся записи. Сохраните номер первой строки для EmpID / EmpSSN и удалите остальные:
это обновит таблицу и удалить все дубликаты из таблицы!
и newtablename не будет иметь повторяющихся записей.
просто измените имя таблицы( newtablename ), нажав F2 в обозревателе объектов в sql server.
как сказал Джош, - даже если вы знаете дубликаты, удаление их будет невозможно, так как вы не можете ссылаться на конкретную запись, если она является точной копией другой записи.
код
объяснение
используйте внутренний запрос для создания представления над таблицей, которое включает поле на основе Row_Number() , секционированный теми столбцами, которые вы хотите быть уникальными.
удалить из результатов этого внутреннего запроса, выбрав все, что не имеет номер строки 1; т. е. дубликаты; не оригинал.
на order by предложение функции окна row_number необходимо для допустимый синтаксис; здесь можно поместить любое имя столбца. Если вы хотите изменить, какой из результатов рассматривается как дубликат(например, сохранить самый ранний или самый последний и т. д.), То используемые здесь столбцы имеют значение; т. е. вы хотите указать порядок, в котором запись, которую вы хотите сохранить, будет первой в результате.
Если вы не хотите создавать новый первичный ключ, вы можете использовать команду TOP в SQL Server:
Читайте также: