SQL - script - subqueries

From Ibbddunq

(Difference between revisions)
Line 101: Line 101:
... con un NOT IN no podemos, porque fecha y hora son dos campos separados.
... con un NOT IN no podemos, porque fecha y hora son dos campos separados.
-
Destacar la aparición de queries correlacionados.
+
Ahí nos sirve el NOT EXISTS, que recibe una consulta por parámetro, y se verifica si el resultado de la consulta tiene 0 filas.
 +
 
 +
A ver en el ejemplo
 +
  -- funciones con leones pero no clowns
 +
  select diaFn, horaFn
 +
  from formaParte fp
 +
  where fp.nomActo = 'leones'
 +
  and not exists (
 +
    select * from formaParte fp1
 +
    where fp1.nomActo = 'clowns'
 +
    and fp1.diaFn = fp.diaFn and fp1.horaFn = fp.horaFn)
 +
  order by diaFn, horaFn
 +
 
 +
¡oh! dentro del query de adentro puedo referirme al query de afuera. Idea de subqueries correlacionados, ¡lo hacemos sólo si lo necesitamos! (diferencia conceptual de performance entre subqueries correlacionados y no correlacionados).
 +
 
 +
 
 +
=== Referirse a atributos calculados por nombre ===
 +
Quiero las funciones con recaudación estimada > 10000. Fácil:
 +
  select diaFn, horaFn, publicoEsperado * precioEntrada as recaudacionEstimada from funcion
 +
  where recaudacionEstimada > 10000
 +
 
 +
oooops no anda, repasar orden de las operaciones.
 +
 
 +
Si tengo muchas cuentas en la proyección y las quiero usar en la selección, puedo hacer un subquery
 +
  select *
 +
  from (select diaFn, horaFn, publicoEsperado * precioEntrada as recaudacionEstimada from funcion) f1
 +
  where recaudacionEstimada > 10000
 +
 
Line 109: Line 136:
* Idem: actos de las funciones con exactamente cuatro actos.
* Idem: actos de las funciones con exactamente cuatro actos.
* Día y hora de la función más larga.
* Día y hora de la función más larga.
 +
** De Córdoba
 +
** De las que tienen exactamente 3 actos
* Días que se hizo función y en el día siguiente no - acá ver ADDDATE.
* Días que se hizo función y en el día siguiente no - acá ver ADDDATE.
* Días en que se se hizo función y en los 10 días siguientes no.
* Días en que se se hizo función y en los 10 días siguientes no.
* Para cada función: día/hora/acto que la cierra.
* Para cada función: día/hora/acto que la cierra.
 +
* Ciudades por las que no pasó/pasa/pasará el circo, pero sí pasó/id por alguna ciudad de su provincia.
 +

Revision as of 13:49, 4 May 2009

Contents

Concepto

Básicamente es lo que ya saben de AR: puedo hacer jugar el resultado de una consulta en otra, combinando operaciones.

Muchas veces se hace innecesario por la sintaxis básica de la sentencia SELECT de SQL, que permite hacer muchos joins, una selección (que se optimiza, entonces es como si fueran varias) y una proyección todo en una sola sentencia, y además lo del nombre compuesto de atributo evita la necesidad de renombrado.

Por otro lado, aparecen casos en donde la técnica de meter una consulta adentro de otra es muy útil. Veamos algunos.


Subqueries en el FROM

Una fácil: UNION ordenado, p.ej. quiero las funciones donde aparecen osos o leones ordenadas por fecha. 

 select *
 from
   (select diaFn, horaFn from formaParte where nomActo = 'osos'
    union
    select diaFn, horaFn from formaParte where nomActo = 'leones') ososOLeones
 order by diaFn, horaFn;

Si quiero usar funciones agrupadas sobre grupos, entonces mi tabla es la de grupos 

 -- la duracion de la funcion mas larga
 select max(duracionFuncion)
 from
   (select diaFn, horaFn, sum(duracion) duracionFuncion
    from formaParte
    group by diaFn, horaFn) as duracionPorFuncion) as duracionPorFuncion;

Otro ejemplo: promedio de población de las ciudades más pobladas de cada provincia.


Subqueries en el WHERE

Tabla de 1x1 en comparaciones

Si quiero el cachet más alto que tiene un artista no necesito nada de subqueries 

 select max(cachet) from artista;

... si quiero el (o los) artista/s de máximo cachet, ahí sí 

 select nomArt, cachet
 from artista
 where cachet = (select max(cachet) from artista);

(o una opción con subquery en el FROM) 

 select nomArt, cachet
 from artista
   natural join (select max(cachet) cachet from artista) as maxCachet;

Otra: quiero las ciudades de Córdoba con más población que cualquier ciudad de San Juan 

 select nomCiudad
 from ciudad
 where provincia = 'Cordoba'
 and poblacion > (select max(poblacion) from ciudad where provincia = 'San Juan');

Otros ejemplos

  • día y hora de las funciones que hayan tenido un acto de anillos que haya durado más que cualquier acto de leones.
  • (combinando las dos cosas que vimos hasta ahora)
    día, hora y duración total de las funciones que hayan durado más que cualquier función del año 2007 (sirve para motivar vistas).


IN / NOT IN

Con estos simulamos intersecciones y uniones, en algunos casos sencillos pero que cubren un montón.

Vamos con una: ciudades en las que se hicieron funciones en 2008 y en 2009. Haciendo un join moñito sale 

 select distinct f2008.nomCiudad
 from funcion f2008
     join funcion f2009 on f2008.nomCiudad = f2009.nomCiudad
 where year(f2008.diaFn) = 2008 and year(f2009.diaFn) = 2009;

pero también sale con

  1. una intersección: ciudades de funciones 2008 inters. ciudades de funciones 2009; que también se puede expresar como
  2. las ciudades 2008 que estén en el conjunto de ciudades 2009.

De la forma 1 no se puede decir en SQL, de la forma 2 sí. 

 select distinct nomCiudad
 from funcion
 where year(diaFn) = 2008
 and nomCiudad in (select nomCiudad from funcion where year(diaFn) = 2009);

Análogo con la resta, lo vamos a decir así:

  • las ciudades 2008 que no estén en el conjunto de ciudades 2009

¿cómo? bien fácil, en vez de IN, se pone NOT IN 

 select distinct nomCiudad
 from funcion
 where year(diaFn) = 2008
 and nomCiudad not in (select nomCiudad from funcion where year(diaFn) = 2009);

Uno parecido: las ciudades donde no se haya hecho ninguna función 

 select * 
 from ciudad 
 where nomCiudad not in
   (select nomCiudad from funcion);

pero ... ¡ojo! si nomCiudad pudiera ser null hay que hacer la salvedad, porque respecto de las comparaciones null se porta de una forma muy loca 

 select * 
 from ciudad 
 where nomCiudad not in
   (select nomCiudad from funcion where nomCiudad is not null);

digo: si hacen comparaciones y no les da lo que esperan, sepan que tal vez el problema es que está comparando con null, excluyen el caso null y listo.

Otros ejemplos: provincias por las que no va a pasar el circo en 2009, días en que se hicieron funciones pero no actos publicitarios.


NOT EXISTS

Ahora queremos las funciones en las que haya habido leones pero no clowns. ¿Cómo hacemos? ... con un NOT IN no podemos, porque fecha y hora son dos campos separados.

Ahí nos sirve el NOT EXISTS, que recibe una consulta por parámetro, y se verifica si el resultado de la consulta tiene 0 filas.

A ver en el ejemplo 

 -- funciones con leones pero no clowns
 select diaFn, horaFn
 from formaParte fp
 where fp.nomActo = 'leones'
 and not exists (
   select * from formaParte fp1
   where fp1.nomActo = 'clowns'
   and fp1.diaFn = fp.diaFn and fp1.horaFn = fp.horaFn)
 order by diaFn, horaFn

¡oh! dentro del query de adentro puedo referirme al query de afuera. Idea de subqueries correlacionados, ¡lo hacemos sólo si lo necesitamos! (diferencia conceptual de performance entre subqueries correlacionados y no correlacionados).


Referirse a atributos calculados por nombre

Quiero las funciones con recaudación estimada > 10000. Fácil: 

 select diaFn, horaFn, publicoEsperado * precioEntrada as recaudacionEstimada from funcion
 where recaudacionEstimada > 10000

oooops no anda, repasar orden de las operaciones.

Si tengo muchas cuentas en la proyección y las quiero usar en la selección, puedo hacer un subquery 

 select *
 from (select diaFn, horaFn, publicoEsperado * precioEntrada as recaudacionEstimada from funcion) f1
 where recaudacionEstimada > 10000


Para pensar

Algunos queries que pueden iluminar

  • Artistas que viven en trailers de exactamente 4 ocupantes.
  • Idem: actos de las funciones con exactamente cuatro actos.
  • Día y hora de la función más larga.
    • De Córdoba
    • De las que tienen exactamente 3 actos
  • Días que se hizo función y en el día siguiente no - acá ver ADDDATE.
  • Días en que se se hizo función y en los 10 días siguientes no.
  • Para cada función: día/hora/acto que la cierra.
  • Ciudades por las que no pasó/pasa/pasará el circo, pero sí pasó/id por alguna ciudad de su provincia.


Resumen - a revisar

Repaso de situaciones donde puede ser útil

  • en el FROM
    • UNION ordenado
    • JOIN con UNION
    • funciones de agrupación sobre grupos (la función más larga)
    • cuando quiero hacer un join con una tabla agrupada
  • tabla de 1x1 en comparaciones en el WHERE
  • IN con tabla de 1 atributo
  • NOT EXISTS vs NOT IN, queries correlacionados
Retrieved from "http://editthis.info/ibbddunq/SQL_-_script_-_subqueries"
Personal tools