Inicio‎ > ‎SQL‎ > ‎Lecciones SQL‎ > ‎

T08 Tablas temporales


Tablas temporales

Nos hemos tomado la libertad de considerar tablas temporales tanto las tablas temporary en 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 tablasTEMPORARY, 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 MySQL no lo necesita realmente, parece conveniente utilizar la palabra reservada AS para mejorar la comprensión de cada parte de la consulta. Por ejemplo, la siguiente orden genera una tabla con 2 columnas a partir de subconsultas completas:

use tiendaonline;
select 
    (select max(pvp) from articulo) as mart, 
    (select max(importe) from linped) as mlinped;

En estos casos se utilizan las subconsultas para realizar cálculos sobre ellas y dentro de una consulta sobre un cierto conjunto de filas:

select cod,pvp,
       pvp/(select max(pvp) from articulo)*100 as tpcpvp, 
       pvp/(select max(importe) from linped)*100 as tpcimporte
from articulo 
where pvp between 1000 and 1200;  

Subconsultas en el from

A veces es necesario incluir una subconsulta como una tabla más a enlazar 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:

select tv.cod,resolucion,tdt,tpcpvp,tpcimporte
from tv,
  (select cod,pvp,
       pvp/(select max(pvp) from articulo)*100 as tpcpvp, 
       pvp/(select max(importe) from linped)*100 as tpcimporte
   from articulo 
   where pvp between 1000 and 1200) as calc  
where tv.cod=calc.cod;




Invertir una tabla


Lo que en otros sistemas se conoce como columnas PIVOT (en SQL Server, por ejemplo) consiste en construir columnas a partir de la información contenida en las filas. Aquí vamos a calcular cuántos pedidos se han realizado en los meses de octubre, noviembre y diciembre de cualquier año. Antes, vamos a comprobar los datos de los que disponemos:

select month(fecha) mes, count(*) 
from pedido 
where month(fecha) in (10,11,12)
group by month(fecha) 
order by month(fecha);

Lo que queremos conseguir es que los meses se conviertan en columnas. Para ello:

select
    (select count(*) 
     from pedido 
     where month(fecha) =10) as octubre,
    (select count(*) 
     from pedido 
     where month(fecha) =11) as noviembre,
    (select count(*) 
     from pedido 
     where month(fecha) =12) as diciembre;




Complicándolo un poco, veamos la cantidad de veces que se solicita cada artículo en cada uno de esos mismos meses:


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)
group by articulo,month(fecha) 
order by articulo,month(fecha);


Con la misma intención que antes, convertiremos los meses en columnas haciendo uso de subconsultas:


select distinct articulo,
    (select count(*) 
     from pedido p join linped l on (p.numpedido=l.numpedido)
     where month(fecha) =10 and l.articulo=ll.articulo) octubre,
    (select count(*) 
     from pedido p join linped l on (p.numpedido=l.numpedido)
     where month(fecha) =11 and l.articulo=ll.articulo) noviembre,
    (select count(*) 
     from pedido p join linped l on (p.numpedido=l.numpedido)
     where month(fecha) =12 and l.articulo=ll.articulo) diciembre
from linped ll;


La misma consulta pero variando ligeramente los orígenes de los datos, lo que evita el uso del distinct. Nótese que ahora las subconsultas hacen referencia a la tabla del select externo:

select cod as articulo,
  (select count(*) 
   from pedido p join linped l on (p.numpedido=l.numpedido)
   where month(fecha) =10 and l.articulo=cod) octubre,
  (select count(*) 
   from pedido p join linped l on (p.numpedido=l.numpedido)
   where month(fecha) =11 and l.articulo=cod) noviembre,
  (select count(*) 
   from pedido p join linped l on (p.numpedido=l.numpedido)
   where month(fecha) =12 and l.articulo=cod) diciembre
from articulo
where cod in (select articulo 
              from pedido p join linped l on (p.numpedido=l.numpedido) 
              where month(fecha) in (10,11,12));

              

Tablas TEMPORARY

Aprovechamos la consulta anterior para crear una tabla temporal. Recuérdese que esta tabla desaparecerá del sistema en cuanto cerremos la conexión al servidor. Aquí se define a partir de una consulta pero estas tablas se pueden crear como cualquier otra con la orden CREATE TABLE.

create temporary table mitabla
    select cod articulo,
      (select count(*) 
       from pedido p join linped l on (p.numpedido=l.numpedido)
       where month(fecha) =10 and l.articulo=cod) octubre,
      (select count(*) 
       from pedido p join linped l on (p.numpedido=l.numpedido)
       where month(fecha) =11 and l.articulo=cod) noviembre,
      (select count(*) 
       from pedido p join linped l on (p.numpedido=l.numpedido)
       where month(fecha) =12 and l.articulo=cod) diciembre
    from articulo
    where cod in (select articulo 
                  from pedido p join linped l on (p.numpedido=l.numpedido) 
                  where month(fecha) in (10,11,12));


Consultemos la tabla recién creada para comprobar su contenido

select * from mitabla where octubre>0 and noviembre>0;


No nos tenemos que preocupar de eliminarla puesto que ya lo hará el propio sistema.
Comments