LookUps, Apply Map, Group By, Select Distinct: diferentes técnicas para marcar filas

Este post tiene por objeto documentar y recopilar los principales métodos que conozco para marcar (“flaguear”) filas en una tabla del modelo respecto de otra, usando un campo creado para tal propósito y agregándolo a la tabla que se busca marcar.

Los motivos por los que se suelen marcar filas mediante el uso de flags son en general de performance y/o de volumen: en el primer caso se pueden contar, sumar o hacer otra medida recorriendo una sola tabla y evitando relaciones que pueden resultar complejas y en el segundocaso  solo se desea calificar la primera entidad según datos de la segunda entidad, pero sin necesidad de preservar los datos de la segunda, economizando espacio. En definitiva es una técnica bastante usada al construir una tabla de hechos (fact tables) en un modelo estrella  y algo menos usadas en un modelo snow-flake.

Si bien existen distintas técnicas lo más importante es tener perfectamente clara la relación que existe entre ambas tablas y su cardinalidad ya que de esto último dependerá el conjunto de técnicas que podrán ser aplicadas sin que se generen errores por duplicación de filas.

Si la relación entre las tablas es 1 a 0-ó-1:

Como ejemplo tomemos dos tablas: Empleados (tabla Employees) y Parejas (tabla Spouses para esposos, esposas, cónyuges en general):

Employees:
load * inline [
EmpId, EmpName
1,   Pedro
2,   Maria
3,   Susana
4,   John
5,   Carlos
6,  Sofia
];

Spouses:
Load * inline [
SpouseID, EmpId, Name
1, 1, Sonia
2, 2, Paul
3, 4, Gabriela
5, 6, Serge
];
 

Lo que se buscamos es marcar los empleados agregando el campo “Spouse” (cónyuge) y asignarle el Valor Y/N según se encuentre o no una fila en la tabla de “Spouse”.

Usando “Left Join”:

Primero el código:

LEFT JOIN (Employees)
LOAD
     EmpId,                                                  /// link field
     if(isNull(EmpId),’N’,’Y’) as Spouse
RESIDENT Spouses;

Drop Table Spouses;

 Y el resultado que obtenemos es:

 Cero

Pero Susana y Carlos en lugar de mostrar “N” (tal como sería el requerimiento de usuario) en el campo Spouse muestran “–“ (nulo). Esto es porque el código if(isNull(EmpId),’N’,’Y’) se ejecuta para cada línea de la tabla Spouse, donde efectivamente no existen filas que correspondan a Susana y Carlos y obviamente nunca EmpId llega a ser nulo en dicha tabla.

Por esto para finalizar el requerimiento necesitamos hace una segunda pasada, costosa y poco recomendable:
tmpEmployees:
Noconcatenate
Load
     EmpId,
     EmpName,
     if(isNull(Spouse),’Y’,’N’) as Spouse
RESIDENT Employees;

Drop table Employees;
Rename Table tmpEmployees to Employees;
 

Aquí buscamos los campos donde “Spouse” es nulo y cambiamos este valor por “N”, obteniendo el siguiente resultado:

 quinta

Por último  el uso de “Left Join” podría  ocasionar que se dupliquen filas si en algún caso la relación 1-a-0/1 no fuera así, creando una situación difícil de detectar entre (algunas veces) millones de registros.

Usando “Apply Map”:

Esta es una técnica Mucho más recomendable por performance, especialmente si se trabaja con grandes volúmenes,  y por requerir una única pasada (además de construir  el mapa): 

mapSPOUSES:   //Construimos el mapa donde a cada empleado que tiene esposa y

             //le asignamos “Spouse=Y”.
MAPPING
LOAD 
     EmpId,
     ‘Y’ as Spouse
Resident Spouses;

Drop Table Spouses;

// Aplicamos el mapa reemplazando el campo EmpId por lo que devuelve el mapa
// si se encuentra EmpId en él (es decir “Y”) y si el mapa no encuentra la
// relación ponemos una “N”

tmpEmployees:
Load
     EmpId,
     EmpName,
     applymap(‘mapSPOUSES’,EmpId,’N’) as Spouse
RESIDENT Employees;

Drop Table Employees;
RENAME Table tmpEmployees to Employees;

 Con lo cual obtenemos en una sola pasada: quinta

Usando “Exists”:

Para usar esta técnica (en este ejemplo) lo primero que debemos hacer es cambiar el nombre del campo EmpId a EmpId2 en la tabla Spouses:

Spouses:
Load * inline [
SpouseID, EmpId2, Name ………

 Luego el código:

Employees2:
NoConcatenate
Load
     EmpId,
     EmpName,
     ‘Y’ as Spouse
RESIDENT Employees
where EXISTS(EmpId2,EmpId);

Lo que produce (solo los empleados en relación conyugal):

 cuarta

Y aquí el código complete la tabla con los “N”:

Employees2:
Load
     EmpId,
     EmpName,
     ‘N’ as Spouse
RESIDENT Employees
where NOT EXISTS(EmpId2,EmpId);

Drop Tables Spouses, Employees;
Rename Table Employees2 to Employees;

 Lo que produce: quinta

Usando “Lookup”:

Employees2:
NoConcatenate
Load
     EmpId,
     EmpName,
     if(isnull(lookup(‘SpouseID’, ‘EmpId’, EmpId, ‘Spouses’)),’N’,’Y’) as Spouse
RESIDENT Employees;

Drop Tables Spouses, Employees;

Y obtenemos: quinta

 Cuando el volumen de las tablas es particularmente grande es altamente aconsejable el empleo de Apply Map por razones de performance por sobre cualquier otra técnica incluso lookup, aunque vale aclarar que el método basado en EXISTs es altamente performante (a mi entender incluso más que apply map) solo que son bastante restringidas las ocasiones en que puede ser empleado.

Si la relación entre las tablas es de 1 a 0,1 o más filas:

Para esto vamos a cambiar un poco el diseño de las tablas sobre el que vamos a trabajar, armando tablas con relación 1 a 0-1-N:

Employees:
load * inline [
EmpId, EmpName
1,   Pedro
2,   María
3,   Susana
4,   John
5,   Carlos
6,  Pablo
];

Childs:
Load * inline [
ChildID, EmpId, Name
1, 1, Joseph
2, 2, Micaela
3, 2, Santino
4, 4, Mili
5, 5, Catty
6, 6, Sebastián
7, 6, Florencia
]; 

Usando “Left Join” con Group By:

Aquí la idea es obtener una única fila de la tabla Childs por cada empleado, agregando cierta información, en este caso contando la cantidad de hijos:

Left Join (Employees)
Load
     EmpId,
     Count(ChildID) as Childs
RESIDENT Childs
Group By EmpId;

Con lo que obtenemos:

 sexta
Y si queremos convertir los nulos en 0 (ceros), agregamos:

tmp_Eployees:
Load
     EmpId,
     EmpName,
     if(isNull(Childs),0,Childs) as Childs2
RESIDENT Employees;

Drop Tables Childs, Employees;
RENAME Table tmp_Employees to Employees;

 Con lo que logramos: septima

Usando “Left Join” con “Select Distinct”:

La idea aquí es nuevamente obtener una única fila para joinear con la tabla Employees y de esta forma no duplicar filas. Pero aquí en lugar de usar Group by (que tiene muy mala performance en grandes volúmenes) lograremos una única fila por empleado de la siguiente forma:

Left Join (Employees)
Load DISTINCT
     EmpId,
     if(not isNull(ChildID),’Y’) as Childs
RESIDENT Childs;

La limitación de ésta técnica a diferencia del Grup By es que no podemos obtener cantidades u otras agregaciones (p.ej: sum, concat, etc.) sino que debemos limitarnos a obtener algún tipo de flag siempre igual, justamente para que el DISTINCT deje solo una fila por empleado, obteniendo: 

octava

Y si queremos convertir los nulos en 0 (ceros), agregamos:

tmp_Eployees:
Load
     EmpId,
     EmpName,
     if(isNull(Childs),’N’,Childs) as Childs2
RESIDENT Employees;

Drop Tables Childs, Employees;
RENAME Table tmp_Employees to Employees;

Obteniendo: novena

 Usando “LookUp “

Employees2:
NoConcatenate
Load
     EmpId,
     EmpName,
     if(isnull(lookup(‘ChildID’, ‘EmpId’, EmpId, ‘Childs’)),’N’,’Y’) as Childs
RESIDENT Employees;

Drop Tables Childs, Employees;

 Y obtenemos el resultado buscado en una sola pasada:

novena

Usando “ Exists”:

Aplicamos la misma técnica antes explicada:

Primero modificamos el nombre del campo EmpId (a EmpId2) en la tabla Childs:

 Childs:
Load * inline [
ChildID, EmpId2, Name …

 Y luego el código:

Employees2:
NoConcatenate
Load
     EmpId,
     EmpName,
     ‘Y’ as Childs
RESIDENT Employees
where EXISTS(EmpId2,EmpId);

Employees2:
Load
     EmpId,
     EmpName,
     ‘N’ as Childs
RESIDENT Employees
where NOT EXISTS(EmpId2,EmpId);

 Obteniendo en dos tiempos: novena

 Por ultimo usando APPLY MAP:

Construimos el mapa donde a cada empleado que tiene hijos y le asignamos Child=Y. 

mapChilds:  

MAPPING
LOAD 
     EmpId,
     ‘Y’ as Child
Resident Childs;
Drop Table Childs;

Aplicamos el mapa reemplazando el campo EmpId por lo que retorna el mapa. Si se encuentra EmpId en él (es decir “Y”) y si el mapa no encuentra la relación ponemos una “N”.

tmpEmployees:
Load
     EmpId,
     EmpName,
     applymap(‘mapChilds’,EmpId,’N’) as Child
RESIDENT Employees;

Drop Table Employees;
RENAME Table tmpEmployees to Employees;

De esta forma obteniendo una única fila para cada empleado ya que APPLY MAP solo devuelve la primera coincidencia por cada fila. En este sentido funciona similar a un Select DISTINCT:

novena

Acerca de pparnisari
Casi 44 vueltas al sol. Licenciado en sistemas. Curioso, investigador, excelente para resolver problemas prácticos. Casado, 2 hijos, cada vez con mas preguntas y respuestas mas en duda. Almost 44 laps to the sun. System engineer. Curious, researcher, great for solving practical problems. Married, 2 children, each time with more questions and more answers in doubt.

Deja un comentario

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: