Amigos,
Desde el año 2005 Microsoft Sql Server ha puesto en nuestras manos una herramienta muy valiosa cuando de simplificar la lectura y escritura de código hablamos.
Se trata de los famosos CTE - "Common Table Expression"
Qué son los CTE ?
En la práctica los CTE son recordset de datos que cuentan con la ventaja de poder ser referenciados dentro de una sentencia select - update - insert - delete tantas veces como sea necesario
Básicamente actúan como vistas y nos permiten prescindir del uso de tablas temporales, cursores, vistas y complejas lógicas (tales como subqueries "poco leíbles")
Los CTE permiten definir uno o varias subqueries con un Alias (el nombre del CTE), y luego utilizar esas subqueries referenciando diréctamente al Alias.
Para qué se usan?
- Como ya se ha mencionado, para agilizar la lectura y escritura de queries complejas que requieren de subqueries, tablas temporales etc.
- En aquellas ocasiones en la que es necesario referenciar el resultado de una tabla en múltiples ocasiones.
- Para poder agrupar por una columna derivada de subqueries.
- Para poder realizar consultas recursivas
Cuál es su syntaxis?
-
La
sintaxis comienza con un WITH seguida del NombredelCTE
- A
continuación pueden detallarse los nombres de las columnas del CTE (opcional)
- Luego
un AS seguido de la SubConsulta entre paréntesis, esto último todo obligatorio
WITH NombredelCTE (columna1, columna2)
AS
(Subconsulta)
Ejemplo 1. Reemplazando una querie tradicional por otra utilizando CTE
Supongamos que tenemos:
- Una tabla llamada Users_Subscriptions con el Teléfono(Ani)y la Fecha de Subscripción de un usuario a X servicio (Date_Begin)
- Una segunda tabla llamada Cdr_Subscriptions con el detalle de las cobranzas hechas a cada ANI de la tabla User_Subscriptions - un registro en esta tabla es una cobranza realizada-
Nuestro jefe nos encarga la tarea de "Hacer un informe que conste de ANI, FechaSubscripción, Cobranzas, para el mes de Febrero de 2015"
Es aquí que podemos resolver nuestra solicitud del modo tradicional (utilizando subqueries) o del modo que les propongo hoy, mucho más sencillo, entendible y escalable en grado de complejidad (utilizando CTE)
A) Resolviendo la requisitoria de la forma tradicional
select
a.ani,
b.date_begin,
a.cobros
from
--
(select ani,
count(*) as cobros from
cdr_subscriptions with (nolock)
where smsdatetime between
'2015-02-01 00:00:00' and
'2015-02-28 23:59:59'
group by
ani) as a
--
INNER JOIN
users_subscriptions as b with (nolock)
on a.ani = b.ani
--
where
date_begin between '2015-02-01 00:00:00' and
'2015-02-28 23:59:59'
B) Resolviendo la requisitoria usando CTE
(entendible y escalable, sumando tantas CTEs como hagan falta y referenciando las mismas en la
query final)
-- Querie
con CTE --
-- Defino al Cte --
WITH A (ANI, COBROS)
AS
(select ani,
count(*) from
cdr_subscriptions with (nolock)
where smsdatetime between
'2015-02-01 00:00:00' and
'2015-02-28 23:59:59'
group by ani)
-- Hago query referenciando al CTE
select A.ani, A.cobros, b.date_begin
from users_subscriptions AS
B
INNER join A on
a.ANI = b.ANI
where
date_begin between '2015-02-01 00:00:00' and
'2015-02-28 23:59:59'
No he querido hacer de este ejemplo un ejemplo complejo, puesto que quiero que se familiaricen y entiendan el funcionamiento de las CTE.
La idéa es que "pierdan el miedo"a utilizar las CTE.
Hace ya 10 años que están disponibles y creanmé aún hay gente que se complica la vida escribiendo y/o interpretando complejísimas queries cuya escencia podría ser alivianada sustantivamente si se utilizacen las CTE.
El objetivo es que, aunque sea, tengan presente la posibilidad de utilizarlas en reemplazo de tablas temporales, vistas, subqueries etc, cuando tengan que encarar trabajos en los que la complejidad de las queries hagan que bien valga la pena el intento de uso de las CTE.
Ejemplo 2. Utilizando Una CTE Recursiva
Una de las grandes utilidades de las CTE está relacionada con la necesidad de utilizar queries recursivas.
Puede ser necesario utilizar una query recursiva cuando queremos mostrar los empleados en un organigrama o una lista de materiales en donde un producto primario tiene uno o varios componentes que, a su vez, tienen subcomponentes o son componentes de otros elementos primarios.
- Un CTE es recursivo
cuando se ejecuta una x cantidad de veces devolviendo subconjunto de datos,
hasta obtener el conjunto de datos completo.
- Una CTE Recursiva se
compone de una o varias CTE combinadas mediante operadores UNION ALL, UNION,
EXCEPT
- La CTE Recursiva finaliza de modo implícito
cuando no se devuelven más filas desde su llamado.
Una vez más voy a utilizar un ejemplo muy sencillo para que se familiaricen con el uso de las CTE, en este caso las recursivas. La idéa es no asustarlos sino invitarlos a que las usen
-----------QUERY
RECURSIVA ----------
WITH MuestraMensaje(Mensaje, Largo)
AS
(
SELECT
Mensaje = CONVERT(varchar(300), 'Amo '),
Largo = LEN('Amo ')
UNION ALL
SELECT
CONVERT (VARCHAR(300), Mensaje + 'Sql Server! '),
LEN(mensaje)
FROM MuestraMensaje
WHERE Largo < 300
)
-- Llamo la
cte recursiva
SELECT mensaje, largo FROM MuestraMensaje
Amigos, espero que haya sido de utilidad este post y como siempre espero sus contactos para resolver dudas. Los saludo cordialmente
