Зачем появляются новые языки программирования? Сколько их и каких на самом деле нужно?

[zVlad]

В реальной же жизни как правило стоит прямо противоположная задача - с какого перепуга у нас тут появляются дубликаты и как их убрать. Но задачка для интервью неплохая.

Тут сразу же напрашивается классический вопрос для интервью - как удалить дубликаты одним sql стейтментом


Sent from my iPhone using Tapatalk Pro

Ну и как?

create table my_table (fld1 varchar(10));

insert into my_table (fld1) values ('aaa'), ('aaa'), ('bbb'), ('aaa'), ('bbb'), ('ccc');

delete from my_table a where ctid > (select min(ctid) from my_table b where a.fld1 = b.fld1) and ctid in (select ctid from my_table b where a.fld1 = b.fld1);

Это ситаксис Postgres. Для Oracle все то же самое, только вместо CTID использовать ROWID. Какой аналог внутренненго уникального идентификатора записи в DB2 я не в курсе, но можно поиграться с row_number() over ().

Насколько я понимаю в Вашем примере будут удалены все записи в таблице начиная с некоторогo минимального значения CTID (+1) найденного в подзапросе. Это могут быть не только строки с a.fld1 = b.fld1 Проверьти сами. Я добавил, кмк, недостающий предикат в Ваш код выше.

В DB2 тоже есть тип данных ROWID. Физический Идентификатор строки существовал всегда, с самого начала, но в нем нужды для программистов не было никакой. Тем более что что в теории реляционных баз данных таблицы содержат физически неупорядоченый набор строк. Тип данных ROWID появился сравнительно недавно (см. ниже из reference) .

Вообще проблема дубликатов не такая тривиальная как Вы ее представили здесь. В Вашем случае с таблицей с одной колонкой все просто и понятно. Понятно и просто если мы говорим об уникальности в смысле значений всех столбцов таблицы. Но более распространенно что уникальность определяется по поднабору столбцов (ключу). И вот у Вас имеется строки с дублирирующими значениями в ключе и разными значениями в других столбцах. Ваши действия?

ROWID
For a row ID type.
A table can have only one ROWID column. The values in a ROWID
column are unique for every row in the table and cannot be updated. You
must specify NOT NULL with ROWID

[sp123]

create table my_table (fld1 int, fld2 varchar(10), txt varchar(100));

select * from my_table;

fld1 fld2 txt
1 aaa whatever1
1 aaa whatever2
1 aaa whatever3
1 bbb whatever4
1 bbb whatever5
1 ccc whatever6

Хотим сделать уникальный ключ fld1 + fld2:

delete from my_table a where ctid > (select min(ctid) from my_table b where a.fld1 = b.fld1 and a.fld2 = b.fld2);

select * from my_table;

fld1 fld2 txt
1 aaa whatever1
1 bbb whatever4
1 ccc whatever6

[zVlad]

create table my_table (fld1 int, fld2 varchar(10), txt varchar(100));

select * from my_table;

fld1 fld2 txt
1 aaa whatever1
1 aaa whatever2
1 aaa whatever3
1 bbb whatever4
1 bbb whatever5
1 ccc whatever6

Хотим сделать уникальный ключ fld1 + fld2:

delete from my_table a where ctid > (select min(ctid) from my_table b where a.fld1 = b.fld1 and a.fld2 = b.fld2);

select * from my_table;

fld1 fld2 txt
1 aaa whatever1
1 bbb whatever4
1 ccc whatever6

Вы не внимательно читали мой текст. А если поле текст make sense? Например мы хочем оставить строку с wharever2? Тогда Ваш вариант решения makes no sense. Согласны?

[sp123]

Вы не внимательно читали мой текст. А если поле текст make sense? Например мы хочем оставить строку с wharever2? Тогда Ваш вариант решения makes no sense. Согласны?

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

Тогда давайте придумаем какой-нибудь удобоваримый критерий. Допустим, если по ключу попался наш любимый 'whatever2', то выбираем его. Если таких строк по ключу больше одной, то выбираем последнюю 'whatever2' по дате поступления в таблицу (заведем в таблице еще одну колонку created_date). Если 'whatever2' по ключу вообще нет, то выбираем просто последнюю запись по дате. Does make sense?

fld1 fld2 txt created_date
1 aaa whatever1 2021-01-04
1 aaa whatever2 2021-01-02
1 aaa whatever2 2021-01-03
1 aaa whatever3 2021-01-01
1 bbb whatever4 2021-01-01
1 bbb whatever5 2021-01-04
1 ccc whatever6 2021-01-01

Для подобного функционала лучше подходит способ # 2, про который я мельком упомянул выше:

delete from my_table where ctid in
(
select ctid
from (select t.ctid,
row_number() over (partition by fld1, fld2 order by case when txt = 'whatever2' then 0 else 1 end, created_date desc) as rank
from my_table t) tt
where tt.rank > 1
);

Результат:

fld1 fld2 txt created_date
1 aaa whatever2 2021-01-03
1 bbb whatever5 2021-01-04
1 ccc whatever6 2021-01-01

Задачка по сложности где-то между easy и intermediate.

[zVlad]

Вы не внимательно читали мой текст. А если поле текст make sense? Например мы хочем оставить строку с wharever2? Тогда Ваш вариант решения makes no sense. Согласны?

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

Тогда давайте придумаем какой-нибудь удобоваримый критерий. Допустим, если по ключу попался наш любимый 'whatever2', то выбираем его. Если таких строк по ключу больше одной, то выбираем последнюю 'whatever2' по дате поступления в таблицу (заведем в таблице еще одну колонку created_date). Если 'whatever2' по ключу вообще нет, то выбираем просто последнюю запись по дате. Does make sense?

fld1 fld2 txt created_date
1 aaa whatever1 2021-01-04
1 aaa whatever2 2021-01-02
1 aaa whatever2 2021-01-03
1 aaa whatever3 2021-01-01
1 bbb whatever4 2021-01-01
1 bbb whatever5 2021-01-04
1 ccc whatever6 2021-01-01

Для подобного функционала лучше подходит способ # 2, про который я мельком упомянул выше:

delete from my_table where ctid in
(
select ctid
from (select t.ctid,
row_number() over (partition by fld1, fld2 order by case when txt = 'whatever2' then 0 else 1 end, created_date desc) as rank
from my_table t) tt
where tt.rank > 1
);

Результат:

fld1 fld2 txt created_date
1 aaa whatever2 2021-01-03
1 bbb whatever5 2021-01-04
1 ccc whatever6 2021-01-01

Задачка по сложности где-то между easy и intermediate.

Оставим пока про whatever2 в стороне.
И давайте Вы все таки еще раз и внимательно прочитаете мой текст на Ваш первый пример и наконец, хотя бы, обратите внимание на то что я добавил к Вашшму "delete" и выделил подчеркиваниeм, жирным и large шрифтом.
Давайте?

[sp123]

И давайте Вы все таки еще раз и внимательно прочитаете мой текст на Ваш первый пример и наконец, хотя бы, обратите внимание на то что я добавил к Вашшму "delete" и выделил подчеркиваниeм, жирным и large шрифтом.
Давайте?

Добавленное условие "and ctid in (select ctid from my_table b where a.fld1 = b.fld1)" излишне и на результат не влияет.

Если есть желание поразмышлять дальше на эту тему, то можно, к примеру, глянуть вот на эту ветку, там этого добра много: https://stackoverflow.com/questions/529 ... -in-oracle

Указанный пример там тоже есть. Да, там Oracle, но подходы и варианты годятся для остальных баз.

[zVlad]

И давайте Вы все таки еще раз и внимательно прочитаете мой текст на Ваш первый пример и наконец, хотя бы, обратите внимание на то что я добавил к Вашшму "delete" и выделил подчеркиваниeм, жирным и large шрифтом.
Давайте?

Добавленное условие "and ctid in (select ctid from my_table b where a.fld1 = b.fld1)" излишне и на результат не влияет.

....

Обоснуй без ссылок, пожaлуйста. Опровергни.

[Uzito]

Дорогие друзья, задача "удалить дубликаты из таблицы" практически никогда не встречается. Это задача для интервью письками померяться кто изрощрённей метод придумает.
Любой ДБА вам скажет, что лучше сохранить старую таблицу и сделать новую без дубликатов как Вам хочется. А то мало ли что Вы там наудаляете в продакшене. У Вас будет возможность проверить новую табличку что все получилось как надо, а бэкап уже потом как-нибудь удалим, через месяц-два.

[zVlad]

Дорогие друзья, задача "удалить дубликаты из таблицы" практически никогда не встречается. Это задача для интервью письками померяться кто изрощрённей метод придумает.
Любой ДБА вам скажет, что лучше сохранить старую таблицу и сделать новую без дубликатов как Вам хочется. А то мало ли что Вы там наудаляете в продакшене. У Вас будет возможность проверить новую табличку что все получилось как надо, а бэкап уже потом как-нибудь удалим, через месяц-два.

Красиво поешь. А когда в базе данных полторы тысячи таблиц?

[Uzito]

Дорогие друзья, задача "удалить дубликаты из таблицы" практически никогда не встречается. Это задача для интервью письками померяться кто изрощрённей метод придумает.
Любой ДБА вам скажет, что лучше сохранить старую таблицу и сделать новую без дубликатов как Вам хочется. А то мало ли что Вы там наудаляете в продакшене. У Вас будет возможность проверить новую табличку что все получилось как надо, а бэкап уже потом как-нибудь удалим, через месяц-два.

Красиво поешь. А когда в базе данных полторы тысячи таблиц?

Вопрос из серии "напугал ежа голой жопой". У меня 1800+ таблиц только в одном приложении. В другом приложении 12000+ пользовательских схем с 200+ таблиц в каждой.

[sp123]

Я сейчас в гостях. Пью пиво. Завтра утром выдам развернутый ответ


Sent from my iPhone using Tapatalk Pro

[KinDzaDza]

И давайте Вы все таки еще раз и внимательно прочитаете мой текст на Ваш первый пример и наконец, хотя бы, обратите внимание на то что я добавил к Вашшму "delete" и выделил подчеркиваниeм, жирным и large шрифтом.
Давайте?

Добавленное условие "and ctid in (select ctid from my_table b where a.fld1 = b.fld1)" излишне и на результат не влияет.

....

Обоснуй без ссылок, пожaлуйста. Опровергни.

Хватает только условия на a.ROWID > (SELECT min(ROWID) FROM T as b WHERE a.fld1 = b.fld1 [AND a.fld2 = b.fld2 …])
и дополнительно проверять что этот a.ROIWID находится в подмножестве «правильных» ROWIDs именно для этих же значений ключевых полей fld1, fld2, … не нужно, потому что они и так этим подзапросом про min(ROWID) уже выбираются для этих и только для этих значений ключевых полей.

Насколько я понимаю в Вашем примере будут удалены все записи в таблице начиная с некоторогo минимального значения CTID (+1) найденного в подзапросе. Это могут быть не только строки с a.fld1 = b.fld1 Проверьти сами. Я добавил, кмк, недостающий предикат в Ваш код выше.

Вы ошибаетесь, все записи начиная с некоторого минимального значения ROWID удалены не будут, потому-то подзапрос на мин ROWID будет вычисляться и проверяться каждый раз для каждой записи с теми же значениями ключевых полей, а не один раз на весь DELETE.

[iDesperado]

Вы ошибаетесь, все записи начиная с некоторого минимального значения ROWID удалены не будут, потому-то подзапрос на мин ROWID будет вычисляться и проверяться каждый раз для каждой записи с теми же значениями ключевых полей, а не один раз на весь DELETE.

фигасе, это жэ баг. с чего бы это оптимизатор обязан исключительно nested loop организовывать ?
я проверил, в оракле с rownum такое не работает.

[zVlad]

И давайте Вы все таки еще раз и внимательно прочитаете мой текст на Ваш первый пример и наконец, хотя бы, обратите внимание на то что я добавил к Вашшму "delete" и выделил подчеркиваниeм, жирным и large шрифтом.
Давайте?

Добавленное условие "and ctid in (select ctid from my_table b where a.fld1 = b.fld1)" излишне и на результат не влияет.

....

Обоснуй без ссылок, пожaлуйста. Опровергни.

Хватает только условия на a.ROWID > (SELECT min(ROWID) FROM T as b WHERE a.fld1 = b.fld1 [AND a.fld2 = b.fld2 …])
и дополнительно проверять что этот a.ROIWID находится в подмножестве «правильных» ROWIDs именно для этих же значений ключевых полей fld1, fld2, … не нужно, потому что они и так этим подзапросом про min(ROWID) уже выбираются для этих и только для этих значений ключевых полей.

Насколько я понимаю в Вашем примере будут удалены все записи в таблице начиная с некоторогo минимального значения CTID (+1) найденного в подзапросе. Это могут быть не только строки с a.fld1 = b.fld1 Проверьти сами. Я добавил, кмк, недостающий предикат в Ваш код выше.

Вы ошибаетесь, все записи начиная с некоторого минимального значения ROWID удалены не будут, потому-то подзапрос на мин ROWID будет вычисляться и проверяться каждый раз для каждой записи с теми же значениями ключевых полей, а не один раз на весь DELETE.

Подзапрос возвращает каждый раз ровно одно значение, значение минимума rowid's всех строк удовлетворяющих предикату в подзапросе. Основной запрос на каждый найденый минимум будет удалять ВСЕ строки с rowid большими чем найденый в подзапросе. Это могут быть дубликаты по условию задачи и любые другие строки, не дубликаты.
Фактически, да все дубликаты, кроме одного, будут удалены в первой же итерации на подзапросе, но вместе с ними будут удалены и все остальные строки за той что будет обработана первой.
Даже если первая строка в таблице не имеет дубликатов delete будет выполнен и в результате в таблице останется одна строка. Первая если delete работает в порядке порядке возрастания rowid's, что в общем случае не обязательно поскольку по теории физический порядок строк не зависит от значений в столбцах, даже если это rowid.
Таким образом результат этого delete будет не предсказуемым. Таблица будет проряжена и транкейтнута случайным образом.

[zVlad]

Дорогие друзья, задача "удалить дубликаты из таблицы" практически никогда не встречается. Это задача для интервью письками померяться кто изрощрённей метод придумает.
Любой ДБА вам скажет, что лучше сохранить старую таблицу и сделать новую без дубликатов как Вам хочется. А то мало ли что Вы там наудаляете в продакшене. У Вас будет возможность проверить новую табличку что все получилось как надо, а бэкап уже потом как-нибудь удалим, через месяц-два.

Красиво поешь. А когда в базе данных полторы тысячи таблиц?

Вопрос из серии "напугал ежа голой жопой". У меня 1800+ таблиц только в одном приложении. В другом приложении 12000+ пользовательских схем с 200+ таблиц в каждой.

Отлично. Ну а теперь примените предложенный Вами метод борьбы с дубликатами к таким БД которые у Вас.
Кстати, это нонсенс создавать такие архитектуры как у Вас.