[SOLUCIONADO] Consulta para obtener el numero de registros coincidentes entre dos tablas

[aragonexiste]

Buenos días,

Actualmente estoy trabajando como diseñador web y en la empresa donde curro en Holanda me han pasado un marrón relacionado con unas bases de datos, consistente en hacer una extracción con tablas de mas de dos millones de registros.

1 - Casuística

Dispongo de dos ficheros que incluyen los datos de la siguiente manera

1 - En el primero, incluye el número de registros totales (Mas de 2 millones de filas) y entre una multitud de campos, incluye los campos "area" y "subarea".

2 - Otro csv que incluye unos 14000 registros (Que también están incluidos en el archivo anterior) y que indica nuestros clientes mediante un campo "status".

La única forma de relacionar ambos ficheros es mediante la concatenación de tres campos que están presentes en ambos archivos "Código postal", "número" y "extra".

Por poneros un ejemplo de las lineas del fichero mas grande :

Area | Sub Area | CP | Número | Extra
Brabante | Nord Eindhoven | 1186VZ | 5 |
Brabante | Nord Eindhoven | 1186VZ | 7 |
Brabante | Nord Eindhoven | 1186VZ | 9 |
Brabante | Nord Eindhoven | 1186VZ | 9 | A
Brabante | Nord Eindhoven | 1186VZ | 11 |
Brabante | Zuid Eindhoven | 1195AS | 232 |

Mientras que un ejemplo de una linea del segundo fichero sería:

CP | Numero | Extra | Status
1186VZ | 5 | | Completed
5836AB | 12 | | Completed

2 - Consulta

Lo que yo precisaría hacer es una consulta que me dijese:

1 - El número total de registros presentes en el fichero 1 de cada "subarea".
2 - El número de registros presentes en el fichero 2 para cada "subarea".

Y el resultado final para el ejemplo que os había indicado seria:

Area | SubArea | total | clientes
Brabante | Nord Eindhoven | 5 | 1
Brabante | Zuid Eindhoven | 1 |

Muchas gracias de antemano por vuestra ayuda

[quimfv]

Dos opciones al vuelo, pero ojo en tu ejemplo "extra" parece nulo en algunos casos, quizas habria que hacer alguna transformación de nulo a un valor determinado (-1 p.e.)....

Código MySQL:

SELECT SBC1.Area,SBC1.SubArea,SBC1.total,IFNULL(SBC2.clientes,0) Clientes
FROM (SELECT Area,SubArea,count(*) total 
           FROM tablaMasde2millones
           GROUP BY Area,SubArea) SBC1
     LEFT JOIN
           (SELECT Area,SubArea,count(*) clientes
           FROM tablaMasde2millones
                     INNER JOIN tabla14000registros
                         ON tablaMasde2millones.`Código postal`=tabla14000registros.`Código postal`
                             AND tablaMasde2millones.`número`=tabla14000registros.`número`
                             AND tablaMasde2millones.`extra`=tabla14000registros.`extra`
           GROUP BY tablaMasde2millones.Area,tablaMasde2millones.SubArea)SBC2
     ON SBC1.Area=SBC2.Area AND SBC1.SubArea=SBC2.SubArea;
 

Código MySQL:

SELECT Area,SubArea,count(*) total,
                SUM(if(tabla14000registros.`Código postal` IS NOT NULL 
                     AND tabla14000registros.`número` IS NOT NULL  
                     AND tabla14000registros.`extra` IS NOT NULL, 1,0)) clientes
           FROM tablaMasde2millones
                     LEFT JOIN tabla14000registros
                         ON tablaMasde2millones.`Código postal`=tabla14000registros.`Código postal`
                             AND tablaMasde2millones.`número`=tabla14000registros.`número`
                             AND tablaMasde2millones.`extra`=tabla14000registros.`extra`
           GROUP BY tablaMasde2millones.Area,tablaMasde2millones.SubArea
 

[aragonexiste]

Iniciado por quimfv

Dos opciones al vuelo, pero ojo en tu ejemplo "extra" parece nulo en algunos casos, quizas habria que hacer alguna transformación de nulo a un valor determinado (-1 p.e.)....

Código MySQL:

SELECT SBC1.Area,SBC1.SubArea,SBC1.total,IFNULL(SBC2.clientes,0) Clientes
FROM (SELECT Area,SubArea,count(*) total 
           FROM tablaMasde2millones
           GROUP BY Area,SubArea) SBC1
     LEFT JOIN
           (SELECT Area,SubArea,count(*) clientes
           FROM tablaMasde2millones
                     INNER JOIN tabla14000registros
                         ON tablaMasde2millones.`Código postal`=tabla14000registros.`Código postal`
                             AND tablaMasde2millones.`número`=tabla14000registros.`número`
                             AND tablaMasde2millones.`extra`=tabla14000registros.`extra`
           GROUP BY tablaMasde2millones.Area,tablaMasde2millones.SubArea)SBC2
     ON SBC1.Area=SBC2.Area AND SBC1.SubArea=SBC2.SubArea;
 

Código MySQL:

SELECT Area,SubArea,count(*) total,
                SUM(if(tabla14000registros.`Código postal` IS NOT NULL 
                     AND tabla14000registros.`número` IS NOT NULL  
                     AND tabla14000registros.`extra` IS NOT NULL, 1,0)) clientes
           FROM tablaMasde2millones
                     LEFT JOIN tabla14000registros
                         ON tablaMasde2millones.`Código postal`=tabla14000registros.`Código postal`
                             AND tablaMasde2millones.`número`=tabla14000registros.`número`
                             AND tablaMasde2millones.`extra`=tabla14000registros.`extra`
           GROUP BY tablaMasde2millones.Area,tablaMasde2millones.SubArea
 

Muchas gracias por la respuesta Quim!

Siento no haberte podido agradecer antes tu aportación, pero reuniones de trabajo me impidieron probar hasta hoy tus soluciones. ¡¡Funcionaron perfectamente!!

Un saludo

[aragonexiste]

Iniciado por quimfv

Dos opciones al vuelo, pero ojo en tu ejemplo "extra" parece nulo en algunos casos, quizas habria que hacer alguna transformación de nulo a un valor determinado (-1 p.e.)....

Código MySQL:

SELECT SBC1.Area,SBC1.SubArea,SBC1.total,IFNULL(SBC2.clientes,0) Clientes
FROM (SELECT Area,SubArea,count(*) total 
           FROM tablaMasde2millones
           GROUP BY Area,SubArea) SBC1
     LEFT JOIN
           (SELECT Area,SubArea,count(*) clientes
           FROM tablaMasde2millones
                     INNER JOIN tabla14000registros
                         ON tablaMasde2millones.`Código postal`=tabla14000registros.`Código postal`
                             AND tablaMasde2millones.`número`=tabla14000registros.`número`
                             AND tablaMasde2millones.`extra`=tabla14000registros.`extra`
           GROUP BY tablaMasde2millones.Area,tablaMasde2millones.SubArea)SBC2
     ON SBC1.Area=SBC2.Area AND SBC1.SubArea=SBC2.SubArea;
 

Código MySQL:

SELECT Area,SubArea,count(*) total,
                SUM(if(tabla14000registros.`Código postal` IS NOT NULL 
                     AND tabla14000registros.`número` IS NOT NULL  
                     AND tabla14000registros.`extra` IS NOT NULL, 1,0)) clientes
           FROM tablaMasde2millones
                     LEFT JOIN tabla14000registros
                         ON tablaMasde2millones.`Código postal`=tabla14000registros.`Código postal`
                             AND tablaMasde2millones.`número`=tabla14000registros.`número`
                             AND tablaMasde2millones.`extra`=tabla14000registros.`extra`
           GROUP BY tablaMasde2millones.Area,tablaMasde2millones.SubArea
 

Hola Quim, perdona que te moleste de nuevo pero debo ampliar la consulta previa un paso mas y no tengo muy claro como hacerlo..

El caso es que se han añadido dos nuevos campo ("new" and "old") que indican si un determinado producto es nuevo o viejo (solo uno de los dos valores puede tener un valor a la vez). El caso es que para indicarlo se rellena el campo con el valor "1", quedando el valor en blanco si no corresponde.

Es decir, la linea del ejemplo quedaria así:

CP | Numero | Extra | Status | New |old
1186VZ | 5 | | Completed|1|
5836AB | 12 | | Completed|1|

y el resultado seria

Area | SubArea | total | clientes | New | old
Brabante | Nord Eindhoven | 5 | 1 | |

Partiendo de tu segunda consulta, habia pensado lo siguiente..

Código MySQL:

SELECT Area,SubArea,count(*) total,
                SUM(if(tabla14000registros.`Código postal` IS NOT NULL 
                     AND tabla14000registros.`número` IS NOT NULL  
                     AND tabla14000registros.`extra` IS NOT NULL, 1,0)) clientes, COUNT( IF( tabla14000registros.`new` = 1, 1, NULL ) ),
COUNT( IF( tabla14000registros.`old` = 1, 1, NULL ) )
           FROM tablaMasde2millones
                     LEFT JOIN tabla14000registros
                         ON tablaMasde2millones.`Código postal`=tabla14000registros.`Código postal`
                             AND tablaMasde2millones.`número`=tabla14000registros.`número`
                             AND tablaMasde2millones.`extra`=tabla14000registros.`extra`
           GROUP BY tablaMasde2millones.Area,tablaMasde2millones.SubArea
 

¿Te parece correcto o ves alguna forma mas secilla de hacerlo?

Muchas gracias y perdona als molestias de nuevo

[quimfv]

Para guardar una caracteristica binaria como es el caso no hacen falta dos campos.

Si es new no es old luego un solo campo es suficiente,

Nuevo true o false

Es más facil de mantener.

Excepto que haya cosas que no sean ni una cosa ni la otra.


Con dos campos

Código MySQL:

SELECT Area,SubArea,count(*) total,
                SUM(if(tabla14000registros.`Código postal` IS NOT NULL 
                     AND tabla14000registros.`número` IS NOT NULL  
                     AND tabla14000registros.`extra` IS NOT NULL, 1,0)) clientes, 
                SUM( IF( tabla14000registros.`new` = 1, 1, 0 ) ) nuevos,
                SUM( IF( tabla14000registros.`old` = 1, 1, 0 ) ) viejos
           FROM tablaMasde2millones
                     LEFT JOIN tabla14000registros
                         ON tablaMasde2millones.`Código postal`=tabla14000registros.`Código postal`
                             AND tablaMasde2millones.`número`=tabla14000registros.`número`
                             AND tablaMasde2millones.`extra`=tabla14000registros.`extra`
           GROUP BY tablaMasde2millones.Area,tablaMasde2millones.SubArea
 

Con un campo

Código MySQL:

SELECT Area,SubArea,count(*) total,
                SUM(if(tabla14000registros.`Código postal` IS NOT NULL 
                     AND tabla14000registros.`número` IS NOT NULL  
                     AND tabla14000registros.`extra` IS NOT NULL, 1,0)) clientes, 
                SUM( IF( tabla14000registros.`new` = 1, 1, 0 ) ) nuevos,
                SUM( IF( tabla14000registros.`new` = 1, 0, 1 ) ) viejos
           FROM tablaMasde2millones
                     LEFT JOIN tabla14000registros
                         ON tablaMasde2millones.`Código postal`=tabla14000registros.`Código postal`
                             AND tablaMasde2millones.`número`=tabla14000registros.`número`
                             AND tablaMasde2millones.`extra`=tabla14000registros.`extra`
           GROUP BY tablaMasde2millones.Area,tablaMasde2millones.SubArea
 

Es mas seguro con un solo campo, al actualizar solo hay que hacer una operación, con dos campos debes forzosamente actualizar dos campos.