Início / dba / Perguntas / 28129
Accepted
icefex
Asked: 2012-11-04 07:56:53 +0800 CST

Como faço para remover entradas duplicadas em um determinado período de tempo?

  • 772

Minha tabela está assim:

+-----------+
|     id    | <- primary key
|    time   | <- DateTime
|    row2   | <- varchar
|    row3   |
|    ...    |
+-----------+

Desejo remover todas as entradas, exceto uma, que está dentro de um período de uma hora inteira e tem o mesmo valor de linha2 (linha2 distinta). É possível fazer isso sem um procedimento armazenado e/ou código não SQL?

mysql

2 respostas

  1. Best Answer

    A solução usual para esse problema é primeiro procurar as linhas que você deseja manter e, em seguida, excluir todas as outras. A primeira parte pode ser realizada com um agregado e agrupamento:

    SELECT min(id)
FROM my_table
GROUP BY row2, DATE_FORMAT(`time`, '%Y-%m-%d %H')

    Então você pode escrever o seu DELETE:

    DELETE FROM my_table
WHERE id NOT IN (
    SELECT min(id)
    FROM my_table
    GROUP BY row2, DATE_FORMAT(`time`, '%Y-%m-%d %H')
);

    Existem variações para esse tema: você pode usar uma NOT EXISTSconstrução em vez de NOT IN(com as alterações apropriadas) ou uma junção usando a palavra- USINGchave.

    • 4

  2. Em 22 de fevereiro de 2011, respondi a uma pergunta sobre uma consulta DELETE usando uma cláusula WHERE contra ela mesma: ( Problema com a subconsulta do MySQL ).

    Aqui está uma abordagem mais segura:

    • Crie uma tabela temporária chamada mytable_key
    • Colete chaves com IDs mínimos por linha2 por hora em mytable_key
    • Crie uma tabela temporária chamada mytable_new
    • Junte todas as chaves em mytable_key com mytable e carregue-as em mytable_new
    • Renomeie mytable para mytable_old
    • Renomeie mytable_new para mytable
    • Eliminar mytable_old
    • Solte mytable_key

    Aqui está o SQL real:

    CREATE TABLE mytable_key
    SELECT id FROM mytable
    WHERE 1=2
;
INSERT INTO mytable_key
    SELECT MIN(id) FROM mytable
    GROUP BY row2,DATE_FORMAT(`time`,'%Y%m%d%H')
;
ALTER TABLE mytable_key ADD PRIMARY KEY (id);
CREATE TABLE mytable_new LIKE mytable;
INSERT INTO mytable_new
    SELECT B.* FROM mytable_key A
    INNER JOIN mytable B USING (id)
;
ALTER TABLE mytable RENAME mytable_old;
ALTER TABLE mytable_new RENAME mytable;
DROP TABLE mytable_old;
DROP TABLE mytable_key;

    Você pode executar esses comandos como estão ou colocá-los em um procedimento armazenado.

    Embora eu dê +1 à resposta de dezso porque funciona mecanicamente no Oracle, PostgreSQL e SQL Server, tire-o de um MySQL DBA, não confio na abordagem do MySQL para otimização interna do SQL por meio de transformação ao fazer um DELETE em uma tabela contra si mesmo. É por isso que enviei esta resposta.

    • 2

relate perguntas