Las CTE o Common Table Expression son expresiones que facilitan la labor de escribir consultas complicadas, permitiendo definir subconsultas previas como tablas que se utilizarán en la consulta. Suena complicado, y realmente a veces lo es: las CTE pueden incluso ser recursivas y hacer consultas que ni te imaginabas que se pudiesen conseguir en una sola consulta (queda pendiente para otro post). Pero conocer y saber utilizar CTE básicas nos puede ayudar a mejorar nuestras consultas de forma muy sencilla. Ese es el caso de este ejemplo.
En la base de datos AdventureWorks existe una tabla con históricos de precios de productos.
select * from Production.ProductCostHistory
order by ProductID asc,StartDate asc; Que da como resultado una tabla como esta:
Queremos hacer una consulta que nos devuelva el primer precio que tuvo asignado cada producto, es decir, para el producto 707 tendría que devolver la prima fila, que se corresponde con el año 2001 y un precio de 12,0278. Lo primero que se nos puede venir a la cabeza será un GROUP BY, pero os adelanto que no va a salir bien :-). Mediante una agrupación podríamos saber la primera fecha para cada productId, pero: ¿y el precio? Tendríamos que hacer un InnerJoin posterior que utilizase este resultado: se complica mucho más si metemos más campos debido a que en el JOIN vamos a tener que igualar todos los campos para asegurarnos de que estamos seleccionando la misma fila.
La solución con CTE quedaría así:
with Ampliada(ProductId,StandardCost,Indice) as
(select ProductID ,StandardCost,
RANK() over(partition by ProductId order by StartDate) Indice
from Production.ProductCostHistory )
select * from Ampliada where Indice=1; Como ves la CTE crea una tabla previa, en este caso llamada «Ampliada» que podemos utilizar en la consulta siguiente (realmente es la misma consulta). Aquí el único truco es el campo índice que está construído a partir de la función Rank().
Sencillo y útil. Muy bueno.
Aunque tampoco es del todo cierto lo que dices a la hora de hacer el join. Bastaría con dos campos, no?.
Ejemplo:
select PCHResume.ProductID, PCHDetail.StandardCost
from
(select ProductID, min(StartDate) FirstStartDate from Production.ProductCostHistory group by ProductID) PCHResume
inner join (select ProductId, StartDate, StandardCost from Production.ProductCostHistory) PCHDetail
on PCHDetail.ProductId = PCHResume.ProductId and PCHDetail.StartDate = PCHResume.FirstStartDate;
También queda elegante, menos, pero funcionaría en sql 2000, como es mi caso 😉
Gracias Jorge por tu opinión.
Sin duda, tu código funcionaría en cualquier versión. Me refería con lo de complicar el join a lo que ocurre si en vez de tener que unir dos campos tuviesemos que unir más. No seleccionar más, si no que la clave de selección de la tabla sean más campos.
Por ejemplo, que estemos escogiendo el primer precio de cada producto en cada almacén y en cada categoría (no en esta tabla). En ese caso la consulta con CTE sería igual pero la del JOIN tendría que llevar todos esos campos.
De todas formas la idea era presentar las CTEs, que hacen cosas que no se podrían hacer de otra forma: CTEs recursivas (pendiente para otro post:-) ).