SQL - script - subqueries - Revision history

Carlos at 13:50, 4 May 2009

Carlos — Mon, 04 May 2009 13:50:17 GMT

←Older revision		Revision as of 13:50, 4 May 2009
Line 155:		Line 155:
	* IN con tabla de 1 atributo		* IN con tabla de 1 atributo
	* NOT EXISTS vs NOT IN, queries correlacionados		* NOT EXISTS vs NOT IN, queries correlacionados
		+	* Para usar atributos calculados en el WHERE

Carlos at 13:49, 4 May 2009

Carlos — Mon, 04 May 2009 13:49:36 GMT

←Older revision		Revision as of 13:49, 4 May 2009
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.
		+

Carlos at 13:38, 4 May 2009

Carlos — Mon, 04 May 2009 13:38:09 GMT

←Older revision		Revision as of 13:38, 4 May 2009
Line 25:		Line 25:
	from formaParte		from formaParte
	group by diaFn, horaFn) as duracionPorFuncion) as duracionPorFuncion;		group by diaFn, horaFn) as duracionPorFuncion) as duracionPorFuncion;
		+
		+	Otro ejemplo: promedio de población de las ciudades más pobladas de cada provincia.


Line 46:		Line 48:
	where provincia = 'Cordoba'		where provincia = 'Cordoba'
	and poblacion > (select max(poblacion) from ciudad where provincia = 'San Juan');		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) <br/> 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).


Line 87:		Line 93:
	(select nomCiudad from funcion where nomCiudad is not null);		(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.		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 ===		=== 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.
		+
	Destacar la aparición de queries correlacionados.		Destacar la aparición de queries correlacionados.

Carlos: /* IN / NOT IN */

Carlos — Tue, 28 Apr 2009 02:06:22 GMT

IN / NOT IN

←Older revision		Revision as of 02:06, 28 April 2009
Line 73:		Line 73:
	where year(diaFn) = 2008		where year(diaFn) = 2008
	and nomCiudad not in (select nomCiudad from funcion where year(diaFn) = 2009);		and nomCiudad not in (select nomCiudad from funcion where year(diaFn) = 2009);
		+
		+	----

	Uno parecido: las ciudades donde no se haya hecho ninguna función		Uno parecido: las ciudades donde no se haya hecho ninguna función
Line 85:		Line 87:
	(select nomCiudad from funcion where nomCiudad is not null);		(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.		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.
-

	=== NOT EXISTS ===		=== NOT EXISTS ===

Carlos at 01:57, 28 April 2009

Carlos — Tue, 28 Apr 2009 01:57:12 GMT

←Older revision		Revision as of 01:57, 28 April 2009
Line 85:		Line 85:
	(select nomCiudad from funcion where nomCiudad is not null);		(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.		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.
		+

	=== NOT EXISTS ===		=== NOT EXISTS ===
-		+	Destacar la aparición de queries correlacionados.


Line 94:		Line 95:
	* Artistas que viven en trailers de exactamente 4 ocupantes.		* Artistas que viven en trailers de exactamente 4 ocupantes.
	* 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í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.
		+

-	== Resumen ==	+	== Resumen - a revisar ==
	Repaso de situaciones donde puede ser útil		Repaso de situaciones donde puede ser útil
	* en el FROM		* en el FROM

Carlos: /* IN / NOT IN */

Carlos — Tue, 28 Apr 2009 01:50:20 GMT

IN / NOT IN

←Older revision		Revision as of 01:50, 28 April 2009
Line 66:		Line 66:
	and nomCiudad in (select nomCiudad from funcion where year(diaFn) = 2009);		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.

	=== NOT EXISTS ===		=== NOT EXISTS ===

Carlos at 01:45, 28 April 2009

Carlos — Tue, 28 Apr 2009 01:45:54 GMT

←Older revision		Revision as of 01:45, 28 April 2009
Line 15:		Line 15:
	(select diaFn, horaFn from formaParte where nomActo = 'osos'		(select diaFn, horaFn from formaParte where nomActo = 'osos'
	union		union
-	select diaFn, horaFn from formaParte where nomActo = 'leones')	+	select diaFn, horaFn from formaParte where nomActo = 'leones') ososOLeones
	order by diaFn, horaFn;		order by diaFn, horaFn;

Line 24:		Line 24:
	(select diaFn, horaFn, sum(duracion) duracionFuncion		(select diaFn, horaFn, sum(duracion) duracionFuncion
	from formaParte		from formaParte
-	group by diaFn, horaFn) as duracionPorFuncion);	+	group by diaFn, horaFn) as duracionPorFuncion) as duracionPorFuncion;


		+	== 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');
		+
		+
		+	=== 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
		+	# una intersección: ciudades de funciones 2008 inters. ciudades de funciones 2009; que también se puede expresar como
		+	# 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);
		+
		+
		+	=== NOT EXISTS ===
		+
		+
		+
		+	== 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.
		+	*
		+
		+	== Resumen ==
	Repaso de situaciones donde puede ser útil		Repaso de situaciones donde puede ser útil
	* en el FROM		* en el FROM
	** UNION ordenado		** 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		** cuando quiero hacer un join con una tabla agrupada
	* tabla de 1x1 en comparaciones en el WHERE		* tabla de 1x1 en comparaciones en el WHERE
	* IN con tabla de 1 atributo		* IN con tabla de 1 atributo
	* NOT EXISTS vs NOT IN, queries correlacionados		* NOT EXISTS vs NOT IN, queries correlacionados

Carlos at 01:20, 28 April 2009

Carlos — Tue, 28 Apr 2009 01:20:39 GMT

←Older revision		Revision as of 01:20, 28 April 2009
Line 1:		Line 1:
-	== ~~sólo un punteo~~ por ~~ahora~~ ==	+	== 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')
		+	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);

-	~~Concepto - básicamente es lo que ya saben de AR~~

	Repaso de situaciones donde puede ser útil		Repaso de situaciones donde puede ser útil
Line 9:		Line 33:
	* tabla de 1x1 en comparaciones en el WHERE		* tabla de 1x1 en comparaciones en el WHERE
	* IN con tabla de 1 atributo		* IN con tabla de 1 atributo
-	* NOT EXISTS	+	* NOT EXISTS vs NOT IN, queries correlacionados
-	* ¿ALL / ANY?	+

Carlos at 02:58, 25 April 2009

Carlos — Sat, 25 Apr 2009 02:58:09 GMT

New page

== sólo un punteo por ahora ==

Concepto - básicamente es lo que ya saben de AR

Repaso de situaciones donde puede ser útil
* en el FROM
** UNION ordenado
** 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
* ¿ALL / ANY?