En entradas anteriores vimos las Subconsultas que son más semejante a una tabla derivada. La diferencia es que en una tabla derivada es definida entre paréntesis en clausula FROM de la consulta externa y se le debe de especificar un nombre. Es necesario indicar que estas tablas son temporales y son creadas en tiempo de consulta.
Las tablas derivadas son usadas cuando necesitamos hacer consultas sobre otra consulta y que son un poco complejas.
Para ilustrar un poco más veamos un ejemplo, utilizando la base de datos Northwind, supongamos que deseamos saber la cantidad de órdenes que despachan según la posición de los empleados y que cantidad y porcentaje vende cada empleado de manera individual.
Primero, veremos la cantidad de órdenes que vende cada posición
--Cantidad de ventas por posición SELECT Title AS Posicion, COUNT(OD.OrderID) AS CantidadPorPosicion FROM Employees AS E INNER JOIN Orders AS O ON E.EmployeeID = O.EmployeeID INNER JOIN [Order Details] AS OD ON O.OrderID = OD.OrderID GROUP BY Title
Obtenemos el siguiente resultado:
Luego, la cantidad de ventas por empleados, aquí también agregamos la columna Posición (title) para poder hacer la relación con la consulta anterior.
-- Cantidad de ventas por empleado SELECT FirstName + ' '+LastName AS FullName,Title AS Posicion,COUNT(OD.OrderID) AS CantidadPorEmpleado FROM Employees AS E INNER JOIN Orders AS O ON E.EmployeeID = O.EmployeeID INNER JOIN [Order Details] AS OD ON O.OrderID = OD.OrderID GROUP BY FirstName, LastName, Title
Resultado:
Ahora utilizaremos cada consulta anterior como una tabla derivada para poder tener el resultado final. Veamos:
SELECT FullName, VentasPosicion.Posicion, VentasEmpleados.CantidadPorEmpleado, VentasPosicion.CantidadPorPosicion, CAST(100.00 * VentasEmpleados.CantidadPorEmpleado / VentasPosicion.CantidadPorPosicion AS decimal(18,2)) AS PorcientoVentaEmpleado FROM -- Inicio de la tabla derivada VentasPosicion (SELECT Title AS Posicion, COUNT(OD.OrderID) CantidadPorPosicion FROM Employees AS E INNER JOIN Orders AS O ON E.EmployeeID = O.EmployeeID INNER JOIN [Order Details] AS OD ON O.OrderID = OD.OrderID GROUP BY Title) AS VentasPosicion --<--Fin de la tabla derivada VentasPosicion donde indicamos el nombre de la misma INNER JOIN -- Inicio de la tabla derivada VentasEmpleados (SELECT FirstName + ' '+LastName AS FullName,Title AS Posicion,COUNT(OD.OrderID) AS CantidadPorEmpleado FROM Employees AS E INNER JOIN Orders AS O ON E.EmployeeID = O.EmployeeID INNER JOIN [Order Details] AS OD ON O.OrderID = OD.OrderID GROUP BY FirstName, LastName, Title) AS VentasEmpleados --<--Fin de la tabla derivada VentasEmpleados donde indicamos el nombre de la misma ON VentasEmpleados.Posicion = VentasPosicion.Posicion ORDER BY VentasPosicion.Posicion
Resultado final:
En la consulta final vemos que cada tabla derivada está encerrada entre paréntesis y le fue colocado un nombre, lo cual es obligatorio. Se realizó un JOIN entre ambas tablas derivadas y se utilizó un fórmula para calcular el porcentaje de venta de cada empleado con relación a la posición del mismo.
Espero le vean la utilidad a las tablas derivadas y les puedan sacar provecho.
¡Saludos!
Pingback: Expresiones de tabla comunes (CTE) en SQL Server – Team Geek RD
Pingback: Expresiones de tabla comunes (CTE) en SQL Server - Robert Lluberes