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>

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>

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.

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.