Foro de elhacker.net

Bien, he generado ya un reporte que me ayuda con eso y tarda algo con la Base de datos de 7000 Registros, unos 3 minutos.

Bien para no hacerla larga la BD es originalemente de MS Access, yo la migre a MySQL por comodidad.

Hay una tabla que no tiene primary key, y contiene algunos registros duplicados, y quiero encontralos

Son 2 campos los que tengo que validar al mismo tiempo.

Id y grupo ambos son varchar(10) La condion es que en cada grupo no debe de existir ID repetidos. y en general puede exisistir mas de un mismo ID, siempre y cuando sean de grupos distintos.

Anterior mente realize una consulta que mencione pero solo lo he logrado hacer para el ID, no he podido combinarla para el Grupo:

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;

Sinceramente eso me fuciona, solo que hay veces que me muestra los Registros con ID Igual pero  de diferente grupo, quisiera poder extender la busqueda para que tome en consideracion los 2, alguna idea ?

---

Solucion completa en: Re: MySQL, reporte "Registros duplicados" (Solucionado) (https://foro.elhacker.net/programacion_general/mysql_reporte_registros_duplicados_solucionado-t234835.0.html;msg1179499#msg1179499)

Compara y da un reporte de registros duplicados en 2 Campos, en una misma Tabla, con mas de ~50K Registros, en menos de 2 o un segundo.

Sobre tu tabla que no tiene Primary Key, tranquilo, esto no es una desventaja solamente en cuanto a optimizacion de busquedad es todo... Pero si tanto te perjudica o molesta esto, sencillamente creale un indice a esa tabla, seleccionare ID como ejemplo :

mysql> alter table tabla add primary key(ID);

Asi ID sera tu primary key...

No comprendo del todo bien que quieres hacer,
dices que en cada campo GRUPO no puede existir ID repetidos... haz pensado utilizar la clausula DISTINCT???

Por ejemplo... luego dices que puede existir o repetirse el mismo ID??? siempre y cuando sean de GRUPO distintos??? algo asi como :

select GRUPO, ID from tabla where GRUPO IN(select distinct(GRUPO) from tabla) ?????

Luego pones una consulta donde agrupas por ID.. y dices que te devuelve todos los registros iguales (lo cual es normal si usas GROUP BY) con distintos GRUPO...

Agradeceria poder comprenderte un pelin mejor. Porque ando un poco perdida en tu solicitud.

Muchas gracias por responder, voy a verificar lo que dices y leer la documentacion de las clausulas que me pasaste.

Explico mejor:

Existen registro con muy variados ID, en algunas ocasiones se puede encontrar mas de un mismo ID, y esto solo es valido si y solo si pertencen a grupos Distintos. En dado caso de exista 2 registros con el mismo ID y el mismo Grupo se considera Repetido, y esos son los que quiero encontrar.

Saludos y gracias.

Ahh quieres encontrar cuales campos estan repetidos bueno  :rolleyes:

No se si podre ayudarte mucho aqui, pero intentare.

Para verificar cuales campos estan repetidos y que cantidad de veces haz esto :


Esto te dara como resultado cuales registros en GRUPO y ID se repiten y cuantas veces se repiten gracias al COUNT :) es lo que se me ocurre mas rapidamente....

Haber si te sirve de algo.

Aqui tengo otra consulta SQL que puede ayudarte, esta me la paso un amigo aca no salio particularmente de mi. Espero que te sirva tambien  ;)

Esta lo que hace es mostrarte explicitamente (Aunque no cuantas veces) se duplica una palabra dentro de la tabla.

Bien nuevamente gracias por las respuestas y por su tiempo :D

Revice las consultas


Es buena la consulta de hecho es muy parecida a:

SELECT id, COUNT(*) FROM tabla GROUP BY id;

junto a la que me los muestra por grupo: mmm union all aaa de hecho es eso :xD

Es me muestra Todos los registros que tengo XD.

Mencione en el principio una consulta que utilizo:

Y la siguiente:

Es como la primera. que mostraste sin el union y no me dice cuantos hay de cada uno.

Ahora:


Ahora mencione una consulta que uso


Esta me muestra exclusivamente los registros que estan duplicados en el ID , pero como lo menciono en esta tabla pueden existir registros duplicados en el ID

Yo quiero extender esta consulta, a que ahi mismo  borre cuando el grupo es Diferente.

Ya tengo como 2 semanas buscando la forma de hacer esto y creeeme que he leido la documentacion bastante tiempo pero es por decir algo, una consulta muy compleja  :-(

Espero encontrar la solucion y postear el resultado que a mas de algun incauto le va a servir como ami.

La consulta que mostre si me funciona, solo que tengo que estar revisando visualmente que el grupo sea Distinto.

Saludos y Muchisimas gracias.

Mi nino, una solucion no es rendirse ante un problema.

Si tienes un problema, se trata de buscar la solucion solo eso.
Yo entendi en tu post que tu decias que querias saber cuantas veces se duplicaba ID y GRUPO, por esa razon te publique :


Yo se que aca se presentaran todos los registros, pero en el alias total y totalisimo te debe indicar cuantas veces se duplican cada uno.. de igual manera esta consulta :


La anterior lo que hace es devolverte cada registro duplicado dentro de GRUPO al decir duplicado me refiero a mayor que 1 vez, en la sentencia anterior te lanze cuales registros de GRUPO se duplican y cuantas veces....

Tu lo dijiste claro ::


Querias saber cuantas veces se duplican GRUPO y ID forma de sobra creo que te he explicado.

Jaja.. :xD

Valla que tienes razon, ahora



No me he rendido, estoy busque y busque, como la pelicula Taken -> En español, Busqueda Implacable :xD.

Si tienes razon, no me doy mucho a entender. igualmente muchas gracias por tu valioso tiempo.

Saludos

De verdad no se en totalidad que es lo que quieres   :huh:   primero dices :


Creo que ya sabes como buscar ID y GRUPO duplicados dentro de una tabla. Luego dices que estas conforme con tu consulta que solo muestra los ID duplicados y que :


Ahora si quieres borrar debes poner el SELECT primero y luego acondicionarlo con WHERE para el DELETE, pero esto no tiene mucha logica, si conoces los registros ya duplicados mejor borralos de paso no????

^TiFa^ , en los pocos post que he compartido con tigo has demostrado siempre tratar de ayudar, y eso se te agradece enormemente. ;)

Vuelvo a repetir, muchas veces no me doy a entender, pero a ver si lo explico ahora con codigo a ver si nos entendemos  :) va.

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
);
 
DELIMITER $$
 
DROP PROCEDURE IF EXISTS `addRegistro` $$
CREATE PROCEDURE `addRegistro`(IN i VARCHAR(10),IN g VARCHAR(10))
BEGIN
	INSERT INTO tabla (id,grupo) VALUES(i,g);
END $$
 
DELIMITER ;
 
CALL addRegistro('1','1');
CALL addRegistro('2','1');
CALL addRegistro('3','1');
CALL addRegistro('4','1');
CALL addRegistro('4','1');
CALL addRegistro('1','2');
CALL addRegistro('2','2');
CALL addRegistro('3','2');
CALL addRegistro('4','2');
CALL addRegistro('5','2');
CALL addRegistro('6','2');
CALL addRegistro('7','2');
 

Bien ya temos el ejemplo de prueba.

Ahora cuando yo ejecuro mi consulta:

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.


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

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.

^TiFa^, un cordial saludo, de antemano gracias por el tiempo que te estas tomando en leerme (^^.)

Saludos.

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 ::


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.

Si solo deseas mostrar los repetidos pues..

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  :xD

Saludos¡¡

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.

Bien, si es lo que estaba buscando.

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 :)

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

Funciona :D.

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.

 :xD

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¡¡

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.

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  :xD

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...  :laugh:

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

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:

#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

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

Nos devuelve una salida muy extensa asi:


Asi unas 50K veces, lo comprobamos con:

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

Despues de Mandar la salida al archivo:

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:

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


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:

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

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.

Anon que bueno que continues aprendiendo mas MySQL.

Ahora, obviamente esta consulta :

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):

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.

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.

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

Seria bueno que pusieras la sentencia que estas usando para eliminar dichos registros.

Saludos