Tengo una tabla que incluye la última IP del usuario. Usando la siguiente consulta, puedo encontrar todas las direcciones IP duplicadas
SELECT id, ip, COUNT(ip) AS ip_count FROM users GROUP BY ip HAVING ip_count > 1Estoy tratando de seleccionar direcciones IP que son diferentes solo en la última parte. Aquí hay unos ejemplos:
+--------------+---------------+---------+ | IP 1 | IP 2 | Similar | +--------------+---------------+---------+ | 230.15.26.79 | 230.15.26.230 | true | | 32.82.0.5 | 32.82.0.180 | true | | 230.15.26.79 | 193.230.15.26 | false | | 230.15.26.79 | 230.15.39.115 | false | +--------------+---------------+---------+Podría encontrar manualmente si hay direcciones IP similares a una en particular usando el siguiente comando:
SELECT id, ip FROM users where ip LIKE "230.15.26.%"Sin embargo, esto significaría que tengo que recorrer toda la base de datos, que es bastante voluminosa.
¿Hay otra forma que pueda usar para hacer lo descrito anteriormente con una o dos consultas solamente?
Puede extraer los datos requeridos con una consulta similar a:
SELECT SUBSTRING_INDEX( ip, '.', 3), COUNT(*) FROM ipadd GROUP BY SUBSTRING_INDEX( ip, '.', 3) HAVING COUNT(*) > 1asumiendo una estructura de tabla en las líneas de
create table ipadd(id INT, ip VARCHAR(15));Puedes verlo en acción aquí
También hay una solución alternativa con MySQL 8 Window Functions y Common Table Expressions . Tal vez sea más rápido que el GROUP BY habitual, pero es necesario verificar:
WITH tmp AS ( SELECT *, COUNT( * ) OVER ( PARTITION BY SUBSTRING_INDEX( ip, '.', 3 ) ) AS three_parts_of_this_ip_are_similar_in_N_ips FROM user_ips ) SELECT * FROM tmp WHERE three_parts_of_this_ip_are_similar_in_N_ips > 1Supuesta tabla y datos:
DROP TABLE IF EXISTS user_ips; CREATE TABLE user_ips ( user_id INT, ip VARCHAR ( 15 ) ); INSERT INTO user_ips ( user_id, ip ) VALUES ( 1, '230.15.26.79' ), ( 1, '32.82.0.5' ), ( 1, '230.15.26.230' ), ( 1, '32.82.0.180' ), ( 1, '193.230.15.26' ), ( 1, '230.15.39.115' );Puedes ver una demostración aquí .
Si necesita contar por usuario, simplemente agregue el campo de usuario a la sección PARTITION BY .