PostgreSQL: Window Functions (Funciones Ventana)

Este es el Post #11 del Topic: Guía Práctica de PostgreSQL

SUGERENCIA:
Si has utilizado la base de datos dbadmision en ejercicios previos es mejor volver a crearla.
Descargar Script: DBAdmision.sql

Como venía adelantanto en el post pasado, ahora hablaré un poco sobre las funciones ventana que vienen incorporadas a partir de la versión 8.4 de PostgreSQL.

Las funciones ventana a mi parecer son una de las principales novedades de esta versión, gracias a ello podremos tener más variedad en al momento de realizar consultas y en otros casos podemos  optimizar como se describirá a continuación.

En el post referente a Consultas Enumeradas vimos que era necesario crear una función extra para poder realizar la enumeración de las filas en una consulta, pues hacer esto con las  funciones ventana es mucho más sencillo

“…Cree una función que liste todos los postulantes, la lista debe estar enumerada y ordenada por apellido paterno, materno y nombres.” <Requerimiento> <ver>

Apellidos y Nombres
1 Alanya Padilla Alina Susan
2 Alarcon Castro Gustavo Claudio Andres
3 Alarco Lama Ricardo Rafael
SELECT row_number() OVER (ORDER BY PC.Paterno,PC.Materno,PC.Nombres) AS "N°"
,PC.Paterno || ' ' || PC.Materno || ' ' || PC.Nombres AS "Apellidos y Nombres"
FROM Persona.Contacto PC
INNER JOIN Persona.Postulante PP ON PP.IDContacto=PC.IDContacto

Si pudieron darse cuenta, a partir de la version 8.4 ya se puede hacer uso de la función row_number(), pero nunca de manera independiente, éste necesita de la cláusula OVER para ser ejecutada. Otra de las cosas que nos ahorramos hacer en este ejemplo en comparación con el desarrollado en el post anterior, es que ahora no necesitamos crear una consulta anidada.

Otro ejemplo:

“…Cree una función que liste todos los postulantes de un determinado periodo académico. La lista debe reiniciar la numeración por orden alfabético.” <ver>

Apellidos y Nombres
1 Alanya Padilla Alina Susan
2 Alarcon Castro Gustavo Claudio Andres
1 Baldeón Balvín Olger
2 Baldeón Sanabria Natalia Ivonne
1 Cachuán Cámac Miguel Fernando
2 Cajachagua Chui Jose Arturo
SELECT row_number() OVER (PARTITION by substring(PC.Paterno FROM 1 FOR 1)
ORDER BY PC.Paterno,PC.Materno,PC.Nombres) AS "N°"
,PC.Paterno || ' ' || PC.Materno || ', ' || PC.Nombres AS "Apellidos y Nombres"
FROM Persona.Contacto PC
INNER JOIN Persona.Postulante PP
ON PP.IDContacto=PC.IDContacto

Aquí hacemos lo mismo que en la primera consulta, pero en ésta hacemos la partición por el inicio de cada apellido, por ejemplo: substring(‘ABCDEF’ from 1 for 1), retorna solo A., de esa manera la numeración se volverá a realizar cuando el inicio del apellido sea diferente al anterior.

En conclusión, lo que hace un Window Function es ejecutar la consulta por cada segmento o grupo de filas, esto dá más poder a una consulta en comparación con los que se aplican implícitamente en todo el resultado.

Un ejemplo más:

Listar todos los postulantes que ingresaron con sus respectivos puntajes ordenado descendentemente y acompañado del puntaje promedio obtenido por cada carrera.

Carrera Postulantes Promedio Puntaje
Administración Cañari Rodriguez Jose Antonio 73.065 97
Administración Huamán Huamaní Nidia Anais 73.065 93
Administración Ricapa Quispe Nilton César 73.065 89
Contabilidad Benito Dionisio Melissa Katherine 74.369 98
Contabilidad Salazar Gutiérrez Enrique Paúl 74.369 98
Contabilidad Sulca Palomino Ivan 74.369 98
SELECT AC.Nombre AS "Carrera"
,PC.Paterno || ' ' || PC.Materno || ' ' || PC.Nombres AS "Postulantes"
,round(AVG(PP.Puntaje) OVER (PARTITION BY PP.idcarrera),3) AS "Promedio"
,PP.Puntaje AS "Puntaje"
FROM Persona.Contacto PC
INNER JOIN Persona.Postulante PP ON PP.IDContacto=PC.IDContacto
INNER JOIN Admision.Carrera AC ON PP.IDCarrera=AC.IDCarrera
WHERE PP.Ingreso='1'
ORDER BY AC.idcarrera, PP.puntaje DESC

También existe otro mecanismo para realizar las segmentaciones, utilizando explìcitamente la cláusula WINDOW y referenciando el campo donde se aplicará con la clàusula OVER.

SELECT AC.Nombre AS "Carrera"
,PC.Paterno || ' ' || PC.Materno || ' ' || PC.Nombres AS "Postulantes"
,ROUND(AVG(PP.Puntaje) OVER w ,3)AS "Promedio"
,PP.Puntaje AS "Puntaje"
FROM Persona.Contacto PC
INNER JOIN Persona.Postulante PP ON PP.IDContacto=PC.IDContacto
INNER JOIN Admision.Carrera AC ON PP.IDCarrera=AC.IDCarrera
WHERE PP.Ingreso='1'
WINDOW w AS (PARTITION BY PP.idcarrera)
ORDER BY AC.idcarrera, PP.puntaje DESC

Esta última consulta retornará exactamente lo mismo que en su forma anterior.

4 thoughts on “PostgreSQL: Window Functions (Funciones Ventana)

  1. continuando, esta bueno tu blog y estaria mucho mejor si subieras mas ejemplos, los detallarlos y/o describirlos un poco mas, soy nuevo en esto, por eso requeriria mas, aqui tampoco pusiste archivos rar, muchas gracias

  2. Hola¡
    Permiteme presentarme soy tatiana administradora de un directorio de blogs, visité tu blog y está genial, me encantaría contar con tu blog en mi sitio web y así mis visitas puedan visitarlo tambien.
    Si estas de acuerdo no dudes en escribirme
    Exitos con tu blog.
    Un beso
    tatiana.
    ta.chang@hotmail.com

Deja un comentario