Ayudar con query sql

[Eux]

Hola a tod@s,

Tengo una tabla de 1.000.000 de registros que tiene entre sus columnas dos que se llaman DNI e IP, en donde los dos datos pueden repetirse, necesito saber:

"DNIs diferentes con la misma IP"

He intentado hacerla como sigue:

Código SQL:

SELECT dni FROM tabla t1, tabla t2 WHERE t1.dni!=t2.dni AND t1.ip=t2.ip
 

pero esta query no es nada viable ya que con la cantidad de registros, que supongo que se multiplican más de esta manera ni termina de ejecutarse.

¿conocéis alguna query eficiente para realizar esta consulta?

Gracias.

[antoniopol]

Se me ocurre la siguiente consulta:

Código SQL:

SELECT DISTINCT(DNI) FROM tabla t1 GROUP BY ip,dni
 

Espero que el resultado es lo que buscas =D

[Eux]

Gracias por responder antoniopol, pero no me sirve esa solución ya que no quita del resultado las ips que siempre tienen el mismo DNI, y eso hace que siga teniendo como resultado muchísimos registros. Gracias de todas maneras.

Ejemplo para aclarar más lo que quiero obtener

DNI - IP
dni1 p1
dni2 ip1
dni3 ip3
dni4 ip4

El resultado debería ser: dni1 y dni2

Es para saber que usuarios se conectan desde una ip que no es la suya, o dejan que otros lo hagan, en este caso dni1 y dni2.

[gnzsoloyo]

Probemos:

Código MySQL:

SELECT dni, ip
FROM tabla
WHERE ip IN(SELECT ip FROM tabla GROUP BY ip HAVING COUNT(*) >1)
 

[Eux]

Gracias por responder gnzsoloyo, INVIABLE igual, ya que me he ido a comer y acabo de volver y se seguía ejecutando, teóricamente es correcta pero el IN es mortal si le pasas gran cantidad de datos y este es el caso, recuerda que la tabla contiene 1.000.000 de registros. Pasa lo mismo con mi query inicial que creo que es correcta pero por la cantidad de registros no se puede duplicar la tabla.

[gnzsoloyo]

Genera un índice sobre ese campo, entonces...

Por cierto: el campo de la IP ¿es de qué tipo? ¿VARCHAR, TEXT o qué?

[Eux]

Los dos campos son VARCHAR.

[jurena]

Estoy de acuerdo con gnzsoloyo en que los índices son convenientes. Podrías intentar también algo así, aunque no sé el rendimiento

Código MySQL:

SELECT CONCAT(dni) listaDNI, ip, count(distinct dni) total
    FROM tabla
    GROUP BY ip HAVING total >1
 

[Eux]

Gracias Jurena, tu query no es muy pesada, aunque no me da lo que necesitaba tal como me lo plantee al principio pero me sirve, ya que de todos modos estos datos los paso a JAVA para seguir calculando cosas. Siendo mi tabla así:

DNI - IP
dni1 p1
dni2 ip1
dni3 ip3
dni4 ip4

tu query me daría: dni1 p1 2. Yo con java de aquí ya saco dni2 por ser P1 sospechosa.

Pero lo perfecto hubiese sido que me diese directamente: dni1 y dni2 ¿se puede conseguir fácil?

[wchiquito]

Hola Eux,

No estoy seguro de entender lo que necesitas como resultado, sin embargo, el uso de la función GROUP_CONCAT quizás te pueda ayudar.

He dejado un pequeño ejemplo en sqlfiddle.com

Espero te sea útil.

[jurena]

El consejo de wchiquito sobre el uso de group_concat es bueno. En realidad, me confundí al escribir CONCAT en lugar de GROUP_CONCAT, que era lo que quería escribir para que la consulta te devolviera separados los dni con coma. Prueba esto:

Código MySQL:

SELECT GROUP_CONCAT(dni) listaDNI, ip, count(DISTINCT dni) total
        FROM tabla
        GROUP BY ip HAVING total >1
 

[Eux]

Gracias por vuestras aportaciones.

wchiquito, teniendo este ejemplo:

DNI - IP
dni1 ip1
dni2 ip1
dni3 ip3
dni4 ip4

(que quizas porque me faltaba una "i" en IP en la primera fila no se entendió lo que quería)

quiero saber ¿quiénes son los dnis que comparten algúna ip?, el resultado que sería en este caso y es el que me gustaría obtener es:

dni1
dni2

así presentado en columna me sería perfecto para tratarlo con java dado que es así como hago la consulta a la bbdd. La query que me habéis facilitado me devolvería:

dni1,dni2 ip1 2

aunque me da el dato de una manera diferente a la que me gustaría, mediante java me he apañado en conseguir lo que quiero, pero al menos se me devolvieron sólo 33.000 registros y la query ha sido VIABLE.

Aunque basándome en vuestra query intenté conseguir los datos clasificados como a mi me gustaría que sería en columna y sólo los dos DNIs sospechosos dejándo la query como sigue:

Código SQL:

SELECT DISTINCT dni total
FROM tabla
GROUP BY ip HAVING total >1
 

pues, otra vez ha sido imposible procesarla ya que tardaba una eternidad, dado que supongo que todos esos dnis que en vuestra query presentaba en la misma fila, ahora tenía que procesarlas para presentarlas en una fila diferente y que no se repita dni, sino no entiendo porque vuestra query no tarda y esta última ni llega a mostrar los resultados.

En fin, aunque he tenido que hacer algunas cosas más en java pero al menos ya con vuestra query ha sido viable solventar lo que quería.

Gracias a tod@s.

[jurena]

A ver:
1) esto que dices que quieres

quiero saber ¿quiénes son los dnis que comparten algúna ip?, el resultado que sería en este caso y es el que me gustaría obtener es:

dni1
dni2

Si tu problema fuera solo el modo de presentación, resolverías la salida de ese GROUP_CONCAT con programación.
Si solo quieres mostrar los dni que aparecen referidos a una misma IP y mostrándo solo los DNI, no sabrás a qué IP se refieren y habrá algún caso en que un DNI esté referido a dos o más IPs y no sepas cuando te aparezca que eso ocurre. Debes mostrar el DNI y la IP a la que se refiere. Eso te lo proporcionaba la consulta anterior, pero si quieres que se muestre de otra manera, intenta esto, pero no antes de indexar el campo DNI, y también el de IP. No lo he probado, pero seguro que será algo más lento que el anterior, dado que se trata de una subconsulta.

Código MySQL:

SELECT
 t2.DNI,
 t2.IP
    FROM tabla t2 
  INNER JOIN
     (SELECT 
         IP, 
        COUNT(*) total
           FROM tabla 
            GROUP BY IP
                  HAVING total > 1)t1
  ON t2.IP = t1.IP ORDER BY t2.IP, t2.DNI
 

Tu consulta

Código MySQL:

SELECT DISTINCT dni total
    FROM tabla
    GROUP BY ip HAVING total >1
 

no puede devolverte nada porque la consulta es realmente extraña, lo que haces, creo, es:
1) primero agrupas por IP, con lo que te quedarían solo un registro de cada IP, el primero almacenado, con lo que perderías por ej. el registro donde guradas dni2 con la IP1
2) Luego haces como total un distinct DNI, de nuevo una agrupación, que esta vez eliminará todas las segundas IP referidas a un mismo DNI
3) Finalmente buscas los distintos DNI que te han quedado que sean mayor que 1, algo difícil, pues el DNI es una cadena alfanumérica (¿no?), pues imagino que incluyes la letra, y además lo guardas, como es lógico, en un campo VARCHAR. La necesidad del casting para la comparación, salvo que interprete el número como carácter, y el hecho de no tratarse el DNI de un número, imposibilita la devolución de resultados o se eterniza.
Prueba la otra consulta y nos dices si sale lo que quieres

[Eux]

Hola Jurena,

En si el problema no es el modo de presentación, sino que con este modo es como en java guardo lo obtenido, y por eso sólo me importa el conjunto de dnis sospechosos, su ip luego no la necesito.

Indexando los datos como me has sugerido he conseguido que la query se haga con rapidez (9 segundos) pero el resultado no fue el correcto, dado que como dije en mi tercer post los dnis que se conectan siempre con la misma ip no deben ser tenidos en cuenta. Ejemplo:

DNI - IP
dni1 ip1
dni2 ip1
dni3 ip3
dni3 ip3
dni4 ip4

Resultado obtenido con la query:
dni1
dni2
dni3

Resultado esperado:
dni1
dni2

Siento por si no me haya explicado mejor para no hacerte tantos cambios.

Saludos y gracias.

[jurena]

A ver esto (yo no lo he probado):

Código MySQL:

SELECT
     t2.DNI
        FROM tabla t2
      INNER JOIN
         (SELECT
             IP,
            COUNT(DISTINCT DNI) total
               FROM tabla
                GROUP BY IP
                      HAVING total > 1)t1
      ON t2.IP = t1.IP ORDER BY t2.DNI
 

[gnzsoloyo]

Bueno, con el último post se va entendiendo un poco el tema, pero me da la impresión de que se trata de un resultado que no es posible obtener con una consulta sencilla, sino en base a algo medio complicado. el problema se origina en que las condiciones a cumplir terminan siendo contradictorias.
Lo que tu necesitas es el listado de DNI distintos que se conectan por la misma IP, pero descartando:
- Los que se han conectado una única vez, por una única IP.
- Los que se han conectado N veces, siempre desde la misma IP.
El problema se produce porque ambas condiciones pueden solaparse con los que si quieres obtener. Al menos desde el punto de vista lógico.
Para que se entienda: en ambos casos se puede dar que un DNI se haya conectado por la misma IP usada por un grupo.
Generar una exclusión de este tipo es algo complicado.
Excluir solamente aquellos asos donde se conectó una única vez, es sencillo:

Código MySQL:

SELECT ip
FROM tabla
WHERE ip NOT IN (SELECT ip FROM tabla GROUP BY ip HAVING COUNT(*) = 1)
 

pero en tu propio ejemplo, hay tres casos de conexiones sobre una misma ip (dni1, dn2 y dn3, sobre ip1). ¿Cuál es la causa de mostrar esos dos y no los tres? Es decir, ¿en base a qué criterio excluyes el dni3?

[wchiquito]

Hola Eux,

Según tu ejemplo, te dejo una consulta en sqlfiddle.com que te puede ser útil, si ya tienes indices creados, serán de ayuda.

[Eux]

Hola, perdonad el retraso.

gnzsoloyo, dni3 se excluye con el criterio de que su ip sólo está asociada a él y a ningún otro dniX, si aparece asociada a un dniX implicará que este dniX y dni3 ya deben aparecer en el resultado.

wchiquito, tu query es la buscada ufff jeje, bueno adaptando un poco el resultado de salida y quedando así:

Código SQL:

SELECT DISTINCT t.dni
FROM (
  SELECT d.ip, COUNT(dni) dnis
   FROM (
    SELECT dni, ip
    FROM tabla
    GROUP BY dni, ip) d
  GROUP BY d.ip
  HAVING dnis > 1) d
  INNER JOIN tabla t ON t.ip = d.ip
ORDER BY d.ip, t.dni;
 

ya me da los dnis sospechosos, he hecho un par de inserciones sobre el ejemplo que has montado para ver si va en otro casos y ha ido perfecta por ahora.

Gracias chic@s por todo vuestro esfuerzo.