Как удалить дубли из таблицы?

Это очень популярный вопрос на собеседовании lvl jun.
И это сложнее вопроса, где нужно просто найти дубли 😄

📌Допустим у нас есть таблица:

create table table_1
(id numeric,
name varchar(32));

insert into table_1 values(1, 'Jon');
insert into table_1 values(1, 'Jon');
insert into table_1 values(1, 'Jon');

insert into table_1 values(2, 'Sam');
insert into table_1 values(2, 'Sam');
commit;

select * from table_1;

id name

1 "Jon"
1 "Jon"
1 "Jon"
2 "Sam"
2 "Sam"

И нам нужно из 5 строк оставить только 2 уникальные.

Очень популярный НЕВЕРНЫЙ ответ:
Нумеруем оконкой все строки, оставляем все строки с номером 1:

WITH cte AS (
SELECT
id,
name,
ROW_NUMBER() OVER (PARTITION BY id,name ORDER BY id,name ) AS row_num
FROM
table_1
)
----
id name row_num

1 "Jon" 1
1 "Jon" 2
1 "Jon" 3
2 "Sam" 1
2 "Sam" 2

----
DELETE FROM table_1
WHERE id IN (
SELECT id FROM cte WHERE row_num > 1
);
Такой код удалит все строки!

Объясняю. В cte нумируем строки оконкой ROW_NUMBER().
Далее в delete мы хотим удалить все, где row_num > 1. Но под это условие попадают id=1 и id=2. Следовательно, delete удаляет все id с такими значениями. Таблица теперь пустая.

Нам нужно как-то разделить полностью уникальные строки.
В этом нам поможет скрытое системное поле, которое уникально идентифицирует строки и есть в каждой таблице. Такие поля есть во всех СУБД (возможны исключения 🤷‍♂️). В Oracle это поле называется rowid, в Postgresql - ctid.

Чуть переделаем предыдущий код:

WITH cte AS (
SELECT
id,
name,
ctid,
ROW_NUMBER() OVER (PARTITION BY id,name ORDER BY id,name ) AS row_num
FROM
table_1
)

----
id name ctid row_num

1 "Jon" "(0,41)" 1
1 "Jon" "(0,42)" 2
1 "Jon" "(0,43)" 3
2 "Sam" "(0,44)" 1
2 "Sam" "(0,45)" 2

----
DELETE FROM table_1
WHERE ctid IN (
SELECT ctid FROM cte WHERE row_num > 1
);

Теперь удалится только 3 строки, вместо 5.
Останется две уникальные.

id name

1 "Jon"
2 "Sam"

Или же можно удалить таким образом:

DELETE FROM table_1
WHERE ctid NOT IN (
SELECT MIN(ctid)
FROM table_1
GROUP BY id,name
);


Если дальше разгонять, то на самом деле можно придумать еще много способов для удаления дублей.
Например, таблицу с дублями переименовать в table_1_old. Создать новую таблицу table_1 и залить в нее данные из table_1_old без дублей (в select добавить distinct). После дропаем table_1_old.

Если таблица очень большая и партицированная, можно пробегаться по всем партициям в цикле и чистить каждую партицию отдельно.

* У меня есть оракловый pl sql cкрипт, который удаляет дубли в таблице через курсор и коллекции. Такой вариант на больших данных будет быстрее работать и не будет блочить всю таблицу. Кому нужен скрипт пишите, пришлю.

Также на собеседовании (да и на реальных кейсах) будет полезно упомянуть, что перед удалением нужно сделать бэкап и сохранить, что мы удаляем, для дальнейшего разбора. Нужно понять почему эти дубли вообще появились.


Как вам инфа? Пишите свои варианты удаления дублей или какие-то еще тонкости и уточнения по данному вопросу? Жду реакций ⬇️😊

it пингвин | data engineer 🐧

#sql #база #jun #Вопросы_с_собесов