Как удалить повторяющиеся записи?
Я должен добавить уникальное ограничение к существующей таблице. Это хорошо, за исключением того, что в таблице уже есть миллионы строк, и многие строки нарушают уникальное ограничение, которое мне нужно добавить.
Какой самый быстрый подход к удалению поврежденных строк? У меня есть оператор SQL, который находит дубликаты и удаляет их, но для его выполнения требуется вечность. Есть ли другой способ решить эту проблему? Может быть, резервное копирование таблицы, а затем восстановление после добавления ограничения?
16 ответов
Например, вы могли бы:
CREATE TABLE tmp ...
INSERT INTO tmp SELECT DISTINCT * FROM t;
DROP TABLE t;
ALTER TABLE tmp RENAME TO t;
Некоторые из этих подходов кажутся немного сложными, и я обычно делаю это следующим образом:
Данная таблица table, хотите присвоить его уникальности (field1, field2), сохраняя строку с максимальным field3:
DELETE FROM table USING table alias
WHERE table.field1 = alias.field1 AND table.field2 = alias.field2 AND
table.max_field < alias.max_field
Например, у меня есть таблица, user_accountsи я хочу добавить уникальное ограничение на электронную почту, но у меня есть некоторые дубликаты. Скажите также, что я хочу сохранить последний созданный (максимальный идентификатор среди дубликатов).
DELETE FROM user_accounts USING user_accounts ua2
WHERE user_accounts.email = ua2.email AND user_account.id < ua2.id;
- Заметка -
USINGэто не стандартный SQL, это расширение PostgreSQL (но очень полезное), но в оригинальном вопросе конкретно упоминается PostgreSQL.
Вместо создания новой таблицы вы также можете повторно вставить уникальные строки в ту же таблицу после ее усечения. Сделайте все это за одну транзакцию. При желании вы можете автоматически удалить временную таблицу в конце транзакции с помощью ON COMMIT DROP, Увидеть ниже.
Этот подход полезен только тогда, когда есть много строк для удаления из всей таблицы. Только для нескольких дубликатов, используйте простую DELETE,
Вы упомянули миллионы строк. Чтобы сделать операцию быстрой, вы хотите выделить достаточно временных буферов для сеанса. Настройка должна быть отрегулирована перед использованием любого временного буфера в текущем сеансе. Узнайте размер вашего стола:
SELECT pg_size_pretty(pg_relation_size('tbl'));
Задавать temp_buffers соответственно. Обильно округляйте, потому что представление в памяти требует немного больше оперативной памяти.
SET temp_buffers = 200MB; -- example value
BEGIN;
-- CREATE TEMPORARY TABLE t_tmp ON COMMIT DROP AS -- drop temp table at commit
CREATE TEMPORARY TABLE t_tmp AS -- retain temp table after commit
SELECT DISTINCT * FROM tbl; -- DISTINCT folds duplicates
TRUNCATE tbl;
INSERT INTO tbl
SELECT * FROM t_tmp;
-- ORDER BY id; -- optionally "cluster" data while being at it.
COMMIT;
Этот метод может быть лучше создания новой таблицы, если существуют зависимые объекты. Представления, индексы, внешние ключи или другие объекты, ссылающиеся на таблицу. TRUNCATE заставляет вас начать с чистого листа в любом случае (новый файл в фоновом режиме) и гораздо быстрее, чем DELETE FROM tbl с большими столами (DELETE на самом деле может быть быстрее с небольшими столами).
Для больших таблиц регулярно быстрее удалять индексы и внешние ключи, пополнять таблицу и заново создавать эти объекты. Что касается ограничений fk, вы должны быть уверены, что новые данные верны, конечно, иначе вы столкнетесь с исключением при попытке создать fk.
Обратите внимание, что TRUNCATE требует более агрессивной блокировки, чем DELETE, Это может быть проблемой для таблиц с большой одновременной нагрузкой.
Если TRUNCATE не подходит или, как правило, для небольших и средних таблиц, существует аналогичная методика с CTE, модифицирующим данные (Postgres 9.1 +):
WITH del AS (DELETE FROM tbl RETURNING *)
INSERT INTO tbl
SELECT DISTINCT * FROM del;
-- ORDER BY id; -- optionally "cluster" data while being at it.
Медленнее для больших столов, потому что TRUNCATE там быстрее Но может быть быстрее (и проще!) Для небольших столов.
Если у вас вообще нет зависимых объектов, вы можете создать новую таблицу и удалить старую, но вряд ли вы что-то выиграете от этого универсального подхода.
Для очень больших таблиц, которые не помещаются в доступную оперативную память, создание новой таблицы будет значительно быстрее. Вам придется взвесить это против возможных неприятностей / накладных расходов с зависимыми объектами.
Вы можете использовать oid или ctid, которые обычно являются "невидимыми" столбцами в таблице:
DELETE FROM table
WHERE ctid NOT IN
(SELECT MAX(s.ctid)
FROM table s
GROUP BY s.column_has_be_distinct);
Оконная функция PostgreSQL удобна для решения этой проблемы.
DELETE FROM tablename
WHERE id IN (SELECT id
FROM (SELECT id,
row_number() over (partition BY column1, column2, column3 ORDER BY id) AS rnum
FROM tablename) t
WHERE t.rnum > 1);
См. Удаление дубликатов.
Обобщенный запрос на удаление дубликатов:
DELETE FROM table_name
WHERE ctid NOT IN (
SELECT max(ctid) FROM table_name
GROUP BY column1, [column 2, ...]
);
Колонка ctid это специальный столбец, доступный для каждой таблицы, но не видимый, если не указано иное. ctid Значение столбца считается уникальным для каждой строки в таблице.
Из старого списка рассылки postgresql.org:
create table test ( a text, b text );
Уникальные ценности
insert into test values ( 'x', 'y');
insert into test values ( 'x', 'x');
insert into test values ( 'y', 'y' );
insert into test values ( 'y', 'x' );
Дублирующиеся значения
insert into test values ( 'x', 'y');
insert into test values ( 'x', 'x');
insert into test values ( 'y', 'y' );
insert into test values ( 'y', 'x' );
Еще один двойной дубликат
insert into test values ( 'x', 'y');
select oid, a, b from test;
Выберите повторяющиеся строки
select o.oid, o.a, o.b from test o
where exists ( select 'x'
from test i
where i.a = o.a
and i.b = o.b
and i.oid < o.oid
);
Удалить повторяющиеся строки
Примечание: PostgreSQL не поддерживает псевдонимы в таблице, упомянутой в from пункт об исключении.
delete from test
where exists ( select 'x'
from test i
where i.a = test.a
and i.b = test.b
and i.oid < test.oid
);
Эта функция удаляет дубликаты без удаления индексов и делает это для любой таблицы.
Использование: select remove_duplicates('mytable');
--- --- remove_duplicates (tablename) удаляет дубликаты записей из таблицы (конвертировать из набора в уникальный набор) --- СОЗДАТЬ ИЛИ ЗАМЕНИТЬ ФУНКЦИЮ remove_duplicates(text) ВОЗВРАЩАЕТСЯ void AS $$ DECLARE табличное имя алиас за 1 доллар; НАЧАТЬ EXECUTE 'CREATE TEMPORARY TABLE _DISTINCT_' || имя таблицы || ' AS (ВЫБЕРИТЕ DISTINCT * FROM ' || tablename || ');'; ВЫПОЛНИТЬ 'УДАЛИТЬ ИЗ' || имя таблицы || ';'; ВЫПОЛНИТЬ 'INSERT INTO' || имя таблицы || ' (SELECT * FROM _DISTINCT_' || tablename || ');'; ВЫПОЛНИТЬ 'DROP TABLE _DISTINCT_' || имя таблицы || ';'; ВЕРНУТЬ; КОНЕЦ; $$ LANGUAGE plpgsql;
Я только что успешно использовал ответ Эрвина Брандштеттера, чтобы удалить дубликаты в объединяющей таблице (таблице, в которой отсутствуют собственные первичные идентификаторы), но обнаружил, что есть одно важное предостережение.
В том числе ON COMMIT DROP означает, что временная таблица будет удалена в конце транзакции. Для меня это означало, что временная таблица больше не была доступна к тому времени, как я ее вставил!
я только что сделал CREATE TEMPORARY TABLE t_tmp AS SELECT DISTINCT * FROM tbl; и все работало нормально.
Временная таблица удаляется в конце сеанса.
Если у вас есть только одна или несколько дублированных записей, и они действительно дублируются (то есть они появляются дважды), вы можете использовать "скрытые" ctid колонка, как предложено выше, вместе с LIMIT:
DELETE FROM mytable WHERE ctid=(SELECT ctid FROM mytable WHERE […] LIMIT 1);
Это удалит только первую из выбранных строк.
DELETE FROM table
WHERE something NOT IN
(SELECT MAX(s.something)
FROM table As s
GROUP BY s.this_thing, s.that_thing);
Во-первых, вам нужно решить, какие из ваших "дубликатов" вы будете хранить. Если все столбцы равны, хорошо, вы можете удалить любой из них... Но, возможно, вы хотите сохранить только самый последний или какой-то другой критерий?
Самый быстрый способ зависит от вашего ответа на вопрос выше, а также от процента дубликатов в таблице. Если вы выбросите 50% своих строк, вам лучше делать CREATE TABLE ... AS SELECT DISTINCT ... FROM ... ;и если вы удалите 1% строк, лучше использовать DELETE.
Также для операций по техническому обслуживанию, как это, обычно хорошо установить work_mem на хороший кусок вашей оперативной памяти: запустите EXPLAIN, проверьте число N сортировок / хэшей и установите для work_mem значение RAM / 2 / N. Используйте много оперативной памяти; это хорошо для скорости. Пока у вас есть только одно одновременное соединение...
Я работаю с PostgreSQL 8.4. Когда я запустил предложенный код, я обнаружил, что он фактически не удаляет дубликаты. Выполняя некоторые тесты, я обнаружил, что добавление "DISTINCT ON (duplicate_column_name)" и "ORDER BY duplicate_column_name" сделало свое дело. Я не гуру SQL, я нашел это в PostgreSQL 8.4 SELECT...DISTINCT doc.
CREATE OR REPLACE FUNCTION remove_duplicates(text, text) RETURNS void AS $$
DECLARE
tablename ALIAS FOR $1;
duplicate_column ALIAS FOR $2;
BEGIN
EXECUTE 'CREATE TEMPORARY TABLE _DISTINCT_' || tablename || ' AS (SELECT DISTINCT ON (' || duplicate_column || ') * FROM ' || tablename || ' ORDER BY ' || duplicate_column || ' ASC);';
EXECUTE 'DELETE FROM ' || tablename || ';';
EXECUTE 'INSERT INTO ' || tablename || ' (SELECT * FROM _DISTINCT_' || tablename || ');';
EXECUTE 'DROP TABLE _DISTINCT_' || tablename || ';';
RETURN;
END;
$$ LANGUAGE plpgsql;
CREATE TABLE test (col text);
INSERT INTO test VALUES
('1'),
('2'), ('2'),
('3'),
('4'), ('4'),
('5'),
('6'), ('6');
DELETE FROM test
WHERE ctid in (
SELECT t.ctid FROM (
SELECT row_number() over (
partition BY col
ORDER BY col
) AS rnum,
ctid FROM test
ORDER BY col
) t
WHERE t.rnum >1);
Это работает очень хорошо и очень быстро:
CREATE INDEX otherTable_idx ON otherTable( colName );
CREATE TABLE newTable AS select DISTINCT ON (colName) col1,colName,col2 FROM otherTable;
DELETE FROM tablename
WHERE id IN (SELECT id
FROM (SELECT id,ROW_NUMBER() OVER (partition BY column1, column2, column3 ORDER BY id) AS rnum
FROM tablename) t
WHERE t.rnum > 1);
Удалите дубликаты по столбцам и сохраните строку с самым низким идентификатором. Шаблон взят из вики постгрес
Используя CTE, вы можете добиться более читаемой версии вышеупомянутого
WITH duplicate_ids as (
SELECT id, rnum
FROM num_of_rows
WHERE rnum > 1
),
num_of_rows as (
SELECT id,
ROW_NUMBER() over (partition BY column1,
column2,
column3 ORDER BY id) AS rnum
FROM tablename
)
DELETE FROM tablename
WHERE id IN (SELECT id from duplicate_ids)