A煤n no tienes acceso a esta clase

Crea una cuenta y contin煤a viendo este curso

Curso Pr谩ctico de SQL

Curso Pr谩ctico de SQL

Israel V谩zquez Morales

Israel V谩zquez Morales

Window functions

27/29
Recursos

Aportes 18

Preguntas 1

Ordenar por:

驴Quieres ver m谩s aportes, preguntas y respuestas de la comunidad? Crea una cuenta o inicia sesi贸n.

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