Eliminar filas duplicadas de la base de datos sqlite

Tengo una enorme tabla - 36 millones de filas - en SQLite3.

En esta tabla muy grande, hay dos columnas

  • hash-text
  • d - real

Sin embargo, algunas de las filas son duplicadas. Es decir, tanto hash como d tienen los mismos valores.

También, si dos hashes son idénticos, también lo son los valores de d, pero dos ds idénticos no implican dos hashes idénticos

De todos modos, quiero eliminar las filas duplicadas. No tengo una columna de clave primaria. ¿Qué es ¿la forma más rápida de hacer esto?

EDITAR: delete from dist where rowid not in (select max(rowid) from dist group by hash);

Parece hacer el truco.

Author: Jaydeep Karena, 2011-11-19
Source

3 answers

Necesita una forma de distinguir las filas. Basado en tu comentario, podrías usar la columna especial rowid para eso.

Para eliminar duplicados manteniendo el menor rowid por (hash,d): 

delete   from YourTable
where    rowid not in
         (
         select  min(rowid)
         from    YourTable
         group by
                 hash
         ,       d
         )
 99
Author: Andomar,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-09-14 03:05:46

Supongo que lo más rápido sería usar la misma base de datos para ello: agregar una nueva tabla con las mismas columnas, pero con restricciones adecuadas (¿un índice único en el par hash/real?), itere a través de la tabla original e intente insertar registros en la nueva tabla, ignorando los errores de violación de restricción (es decir, continúe iterando cuando se generan excepciones).

Luego elimine la tabla antigua y cambie el nombre de la nueva a la antigua.

 5
Author: MaDa,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-11-19 00:04:44

Si agregar una clave primaria no es una opción, entonces un enfoque sería almacenar los duplicados DISTINTOS en una tabla temporal, eliminar todos los registros duplicados de la tabla existente y luego agregar los registros a la tabla original de la tabla temporal.

Por ejemplo (escrito para SQL Server 2008, pero la técnica es la misma para cualquier base de datos): 

DECLARE @original AS TABLE([hash] varchar(20), [d] float)
INSERT INTO @original VALUES('A', 1)
INSERT INTO @original VALUES('A', 2)
INSERT INTO @original VALUES('A', 1)
INSERT INTO @original VALUES('B', 1)
INSERT INTO @original VALUES('C', 1)
INSERT INTO @original VALUES('C', 1)

DECLARE @temp AS TABLE([hash] varchar(20), [d] float)
INSERT INTO @temp
SELECT [hash], [d] FROM @original 
GROUP BY [hash], [d]
HAVING COUNT(*) > 1

DELETE O
FROM @original O
JOIN @temp T ON T.[hash] = O.[hash] AND T.[d] = O.[d]

INSERT INTO @original
SELECT [hash], [d] FROM @temp

SELECT * FROM @original

No estoy seguro de si sqlite tiene una función de tipo ROW_NUMBER(), pero si lo hace, también podría probar algunos de los enfoques enumerados aquí: Eliminar registros duplicados de una tabla SQL sin una clave primaria

 1
Author: rsbarro,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-23 12:10:39