repetir - sql eliminar registros duplicados menos uno

SQL para encontrar entradas duplicadas(dentro de un grupo)

Question

SQL para encontrar entradas duplicadas (dentro de un grupo)

Tengo un pequeño problema y no estoy seguro de cuál sería la mejor manera de solucionarlo, ya que solo tengo acceso limitado a la base de datos (Oracle). En nuestra Tabla "EVENTO" tenemos aproximadamente 160k entradas, cada EVENTO tiene un GRUPO y una entrada normal tiene exactamente 5 filas con el mismo GRUPO. Debido a un error, actualmente obtenemos un par de entradas duplicadas (duplicadas, por lo que 10 filas en lugar de 5, solo un EVENTID diferente. Esto puede cambiar, por lo que es <> 5). Necesitamos filtrar todas las entradas de estos grupos.

Debido al acceso limitado a la base de datos, no podemos usar una tabla temporal, ni podemos agregar un índice a la columna GROUPID para hacerlo más rápido.

Podemos obtener los GROUPID con esta consulta, pero necesitaríamos una segunda consulta para obtener los datos necesarios

select A."GROUPID"
from "EVENT" A
group by A."GROUPID"
having count(A."GROUPID") <> 5

Una solución sería una subselección:

select *
from "EVENT" A
where A."GROUPID" IN (
  select B."GROUPID"
  from "EVENT" B
  group by B."GROUPID"
  having count(B."GROUPID") <> 5
)

Sin un índice en GROUPID y 160k entradas, esto lleva demasiado tiempo. Intenté pensar en una unión que pueda manejar esto, pero no puedo encontrar una buena solución hasta el momento.

¿Alguien puede encontrar una buena solución para esto tal vez?

Edición pequeña: no tenemos el 100% de duplicados aquí, ya que cada entrada tiene una ID única y GROUPID tampoco es única (por eso tenemos que usar "agrupar por") - o tal vez solo extraño una solución fácil para eso :)

Pequeño ejemplo sobre los datos (no quiero borrarlo, solo encontrarlo)

EVENTID | GROUPID | TYPEID
123456 123 12
123457 123 145
123458 123 2612
123459 123 41
123460 123 238

234567 123 12
234568 123 145
234569 123 2612
234570 123 41
234571 123 238

Tiene algunas columnas más, como la marca de tiempo, etc., pero como puede ver ya, todo es idéntico, además del EVENTID.

Lo ejecutaremos con más frecuencia para realizar pruebas, para encontrar el error y verificar si ocurre nuevamente.

Answer 1

¿Cuánto tiempo realmente toma SQL? ¿Solo lo ejecutará una vez que presumo, habiendo arreglado el error que causó la corrupción en primer lugar? Acabo de configurar un caso de prueba como este:

SQL> create table my_objects as 
  2  select object_name, ceil(rownum/5) groupid, rpad(''x'',500,''x'') filler
  3  from all_objects;

Table created.

SQL> select count(*) from my_objects;

  COUNT(*)
----------
     83782

SQL> select * from my_objects where groupid in (
  2  select groupid from my_objects
  3  group by groupid
  4  having count(*) <> 5
  5  );

OBJECT_NAME                       GROUPID FILLER
------------------------------ ---------- --------------------------------
XYZ                                 16757 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
YYYY                                16757 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Elapsed: 00:00:01.67

Menos de 2 segundos OK, mi mesa tiene la mitad de filas que la tuya, pero 160K no es muy grande. Agregué la columna de relleno para hacer que la tabla ocupara espacio en el disco. El plan de ejecución de AUTOTRACE fue:

-------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |   389 |   112K| 14029   (2)|
|*  1 |  HASH JOIN            |            |   389 |   112K| 14029   (2)|
|   2 |   VIEW                | VW_NSO_1   | 94424 |  1198K|  6570   (2)|
|*  3 |    FILTER             |            |       |       |            |
|   4 |     HASH GROUP BY     |            |     1 |  1198K|  6570   (2)|
|   5 |      TABLE ACCESS FULL| MY_OBJECTS | 94424 |  1198K|  6504   (1)|
|   6 |   TABLE ACCESS FULL   | MY_OBJECTS | 94424 |    25M|  6506   (1)|
-------------------------------------------------------------------------

Answer 2

¿Este trabajo hace lo que quieres y ofrece un mejor rendimiento? (Pensé que lo lanzaría como una sugerencia).

select * 
from group g
where (select count(*) from event e where g.groupid = e.groupid) <> 5

Answer 3

¿Qué tal un analítico?

SELECT * FROM (
SELECT eventid, groupid, typeid, COUNT(groupid) OVER (PARTITION BY groupid) group_count
  FROM event
)
  WHERE group_count <> 5

Answer 4

Desde una perspectiva SQL, creo que ya has respondido tu propia pregunta. El enfoque que ha descrito (es decir, el uso de la sub selección) está bien, y me sorprendería que cualquier otra forma de escribir la consulta difiera enormemente en el rendimiento.

160K registros no parece mucho para mí. Pude entender si no estaba contento con la ejecución de esa consulta si entraba en una parte del código de la aplicación, pero a juzgar por los sonidos, solo la está usando como parte de un ejercicio de limpieza de datos. (y por lo tanto esperaría que sea un poco más tolerante en términos de rendimiento).

Incluso sin ningún índice de respaldo, sigue habiendo solo dos escaneos completos de tabla de tabla en filas de 160K, lo que francamente, esperaría realizar en algún tipo de tiempo vagamente razonable.

Habla con tus administradores de DB. Han ayudado a crear el problema, así que déjalos ser parte de la solución.

/ EDIT / Mientras tanto, ejecuta la consulta que tienes. Averigüe cuánto tiempo lleva, en lugar de adivinar. Aún mejor sería ejecutarlo, con establecer la marcha automática, y publicar los resultados aquí, entonces podríamos ayudarlo a refinarlo un poco.

Answer 5

Puede obtener la respuesta con una combinación en lugar de una subconsulta

select
    a.*
from
    event as a
inner join
    (select groupid
     from event
     group by groupid
     having count(*) <> 5) as b
  on a.groupid = b.groupid

Esta es una forma bastante común de obtener toda la información de las filas en un grupo.

Al igual que su respuesta sugerida y las otras respuestas, esto se ejecutará mucho más rápido con un índice en groupid. Depende del DBA equilibrar el beneficio de hacer que su consulta se ejecute mucho más rápido contra el costo de mantener otro índice.

Si el DBA decide contra el índice, asegúrese de que las personas adecuadas comprendan que es la estrategia de índice y no la forma en que escribió la consulta lo que está ralentizando las cosas.

Answer 6

Si sus DBA no agregarán un índice para hacerlo más rápido, pregúnteles qué sugieren que haga (para eso se les paga, después de todo). Presumiblemente, usted tiene un caso de negocio por el cual necesita esta información, en cuyo caso su administración inmediata debe estar de su lado.

Tal vez podría pedirles a sus DBA que dupliquen los datos en una base de datos donde podrían agregar un índice.

Answer 7

Un problema clásico para las consultas analíticas para resolver:

select eventid,
       groupid,
       typeid
from   (
       Select eventid,
              groupid,
              typeid,
              count(*) over (partition by group_id) count_by_group_id
       from   EVENT
       )
where count_by_group_id <> 5