[SQL] Из вопросов на собеседовании: как удалить дубликаты?

uncle17

Есть
TBL:
ID primary key
User int
Thing int
На User,Thing не поставлен UNIQUE
Как одним ANSI-запросом вычистить дубликаты, которые появились? У кого какие идеи? Чисто сравнить

Teteshnik

а в чем проблема?

uncle17

Проблемы нет, были несколько решений. Какое круче?

Teteshnik

так это. тут разные "если" есть. а стандартное наваять не факт оптимальное проблем нет.
приведи хоть варианты. интересно

6yrop

так и пишешь, удалить записи, для которых существуют дубликаты. Ну и какой-нибудь элемент из группы дубликатов оставляешь, например, с меньшим ID-шником.

Teteshnik

delete from tbl
where not id in (select min(id) from tbl group by user, thing)

uncle17

Ребята имели в виду


DELETE FROM

  TBL

WHERE

  ID IN

  (

   SELECT A.ID FROM

       TBL A,

       TBL B

   WHERE

      A.User=B.User

   AND

     A.Thing = B.Thing

    AND

     A.ID <> B.ID

  )

AND

  ID

  NOT IN

  (

   SELECT MIN (A.ID) FROM

       TBL A,

       TBL B

   WHERE

      A.User=B.User

   AND

     A.Thing = B.Thing

    AND

     A.ID <> B.ID

  )

А я имел в виду MySQL:


ALTER [b]IGNORE[/b] TABLE TBL ADD UNIQUE (User, Thing)

uncle17

блеать, сработает?
Я лох

Teteshnik

в оракле сработает. если в мускуле есть IN, то должно сработать. я не помню.

uncle17

есть
Но у тебя
not id in,
а у меня
id not in
Думаю, похуй

Teteshnik

конечно одно ито же. это у меня выебоны.

uncle17

Да, я точно лох
Но фикус с alter ignore мне понравился - это точно быстрее любых выборок

6yrop

я имел ввиду вот это
 


DELETE  FROM TBL

WHERE   EXISTS ( SELECT 1

                 FROM   TBL TBL2

                 WHERE  TBL.User = TBL2.User

                        AND TBL.Thing = TBL2.Thing

                        AND TBL.ID > TBL2.ID )

Teteshnik

ты только с альтером объясни. разве оно дубликаты удалит? я чот не очень мануал понял.

uncle17

А вот тут как раз вопрос:
Удалятся только дубликаты или дубликаты вместе с оригиналами?

uncle17

Удалит, мы попробовали, точно делает именно то, что надо. Но только в MySQL

6yrop

для "оригиналов" вот это условие не выполняется
AND TBL.ID > TBL2.ID

uncle17

дадад, я как раз хотел оправдаться

Teteshnik

да, нашел на русском. у тебя круче решение. я в альтере не силен. тем более мускульном.

uncle17

а игнор как раз только там и есть

Teteshnik

у меня фэйл. удаление из подзапросов - это расширение над ANSI.
link

uncle17

Опачки:)
А не судьба с темп-таблицей? Мне дочитать лень:)

Teteshnik

ну наверно можно создать таблицу, в нее инсертом залить данные и потом обратно. это пожалуй проще для понимания, но по времени дольше чем ребята написали.
а для тебя у меня тоже плохие новости. ignore - это тоже расширение

hprt

для mssql 2005+


with cte as (

    select 

        row_number over (partition by [user], thing order by Id) rn 

    from tbl

)

delete cte

where rn > 1

6yrop

Ребята имели в виду
DELETE FROM
TBL
WHERE
ID IN
(
SELECT A.ID FROM
TBL A,
TBL B
WHERE
A.User=B.User
AND
A.Thing = B.Thing
AND
A.ID <> B.ID
)
AND
ID
NOT IN
(
SELECT MIN (A.ID) FROM
TBL A,
TBL B
WHERE
A.User=B.User
AND
A.Thing = B.Thing
AND
A.ID <> B.ID
)
ребята или ты сам это придумал?

Teteshnik

у ребят тоже подзапрос. тоже пусть расслабятся. ответа верного никто пока не привел.

Dasar

ну наверно можно создать таблицу, в нее инсертом залить данные и потом обратно.
вместо обратной переливки можно старую таблицу drop-нуть, а временную переименовать в старую

6yrop

какой ASNI имеется ввиду? в какой-нибудь 2003 даже row_number входит, имхо

Teteshnik

можно. а теперь это все одним запросом...

Teteshnik

я ваще хз. это нужно просто давно иметь дело с базами. когда модных наворотов не было.

6yrop

вместо обратной переливки можно старую таблицу drop-нуть, а временную переименовать в старую
вы всю таблицу собираетесь переливать, а дубликат может оказаться один на миллиард?

Papazyan

А че нельзя типа
select min id,a,b from tbl group by a,b

6yrop

в какой-нибудь 2003 даже row_number входит
да в ISO SQL:2003 входит
http://en.wikipedia.org/wiki/Window_function_%28SQL%29#Limit...

hprt

Если DDL использовать, то подобное решение может быть быстрее (особенно, если есть FK - но их при удалении все равно надо будет обработать). Создать таблицу с такой же структурой, сделать switch old_table to new_table, вставить данные запросом с min/max. В общем, в любом случае, оптимальность решения только практика покажет.
PS MS SQL Server

uncle17

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

uncle17

а дубликат может оказаться один на миллиард
именно
а может оказаться и полмиллиарда на миллиард

Teteshnik

select
"это вынос мозга"
from tbl

Teteshnik

короче по счастливой случайности у меня оказался очень урезанный мускуль сервер. кстати скачанный совсем недавно с мускульного сайта 5.5.21. у меня нет даже row_number. так же не действуют никакие предложения умников из инета. банально не работает
delete from (select * from tbl) что вроде должно соответствовать delete from tbl.
так вот, все что я смог сделать - это
CREATE TABLE tbl1 AS select id, user, thing from tbl where id in (select min(id) from tbl group by user, thing)
кстати тот запрос что я привел первым тоже не работает. поскольку в условии не указано что нельзя создавать другую таблицу, то походу - это оно.
так
ALTER IGNORE TABLE tbl ADD UNIQUE (User, Thing)
у меня тоже не работает, к слову.

doublemother

CREATE TABLE tbl1 AS select id, user, thing from tbl where id in (select min(id) from tbl group by user, thing)
CREATE TABLE tbl1 AS SELECT min(id) as id, user, thing FROM tbl GROUP BY user, thing

Teteshnik

ну да. копипастил из кучи селектов, навставлял при этом ошибок. все исправил кроме этого.

Teteshnik

бля, я сделал это. давайте мою гребаную печеньку.
DELETE from tbl WHERE not EXISTS (
SELECT q.ID FROM (
select min(b.id) id from tbl b group by b.user, b.thing
) q where tbl.id = q.id
);

uncle17

вот же ж:)
Как, оказывается, всё depends от реализации и версии
Ну, лови, раз обещал:

dava

может по
rowid?
Хотя это будет не анси, они у всех по разному декларируются, зато не привязаны ко всяким суррогатным ID
delete from table_1 a where rowid not in (select min(rowid user, thing from table_1 b where a.user = b.user and a.thing = b.thing group by user, thing)

luna89

может по
rowid?
Хотя это будет не анси, они у всех по разному декларируются, зато не привязаны ко всяким суррогатным ID

Вроде rowid неуникальны в случае кластерной таблицы.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUES...
Оставить комментарий
Имя или ник:
Комментарий: