Window functions

Window Functions

¿Qué son?

Realizan cálculos en algunas tuplas que se encuentran relacionadas con la tupla actual.

¿Para que sirven?

Evitan el uso de self joins y reduce la complejidad alrededor de la analítica, agregaciones y uso de cursores.

El poder de Window functions es increible, yo usualmente trabajo mucho con SQL Server y esta funcion la usaba solamente para numerar grupos, pero ya veo que hay mas posibilidades.

![](

Window Functions
Realizan cálculos que relacionan una tupla con el resto dentro de un mismo scope o partición.

• Evita el uso del self joins
• Reduce la complejidad alrededor de la analítica, agregaciones y cálculos
• Luego de una agregación, la función OVER dicta el scope de la window function, al realizar un PARTITION BY campo
• Si no se especifica un PARTITION BY, la funcion OVER() por default tomará toda la tabla
• También se puede usar ORDER BY campo, esto agrega un campo de granularidad al cálculo, a la vez que agrupa todos los valores iguales dentro de la partición, que ahora se encuentran ordenados

SELECT *
SUM(colegiatura) OVER (PARTITION BY carrera_id ORDER BY colegiatura)
FROM platzi.alumnos;

• Podemos usar funciones de RANK()
• Las Window Function se procesan casi al final de todas las operaciones, por eso para usar estas WF como un campo en WHERE, debemos hacer un subquery

SELECT *
FROM (
SELECT *,
RANK() OVER (PARTITION BY carrera_id ORDER BY colegiatura DESC) AS brand_rank
FROM platzi.alumnos
) AS ranked_colegiaturas_por_carrera
WHERE brand_rank < 3
ORDER BY brand_rank;

Una función de ventana es una variación de una función de agregación. Cuando una función de agregación, como sum()y mean(), toma n entradas y devuelve un solo valor, una función de ventana devuelve n valores. La salida de una función de ventana depende de todos sus valores de entrada, por lo que las funciones de ventana no incluyen funciones que funcionan por elementos, como round(). Las funciones de ventana incluyen variaciones en funciones agregadas, como cumsum()y cummean(), funciones para clasificar y ordenar, como rank(), y funciones para tomar compensaciones, como lead()y lag().

Para SQL Server:

-- PROMEDIO DE CALEGIATURA POR CARRERA_ID
-- Y MOSTRARLO POR CARRERA_ID
-- ORDEN ASCENDENTE POR CARRERA_ID
USE [platzi]
SELECT carrera_id, AVG(colegiatura) AS promedio
FROM platzi.alumnos
GROUP BY carrera_id
GO


-- PROMEDIO DE COLEGIATURA POR CARRERA_ID
-- Y MOSTRARLO EN CADA REGISTRO
-- ORDEN ASCENDENTE POR CARRERA_ID
USE [platzi]
SELECT *, AVG(colegiatura) OVER (PARTITION BY carrera_id) AS promedio
FROM platzi.alumnos
GO


-- SUMA DE COLEGIATURA POR COLEGIATURA
-- Y MOSTRARLO EN CADA REGISTRO
-- ORDEN ASCEDENTE POR COLEGIATURA
USE [platzi]
SELECT *, SUM(colegiatura) OVER (PARTITION BY colegiatura) AS suma
FROM platzi.alumnos
GO


-- SUMA DE COLEGIATURA POR COLEGIATURA
-- Y MOSTRARLO EN CADA REGISTRO
-- Y SUMARLO CONSECUTIVAMENTE
-- ORDEN ASCEDENTE POR COLEGIATURA
USE [platzi]
SELECT *, SUM(colegiatura) OVER (ORDER BY colegiatura ASC) AS suma
FROM platzi.alumnos
GO


-- SUMA DE COLEGIATURA POR CARRERA_ID
-- Y MOSTRARLO EN CADA REGISTRO
-- Y SUMARLO CONSECUTIVAMENTE
-- ORDEN ASCEDENTE POR CARRERA_ID
USE [platzi]
SELECT *, SUM(colegiatura) OVER (ORDER BY carrera_id ASC) AS suma
FROM platzi.alumnos


-- SUMA DE COLEGIATURA POR CARRERA_ID Y COLEGIATURA
-- Y MOSTRARLO EN CADA REGISTRO
-- Y SUMARLO CONSECUTIVAMENTE
-- ORDEN ASCENDENTE POR CARRERA_ID
-- Y ORDEN ASCENDENTE POR COLEGIATURA EN EL GRUPO CARRERA_ID
USE [platzi]
SELECT *, SUM(colegiatura) OVER (PARTITION BY carrera_id ORDER BY colegiatura ASC) AS suma
FROM platzi.alumnos


-- MUESTRA EL ORDEN POR COLEGIATURA
-- DENTRO DE CADA CARRERA_ID
-- ORDEN ASCENDENTE POR CARRERA_ID
USE [platzi]
SELECT *, RANK() OVER (PARTITION BY carrera_id ORDER BY colegiatura DESC) AS ranking
FROM platzi.alumnos
GO



-- LO ANTERIOR
-- DONDE EL RANKING < 3
USE [platzi]
SELECT *
FROM
(
	SELECT *, RANK() OVER (PARTITION BY carrera_id ORDER BY colegiatura DESC) AS ranking
	FROM platzi.alumnos
) AS ranked_colegiaturas_por_carrera
WHERE ranking < 3
GO


-- MUESTRA EL ORDEN POR COLEGIATURA
-- DENTRO DE CADA CARRERA_ID
-- ORDEN ASCENDENTE POR CARRERA_ID Y RANKING
USE [platzi]
SELECT *, RANK() OVER (PARTITION BY carrera_id ORDER BY colegiatura DESC) AS ranking
FROM platzi.alumnos
ORDER BY carrera_id, ranking
GO

Like si vives en el mundo de Oracle y tienes que buscar alternativas a esto 😦

Las 3 colegiaturas más caras con Join y Window Functions

Muestra el top 3 de colegiaturas más caras con el nombre de la carrera traído desde la tabla platzi.carreras:

SELECT *
FROM (
	SELECT DISTINCT c.carrera, a.colegiatura,
		RANK() OVER(PARTITION BY c.carrera ORDER BY colegiatura DESC) AS ranking
	FROM platzi.alumnos AS a
		INNER JOIN platzi.carreras AS c
		ON c.id = a.carrera_id
	GROUP BY c.carrera, a.colegiatura
	ORDER BY ranking) AS ranking_calegiatura_table	
WHERE ranking <= 3;

Genial, con las window functions puedo resolver un problema que tenía en la cabeza hace días.

Pérame tantito, no que por la manera de los ranks no había rank número dos? por qué al final sí hubo rank dos?

Inicio de pg admin

SUM (colegiatura) OVER (PARTITION carrera_id, ORDER BY colegiatura)

Ranking con subqueries

AVG con Window Function

Ranking

Un dato importante al momento de usar Windows Function y se usa la clausula ORDER BY en la misma, es que el order by de la consulta general tiene que ser el mismo que se usa en la windows function, sino se pueden obtener resultados innesperados. Saludos

Misma query pero sin window function es mucho mas compleja:

-- Para hacer esto sin WF primero debo agrupar por carrera_id y calcular el promedio y luego hacer un join

SELECT *
FROM platzi.alumnos AS alum
	INNER JOIN (
				SELECT carrera_id, AVG(colegiatura)
				FROM platzi.alumnos
				GROUP BY carrera_id
				ORDER BY carrera_id ASC) AS prom
	ON alum.carrera_id = prom.carrera_id
ORDER BY alum.carrera_id ASC;

XXVI. WINDOW FUNCTIONS

• Realizan cálculos entre algunas tuplas que se encuentran relacionadas con una tupla actual

Evitan el uso de self joins y reduce la complejidad alrededor de la analítica, agregaciones y uso de cursores