jueves, 18 de agosto de 2011

Unir 2 tablas usando linq y claves compuestas

Haciendo una consulta Linq con Entity Framework, he encontrado un problemilla que me ha costado algo de tiempo solucionarlo, y creo que era digno merecedor de una entrada en el blog.

Estaba haciendo una consulta join a 2 tablas con clave compuesta.

var listaCentro = from f in ctx.Facturas
join c in ctx.CentroCoste
on new { f.empresa, f.centroCoste } equals new { c.empresa, c.centro}
select new {c.empresa, c.nombre};

y me aparecía el siguiente error:

“El tipo de una de las expresiones de la cláusula join es incorrecto. No se pudo inferir el tipo en la llamada a 'Join'”

El MSDN me sugería que verificara los tipos del join, comprobando que fueran del mismo tipo las parejas f.empresa, c.empresa y f.centroCoste, c.centro.

Comprobado esto, seguí buscando y me encontré con la siguiente frase:

“La inferencia de tipos en claves compuestas depende de los nombres de las propiedades en las claves y del orden en el que ocurren”

Las claves f.empresa coinciden con c.empresa, pero f.centroCoste no coincide con c.centro, y debían de coincidir.

La solución estaba en poner nombre a las propiedades de las clases anónimas que usamos para las claves compuestas.

var listaCentro = from f in ctx.Facturas
join c in ctx.CentroCoste
on new { emp = f.empresa, centro = f.centroCoste } equals new { emp = c.empresa, centro = c.centro}
select new {c.empresa, c.nombre};

Enlace al artículo MSDN que habla sobrer las claves compuestas:
http://msdn.microsoft.com/es-es/library/bb907099.aspx


martes, 9 de agosto de 2011

Funciones de categoría de SQL Server: rank, dense_rank, row_number, ntile

Voy a dar un repaso a unas funciones estadísticas que os pueden ser bastante útiles en vuestras queries.
Estas funciones están disponibles a partir del SQL Server 2005.

Nos basaremos en la base de datos AdventureWork y con los siguientes datos:

select FirstName, LastName, TerritoryGroup, SalesQuota FROM Sales.vSalesPerson where SalesQuota is not null




Función row_number()
Esta función nos mostrará un número correlativo según el orden que le especifiquemos.
El orden es obligatorio.
select row_number() over (order by salesquota desc) rowNumber, FirstName, LastName, TerritoryGroup, SalesQuota FROM Sales.vSalesPerson where salesquota is not null



Con esta función hemos conseguido un número de fila correlativo ordenado descendentemente por SalesQuota.

Función RANK()
Si lo que queremos es conocer la posición individual que ocupa (el ranking) en una lista , lo obtenemos de la siguiente forma:
select rank() over (order by salesquota desc) Rank, FirstName, LastName, TerritoryGroup, SalesQuota FROM Sales.vSalesPerson where salesquota is not null






Función DENSE_RANK()
Si lo que queremos es conocer la posición global que ocupa (el ranking) en una lista , lo obtenemos de la siguiente forma:
select dense_rank() over (order by salesquota desc) dense_rank, FirstName, LastName, TerritoryGroup, SalesQuota FROM Sales.vSalesPerson where salesquota is not null



Función NTILE()
Con esta función podemos dividir en grupos de N elementos un conjunto de filas.
select NTILE(4) over (order by salesquota desc) rowNumber, FirstName, LastName, TerritoryGroup, SalesQuota FROM Sales.vSalesPerson where salesquota is not null



En este ejemplo, el conjunto de filas lo hemos dividido en 4 grupos.
En el caso que el grupo no sea divisible, los primeros grupos tendrán más elementos que los últimos.
Es decir, como 14 filas no son divisibles entre 4,
el grupo 1 y 2 tiene 4 elementos, y el grupo 3 y 4 tiene 3 elementos.

Particionando los resultados
Si quisiéramos agrupar estos resultados deberíamos de usar el atributo PartitionBy.
select dense_rank() over (Partition by TerritoryGroup order by salesquota desc) rowNumber, FirstName, LastName, TerritoryGroup, SalesQuota FROM Sales.vSalesPerson where salesquota is not null



En este ejemplo, podemos ver el ranking de SalesQuota por territorio.
PartitionBy también se aplica al resto de funciones de categoría.



Otro ejemplo de Partition; esta vez mostraremos el rango que ocupa cada vendedor por territorio
select rank() over (Partition by TerritoryGroup order by salesYTD desc) rango , FirstName, LastName, TerritoryGroup, salesYTD FROM Sales.vSalesPerson




Por último, y a modo de resumen, muestro un resumen con todas las funciones de las que hemos hablado, para mostrar las diferencias.

select row_number() over (order by salesquota desc) rowNumber,  
rank() over (order by salesquota desc) rank,
dense_rank() over (order by salesquota desc) dense_rank,
NTILE(4) over (order by salesquota desc) ntile,
FirstName, LastName, TerritoryGroup, SalesQuota FROM Sales.vSalesPerson where salesquota is not null



Os dejo un enlace al MSDN donde podéis encontrar más información de estas funciones.
http://msdn.microsoft.com/es-es/library/ms189798.aspx


Saludos.