viernes, 27 de febrero de 2015

COMMON TABLE EXPRESSION (CTE)


   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?


  1.    Como ya se ha mencionado, para agilizar la lectura y escritura de queries complejas que requieren de subqueries, tablas temporales etc.
  2.    En aquellas ocasiones en la que es necesario referenciar el resultado de una tabla en múltiples ocasiones.
  3.    Para poder agrupar por una columna derivada de subqueries.
  4.    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

         

2 comentarios:

  1. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  2. Hola don Gustavo, seria mucho pedir que porfavor puedas explicar ciclo a ciclo el query recurcivo de la CTE.

    Ya que entiendo el primer ciclo:

    SELECT mensaje = CONVERT(VARCHAR(300),'Amo'), largo = LEN('Amo')
    UNION ALL
    SELECT CONVERT(VARCHAR(300), mensaje+' sqlserver'), LEN(mensaje)

    mensaje largo
    Amo 3
    Amo sqlserver 3

    Pero al momento en que se llama la CTE con sigo misma la UNION de conjunto de la SELECT no recursiva con la SELECT recurciva es en donde me pierdo..

    Gracias

    ResponderEliminar