repetir - sql eliminar registros duplicados menos uno

SQL: ¿Cómo encontrar duplicados basados en dos campos?

Question

Tengo filas en una tabla de base de datos de Oracle que deberían ser únicas para una combinación de dos campos, pero la restricción única no está configurada en la tabla, así que necesito encontrar todas las filas que violen la restricción usando SQL. Desafortunadamente, mis escasas habilidades de SQL no están a la altura de la tarea.

Mi tabla tiene tres columnas que son relevantes: entity_id, station_id y obs_year. Para cada fila, la combinación de station_id y obs_year debería ser única, y quiero averiguar si hay filas que lo violen al eliminarlas con una consulta SQL.

He intentado el siguiente SQL (sugerido por esta pregunta anterior ) pero no funciona para mí (obtengo la columna ORA-00918 definida de forma ambigua):

SELECT
entity_id, station_id, obs_year
FROM
mytable t1
INNER JOIN (
SELECT entity_id, station_id, obs_year FROM mytable
GROUP BY entity_id, station_id, obs_year HAVING COUNT(*) > 1) dupes
ON
t1.station_id = dupes.station_id AND
t1.obs_year = dupes.obs_year

¿Puede alguien sugerir lo que estoy haciendo mal y / o cómo resolver esto?

Answer 1

¿No podría crear una nueva tabla que incluya la restricción única y luego copiarla fila por fila, ignorando las fallas?

Answer 2

Cambia los 3 campos en la selección inicial para ser

SELECT
t1.entity_id, t1.station_id, t1.obs_year

Answer 3

Debe especificar la tabla para las columnas en la selección principal. Además, suponiendo que entity_id es la clave única para mytable y es irrelevante para encontrar duplicados, no debe agruparse en la subconsulta de duplicados.

Tratar:

SELECT t1.entity_id, t1.station_id, t1.obs_year
FROM mytable t1
INNER JOIN (
SELECT station_id, obs_year FROM mytable
GROUP BY station_id, obs_year HAVING COUNT(*) > 1) dupes
ON
t1.station_id = dupes.station_id AND
t1.obs_year = dupes.obs_year

Answer 4

Re-escritura de su consulta.

SELECT
t1.entity_id, t1.station_id, t1.obs_year
FROM
mytable t1
INNER JOIN (
SELECT entity_id, station_id, obs_year FROM mytable
GROUP BY entity_id, station_id, obs_year HAVING COUNT(*) > 1) dupes
ON
t1.station_id = dupes.station_id AND
t1.obs_year = dupes.obs_year

Creo que el error de columna ambiguo (ORA-00918) se debió a que estaba select columnas cuyos nombres aparecían tanto en la tabla como en la subconsulta, pero no especificó si lo quería de dupes o de mytable (alias como t1 ).

Answer 5

SELECT *
FROM (
SELECT t.*, ROW_NUMBER() OVER (PARTITION BY station_id, obs_year ORDER BY entity_id) AS rn
FROM mytable t
)
WHERE rn > 1

Answer 6

SELECT *
FROM (
SELECT t.*, ROW_NUMBER() OVER (PARTITION BY station_id, obs_year ORDER BY entity_id) AS rn
FROM mytable t
)
WHERE rn > 1

Por Quassnoi es el más eficiente para mesas grandes. Tuve este análisis de costo:

SELECT a.dist_code, a.book_date, a.book_no
FROM trn_refil_book a
WHERE EXISTS (SELECT 1 from trn_refil_book b Where
a.dist_code = b.dist_code and a.book_date = b.book_date and a.book_no = b.book_no
AND a.RowId <> b.RowId)
;

Dio un costo de 1322341

SELECT a.dist_code, a.book_date, a.book_no
FROM trn_refil_book a
INNER JOIN (
SELECT b.dist_code, b.book_date, b.book_no FROM trn_refil_book b
GROUP BY b.dist_code, b.book_date, b.book_no HAVING COUNT(*) > 1) c
ON
a.dist_code = c.dist_code and a.book_date = c.book_date and a.book_no = c.book_no
;

Dio un costo de 1271699

mientras

SELECT dist_code, book_date, book_no
FROM (
SELECT t.dist_code, t.book_date, t.book_no, ROW_NUMBER() OVER (PARTITION BY t.book_date, t.book_no
ORDER BY t.dist_code) AS rn
FROM trn_refil_book t
) p
WHERE p.rn > 1
;

Dio un costo de 1021984

La tabla no fue indexada ....

Answer 7

SELECT entity_id, station_id, obs_year
FROM mytable t1
WHERE EXISTS (SELECT 1 from mytable t2 Where
t1.station_id = t2.station_id
AND t1.obs_year = t2.obs_year
AND t1.RowId <> t2.RowId)

Answer 8

 SELECT entity_id, station_id, obs_year
FROM mytable
GROUP BY entity_id, station_id, obs_year
HAVING COUNT(*) > 1

Especifique los campos para encontrar duplicados tanto en SELECT como en GROUP BY.

Funciona utilizando GROUP BY para buscar cualquier fila que coincida con cualquier otra fila basada en las Columnas especificadas. La cuenta HAVING COUNT(*) > 1 dice que solo estamos interesados ​​en ver las filas que se producen más de 1 vez (y por lo tanto son duplicados)