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

Question

Mariadb: mysql  Ver 15.1 Distrib 10.3.8-MariaDB

У меня есть таблица, построенная как объединение запросов из нескольких других таблиц. Эта таблица выглядит примерно так

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| source_id   | int(11)      | YES  |     | NULL    |                |
| code        | varchar(64)  | YES  |     | NULL    |                |
| description | varchar(255) | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

Поле кода может содержать "лишнюю" информацию в фигурных скобках {думать о них как о комментариях}. Я хочу найти все строки, где поле кода равно, если комментарии игнорируются, а затем оставить только одну строку из каждой дублирующейся группы.

Это бит "держать один ряд в каждой группе", который вызывает у меня проблемы.

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

select id
from mytable
group by (regexp_replace(code, '(?-i){.*?}', ''))
having count(*) > 1

но если я сделаю это подзапросом в операции DELETE WHERE, он удалит все дубликаты. Не то, что я хочу.

После поиска функции «все, кроме одного» или "вытолкнуть одного из списка вывода" я наткнулся на версию LIMIT с двумя аргументами и OFFSET-

select id
from mytable
group by (regexp_replace(code, '(?-i){.*?}', ''))
having count(*) > 1 limit 1000000 offset 1

но если вы поместите это в команду DELETE, это не удастся

delete from mytable where id in(
    select id
    from mytable
    group by (regexp_replace(code, '(?-i){.*?}', ''))
    having count(*) > 1 limit 1000 offset 1
);
ERROR 1235 (42000): This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Есть идеи как этого добиться? Предпочтительно на месте, но в худшем случае я мог бы ВЫБРАТЬ во временную таблицу, а затем заменить оригинал, если это то, что нужно.

Answer 1

Первоначально я получил эту работу, выбрав потерянные результаты в новую таблицу

truncate dedup_table;
insert into dedup_table
    (source_id, source, code, description)
    select source_id, source, code, description
        from hasdupes_table
        where id in
        (select min(id) from hasdupes_table
         group by regexp_replace(code, '(?-i){.*?}', ''));

По сути, это тот же подход, который рекомендован @berty, но с использованием подзапроса вместо временной таблицы.

Затем я понял, что это можно сделать так, как я планировал, инвертировав подзапрос с помощью NOT IN, чтобы определить, какие строки следует удалить из таблицы "hasdupes".

Я на самом деле не проверял это, но я верю, что это сработает

delete from hasdupes_table where id not in
            (select min(id) from hasdupes_table
             group by regexp_replace(code, '(?-i){.*?}', ''));

Answer 2

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

Мы можем сделать это, поместив идентификаторы строк, которые мы хотим сохранить, во временную таблицу, а затем удалив все строки, идентификаторы которых отсутствуют в этом списке.

Я разработал небольшой пример сценария, чтобы сделать его простым:

DROP TABLE IF EXISTS hasdupes;
CREATE TABLE hasdupes (
 Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
 Value VARCHAR(100)
) ENGINE=InnoDB;

INSERT INTO hasdupes (Value) VALUES ('red'), ('green'), ('blue'), ('blue'),
('green'), ('blue'), ('red'), ('orange'), ('red'), ('blue'), ('green'), ('white');

SELECT Value, COUNT(*) FROM hasdupes GROUP BY Value;
/*
blue    4
green   3
orange  1
red     3
white   1
*/

/* SELECTS an Id for each value in a temporary table */
CREATE TEMPORARY TABLE keep SELECT Id FROM hasdupes GROUP BY Value;
DELETE FROM hasdupes WHERE hasdupes.Id NOT IN (SELECT Id FROM keep);
DROP TEMPORARY TABLE keep;

SELECT Value, COUNT(*) FROM hasdupes GROUP BY Value;
/*
blue    1
green   1
orange  1
red     1
white   1
*/