Inicio‎ > ‎SQL‎ > ‎Lliçons SQL‎ > ‎

T08 Taules temporals

Taules temporals

Ens hem pres la llibertat de considerar taules temporals tant les taules taules temporary en MySQL, com les subconsultes utilitzades com a columna en la select o com a taula en el from.

S’entèn per taula temporal aquella que es crea i utilitza en un context limitat, bé siga una orde concreta de SQL (el cas genèric de les subconsultes) o una sessió o connexió. Les taules TEMPORARY, per exemple, són objectes que desapareixen automàticament quan es tanca la sessió d’usuari. Una subconsulta és accessible només mentres s’executa una determinada orde. En aquesta lliçó només tractarem els casos de subconsultes, les taules TEMPORARY no necessiten més explicació.


Subconsultes com columnes calculades

Una consulta pot utilitzar-se com una columna més de qualsevol consulta, només hi ha que tenir la precaució de donar-li nom. Encara que MySQL no ho necessita realment, pareix convenient utilitzar la paraula reservada AS per a millorar la comprensió de cada part de la consulta. Per exemple, la següent orde genera una taula amb 2 columnes a partir de subconsultes completes:
use tiendaonline;
select
    (select max(pvp) from articulo) as mart,
    (select max(precio) from linped) as mlinped;

En aquests casos s’utilitzen les subconsultes per a realitzar càlculs sobre elles i dins d’una consulta sobre un cert conjunt de files:

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

Subconsultes en el from

A vegades és necessari incloure una subconsulta com una taula més a enllaçar en una altra consulta d’orde superior. Novament, cal donar-li nom a aqueixa taula temporal per a poder fer referència a ella. En aquest cas, aprofitem la consulta anterior per a utilitzar en una altra més complexa:

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 taula


El que en altres sistemes es coneix com a columnes PIVOT (en SQL Server, per exemple) consisteix a construir columnes a partir de la informació continguda en les files. Ací calcularem quantes comandes s’han realitzat en els mesos d’octubre, novembre i desembre de qualsevol any. Abans, comprovarem les dades de què disposem:
select month(fecha) mes, count(*)
from pedido
where month(fecha) in (10,11,12)
group by month(fecha)
order by month(fecha);

El que volem aconseguir és que els mesos es convertisquen en columnes. Per a això:

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;




Complicant-ho un poc, vegem la quantitat de vegades que se sol·licita cada article en cada un d’aquestos mateixos mesos:
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);


Amb la mateixa intenció que abans, convertirem els mesos en columnes fent ús de subconsultes:


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 mateixa consulta però variant lleugerament els orígens de les dades, la qual cosa evita l’ús del distinct. Note’s que ara les subconsultes fan referència a la taula del select extern:

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));

             

Taules TEMPORARY


Aprofitem la consulta anterior per a crear una taula temporal. Recorde’s que aquesta taula desapareixerà del sistema quan tanquem la connexió al servidor. Ací es defineix a partir d’una consulta però aquestes taules es poden crear com qualsevol altra amb l’orde 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));

Consultem la taula acabada de crear per a comprovar el seu contingut

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


No ens hem de preocupar d’eliminar-la ja que ja ho farà el propi sistema.