Re: Ayuda sobre consulta!!!!

Flavio:

2015-10-22 14:58 GMT+02:00 Flavio Roche <feroche(at)uci(dot)cu>:
> Les escribo porque tengo la siguiente problemática, tengo una tabla persona
> la cual cuenta con la siguiente estructura....
> CREATE TABLE persona
> (
> pasaporte integer,
> nombre text,
> calificacion integer,
> fecha text,
> acumula boolean
> )
>
> Y pongo un ejemplo de los datos que tengo almacenados en la misma
>
> pasaporte nombre calificacion fecha
> acumula
> 1 Pepe 4 12/10/2015 t
> 2 Jose 5 13/10/2015 f
> 1 Pepe 3 12/11/2015 t
> 3 Ramon 5 14/10/2015 t
> 2 Jose 3 13/11/2015 f

> Necesito hacer una funcion que se le pase por parámetro un rango de fecha, y
> calcule la calificación promedio de las personas en ese intervalo de tiempo
> en caso de que el parámetro acumula tenga valor true y en caso que no
> acumule, la última calificación obtenida..... Ejemplo de la salida deseada
> al llamar ejecutar la funcion..
> Select * from consulta('01/10/2015','25/11/2015')
>
> 1 Pepe 3.5 (acumula)
> 2 Jose 3 (No acumula)
> 3 Ramon 5 (Acumula)

Aqui le veo un problema gordo, tu tabla parece estar desnormalizada y
sin restricciones ( es decir, Jose podria tener pasaportes 2 y 22 , y
pepe podria tener acumula t y f, y Ramon pasaporte y acumula a null.
Esto hace que la funcion sea enormemente compleja. Para reducirlo un
poco te comento sobre una estructura equivalente pero normalizada en
dos tablas, persona ( pasaporte primary key, nombre not null, acumula
not null) y calificaciones ( pasaport foreign key references persona,
calificacion not null, fecha not null).

Otro problema gordo es que metes FECHA como texto, malo, y encima en
DD/MM/YYYY, peor ( los que venimos de la epoca de las tarjetas, y
ademas seguimos usando archivos de texto plano, sabemos que las fechas
es mejor guardarlas como numeros de 8 digitos o textos de 8 o 10
caracteres pero con la forma (A*100+M)*100+D o 'AAAA/MM/DD' para
que el sort natural del campo ordene por fecha automaticamente.
Supondre tambien que puedes corregir eso.

En ese caso con una funcion en cualquier lenguage no es dificil ( no
hay mas que hacer dos select ordenados o con joins y barrer), incluso
con las funciones de ventana creo que se podria hacer en un solo
query.

Para eso usas un truco tipico, que es empezar por calcular las dos
cosas en la tabla de calificaciones, la media es facil,
avg(calificacion*1.0), o avg(calificacion::real) ( otro problema en tu
modelo de datos calificacion es integer pero la media la quieres con
decimales ). el ultimo valor creo que se saca con
last_value(calificacion) over (group by fecha, order by fecha), o algo
asi, no tengo accesible donde probarlo en este momento pero no deberia
ser dificil. Supongamos que tu query es:

select pasaporte,
avg(calificacion::real) as media,
last_value(calificacion::real) over ( group by pasaporte order by
fecha) as ultima
from calificaciones
group by pasaporte

Ya solo tienes que hacer un join con personas para sacar las cosas.
Con un CTE es facil:

with aux as (
select pasaporte,
avg(calificacion::real) as media,
last_value(calificacion::real) over ( group by pasaporte order by
fecha) as ultima
from calificaciones
group by pasaporte
)
select
per.pasaporte,
per.name,
case when per.acumula then media else ultima end,
case when per.acumule then "(acumula)" else "(No acumula)" end
from aux, personas per
where aux.pasaporte = per.pasaporte
order by pasaporte

Esto seria mas o menos si normalizas las tablas y usas fechas. Si no
puedes hacer algo similar con tu tabla original en varios pasos(
usando CTE o select sobre select sobre select), empieza por
seleccionar un to_date del fecha, para tener los ordenes correctos.
Sobre este resultado haz el equivalente al primer query, pero añade
los valores de nombre y pasaporte ( usa max(nombre), max(pasaporte),
p.e., ya que es un query agregado, sobre este ultimo haz el case para
formatear / seleccionar. Una vez que los tengas todos puedes
simplificarlo a un unico query, pero te saldra complicado.

Francisco Olarte.

-
Enviado a la lista de correo pgsql-es-ayuda (pgsql-es-ayuda(at)postgresql(dot)org)
Para cambiar tu suscripcin:
http://www.postgresql.org/mailpref/pgsql-es-ayuda