Una de las funcionalidades que vienen bien tanto a desarrolladores como a administradores de bases de datos son las CTE’s. Una definición , poco rigurosa, pero muy ilustrativa sería describirlas como vistas temporales en el ámbito de una consulta, es decir, a un query mas o menos complejo se le puede poner un alias y usarlo varias veces. Si te has enfrentado a los dialectos SQL y te ha tocado hacer subqueries complejas por ejemplo para sacar las ventas de este año y las del año pasado, haciéndolo con subconsultas da queries muy largas (la complejidad depende más de lo que se busque) en el que hay repetidas varias veces la misma premisa.
Vamos a ver lo que decimos a través de un ejemplo, vamos a calcular las ventas por año de nuestra base de datos adventure works. Para eso lo primero es tomar la cabecera de ventas y las lineas de venta, en la cabecera tenemos el año, en las lineas el importe, para comenzar haríamos algo como esto
<preclass=”lang:tsql decode:true ” title=”cabecera y lineas de venta”>SELECT * FROM Sales.SalesOrderHeader soh INNER JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID;
Obviamente ahí no hay ningún cálculo, solo hemos puesto el join inicial que nos permite unir tablas y obtener los datos que necesitamos -fecha e importe- en la misma consulta.
El valor por años de las ventas sería algo como esto
|
1 2 3 4 |
SELECT YEAR(OrderDate) Salesby, SUM (lineTotal) Total FROM Sales.SalesOrderHeader soh INNER JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID GROUP BY YEAR(OrderDate) ; |
Ahora vamos a volver la premisa inicial, vamos a obtener en la misma fila las ventas de un año y las del año anterior, para eso hemos de hacer dos subqueries y unirlos
El resultado sin CTES sería algo así
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECT * FROM ( SELECT YEAR(OrderDate) Salesby, SUM(lineTotal) Total FROM Sales.SalesOrderHeader soh INNER JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID GROUP BY YEAR(OrderDate) ) VentasporAño1 FULL JOIN ( SELECT YEAR(OrderDate) Salesby, SUM(lineTotal) Total FROM Sales.SalesOrderHeader soh INNER JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID GROUP BY YEAR(OrderDate) ) VentasporAño2 ON VentasporAño1.Salesby = VentasporAño2.Salesby + 1 ORDER BY 1; |
Si usamos CTE’s la idea es poder ponerle un alias al primero de los conjuntos y volver a usarlo cuantas veces sea necesario, por ejemplo así
|
1 2 3 4 5 6 7 8 9 10 |
WITH VentasPorAño AS (SELECT YEAR(OrderDate) Salesby, SUM(lineTotal) Total FROM Sales.SalesOrderHeader soh INNER JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID GROUP BY YEAR(OrderDate)) SELECT * FROM VentasPorAño vp1 FULL JOIN VentasPorAño vp2 ON vp1.Salesby = vp2.Salesby + 1 ORDER BY 1; |
Mucho mas limpio, pero no solo eso sino que mucho más versátil para peticiones y cambios, por ejemplo si en lugar de agrupar por año queremos año y mes el cambio sería solamente en el query de la CTE no en toda la parte de abajo, con subqueries habría que cambiarlo en las dos y eso es bastante más lioso
Otro ejemplo imaginemos que queremos un año atras y dos años atras…
|
1 2 3 4 5 6 7 8 9 10 11 |
WITH VentasPorAño AS (SELECT YEAR(OrderDate) Salesby, SUM(lineTotal) Total FROM Sales.SalesOrderHeader soh INNER JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID GROUP BY YEAR(OrderDate)) SELECT * FROM VentasPorAño vp1 FULL JOIN VentasPorAño vp2 ON vp1.Salesby = vp2.Salesby + 1 FULL JOIN VentasPorAño vp3 ON vp1.Salesby = vp3.Salesby + 2 ORDER BY 1; |
Aunque solamente esta funcionalidad ya sería más que suficiente para resaltar las cualidades de las CTE’s, aún tienen muchas más, ya que pueden ser Recursivas, y de esa forma nos ayudan a recorrer árboles, o a crear datos, dimensiones, en breve publicaré el siguiente apunte de la serie de cte’s en el que veremos esas funciones recursivas con algunos ejemplos y su utilidad.