Relaciones Parent – Child en T-SQL

En  la mayoría de proyectos de BI, por no decir en todos, es necesario generar consultas de SQL, en mi caso T-SQL para SQL Server, para completar tareas de las distintas etapas de modelado de datos para análisis: extracción, staging, etc… Scripts de creación de tablas, índices, select, updates, todo lo que sea necesario para conformar un eficiente modelo dimensional.
Podemos encontramos con datos transaccionales que guardan relación padre-hijo en la misma tabla y nos interesa extraer esta relación como jerarquía para el modelo dimensional. El ejemplo clásico de este tipo de relación suele ser la entidad empleado y la organización jerárquica de funciones.
Vamos a ver la tabla:

Create table Employee   (    
EmployeeID int       
, FullName varchar(255)         
, ManagerID int )

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Y los datos con los que vamos a trabajar:

 

insert into Employee (EmployeeID,FullName,ManagerID)
values 
(1, 'Peter Brewick', null)
,(2, 'Margaret Thompson', 1)
,(3, 'Michel Sams', 1)
,(4, 'Susan Rogers', 3)
,(5, 'Tom Smith', 3)
,(5, 'John Little', 2)

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

La columna ManagerID hace referencia a EmployeeID de forma que para obtener el responsable de cada empleado hacemos la siguiente consulta:

   1:  Select e.EmployeeID, e.FullName EmployeeName, e.ManagerId, m.FullName ManagerName
   2:  from Employee e left join Employee m on e.ManagerID = m.EmployeeID

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Y efectivamente tenemos el nombre de responsable a nivel de cada fila. Esto puede complicarse si nos plantearan conocer el nivel del personal dentro de la relación, recorrer el árbol en busca de todos los niveles. Es decir, si mi jefe tiene un jefe (suele pasar Sonrisa)  yo estaría situado en el tercer nivel en la jerarquía ¿cómo consultamos este dato?

Las Common Table Expressions (CTE) fueron introducidas en SQL Server en su versión 9.0 (2005) y son de gran utilidad para resolver algunos escenarios, como el que tenemos por ejemplo.

Aunque existen varias alternativas para obtener los datos, voy a utilizar una consulta recursiva que escribió nuestro compañero Javier Loria (blog | twitter) en el blog de SolidQ hace ya unos años:

WITH Empleados (EmployeeID, ManagerID, Level) AS (
SELECT EmployeeID, ManagerID, 1FROM EmployeeWHERE ManagerID IS NULL
UNION ALLSELECT Employee.EmployeeID, Employee.ManagerID, Empleados.Level+1
FROM EmployeeJOIN EmpleadosON Empleados.EmployeeID=Employee.ManagerID
)
SELECT * FROM Empleados

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Con esta query conseguimos listar todos los empleados con su respectivo responsable además de informar del nivel en el que se encuentra dentro de la jerarquía, recorriendo todos los niveles. Básicamente se utiliza un dataset base (nivel 0) y se hace unión con la misma consulta haciendo Join por el campo que referencia al padre contra la propia CTE, de esta forma se van generando los distintos niveles:

CTE Parent and Child Result

Este tipo de consultas puede ser especialmente útil en escenarios dónde sea necesario filtrar datos agregables por su nivel en una jerarquía.

En la MSDN puedes encontrar mas información acerca de el uso de Common Table Expression y las consultas recursivas

Espero que les sea de utilidad Sonrisa

Anuncios
Esta entrada fue publicada en BI, CTE, http://schemas.google.com/blogger/2008/kind#post, Parent and Child, SolidQ, SQL Server, T-SQL. Guarda el enlace permanente.

3 respuestas a Relaciones Parent – Child en T-SQL

  1. Santy dijo:

    Muy bueno el artículo señor!

  2. Miguel dijo:

    Buenisimo!!! Me salvaste!! Muchísimas gracias!

  3. Miguel dijo:

    excelente! me sirvió muchisimo!!

Responder

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