MySQL, reporte "Registros duplicados" (Solucionado)

[^Tifa^]

Ya te entendi, aunque dejame decirte que eso que pides por NOrmalizacion, no existe o sea se da el caso que pides y es posible hacerlo en 2 tablas distintas... pero en 1 misma tabla a la vez? O sea esa condicion que pides de verdad no suele darse en una misma tabla... Estoy buscando una via factible.. aunque esto se salga de la Normalizacion puedes probar hacer esto ::

mysql> select grupo, null id
    -> from tabla
    -> group by grupo
    -> having count(grupo)>1
    -> union all
    -> select null grupo, id
    -> from tabla
    -> group by id
    -> having count(id)>1;
+-------+------+
| grupo | id   |
+-------+------+
| 1     | NULL |
| 2     | NULL |
| NULL  | 1    |
| NULL  | 2    |
| NULL  | 3    |
| NULL  | 4    |
+-------+------+
6 rows in set (0.00 sec)

Recuerda que en ambos campos hay distintos numeros de columnas con registros repetidos y por este hecho no pueden caer ambos campos juntos, por ende estoy buscando una forma de no utilizar NULL para rellenar los faltantes..... Pero realmente no es algo comun de ver.

[alone-in-the-chat]

Ahora cuando yo ejecuro mi consulta:

Código (sql) [Seleccionar] Expandir


SELECT * FROM tabla WHERE id IN (SELECT id FROM tabla AS x
GROUP BY id HAVING count(*)>1 AND tabla.id=x.id)
ORDER BY id;


Obtengo el siguiente resultado.

id   grupo
1   1
1   2
2   1
2   2
3   1
3   2
4   1
4   1
4   2

Cuando digo extender la consulta anterior, yo desearia que solo me indicase:

id   grupo
4   1
4   1

Solo los que se repiten.

Cuando mensione que la consulta que hago si me sirve, en realidad si sirve, pero tengo que comprar visualmente, cuales son los que en verdad se repite el id y el grupo, y ese es el facto que quiero eliminar.

Si solo deseas mostrar los repetidos pues..

Código (sql) [Seleccionar] Expandir


SELECT *
FROM tabla
WHERE CONCAT( id, grupo )
IN (

SELECT CONCAT( id, grupo )
FROM tabla
GROUP BY CONCAT( id, grupo )
HAVING count( CONCAT( id, grupo ) ) >1
)


Queria ver si me podia salir y si me salio 

Saludos¡¡

[^Tifa^]

Creo que ahi tienes tu respuesta, si era eso lo que estabas buscando que aun no lo se    tengo una confusion sobre si son todos los registros que se repiten en cada campo o es todos los registros que concuerdan en los 2 campos...

Pero sea cual sea el caso, espero que la respuesta anterior te haya servido
Aunque no veo la necesidad de tantos CONCAT.

select * from tabla
where(id, grupo)
in(
select id, grupo from tabla
group by id, grupo
having count(*)>1);

[AlbertoBSD]

Bien, si es lo que estaba buscando.

Código (sql) [Seleccionar] Expandir


SELECT *
FROM tabla
WHERE CONCAT( id, grupo )
IN (

SELECT CONCAT( id, grupo )
FROM tabla
GROUP BY CONCAT( id, grupo )
HAVING count( CONCAT( id, grupo ) ) >1
)



Funciona

Código (sql) [Seleccionar] Expandir

select * from tabla
where(id, grupo)
in(
select id, grupo from tabla
group by id, grupo
having count(*)>1);


Funciona .

Pues la de ^TiFa^ no tiene que concatenar, eso significa que gasta menos procesador y memoria a la hora de ejecutarlo sobre una tabla mediana de uno 7000 Registros.

Se los agradezco a los 2. Muchisimas gracias.

Ahora podre evitar la tediosidad de estar revisando visualmente todos los campos repetidos.

Saludos.

[alone-in-the-chat]

 

Si tienes razon no necesitaba tantos concat .

Me tomo 5 min. hacerlo no tuve tiempo ( eso tan escurridizo ) de optimizar nada , vi el post dije a ver funcionara asi?? y funciono.

La proxima me tomare una media de 10 min. para hacer una consulta n_n o revisare mi consulta para ver que mejorarle .

Un consejo para Anon , no te compliques con cosas sencillas .

Saludos¡¡

[AlbertoBSD]

Ya esta mi estimado, no me complicare tanto, al fin y al cabo las cosas muchas veces se resuelven de manera mas simple que quien sabe por que nunca la vez a la primera.

Saludos y gracias.

[^Tifa^]

El nino no se complica es normal que pregunte.

Mi caso fue peor que no comprendi en todo el post que era lo que realmente el estaba solicitando .. nisiquiera porque me lo explico con cucharitas 

Yo tenia claro en mi cabeza que el deseaba todos los registros que se repetian en ambos campos sin importar si conjugaban o no en los 2 al mismo tiempo.... y me estaba haciendo un ocho porque una peticion asi es un poco imposible... porque rompe el agrupamiento de los registros al haber mas o menos duplicados en cada campo... 

Pero me alegra sobremanera.. que otro nino haya entendido la peticion en si, y haya podido ayudarte    creo que a partir de ahora estaras mas amistoso con Mysql 

[AlbertoBSD]

Bueno, he encontrado otra forma de encontrar los registros duplicados de una manera muchísimo mas Eficiente. La anterior era totalmente ineficiente con bases de datos grande.

Al principio sirvió Fantástico pero he llegado al punto que se vuelve eterno esperar la respuesta del servidor MySQL ya que la información a crecido muy rápidamente, ahora estoy hablando de ~45K aproximadamente.

Generaré 50K registros con el siguiente codigo en C:

Código (c) [Seleccionar] Expandir


#include<stdio.h>

int main() {
srandomdev();
unsigned short i=0;
unsigned char a,b;
while(i < 50000) {
a = (unsigned char) random();
b = (unsigned char) random();
printf("%d,%d\n",a,b);
i++;
}
return 0;
}



Despues de compilarlo y ejecutarlo

Código (bash) [Seleccionar] Expandir

Anon@localhost % gcc csvCrandom.c -o csvCrandom
Anon@localhost % ./csvCrandom


Nos devuelve una salida muy extensa asi:

79,183
213,188
177,147
31,158
90,157
230,190
40,222
176,219
37,14
237,130
116,104
66,145
...

Asi unas 50K veces, lo comprobamos con:

Código (bash) [Seleccionar] Expandir


Anon@localhost % ./csvCrandom | wc -l
   50000


Despues de Mandar la salida al archivo:

Código (bash) [Seleccionar] Expandir


Anon@localhost % ./csvCrandom > ./csvRandom.csv
Anon@localhost % wc -l csvRandom.csv
   50000 csvRandom.csv


Vemos que tiene nuestras 50K Lineas entonces desde MySQL empezamos desde 0 con el codigo que mistre anteriormente Modificado:

Código (sql) [Seleccionar] Expandir


DROP DATABASE IF EXISTS `db`;
CREATE DATABASE  `db`;
USE db;

DROP TABLE IF EXISTS `tabla`;

CREATE TABLE tabla (
id VARCHAR(10) NULL,
grupo  VARCHAR(10) NULL
);

LOAD DATA INFILE '/path/to/file/csvRandom.csv' INTO TABLE tabla FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';


Una vez cargada la información comprobábamos en MySQL que se carge correctamente con count

mysql> Select count(*) from tabla;
+----------+
| count(*) |
+----------+
|    50000 |
+----------+
1 row in set (0.00 sec)

Vemos que todo esta bien, ahora si llamamos al primer escript para comparar vemos que podría tardar horas y muchos se desesperarían.

Primer script:

Código (sql) [Seleccionar] Expandir

SELECT * FROM tabla
WHERE(id, grupo)
IN(
SELECT id, grupo FROM tabla
GROUP BY id, grupo
HAVING count(*)>1);

Ahora si lo reducimos a una línea y sin subconsulta queda asi:

Segundo script

Código (sql) [Seleccionar] Expandir

Select *,count(*) as 'Veces Repetido' from tabla group by concat(id,grupo) Having count(*)>1;

Vemos que no dura mas de 2 Segundos en ejecutarse Y encuentra la misma cantidad registros duplicados.

Tal vez ya lo hubiesen pensado, sin embargo como no lo encontré publicado en el foro, lo pongo. Espero y le sirva a alguien mas.

Saludos.

[^Tifa^]

Anon que bueno que continues aprendiendo mas MySQL.

Ahora, obviamente esta consulta :

Código (sql) [Seleccionar] Expandir


SELECT * FROM tablaWHERE(id, grupo)IN(SELECT id, grupo FROM tablaGROUP BY id, grupoHAVING count(*)>1);


Tarda una vida entera en una tabla con una cantidad de registro enormes... hablamos de millones. Y la respuesta es mas que simple, en la consulta anterior no se esta filtrando por indices en ninguna parte, en el primer caso expuesto por ti, al ser 7000 registros pues la cosa no era complicada y no merecia filtrar la verdad sobretodo con la rapida respuesta de MySQL con las clausulas SELECT en tablas MyIsam.

Pero por experiencia personal, cuando debes hacer una consulta de registros masivos digamos millones de estos.. nosotr@s filtramos por indices. He visto tablas en Oracle donde inclusive el DBA creaba hasta 20 indices, y todo precisamente para que los analistas pudiesemos filtrar efectivamente y optimizar una consulta y no tardar 30 minutos esperando algo que puedo obtener en segundos con indices. Pero subsecuentemente te iras familiarizando mas y mas con cada necesidad de SQL el camino no es corto, pero vale la pena.

Tengo una anecdota muy peculiar, un programador profesional en PHP que conozco que hace gala de su certificaciones en Zend de PHP y de su diplomado universitario, y de que el sigue las reglas estructuradas de programacion y que el y que el... en fin, me intento discutir que era imposible realizar dentro de MySQL una consulta a 50 millones de registros donde esta no tardaze mas de 3 minutos. A lo cual le dije, yo puedo asegurarte dependiendo que consulta estas realizando que no la se, que no hay necesidad de que esto tarde tanto... y el insistia que si que si, que MySQL apestaba con devolver muchos registros, que era lento, etc...

Asi que hice algo similar a ti, pero no con C (El Api C de MySQL por cierto me encanta) cree una sencilla tabla en MySQL con 3 campos, id, nombre, apellido. Donde id era una llave primaria y nombre / apellido indices. Luego cree un Procedimiento Almacenado dentro de mi MySQL con un bucle que me lleno la tabla de registros hasta que alcanzo los 50 millones.... al finalizar.

Asi que procedi a hacer lo mismo que ignorantemente hacia mi amigo PHPlero al consultar 50 millones de registros (Mas para comprobarle a el, que el problema no era MySQL en si, sino el que no sabia generar una consulta SQL satisfactoria):

Código (sql) [Seleccionar] Expandir


SELECT * FROM tabla;


Eso me tardo obviamente unos 5 o 6 minutos en devolver... mas sin embargo utilize un poco de Tunning para exponer mi segunda consulta.

Código (sql) [Seleccionar] Expandir


EXPLAIN SELECT * FROM TABLA WHERE id IS NOT NULL and nombres IS NOT NULL and apellidos IS NOT NULL;


Luego de verificar el Tunning con Explain, vi que esa consulta estaba optimizada para el ejemplo que deseaba exponer a mi amigo, asi que la ejecute... y de 5 minutos.. esta bajo a 1 minuto, procedi a ejecutarla la tercera vez y de 1 minuto redujo finalmente a 0.70 segundos (Gracias a la Cache)

EN resumen, mi amigo no estaba utilizando indices, no tenia la Cache de MySQL activada tampoco.. y no utilizaba Tunning para evaluar que consulta SQL era la mas satisfactoria. Claro esto vas conociendolo en el camino repito.

Otra recomendacion que te puedo ofrecer Anon, si vas a guardar datos caracteres menores a 20 caracteres, procura utilizar CHAR en vez de VARCHAR.

CHAR consume muchisimo menos memoria Ram a la hora de lectura de datos en una consulta, y los datos tipo CHAR tienden a no corromperse facilmente cuando hay fallas por alguna razon en las transacciones. VARCHAR es mas vulnerable, y a no ser que sea extremadamente necesario ya sea que vayas a guardar una info un pelin larga yo suelo utilizar mas CHAR, es cierto que CHAR consume mas espacio de disco porque su tamano no es variante sino constante sin importar la cantidad de caracteres que ingreses, pero.. es mas estable a soportar fallas y es mas rapido en devolver a lecturas de consultas. En el caso de VARCHAR su tamano al ser variante pues se tarda unos segundos mas ya que la base de datos sea cual sea no solo aplica para MySQL, tiene que verificar el espacio consumido por un dato en el HD antes de seguir verificando hacia el otro dato y asi sucesivamente...

Despues de todo, me alegra sobremanera que estes animado a seguirle

Buena suerte en tu camino, son pocos los jovenes que dedican su tiempo en aprender cosas productivas como SQL en vez de ponerse a jugar con crack y troyanitos que a la larga no les servira de nada.

[GUST_UNAD]

Un gran saludo a todos y Mil gracias por compartir su tiempo y conocimiento con todos los que apenas empezamos en este maravilloso mundo.
No se si en los ultimos aportes hayan contestado a lo que voy a preguntar y de ser asi espero me disculpen.
Mi consulta es la sgte:
Tengo una tabla con millones de registros (90) la cual tiene muchos registros identicos en todos sus campos lo que me toca hacer es eliminar solo aquellos repetidos en 5 de sus campos, algo asi

ID                    |  TEL1   TEL2     TEL3    COD_VIA
------------------+----------------------------------
111111            |    60   28.7%    28.7%    A111A
489713849       |    20    9.6%    38.3%    B222A
111111            |    60   28.7%    28.7%    A111X
45351158         |    84   40.2%    92.3%    A111A
111111            |    60   28.7%    28.7%    J222A
------------------+----------------------------------
De esta tabla deberia eliminarme solo el primer y ultimo registro y dejar los otros 3. He intentado varias cosas pero se queda por horas y no hace nada.
De antemano mis agradecimientos por la ayuda.
Gustavo