SQL - script - agrupamiento

From Ibbddunq

Contents

Empezamos con funciones

A las funciones que vimos hasta ahora les alcanza con mirar fila por fila:

  • DAY (=DAYOFMONTH) / MONTH / YEAR
  • operaciones matemáticas
  • IFNULL(valor1,valor que va si el valor1 es null)
  • ... alguna otra que se nos ocurra

Ahora, también tenemos estas funciones 

  COUNT / SUM / AVG / MAX / MIN

que no se pueden evaluar para una fila sola, el MAX es el máximo de todas las filas del resultado, y así siguiendo.

P.ej. 

  -- cantidad de actos y duracion total de una funcion
  select diaFn, horaFn, max(nroOrden), sum(duracion)
  from formaParte
  where diaFn = 20080615 and horaFn = 20;

o 

  -- acto, grado de peligrosidad, cantidad de artistas que lo pueden hacer
  -- menor destreza, para el acto de los leones
  select nomActo, gradoPel, count(nomArt), min(destreza)
  from (acto natural join puedeParticipar)
  where nomActo = "leones";

(cómo obtener el artista de menor destreza ... para eso falta un rato)


Ahora sí, agrupamiento

Motivación

Ahora quiero

  • cantidad de actos y duración total de cada función,
  • cantidad de artistas/menor destreza de cada acto.

Concepto

Para eso tendría que separar (ponele) la tabla formaParte en pedacitos, cada pedacito son las filas de una función.

Dar la visión de "tabla de agrupados", o sea, visión conceptual de tabla donde cada fila representa un grupo de filas de la tabla original. Entonces de cada grupo sí tiene sentido el count, el min, el max, etc..

Destacar la diferencia entre agrupar y seleccionar (filtrar = seleccionar).

Sintaxis y primeros ejemplos

Ahora sí, sintaxis: GROUP BY, funciones que actúan sobre agrupados.

Ejemplos:
1 

  -- cantidad de actos y duracion total de cada funcion
  select diaFn, horaFn, count(nroOrden), sum(duracion)
  from formaParte
  group by diaFn, horaFn
  order by diaFn, horaFn;

2 

  -- cantidad de actos y duracion total de cada funcion que empieza a las 20 horas
  select diaFn, horaFn, count(nroOrden), sum(duracion)
  from formaParte
  where horaFn = 20
  group by diaFn, horaFn
  order by diaFn, horaFn;

acá se ve la diferencia entre contar y sumar

3 

  -- funcion con cantidad de actos de duracion mala ... facil!
  -- ... de aca ...
  select diaFn, horaFn, duracion, dmin, dmax, duracion-dmin, dmax-duracion
  from formaParte
      natural join acto
  where duracion-dmin < 0 or dmax-duracion < 0
  order by diaFn, horaFn, nroOrden;
  -- ... agrupamos ...
  select diaFn, horaFn, duracion, count(nroOrden)
  from formaParte
      natural join acto
  where duracion-dmin < 0 or dmax-duracion < 0
  group by diaFn, horaFn
  order by diaFn, horaFn;

4 - group + join 

  select diaFn, horaFn, nomCiudad, provincia, count(nroOrden), sum(duracion)
  from formaParte
      natural join funcion
      natural join ciudad
  group by diaFn, horaFn
  order by diaFn, horaFn;
  -- order by sum(duracion) desc -- opcion

5
Otros que inventemos, se me ocurren

  • cantidad de artistas y menor/mayor/promedio/dispersión de destreza para cada acto
  • cuánta guita pienso recaudar en cada ciudad / en cada provincia
  • cantidad de ciudades y población total en cada provincia
  • ciudad / cant funciones / publico esperado total / recaudación esperada total / 1ra fecha / ult. fecha
  • (acto, grado pel, cant artistas que lo pueden hacer, grado max pel del más macho) para cada acto
  • (dia, hora, recaudación, cachet total) por función

Para pensar un poco

¿qué es esta consulta? 

  select diaFn, horaFn, count(nroOrden), sum(duracion)
  from formaParte
  order by diaFn, horaFn;

(mira toooodas las filas en formaParte)

¿qué es esta consulta? 

  select diaFn, horaFn, nomActo, count(nroOrden), sum(duracion)
  from formaParte
  group by diaFn, horaFn
  order by diaFn, horaFn;

Esta consulta muestra en MySQL un nomActo cualquiera de cada grupo, lo vamos a considerar como un error. En el SELECT sólo puede haber cosas que tienen el mismo valor para todos los integrantes del grupo.


HAVING

Ejemplo motivador: cantidad de actos y duracion total de cada funcion compuesta por mas de dos actos 

  select diaFn, horaFn, count(nroOrden), sum(duracion)
  from formaParte
  group by diaFn, horaFn
  having count(nroOrden) > 2
  order by diaFn, horaFn;

En el WHERE no vale, porque el WHERE mira filas individuales, no grupos. Para entenderlo, ver ...

el orden conceptual con todo-todo 

  Order(Having(Project(Group(Select(Join)))))

en SQL es 

  ORDER BY ( HAVING ( SELECT ( GROUP BY ( WHERE ( FROM ) ) ) ) )

Implicancias

  1. no puedo usar renombres de atributos en el WHERE, sí en el HAVING y en el ORDER BY
  2. las condiciones del WHERE no pueden mirar grupos, miran filas antes del agrupamiento

Como somos ordenamos, ponemos

  1. las condiciones que corresponden a filas individuales en el WHERE
  2. las condiciones que corresponden a grupos en el HAVING

Cantidad de actos y duracion total de cada funcion compuesta por mas de dos actos, sin tener en cuenta los actos que duran menos de 5 minutos 

  select diaFn, horaFn, count(nroOrden), sum(duracion)
  from formaParte
  where duracion >= 5
  group by diaFn, horaFn
  having count(nroOrden) > 2
  order by diaFn, horaFn;

Acá la condición "la función es a las 20.00" anda en cualquiera de los dos, pero ... 

  select diaFn, horaFn, count(nroOrden), sum(duracion)
  from formaParte
  where horaFn = 20
  group by diaFn, horaFn
  having count(nroOrden) > 2
  order by diaFn, horaFn;

... como somos ordenaditos, lo ponemos en el WHERE porque mira filas individuales.


Un par más de cosas

Otras funciones agregadas 

  select diaFn, horaFn as horita, count(nroOrden),
     sum(duracion), max(duracion), min(duracion), avg(duracion),
     max(duracion) - min(duracion) as dispersion
  from formaParte
  group by diaFn, horaFn
  having dispersion > 5
  order by dispersion desc, diaFn, horaFn;

Al ser group(...(join) ) puedo hacer cuentas sobre valores de tablas relacionadas 

  select diaFn, horaFn,
         sum(duracion), sum(dmin), sum(dmax), 
         max(duracion-dmin), max(dmax-duracion)
  from formaParte
      natural join acto
  group by diaFn, horaFn
  order by diaFn, horaFn;

Mientras joinemos de forma tal que lo que querramos mostrar de lo que agregamos es lo mismo para todas las filas de cada grupo, OK Peeeero ... esto esta mal 

  select diaFn, horaFn, nomCiudad, provincia, dmin, count(nroOrden), sum(duracion)
  from formaParte
      natural join funcion
      natural join ciudad
      natural join acto
  group by diaFn, horaFn
  order by diaFn, horaFn;
Retrieved from "http://editthis.info/ibbddunq/SQL_-_script_-_agrupamiento"
Personal tools