Tablas temporales

Nos hemos tomado la libertad de considerar tablas temporales tanto las tablas temporary en MariaDB/MySQL, como las subconsultas utilizadas como columna en la select o como tabla en el from.

Se entiende por tabla temporal aquella que se crea y utiliza en un contexto limitado, bien sea una orden concreta de SQL —el caso genérico de las subconsultas— o una sesión o conexión. Las tablas TEMPORARY, por ejemplo, son objetos que desaparecen automáticamente cuando se cierra la sesión de usuario. Una subconsulta es accesible solo mientras se ejecuta una determinada orden. En esta lección solo vamos a tratar los casos de subconsultas, las tablas TEMPORARY no necesitan más explicación.

Subconsultas como columnas calculadas

Una consulta puede utilizarse como una columna más de cualquier consulta, solo hay que tener la precaución de darle nombre. Aunque MariaDB/MySQL no lo necesita realmente, parece conveniente utilizar la palabra reservada AS para mejorar la comprensión de cada parte de la consulta. 

Supongamos dos consultas independientes:

Obviamente, una consulta nada tiene que ver con la otra. Si por cualquier razón quisiéramos obtener estos dos resultados en una única tabla, no necesitamos consultas complejas, simplemente utilizarlas como columnas de una consulta general.

Invertir una tabla

También conocido como pivotar una tabla —del inglés pivot— consiste en construir columnas a partir de la información contenida en las filas. En algunos sistemas hay órdenes disponibles para esta acción en concreto, pero aquí vamos a ver la solución básica a partir de subconsultas. 

Vamos a calcular cuántos pedidos se han realizado en los meses de octubre, noviembre y diciembre de cualquier año. 

Ten en cuenta que esta solución es ineficiente, recorremos la tabla pedido 3 veces. Hay otras forma de calcular lo mismo de forma más eficiente, como veremos más adelante.

Subconsultas en el from

A veces es necesario incluir una subconsulta como una tabla más y enlazarla en otra consulta de orden superior. Nuevamente, hay que darle nombre a esa tabla temporal para poder hacer referencia a ella. En este caso, aprovechamos la consulta anterior para utilizar en otra más compleja:

Expresiones de tabla común

Del inglés common table expressions (CTE), es una construcción introducida en el estándar SQL hace relativamente poco (SQL:1999) y no está soportado por todos los sistemas, aunque los más importantes la han ido incorporándo.

Básicamente, podemos entenderlas como una consulta que obtiene un resultado temporal sobre el que podemos hacer consultas posteriores.

WITH nombreCTE as (select...)
select ... from nombreCTE...

Tras la palabra reservada WITH debemos dar un nombre a la CTE para, a continuación, entre paréntesis, definir la consulta que actuará como resultado o tabla temporal. La siguiente consulta es la que hace uso de de la CTE. 

Volvemos a hacer uso de la consulta anterior, pero haciendo uso de una CTE que llamaremos "calc".

obtendríamos una tabla con 3 filas igualmente, y 3 columnas. IF() hace que cada columna solo tenga un valor distinto de 0. Al aplicar la función MAX() nos conseguimos esa una única fila que deseamos.

Las CTE tienen otros usos más sofisticados como la recursividad, pero aquí simplemente nos quedamos con que es otra forma de simplificar consultas complejas, en base a hacer una primera consulta sobre la que obtener el resultado que realmente buscamos.

Tablas TEMPORARY

También podemos definir tablas temporales como tales, es decir, tablas que se crean en un momento dado por que las necesitemos para hacer consultas posteriores, pero que no se limitan a un único uso. Esa tabla estará disponible como una tabla más y podremos utilizarla cuando queramos. La única limitación es que desaparecerá automáticamente al cerrar nosotros la sesión, al desconectarnos.

Aprovechamos la consulta anterior para crear una tabla temporal. Aquí se define a partir de una consulta pero estas tablas se pueden crear como cualquier otra con la orden CREATE TABLE. Como se trata de la creación de un objeto, debemos estar en una base de datos en la que tengamos los permisos necesarios para hacerlo.

CREATE TEMPORARY TABLE mitabla

  select articulo, month(fecha) mes, count(*) veces 

  from pedido p join linped l on (p.numpedido=l.numpedido)

  where month(fecha) in (10,11,12) and articulo < 'A0031'

  group by articulo,month(fecha);

No nos tenemos que preocupar de eliminarla puesto que ya lo hará el propio sistema cuando se cierre la conexión —o cuando lo considere oportuno—.

Un último ejemplo

Vamos a combinar varias de las técnicas con subconsultas aquí mostradas.

Obtengamos cuántas veces —no nos importa en qué cantidad— se solicita cada artículo en octubre, noviembre o diciembre de cualquier año.

En la CTE está la consulta anteriormente comentada, y se aprovecha en la siguiente consulta para formatear el resultado en columnas por meses. Del resultado anterior vemos que el artículo A0023 se ha pedido una vez en octubre y otra vez en noviembre. Al mostrarlo como columnas obtener (A0023, 1, 1, null), la misma información, pero tabulada por meses. 

Fijémonos en que la  segunda consulta renombra "micte" como "t2" en el from. Digamos que consulta "micte" y obtiene t2.articulo, es decir recorre "micte" para recuperar los códigos de artículo que resultan de esa consulta. 

with micte as (select ...)

select distinct articulo from micte as t2; 

Artículo a artículo, las 3 subconsultas que definen las columnas de cada mes filtran de nuevo "micte" buscando la fila de ese artículo y de un més en concreto. Para esto era renombrar la CTE, para poder enlazar las subconsultas a cada artículo recorrido.

Podemos decir que "micte"  se usa en 4 lugares de la consulta general, pero solo la hemos definido una vez.

Evidentemente, al formatear de esta manera, hay ciertos meses en los que un articulo no se ha pedido, de ahí la aparición de valores nulos. Si no nos gusta el aspecto conseguido, podemos hacer uso de la función IFNULL() o COALESCE(). La primera es propia de MariaDB/MySQL pero no estándar, mientras que la segunda (devuelve el primer valor no nulo de su lista de argumentos) sí forma parte del estándar ANSI.

with micte as (

select articulo, month(fecha) mes, count(*) veces 

from pedido p join linped l on (p.numpedido=l.numpedido)

where month(fecha) in (10,11,12) and articulo < 'A0031'

group by articulo,month(fecha) 

)

select distinct articulo,

  ifnull((select veces from micte where mes = 10 and micte.articulo=t2.articulo),'') as octubre,

  ifnull((select veces from micte where mes = 11 and micte.articulo=t2.articulo),'') as noviembre,

  ifnull((select veces from micte where mes = 12 and micte.articulo=t2.articulo),'') as diciembre

from micte as t2;